使用这些数据 -
d2 = {'Division': ['DIV1', 'DIV2', 'DIV1', 'DIV3', 'DIV2'],'Region': ['DIV1-South', 'DIV2-North', 'DIV1-North', "DIV3-East", "DIV2-South"]
,'MD': ["Susie", 'Martha', "Jane", "Nichole", "Randall"], 'Month': ['JAN', 'JAN', 'FEB', 'MAR', "APR"]}
df2 = pd.DataFrame(d2)
看起来像这样：
Division Region MD Month
0 DIV1 DIV1-South Susie JAN
1 DIV2 DIV2-North Martha JAN
2 DIV1 DIV1-North Jane FEB
3 DIV3 DIV3-East Nichole MAR
4 DIV2 DIV2-South Randall APR
感谢这里的社区，我能够转换这些数据以获得不同月份的总数：使用这行代码
pivoted = df.pivot_table(index=['Division', 'Region', 'NP'], columns='Month', aggfunc=len, fill_value=0)
Month APR FEB JAN MAR
Division Region MD
DIV1 DIV1-North Jane 0 1 0 0
DIV1-South Susie 0 0 1 0
DIV2 DIV2-North Martha 0 0 1 0
DIV2-South Randall 1 0 0 0
DIV3 DIV3-East Nichole 0 0 0 1
所以，这可能是不可能的，但我只在网上找到了一个参考来产生一个包含各个部分小计的数据透视结果。不幸的是，那个例子没有奏效。理想的结果是：
Month APR FEB JAN MAR
Division Region MD
DIV1 DIV1-North Jane 0 1 0 0
DIV1-North SubTotal 0 1 0 0
DIV1-South Susie 0 0 1 0
DIV1-South SubTotal 0 0 1 0
DIV1 TOTAL 0 1 1 0
DIV2 DIV2-North Martha 0 0 1 0
DIV2-North SubTotal 0 0 1 0
DIV2-South Randall 1 0 0 0
DIV2-South SubTotal 1 0 0 0
DIV2 TOTAL 1 0 1 0
DIV3 DIV3-East Nichole 0 0 0 1
DIV3-East SubTotal 0 0 0 1
DIV3 TOTAL 0 0 0 1
这有点令人费解，甚至可能不可能，但由于这在 excel 数据透视表中相当容易，我希望熊猫在某个地方启用了此功能，但我找不到它。（尽管进行了数天的搜索和测试，但这一点一直是正确的）。感谢您提供的任何见解。
您可以创建Division 总计和Region 小计，通过与各层次分组.groupby()和GroupBy.sum()，如下所示：
pivoted2 = pivoted.reset_index()
# Create `Division` Total
df_Div_sum = pivoted2.groupby('Division', as_index=False).sum()
df_Div_sum['Region'] = '_' + df_Div_sum['Division'] + ' Total'
df_Div_sum['MD'] = ''
# Create `Region` SubTotal
df_Reg_sum = pivoted2.groupby(['Division', 'Region'], as_index=False).sum()
df_Reg_sum['MD'] = '_' + df_Reg_sum['Region'] + ' SubTotal'
# Concat results and set index + sort index
df_out = (pd.concat([pivoted2,
df_Reg_sum,
df_Div_sum
])
.set_index(['Division', 'Region', 'MD'])
.sort_index()
)
输入设置
d2 = {'Division': ['DIV1', 'DIV2', 'DIV1', 'DIV3', 'DIV2'],'Region': ['DIV1-South', 'DIV2-North', 'DIV1-North', "DIV3-East", "DIV2-South"]
,'MD': ["Susie", 'Martha', "Jane", "Nichole", "Randall"], 'Month': ['JAN', 'JAN', 'FEB', 'MAR', "APR"]}
df = pd.DataFrame(d2)
pivoted = df.pivot_table(index=['Division', 'Region', 'MD'], columns='Month', aggfunc=len, fill_value=0)
输出
print(df_out)
Month APR FEB JAN MAR
Division Region MD
DIV1 DIV1-North Jane 0 1 0 0
_DIV1-North SubTotal 0 1 0 0
DIV1-South Susie 0 0 1 0
_DIV1-South SubTotal 0 0 1 0
_DIV1 Total 0 1 1 0
DIV2 DIV2-North Martha 0 0 1 0
_DIV2-North SubTotal 0 0 1 0
DIV2-South Randall 1 0 0 0
_DIV2-South SubTotal 1 0 0 0
_DIV2 Total 1 0 1 0
DIV3 DIV3-East Nichole 0 0 0 1
_DIV3-East SubTotal 0 0 0 1
_DIV3 Total 0 0 0 1
由于您的样本数据每个只有一个数据Region，因此我添加了更多测试数据以进行更完整的测试：
输入设置
data = {'Division': ['DIV1', 'DIV1', 'DIV2', 'DIV2', 'DIV1', 'DIV1', 'DIV3', 'DIV3', 'DIV2', 'DIV2', 'DIV2'],
'Region': ['DIV1-South', 'DIV1-South', 'DIV2-North', 'DIV2-North', 'DIV1-North', 'DIV1-North', 'DIV3-East', 'DIV3-East', 'DIV2-South', 'DIV2-South', 'DIV2-South'],
'MD': ['Susie', 'Susie2', 'Martha', 'Martha2', 'Jane', 'Jane2', 'Nichole', 'Nichole2', 'Randall2', 'Randall3', 'Randall'],
'Month': ['JAN', 'FEB', 'JAN', 'MAR', 'FEB', 'APR', 'MAR', 'APR', 'FEB', 'MAR', 'APR']}
df = pd.DataFrame(data)
pivoted = df.pivot_table(index=['Division', 'Region', 'MD'], columns='Month', aggfunc=len, fill_value=0)
print(pivoted)
Month APR FEB JAN MAR
Division Region MD
DIV1 DIV1-North Jane 0 1 0 0
Jane2 1 0 0 0
DIV1-South Susie 0 0 1 0
Susie2 0 1 0 0
DIV2 DIV2-North Martha 0 0 1 0
Martha2 0 0 0 1
DIV2-South Randall 1 0 0 0
Randall2 0 1 0 0
Randall3 0 0 0 1
DIV3 DIV3-East Nichole 0 0 0 1
Nichole2 1 0 0 0
输出
print(df_out)
Month APR FEB JAN MAR
Division Region MD
DIV1 DIV1-North Jane 0 1 0 0
Jane2 1 0 0 0
_DIV1-North SubTotal 1 1 0 0
DIV1-South Susie 0 0 1 0
Susie2 0 1 0 0
_DIV1-South SubTotal 0 1 1 0
_DIV1 Total 1 2 1 0
DIV2 DIV2-North Martha 0 0 1 0
Martha2 0 0 0 1
_DIV2-North SubTotal 0 0 1 1
DIV2-South Randall 1 0 0 0
Randall2 0 1 0 0
Randall3 0 0 0 1
_DIV2-South SubTotal 1 1 0 1
_DIV2 Total 1 1 1 2
DIV3 DIV3-East Nichole 0 0 0 1
Nichole2 1 0 0 0
_DIV3-East SubTotal 1 0 0 1
_DIV3 Total 1 0 0 1
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句