Odtnhj

I would like to pivot a dataframe in Pandas. I'm following the doc here: https://pandas.pydata.org/pandas-docs/stable/reshaping.html

From this dataframe:

 date variable value
0 2000-01-03 A 0.469112
1 2000-01-04 A -0.282863
2 2000-01-05 A -1.509059
3 2000-01-03 B -1.135632
4 2000-01-04 B 1.212112
5 2000-01-05 B -0.173215
6 2000-01-03 C 0.119209
7 2000-01-04 C -1.044236
8 2000-01-05 C -0.861849
9 2000-01-03 D -2.104569
10 2000-01-04 D -0.494929
11 2000-01-05 D 1.071804

Running df.pivot(index='date', columns='variable', values='value')

Will give me this:

variable A B C D
date 
2000-01-03 0.469112 -1.135632 0.119209 -2.104569
2000-01-04 -0.282863 1.212112 -1.044236 -0.494929
2000-01-05 -1.509059 -0.173215 -0.861849 1.071804

I end up with a MultiIndex dataframe. An image might be better to describe what happens:

However, I would like to do this:

All the approaches I could find to flatten the multiindex end up giving me foo and bar on different rows. Could you give me a hand here?

Ok after a few hours of intensive search, here is the simple solution I found:

df.columns = [col[0] + f"_rcol[1]" for col in df.columns]

I believe you need add_prefix for change columns names, then remove column.name by rename_axis and for column from index add reset_index:

df1 = df.pivot(index='date', columns='variable', values='value')

df1 = df1.add_prefix(df1.columns.name + '_').rename_axis(None, axis=1).reset_index()
print (df1)
 date variable_A variable_B variable_C variable_D
0 2000-01-03 0.469112 -1.135632 0.119209 -2.104569
1 2000-01-04 -0.282863 1.212112 -1.044236 -0.494929
2 2000-01-05 -1.509059 -0.173215 -0.861849 1.071804

EDIT:

If need flatten MultiIndex in columns use list comprehension:

mux = pd.MultiIndex.from_product([["A", "B", "C", "D"], ["X", "Y"]])
df = pd.DataFrame([np.arange(8)], columns=mux)
print(df)
 A B C D 
 X Y X Y X Y X Y
0 0 1 2 3 4 5 6 7

df.columns = [f"a_rb" for a, b in df.columns]
print (df)
 A_rX A_rY B_rX B_rY C_rX C_rY D_rX D_rY
0 0 1 2 3 4 5 6 7