Reshape pandas dataframe: Create multiple columns from one column-CodePudding

I would like to reshape the folowing dataframe

into

Could somebody help me with that?

CodePudding user response：

You can try

out = df.pivot(index=['A', 'B'], columns='C', values='D').reset_index()

print(out)

C  A   B  X  Y
0  a  aa  1  5
1  b  bb  6  2
2  c  cc  3  7
3  d  dd  8  4

CodePudding user response：

Have you tried df.pivot() or pd.pivot()? The values in column C will become column headers. After that, flatten the multi-index columns, and rename them.

import pandas as pd

#df = df.pivot(['A', 'B'], columns='C').reset_index()    #this also works
df = pd.pivot(data=df, index=['A', 'B'], columns='C').reset_index()
df.columns = ['A', 'B', 'X', 'Y']
print(df)

Output

   A   B  X  Y
0  a  aa  1  5
1  b  bb  6  2
2  c  cc  3  7
3  d  dd  8  4

Sometimes, there might be repeated records with the same index, then you'd have to use pd.pivot_table() instead. The param aggfunc=np.mean will take the mean of these repeated records, and become type float as you can see from the output.

import pandas as pd
import numpy as np

df = pd.pivot_table(data=df, index=['A', 'B'], columns='C', aggfunc=np.mean).reset_index()
df.columns = ['A', 'B', 'X', 'Y']
print(df)

Output

   A   B    X    Y
0  a  aa  1.0  5.0
1  b  bb  6.0  2.0
2  c  cc  3.0  7.0
3  d  dd  8.0  4.0