将带有小计的 pandas groupby 结果转换为相对值 [英] Transform pandas groupby result with subtotals to relative values

问题描述

我遇到了一个将小计插入到pandas groupby数据框中的好解决方案.但是，现在我想修改结果以显示相对于小计的相对值，而不是绝对值.

I have come accross a nice solution to insert subtotals into a pandas groupby dataframe. However, now I would like to modify the result to show relative values with respect to the subtotals, instead of the absolute values.

这是显示groupby的代码:

This is the code to show the groupby:

import pandas as pd
import numpy as np

df = pd.DataFrame(
    {
        "Category": np.random.choice(["Group A", "Group B"], 50),
        "Product": np.random.choice(["Product 1", "Product 2"], 50),
        "Units_Sold": np.random.randint(1, 100, size=(50)),
        "Date": np.random.choice(
            pd.date_range("1/1/2011", "03/31/2011", freq="D"), 50, replace=False
        ),
    }
)

iList = ["Category", "Product"]

pvt = pd.concat(
    [df.assign(**{x: "" for x in iList[i:]}).groupby(iList).sum() for i in range(1, 3)]
).sort_index()

print(pvt)

结果为

                    Units_Sold
Category Product              
Group A                   1170
         Product 1         434
         Product 2         736
Group B                    980
         Product 1         437
         Product 2         543

我已经尝试过类似的东西

I have tried something along the lines

pvt.transform(lambda x: (round(x / x['Group A', ''],2)*100).astype(int).astype(str)+"%")

但显然这只计算相对于第一行的值.

but obviously this only calculates values relative to the first row.

我要找的是

                    Units_Sold
Category Product              
Group A                   100%
         Product 1         37%
         Product 2         63%
Group B                   100%
         Product 1         45%
         Product 2         55%

非常感谢！

推荐答案

使用 GroupBy.apply 通过第一级 MultiIndex 和 lambda 函数:

Use GroupBy.apply by first level of MultiIndex with lambda function:

f = lambda x: (x / x.iloc[0]).mul(100).round(2).astype(int).astype(str)+"%"
df = pvt.groupby(level=0).apply(f)
print (df)
                   Units_Sold
Category Product             
Group A                  100%
         Product 1        49%
         Product 2        50%
Group B                  100%
         Product 1        52%
         Product 2        47%

或者使用<代码>GroupBy.transform 与 GroupBy.first:

df = (pvt.div(pvt.groupby(level=0).transform('first'))
         .mul(100)
         .round(2)
         .astype(int)
         .astype(str)+"%")
print (df)

                   Units_Sold
Category Product             
Group A                  100%
         Product 1        43%
         Product 2        56%
Group B                  100%
         Product 1        58%
         Product 2        41%

这篇关于将带有小计的 pandas groupby 结果转换为相对值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！