python - Pandas:如何包含多个数据透视表的所有列和所有索引
问题描述
我正在尝试从以下数据框(df
)为不同的位置创建数据透视表:
地点 | 类别 | 地位 | 价格 |
---|---|---|---|
1 | 家具 | 新的 | 100 美元 |
1 | 家具 | 老的 | 50 美元 |
2 | 办公用品 | 新的 | 200 美元 |
1 | 家具 | 新的 | 100 美元 |
1 | 办公用品 | 新的 | 300 美元 |
1 | 办公用品 | 老的 | 150 美元 |
首先,我过滤了数据框,用代码将位置 1 和 2 分开:
df1 = df[df['Location'] == 1]
df2 = df[df['Location'] == 2]
接下来我使用了标准的 pandas 数据透视表函数:
pd.pivot_table(df1, values='Price', index='Status', columns='Category', aggfunc=np.sum)
pd.pivot_table(df2, values='Price', index='Status', columns='Category', aggfunc=np.sum)
所以我有以下两个数据透视表作为输出:
位置 1:
地位 | 家具 | 办公用品 |
---|---|---|
新的 | 200 美元 | 300 美元 |
老的 | 50 美元 | 150 美元 |
位置 2:
地位 | 办公用品 |
---|---|
新的 | 200 美元 |
但是,我希望位置 2 的数据透视表包含所有可能的类别和状态,如果它们不存在,则为 0。总而言之,我想要位置 2 的以下数据透视表:
位置 2:
地位 | 家具 | 办公用品 |
---|---|---|
新的 | $0 | 200 美元 |
老的 | $0 | $0 |
我已经完成了 pivot_table() 函数的所有选项,但到目前为止还没有找到解决这个问题的方法。
解决方案
您可以在拆分之前创建数据透视表Location
。
- 设置索引
index=['Location', 'Status']
- 传递参数
dropna=False
以允许所有类别显示所有类别,Location
即使Status
为空也是如此。(默认是隐藏空条目)。 - 传递参数
fill_value=0
以将值填充NaN
为0
然后Location
从数据透视表中定位.loc
,如下:
df_out = pd.pivot_table(df,
values='Price',
index=['Location', 'Status'],
columns='Category',
aggfunc=np.sum,
dropna=False,
fill_value=0)
结果:
print(df_out)
Category Furniture Office Supplies
Location Status
1 New 200 300
Old 50 150
2 New 0 200
Old 0 0
然后,要仅获取Location
2 的数据透视表,您可以使用.loc
,如下所示:
df2 = df_out.loc[2]
输出:
print(df2)
Category Furniture Office Supplies
Status
New 0 200
Old 0 0
编辑(用于添加总计和小计)
如果您还想包括Total(for all Location
)和Sub-total(for each Location
),您也可以这样做,如下所示:
- 传递参数
margins=True
并margins_name='Total'
设置Total的边距(全部总计Location
) - 将命令链接
fillna(0, downcast='infer')
在pd.pivot_table
. 这是为了处理毛刺/错误,即使指定了参数pd.pivot_table
,保证金总额仍将显示NaN
为空条目(例如在这种情况下) 。Location=2
Status='Old'
fill_value=0
df_out = pd.pivot_table(df,
values='Price',
index=['Location', 'Status'],
columns='Category',
aggfunc=np.sum,
dropna=False,
fill_value=0,
margins=True,
margins_name='Total'
).fillna(0, downcast='infer')
结果
print(df_out)
Category Furniture Office Supplies Total
Location Status
1 New 200 300 500
Old 50 150 200
2 New 0 200 200
Old 0 0 0
Total 250 650 900
然后,要添加小计(每个Location
),我们进一步使用:
(pd.concat([df_out,
df_out.query('Location != "Total"')
.groupby(level=0).sum()
.assign(Status='Sub-total')
.set_index('Status', append=True)])
.sort_index())
结果:
Category Furniture Office Supplies Total
Location Status
1 New 200 300 500
Old 50 150 200
Sub-total 250 450 700
2 New 0 200 200
Old 0 0 0
Sub-total 0 200 200
Total 250 650 900