Reputation: 4418
My DataFrame looks like this:
How can I have Budget
for each Division
?
Should look like this:
import pandas as pd
df=pd.DataFrame ({
'TotalAmount':[500,600,780,900],
'Division': ['Commercial Auto', 'Commercial Auto','Surety', 'Worker''s Comp'],
'MonthNumber':[1,2,3,4],
'YearNumber': [2019,2019,2019,2019],
'Budget': [550,570,700,920],
'PostDate': ['2019-01-01', '2019-02-01','2019-03-01','2019-04-01']
})
#print(df)
t = pd.pivot_table(df, values='Budget', index='Division',columns='MonthNumber',fill_value=0).reset_index()
print(t)
Upvotes: 2
Views: 98
Reputation: 30579
df1 = pd.pivot_table(df, values=['TotalAmount','Budget'] , index='Division',columns='MonthNumber',fill_value=0).T.unstack(0).T.reset_index()
df1.columns.name = None
df1 = df1.sort_values(['Division','level_1'], 0, [True,False])
df1.Division = df1.level_1.where(df1.level_1 == 'Budget', df1.Division)
df1 = df1.drop('level_1', 1)
Result:
Division 1 2 3 4
1 Commercial Auto 500 600 0 0
0 Budget 550 570 0 0
3 Surety 0 0 780 0
2 Budget 0 0 700 0
5 Workers Comp 0 0 0 900
4 Budget 0 0 0 920
Upvotes: 3