Could you please help me pivot this pandas data frame in python?

Question

I am trying to figure out how to pivot this table in Python.

example = {"ABC":{'2020-09':1.33,'2020-10':0.75,'2020-11':1.55},
"DEF":{'2020-09':1.22,'2020-10':1.75,'2020-11':2.73}}

df = pd.DataFrame(example)
df

Output of above code

I want it to pivot like this:

example = {"Date":['2020-09','2020-10','2020-11','2020-09','2020-10','2020-11'],
"Col1":['ABC','ABC','ABC','DEF','DEF','DEF'],
"Col2":[1.33,0.75,1.55,1.22,1.75,2.73]}

df = pd.DataFrame(example)
df

Output of above code

I created the two dictionaries to just illustrate the problem here. I have a much larger code that generates the first dataframe.

Could you please help me with transforming the first dataframe into the second one?

Thanks

Regards

Answer 1

The simplest version is probably

df.unstack()

which gives you an outcome with multiindex:

ABC  2020-09    1.33
     2020-10    0.75
     2020-11    1.55
DEF  2020-09    1.22
     2020-10    1.75
     2020-11    2.73
dtype: float64

If you don't want the multiindex, you can do

df.unstack().reset_index()


    level_0     level_1     0
0   ABC     2020-09     1.33
1   ABC     2020-10     0.75
2   ABC     2020-11     1.55
3   DEF     2020-09     1.22
4   DEF     2020-10     1.75
5   DEF     2020-11     2.73

Alternatively,

df.reset_index().melt(id_vars='index', var_name='key', value_name='value')

    index   key     value
0   2020-09     ABC     1.33
1   2020-10     ABC     0.75
2   2020-11     ABC     1.55
3   2020-09     DEF     1.22
4   2020-10     DEF     1.75
5   2020-11     DEF     2.73