Python - pivot DataFrame with multiple indexes on columns

Question

I have a simple df like below:

           ID     Provider  Single_Cost    Bundle_ID  Bundle_Cost
0      L_0001         P_01       1075.0          NaN          NaN
1      L_0002         P_02        590.0          NaN          NaN
2      L_0003         P_02       6900.0          NaN          NaN
3      L_0004         P_02        625.0          NaN          NaN
4      L_0005         P_02       5775.0          NaN          NaN
5      L_0006         P_02        495.0          NaN          NaN
6      L_0007         P_02        570.0          NaN          NaN
7      L_0008         P_02       1250.0          NaN          NaN
8      L_0009         P_03       2940.0       P_03_1       1470.0
9      L_0010         P_03       7608.0       P_03_1       7308.0

And I need to transform it to get 2 levels of columns:

Level 1: Provider (the number of providers varies)
Level 2: Single_Cost, Bundle_ID and Bundle_Cost (constant 3 columns for all providers)

     Provider  P_01                                 P_02                                 P_03
           ID  Single_Cost  Bundle_ID  Bundle_Cost  Single_Cost  Bundle_ID  Bundle_Cost  Single_Cost  Bundle_ID  Bundle_Cost
0      L_0001      1075.0         NaN          NaN          NaN       NaN           NaN          NaN       NaN           NaN
1      L_0002         NaN         NaN          NaN        590.0       NaN           NaN          NaN       NaN           NaN
2      L_0003         NaN         NaN          NaN       6900.0       NaN           NaN          NaN       NaN           NaN
3      L_0004         NaN         NaN          NaN        625.0       NaN           NaN          NaN       NaN           NaN
4      L_0005         NaN         NaN          NaN       5775.0       NaN           NaN          NaN       NaN           NaN
5      L_0006         NaN         NaN          NaN        495.0       NaN           NaN          NaN       NaN           NaN
6      L_0007         NaN         NaN          NaN        570.0       NaN           NaN          NaN       NaN           NaN
7      L_0008         NaN         NaN          NaN       1250.0       NaN           NaN          NaN       NaN           NaN
8      L_0009         NaN         NaN          NaN          NaN       NaN           NaN       2940.0    P_03_1        1470.0
9      L_0010         NaN         NaN          NaN          NaN       NaN           NaN       7608.0    P_03_1        7308.0

I think it can be done somehow with merging 2 dataframes after groupby, but I'm not sure how to start. Can you help?

Unfortunately is is only part dupe, mainly because Provider, ID column, so reopened. — jezrael
– jezrael, Commented Jan 24, 2020 at 15:07

jezrael · Accepted Answer · 2020-01-24 15:17:10Z

Use modified another solution with pass 2 columns to set_index, last reset ID column and rename it to correct MultiIndex labels, here (Provider, ID):

df = (df.set_index(['ID','Provider'], append=True)
        .unstack()
        .swaplevel(1, 0, axis=1)
        .sort_index(axis=1)
        .reset_index(level=1)
        .rename_axis((None, None), axis=1)
        .rename(columns={'':'ID'})
        .rename(columns={'ID':'Provider'}, level=0))
print (df)
  Provider        P_01                              P_02            \
        ID Bundle_Cost Bundle_ID Single_Cost Bundle_Cost Bundle_ID   
0   L_0001         NaN       NaN      1075.0         NaN       NaN   
1   L_0002         NaN       NaN         NaN         NaN       NaN   
2   L_0003         NaN       NaN         NaN         NaN       NaN   
3   L_0004         NaN       NaN         NaN         NaN       NaN   
4   L_0005         NaN       NaN         NaN         NaN       NaN   
5   L_0006         NaN       NaN         NaN         NaN       NaN   
6   L_0007         NaN       NaN         NaN         NaN       NaN   
7   L_0008         NaN       NaN         NaN         NaN       NaN   
8   L_0009         NaN       NaN         NaN         NaN       NaN   
9   L_0010         NaN       NaN         NaN         NaN       NaN   

                     P_03                        
  Single_Cost Bundle_Cost Bundle_ID Single_Cost  
0         NaN         NaN       NaN         NaN  
1       590.0         NaN       NaN         NaN  
2      6900.0         NaN       NaN         NaN  
3       625.0         NaN       NaN         NaN  
4      5775.0         NaN       NaN         NaN  
5       495.0         NaN       NaN         NaN  
6       570.0         NaN       NaN         NaN  
7      1250.0         NaN       NaN         NaN  
8         NaN      1470.0    P_03_1      2940.0  
9         NaN      7308.0    P_03_1      7608.0

Another idea is create tuples and assign new columns, here MultiIndex by MultiIndex.from_tuples:

df = (df.set_index(['ID','Provider'], append=True)
        .unstack()
        .swaplevel(1, 0, axis=1)
        .sort_index(axis=1)
        .reset_index(level=1)
        .rename_axis((None, None), axis=1))

mux = [('Provider', 'ID')] + df.columns.tolist()[1:]
df.columns = pd.MultiIndex.from_tuples(mux)

print (df)
  Provider        P_01                              P_02            \
        ID Bundle_Cost Bundle_ID Single_Cost Bundle_Cost Bundle_ID   
0   L_0001         NaN       NaN      1075.0         NaN       NaN   
1   L_0002         NaN       NaN         NaN         NaN       NaN   
2   L_0003         NaN       NaN         NaN         NaN       NaN   
3   L_0004         NaN       NaN         NaN         NaN       NaN   
4   L_0005         NaN       NaN         NaN         NaN       NaN   
5   L_0006         NaN       NaN         NaN         NaN       NaN   
6   L_0007         NaN       NaN         NaN         NaN       NaN   
7   L_0008         NaN       NaN         NaN         NaN       NaN   
8   L_0009         NaN       NaN         NaN         NaN       NaN   
9   L_0010         NaN       NaN         NaN         NaN       NaN   

                     P_03                        
  Single_Cost Bundle_Cost Bundle_ID Single_Cost  
0         NaN         NaN       NaN         NaN  
1       590.0         NaN       NaN         NaN  
2      6900.0         NaN       NaN         NaN  
3       625.0         NaN       NaN         NaN  
4      5775.0         NaN       NaN         NaN  
5       495.0         NaN       NaN         NaN  
6       570.0         NaN       NaN         NaN  
7      1250.0         NaN       NaN         NaN  
8         NaN      1470.0    P_03_1      2940.0  
9         NaN      7308.0    P_03_1      7608.0

Thanks! That helped a lot, I didn't knew .unstack and .swaplevel.

Collectives™ on Stack Overflow

Python - pivot DataFrame with multiple indexes on columns

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related