CODEWITHSUNDEEP
pythonpandas
Z_D
Z_D

Reputation: 817

Pandas: Cast/Pivot Multiple Columns from long to wide?

I am trying to reconfigure my data from long to wide based on multiple columns. I am generally familiar with the cast function (from R) and the pivot function, but neither is getting me the desired result.

Please see below - The Current table is what I have currently: 

d = {'Name': ['Tom','Tom','Tom','Tom','Tom','Tom','Jerry','Jerry','Jerry','Jerry'], 'col2': ['A','B','A','B','A','B','A','B','A','B'],
     'Result':[10,5,9,2,0,1,10,2,4,5],'Change:':[8,8,4,4,3,3,0,0,8,8]}
df = pd.DataFrame(data=d)

Then the Intermediate table pictured is the wide data I desire, where I group by both Name and Date and then make the Result column the values. For each Name-Date, there is only one Change result - so the only column I need to make wide is Type.

The last step is to calculate the r^2 values for each Name-Date for both Types A and B. That is the Output table.

I have tried to use the cast function per below, as well as the pivot function. Any help would be much appreciated!

zz_cast=cast(df,'Type','Result(%)')
zz_pivot=df.pivot(index='Date',columns='Type', values='Result (%)')

enter image description here

Upvotes: 3

Views: 1212

Answers (1)

Guilherme Costa
Guilherme Costa

Reputation: 308

This is what I did to create your intermediate table (wide data):

import numpy as np
import pandas as pd

d = {'Name': ['Tom','Tom','Tom','Tom','Tom','Tom','Jerry','Jerry','Jerry','Jerry'], 
 'Date': ['2018/07/31','2018/07/31','2018/04/30','2018/04/30','2018/01/31','2018/01/31','2018/10/31','2018/10/31','2018/07/31','2018/07/31'], 
 'Type': ['A','B','A','B','A','B','A','B','A','B'],
 'Result':[10,5,9,2,0,1,10,2,4,5],'Change:':[8,8,4,4,3,3,0,0,8,8]}
df = pd.DataFrame(data=d)

df.set_index(['Name','Date','Type']).unstack().reset_index()

Upvotes: 1

Related Questions