Pandas: Restructuring DataFrames
I have the following pandas dataframe and base value:
df = pd.DataFrame(data=[
{'yr': 2010, 'month': 0, 'name': 'Johnny', 'total': 50},,
{'yr': 2010, 'month': 0, 'name': 'Johnny', 'total': 50},
{'yr': 2010, 'month': 1, 'name': 'Johnny', 'total': 105},
{'yr': 2010, 'month': 0, 'name': 'Zack', 'total': 90}
])
baseline_value = 100
I am grouping and aggregating data based on year, month and name. Then I calculate the net amount relative to the base value:
pt = pd.pivot_table(data=df, index=['yr', 'month', 'name'], values='total', aggfunc=np.sum)
pt['net'] = pt['total'] - baseline_value
print(pt)
total net
yr month name
2010 0 Johnny 100 0
Zack 90 -10
1 Johnny 105 5
How can I restructure this DataFrame so that the result looks something like this:
value
yr month name type
2010 0 Johnny Total 100
Net 0
Zack Total 90
Net -10
1 Johnny Total 105
Net 5
source to share
Option 1: Change the shape of the data frame: pt
Use stack
, rename
and to_frame
:
pt.stack().rename('value').to_frame()
Output:
value
yr month name
2010 0 Johnny total 100
net 0
Zack total 90
net -10
1 Johnny total 105
net 5
Option 2 using
set_index
andsum
from the original df
Here's another approach starting from your df source using set_index
and sum
with a parameter level
, then reshape with stack
:
baseline_value = 100
(df.set_index(['yr','month','name'])
.sum(level=[0,1,2])
.eval('net = @baseline_value - total',inplace=False)
.stack()
.to_frame(name='value'))
Output:
value
yr month name
2010 0 Johnny total 100
net 0
Zack total 90
net 10
1 Johnny total 105
net -5
source to share