Converting columns with date in names to separate rows in Python

Question

I already got answer to this question in R, wondering how this can be implemented in Python.

Let's say we have a pandas DataFrame like this:

import pandas as pd
d = pd.DataFrame({'2019Q1':[1], '2019Q2':[2], '2019Q3':[3]})

which displays like this:

   2019Q1  2019Q2  2019Q3
0       1       2       3

How can I transform it to looks like this:

Year    Quarter    Value
2019    1          1
2019    2          2
2019    3          3

Answer 1

Using DataFrame.stack with DataFrame.pop and Series.str.split:

df = d.stack().reset_index(level=1).rename(columns={0:'Value'})
df[['Year', 'Quarter']] = df.pop('level_1').str.split('Q', expand=True)

   Value  Year Quarter
0      1  2019       1
0      2  2019       2
0      3  2019       3

---

If you care about the order of columns, use reindex:

df = df.reindex(['Year', 'Quarter', 'Value'], axis=1)

   Year Quarter  Value
0  2019       1      1
0  2019       2      2
0  2019       3      3