Pandas: subtract one column from another into pivot table
I would like to subtract one column from another into a pivot table. "diff" will be the difference between 2017 and 2016.
raw_data = {'year': [2016,2016,2017,2017],
'area': ['A','B','A','B'],
'age': [10,12,50,52]}
df1 = pd.DataFrame(raw_data, columns = ['year','area','age'])
table=pd.pivot_table(df1,index=['area'],columns=['year'],values['age'],aggfunc='mean')
table['diff']=table['2017']-table['2016']
+1
source to share
1 answer
You need to delete []
in pivot_table
to create MultiIndex
in columns:
table=pd.pivot_table(df1,index='area',columns='year',values='age',aggfunc='mean')
print (table)
year 2016 2017
area
A 10 50
B 12 52
table['diff']=table[2017]-table[2016]
print (table)
year 2016 2017 diff
area
A 10 50 40
B 12 52 40
Another possible solution is droplevel
:
table=pd.pivot_table(df1,index=['area'],columns=['year'],values=['age'],aggfunc='mean')
table.columns = table.columns.droplevel(0)
print (table)
year 2016 2017
area
A 10 50
B 12 52
+1
source to share