使用两个数据帧进行百分比计算

Question

我试图用每个多重指标的总销售额来计算销售额的百分比。 我的数据帧是这样的：

local   categoria   fabricante   tipo      consistencia    peso         pacote   ordem vendas_kg
AREA I  SABAO       ASATP        DILUIDO   LIQUIDO         1501 A 2000g PLASTICO 1    10
AREA I  SABAO       TEPOS        DILUIDO   LIQUIDO         1501 A 2000g PLASTICO 1    20
AREA I  SABAO       ASATP        CAPSULA   LIQUIDO         1501 A 2000g PLASTICO 1    20
AREA I  SABAO       TEPOS        CAPSULA   LIQUIDO         1501 A 2000g PLASTICO 1    30
AREA I  SABAO       ASATP        DILUIDO   LIQUIDO         1501 A 2000g PLASTICO 2    20
AREA I  SABAO       TEPOS        DILUIDO   LIQUIDO         1501 A 2000g PLASTICO 2    30
AREA I  SABAO       ASATP        CAPSULA   LIQUIDO         1501 A 2000g PLASTICO 2    20
AREA I  SABAO       TEPOS        CAPSULA   LIQUIDO         1501 A 2000g PLASTICO 2    30
AREA II SABAO       ASATP        DILUIDO   LIQUIDO         1501 A 2000g PLASTICO 1    10
AREA II SABAO       TEPOS        DILUIDO   LIQUIDO         1501 A 2000g PLASTICO 1    15
AREA II SABAO       ASATP        CAPSULA   LIQUIDO         1501 A 2000g PLASTICO 1    25
AREA II SABAO       TEPOS        CAPSULA   LIQUIDO         1501 A 2000g PLASTICO 1    35
AREA II SABAO       ASATP        DILUIDO   LIQUIDO         1501 A 2000g PLASTICO 2    20
AREA II SABAO       TEPOS        DILUIDO   LIQUIDO         1501 A 2000g PLASTICO 2    25
AREA II SABAO       TEPOS        CAPSULA   LIQUIDO         1501 A 2000g PLASTICO 2    20
AREA II SABAO       TEPOS        CAPSULA   LIQUIDO         1501 A 2000g PLASTICO 2    30

所以我计算索引中每个唯一元组的总销售额，并存储为一个总数据帧。我的目标是计算每个['fabricante']市场份额，但目前我的目标是TEPOS` 旋转数据帧后，数据帧会变成这样：

                                       sum           sum
                                 vendas_kg     vendas_kg
fabricante                           ASATP         TEPOS      Total
local          tipo      ordem 
AREA I         DILUIDO     1          10            20         30
                           2          20            30         50
               CAPSULA     1          10            20         30
                           2          20            30         50
AREA II        DILUIDO     1          10            15         25
                           2          20            25         45
               CAPSULA     1          25            35         55
                           2          20            30         50

我用来计算total和用multiindex创建dataframe的代码是：

#creating a sample from all data

a = df.sample(n=50)

#creating a multiindex dataframe

temp_df = pd.pivot_table(a.fillna(value=0), index=['tipo','local','pacote'],columns=['fabricante'], values=['vendas_kg'], fill_value=0, aggfunc=[np.sum])
total = temp_df.sum(level=1, axis=1)

#calculating the marketshare for Tepos

temp_df[('sum','vendas_kg','TEPOS')] = temp_df[('sum','vendas_kg','TEPOS')] / temp_df.sum(level=1, axis=1)

有两件事发生了，如果我使用所有列，所有数据都变成NaN，如果我使用上面的代码。你知道吗

ValueError: cannot join with no level specified and no overlapping names

我的目标是这样：

                                       sum           sum
                                 vendas_kg     vendas_kg
fabricante                           ASATP         TEPOS     % segment  Total
local          tipo      ordem 
AREA I         DILUIDO     1          33%            66%         50%     30
                           2          40%            60%         50%     50
               CAPSULA     1          33%            66%         50%     30
                           2          40%            60%         50%     50
AREA II        DILUIDO     1          40%            60%        31.25%   25
                           2         44.44%         55.56%      47.37%   45
               CAPSULA     1         43.64%         57.36%      53.63%   55
                           2          40%            60%        53.63%   50

有人能帮我吗？ 有关数据和目标的更多信息，请访问：Percentage calculation in pivot table pandas with columns

Answer 1

试试这个：

df_percent = temp_df.apply(lambda x: round(x / x.sum() * 100, 2), axis = 1)

                            sum
                            vendas_kg
              fabricante    ASATP   TEPOS
tipo    local   pacote      
CAPSULA AREA I  PLASTICO    40.00   60.00
        AREA II PLASTICO    22.73   77.27
DILUIDO AREA I  PLASTICO    37.50   62.50
        AREA II PLASTICO    42.86   57.14

要添加total列，请执行以下操作：

df_percent['total'] = total

---

说明

apply相当于一个循环，axis命令告诉apply他将在列中滚动。代码所做的只是取每一行的值，除以整行的和。我实现的方法只在添加其他列之前起作用，比如total