How to partially transpose pandas a dataframe

user_5779017 0
Viewed 843

I have a question similar to this one here. I'd like to partially transpose a pandas dataframe. I got my hands on a dataframe looking similar to the following: 

data = [{"Student" : "john", "Subject" : 'Math', 'Plan_Actual_Delta' : 'Plan' , "2009" : 100, "2010" : 100},
            {"Student" : "john", "Subject" : 'Math', 'Plan_Actual_Delta' : 'Actual' ,"2009" : 80, "2010" : 100}, 
            {"Student" : "john", "Subject" : 'Math' , 'Plan_Actual_Delta' : 'Delta' ,"2009" : -20, "2010" : 0},
            {"Student" : "lisa", "Subject" : 'Math', 'Plan_Actual_Delta' : 'Plan' ,"2009" : 80, "2010" : 100},
            {"Student" : "lisa", "Subject" : 'Math', 'Plan_Actual_Delta' : 'Actual' ,"2009" : 75, "2010" : 100},
            {"Student" : "lisa", "Subject" : 'Math', 'Plan_Actual_Delta' : 'Delta' ,"2009" : -5, "2010" : 0}]

df = pd.DataFrame(data)

It shows students and their planned and actual performance (and the delta) for a given subject in a given year. The years are columns in this example. And whether the row shows the planned, actual or the delta of the students performance is given in the rows.

I'd like to transform it in a way that plan, actual and delta become columns. My goal is hence the following strucuture:

data = [{"Student" : "john", "Subject" : 'Math', 'Year': '2009', 'Plan':100, 'Actual':80, 'Delta': -20},
       {"Student" : "john", "Subject" : 'Math', 'Year': '2010', 'Plan':100, 'Actual':100, 'Delta': 0},
        {"Student" : "lisa", "Subject" : 'Math', 'Year': '2009', 'Plan':80, 'Actual':75, 'Delta': -5},
       {"Student" : "lisa", "Subject" : 'Math', 'Year': '2010', 'Plan':100, 'Actual':100, 'Delta': 0}]

df = pd.DataFrame(data)

How would you do that? Thanks in advance /R

pandas transpose dataframe python
1 Answers

Use DataFrame.set_index with reshape by DataFrame.stack and Series.unstack by third level:

df = (df.set_index(['Student','Subject','Plan_Actual_Delta'])
        .rename_axis('Year', axis=1)
        .stack()
        .unstack(2)
        .reset_index()
        .rename_axis(None, axis=1))
print (df)
  Student Subject  Year  Actual  Delta  Plan
0    john    Math  2009      80    -20   100
1    john    Math  2010     100      0   100
2    lisa    Math  2009      75     -5    80
3    lisa    Math  2010     100      0   100

Another solution, if not working first with possible aggregation with DataFrame.melt and DataFrame.pivot_table:

df = (df.melt(['Student','Subject','Plan_Actual_Delta'], var_name='Year')
        .pivot_table(index=['Student','Subject','Year'], 
                     columns='Plan_Actual_Delta',
                     values='value',
                     aggfunc='mean')
        .reset_index()
        .rename_axis(None, axis=1)
)
print (df)
  Student Subject  Year  Actual  Delta  Plan
0    john    Math  2009      80    -20   100
1    john    Math  2010     100      0   100
2    lisa    Math  2009      75     -5    80
3    lisa    Math  2010     100      0   100
user_29010020
Related