我有一個如下的數據框：data =[['A','ABC001','18M01',1,3],['A','ABC002','18M01',2,4],['A','ABC001','18M02',3,3],['B','ABC001','18M01',4,3],['B','ABC002','18M02',5,4],['B','ABC002','18M02',6,4]]df = pd.DataFrame(data,columns=['Type','Product','Month','Requirement','Inventory'])df輸入：Type Product Month Requirement InventoryA ABC001 18M01 1 3A ABC002 18M01 2 4A ABC001 18M02 3 3B ABC001 18M01 4 3B ABC002 18M02 5 4B ABC002 18M02 6 4我想做的是將它總結成一個像這樣的數據框輸出：Type Product Values 18M01 18M02A ABC001 Sum of Requirement 1 3A ABC001 Average of Inventory 3 3A ABC002 Sum of Requirement 2 NaNA ABC002 Average of Inventory 4 NaNB ABC001 Sum of Requirement 4 NaNB ABC001 Average of Inventory 3 NaNB ABC002 Sum of Requirement NaN 11B ABC002 Average of Inventory NaN 4我可以很容易地在 pivot excel 中創建它，但是在使用 pandas pivot 時完全不知道。請幫忙
2 回答
守著一只汪
TA貢獻1872條經驗 獲得超4個贊
我想你需要通過匯總sum和mean，通過扁平化多指標列和重塑stack有unstack：
df1 = (df.groupby(['Type','Product','Month'])
.agg({'Requirement': 'sum','Inventory':'mean'})
.rename(columns={'Requirement':'Sum of Requirement',
'Inventory':'Average of Inventory'})
.stack()
.unstack(2)
.reset_index()
.rename(columns={'level_2':'Values'}))
print (df1)
Month Type Product Values 18M01 18M02
0 A ABC001 Sum of Requirement 1.0 3.0
1 A ABC001 Average of Inventory 3.0 3.0
2 A ABC002 Sum of Requirement 2.0 NaN
3 A ABC002 Average of Inventory 4.0 NaN
4 B ABC001 Sum of Requirement 4.0 NaN
5 B ABC001 Average of Inventory 3.0 NaN
6 B ABC002 Sum of Requirement NaN 11.0
7 B ABC002 Average of Inventory NaN 4.0
白板的微信
TA貢獻1883條經驗 獲得超3個贊
pivot_table做事的一種方式——
df1 = df.pivot_table('Requirement', ['Type','Product'], 'Month', aggfunc='sum')
df1['Values'] = 'Sum of Requirement'
df2 = df.pivot_table('Inventory', ['Type','Product'], 'Month', aggfunc='mean')
df2['Values'] = 'Average of Inventory'
df1.append(df2)
輸出
Month 18M01 18M02 Values
Type Product
A ABC001 1.0 3.0 Sum of Requirement
ABC002 2.0 NaN Sum of Requirement
B ABC001 4.0 NaN Sum of Requirement
ABC002 NaN 11.0 Sum of Requirement
A ABC001 3.0 3.0 Average of Inventory
ABC002 4.0 NaN Average of Inventory
B ABC001 3.0 NaN Average of Inventory
ABC002 NaN 4.0 Average of Inventory
你可以扔進reset_index()去讓它變得更好 -
df1.append(df2).reset_index()
Month Type Product 18M01 18M02 Values
0 A ABC001 1.0 3.0 Sum of Requirement
1 A ABC002 2.0 NaN Sum of Requirement
2 B ABC001 4.0 NaN Sum of Requirement
3 B ABC002 NaN 11.0 Sum of Requirement
4 A ABC001 3.0 3.0 Average of Inventory
5 A ABC002 4.0 NaN Average of Inventory
6 B ABC001 3.0 NaN Average of Inventory
7 B ABC002 NaN 4.0 Average of Inventory
添加回答
舉報
0/150
提交
取消