CODEWITHSUNDEEP
pythonpython-3.xpandasdataframe
howard roark
howard roark

Reputation: 638

Pandas Multilevel Dataframe Melt

I have a following pivoted multilevel pandas dataframe structure:

       Example1 Example2 Weight Rank Difference
VC                        X Y   X Y
0        ABC      XYZ     1 2   1 2     0
1        PQR      BCD     3 4   3 4     1

I want to melt the data frame and get the following structure:

 VC Example1 Example2 Weight Rank Difference
 X    ABC       XYZ      1      1      0
 Y    ABC       XYZ      2      2      0
 X    PQR       BCD      3      3      1
 Y    PQR       BCD      4      4      1

Code:

df = df.pivot_table(index = 
['Example1','Example2'],columns='VC', values=
['Weight','Rank']).reset_index()
df['Difference'] = (df['rank']['X']-df['rank']['Y'])

The above code got me to the pivoted frame, the original frame is the output required. So basically, I pivoted a dataframe, now want to melt it to get it back to same structure.

Original Dataframe:

 VC Example1 Example2 Weight Rank
 X    ABC       XYZ      1      1  
 Y    ABC       XYZ      2      2    
 X    PQR       BCD      3      3      
 Y    PQR       BCD      4      4

Any help is appreciated! Thanks!

Upvotes: 0

Views: 531

Answers (1)

Scott Boston
Scott Boston

Reputation: 153460

IIUC, I think you need stack, groupby, ffill:

df2.stack(1).groupby(level=0).ffill().dropna().reset_index().drop('level_0', axis=1)

Or

df.fillna(9999999).stack(1).groupby(level=0).bfill().dropna(‌​).reset_index().drop‌​('level_0', axis=1)

EXAMPLE

df_in

  VC Example1 Example2  Weight  Rank
0  X      ABC      XYZ       1     1
1  Y      ABC      XYZ       2     2
2  X      PQR      BCD       3     3
3  Y      PQR      BCD       4     4

Your code:

df2 = df_in.pivot_table(index = 
['Example1','Example2'],columns='VC', values=
['Weight','Rank']).reset_index()

df2['Difference'] = (df2['Rank']['X']-df2['Rank']['Y'])

df2

   Example1 Example2 Rank    Weight    Difference
VC                      X  Y      X  Y           
0       ABC      XYZ    1  2      1  2         -1
1       PQR      BCD    3  4      3  4         -1

Reshaping:

df2.stack(1).groupby(level=0).ffill().dropna().reset_index().drop('level_0', axis=1)

Output:

  VC  Difference Example1 Example2  Rank  Weight
0  X        -1.0      ABC      XYZ   1.0     1.0
1  Y        -1.0      ABC      XYZ   2.0     2.0
2  X        -1.0      PQR      BCD   3.0     3.0
3  Y        -1.0      PQR      BCD   4.0     4.0

Upvotes: 1

Related Questions