在多重索引组底部添加总计。

huangapple
go评论97阅读模式
英文:

adding total to bottom of multiindex groups

问题

我正在尝试在我的多级索引数据框中为每个分组添加总和

  1.                           计数
  2. 州       车型   状态
  3. 得克萨斯   公民   新           11
  4.                未受损     11
  5.                损坏       10
  6.                报废         5
  7. 弗吉尼亚   公民   新           10
  8.                未受损     20
  9.                损坏       10
  10.                报废         5

我想它看起来像:

  1.                           计数
  2. 州       车型   状态
  3. 得克萨斯   公民   新           11
  4.                损坏       10
  5.                报废         5
  6.                未受损     11
  7.                总计         37
  8. 弗吉尼亚   公民   新           10
  9.                损坏       10
  10.                报废         5
  11.                未受损     20
  12.                总计         45

我尝试过

  1. s = test.groupby(level=[0,1]).sum()
  2. s.index = pd.MultiIndex.from_product(
    展开收缩
    ])
  4. df_out = df_full.append(s).sort_index()

但是它会抛出

> 未实现的错误: MultiIndex没有定义isna

英文:

I am trying to add a sum to my multiindex dataframe by each grouping

  1.                           Count
  2. state    car   status
  3. texas    civic New           11
  4.                undamaged     11
  5.                damaged       10
  6.                totalled       5
  7. virginia civic New           10
  8.                undamaged     20
  9.                damaged       10
  10.                totalled       5

I want it to look like:

  1.                           Count
  2. state    car   status
  3. texas    civic New           11
  4.                damaged       10
  5.                totalled       5
  6.                undamaged     11
  7.                total         37
  8. virginia civic New           10
  9.                damaged       10
  10.                totalled       5
  11.                undamaged     20
  12.                total         45

I have tried

  1. s = test.groupby(level=[0,1]).sum()
  2. s.index = pd.MultiIndex.from_product(
    展开收缩
    ])
  4. df_out = df_full.append(s).sort_index()

but it throws

> NotImplementedError: isna is not defined for MultiIndex

答案1

得分: 0

你的问题是 pd.MultiIndex.from_product 不支持多级索引和列表之间的乘积操作，你可以使用 pd.MultiIndex.from_frame 替代。

  1. s = df.groupby(level=[0,1]).sum()
  2. s.index = pd.MultiIndex.from_frame(s.index.to_frame().assign(status='total'))
  3. out = df.append(s).sort_index()
  1. print(out)
  3.                           Count
  4. state    car   status
  5. texas    civic New           11
  6.                damaged       10
  7.                total         37
  8.                totalled       5
  9.                undamaged     11
  10. virginia civic New           10
  11.                damaged       10
  12.                total         45
  13.                totalled       5
  14.                undamaged     20

然而，.sort_index() 会改变索引顺序，你可以尝试以下方式代替：

  1. df_ = df['Count'].unstack()
  2. df_['total'] = df_.sum(axis=1)
  3. df_ = df_.stack().to_frame('Count')
  4. # 或者在一行中完成
  5. df_ = (df['Count'].unstack()
  6.        .pipe(lambda d: d.assign(total=d.sum(axis=1)))
  7.        .stack().to_frame('Count'))
  1. print(df_)
  3.                           Count
  4. state    car   status
  5. texas    civic New           11
  6.                damaged       10
  7.                totalled       5
  8.                undamaged     11
  9.                total         37
  10. virginia civic New           10
  11.                damaged       10
  12.                totalled       5
  13.                undamaged     20
  14.                total         45
英文:

You problem is that pd.MultiIndex.from_product doesn't support product between multindex and list, instead you can use pd.MultiIndex.from_frame

  1. s = df.groupby(level=[0,1]).sum()
  2. s.index = pd.MultiIndex.from_frame(s.index.to_frame().assign(status='total'))
  3. out = df.append(s).sort_index()
  1. print(out)
  3.                           Count
  4. state    car   status
  5. texas    civic New           11
  6.                damaged       10
  7.                total         37
  8.                totalled       5
  9.                undamaged     11
  10. virginia civic New           10
  11.                damaged       10
  12.                total         45
  13.                totalled       5
  14.                undamaged     20

However, .sort_index() will change the index order, you can try following instead

  1. df_ = df['Count'].unstack()
  2. df_['total'] = df_.sum(axis=1)
  3. df_ = df_.stack().to_frame('Count')
  4. # or in one line
  5. df_ = (df['Count'].unstack()
  6.        .pipe(lambda d: d.assign(total=d.sum(axis=1)))
  7.        .stack().to_frame('Count'))
  1. print(df_)
  3.                           Count
  4. state    car   status
  5. texas    civic New           11
  6.                damaged       10
  7.                totalled       5
  8.                undamaged     11
  9.                total         37
  10. virginia civic New           10
  11.                damaged       10
  12.                totalled       5
  13.                undamaged     20
  14.                total         45

答案2

得分: 0

An easy way I’ve implemented this in my workflow is to use the Sidetables package. Link

You can use it like: test.groupby(level=[0,1]).sum().stb.subtotal(sub_level=2) will accomplish what you’re looking for.

英文:

An easy way I’ve implemented this in my workflow is to use the Sidetables package. Link

You can use it like: test.groupby(level=[0,1]).sum().stb.subtotal(sub_level=2) will accomplish what you’re looking for.

huangapple
  • 本文由 发表于 2023年3月31日 21:55:59
  • 转载请务必保留本文链接：https://go.coder-hub.com/75899360.html
  • indexing
  • pandas
  • python
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定