Ndtyjky

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"_r{col[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}_r{b}" 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