How To Dcast In Pandas With More Than One Columns For Columns Argument

I have the following dataframe import pandas as pd df = pd.DataFrame({'id':[1,2,3,4,5,6], 'id_2':[6,5,4,3,2,1], 'col_1':['A','A','A','B','B','B'], 'col_2':['X','Z','X','Z','X','Z']

Solution 1:

You need remove top level value from Multiindex - by Index.droplevel or with list comprehension:

print (df.columns)
MultiIndex(levels=[['value'], ['A', 'B'], ['X', 'Z']],
           codes=[[0, 0, 0, 0], [0, 0, 1, 1], [0, 1, 0, 1]],
           names=[None, 'col_1', 'col_2'])

df.columns = df.columns.droplevel(0).map('_'.join)

Or:

df.columns = [f'{b}_{c}' for a,b,c in df.columns]

---

df = df.reset_index()
print (df)

   id  id_2   A_X   A_Z   B_X   B_Z
01610.0NaNNaNNaN125NaN20.0NaNNaN23430.0NaNNaNNaN343NaNNaNNaN40.0452NaNNaN50.0NaN561NaNNaNNaN60.0

Another solution is specify value parameter in pivot_table:

df= df.pivot_table(index=['id','id_2'], columns=['col_1', 'col_2'], values='value')

print (df.columns)
MultiIndex(levels=[['A', 'B'], ['X', 'Z']],
           codes=[[0, 0, 1, 1], [0, 1, 0, 1]],
           names=['col_1', 'col_2'])

df.columns = df.columns.map('_'.join)
df = df.reset_index()
print (df)

   id  id_2   A_X   A_Z   B_X   B_Z
01610.0   NaN   NaN   NaN
125   NaN  20.0   NaN   NaN
23430.0   NaN   NaN   NaN
343   NaN   NaN   NaN  40.0452   NaN   NaN  50.0   NaN
561   NaN   NaN   NaN  60.0

Solution 2:

df2 = (df.pivot_table(index=['id','id_2'], columns=['col_1', 'col_2'],
                         values='value')
            .reset_index()
        )

Output:

    id  id_2    A   B
                X   Z   X   Z
01610.0NaNNaNNaN125NaN20.0NaNNaN23430.0NaNNaNNaN343NaNNaNNaN40.0452NaNNaN50.0NaN561NaNNaNNaN60.0