Skip to main content
 首页 » 编程设计

python之带列的数据透视表 pandas 中的百分比计算

2024年11月24日10langtianya

我有一个数据集,其中包含来自不同供应商、地点、日期和产品的多个销售登记册。 数据集是这样的:

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 

我正在使用以下代码旋转此数据集:

temp_df = pd.pivot_table(df,index=['local','tipo','ordem'], values=['vendas_kg'] , aggfunc=[np.sum], columns=['fabricante'], fill_values=0, margins=True, margins_name= 'Total') 

我得到了这个输出:

                                       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 

我想计算每个 ['ordem'] 的百分比和每个 segment.g 的百分比。顺序,tipo 和本地,像这样:

                                       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 

因此 AREA I DILUIDO 1 的总销售额为 30,ASATP 销售额占其中的 33%,TEPOS 销售额占 66%,而在 AREA I 1 的总销售额中,DILUIDOs 销售额占 50%,依此类推。

我还想比较 ['ordem'] 之间的销售差异,例如segment 和 ['fabricante'] 的百分比增长并像这样存储在新表中:

                               % change in   % change in 
                                 vendas_kg     vendas_kg   % change in  % change in 
fabricante                           ASATP         TEPOS     % segment        Total 
AREA I  DILUIDO 1                      0             0          0                 0                  
                2                     +7%           -6%         0                20 
                3                      0             0          0                 0 
AREA I  CAPSULA 1                      0             0          0                 0  
                2                     +7%           -6%         0                20 
                3                      0             0          0                 0 
AREA II DILUIDO 1                      0             0          0                 0  
                2                    +4.44%        -4.44%    +16.12%             20 
                3                      0             0          0                 0 
AREA II CAPSULA 1                      0             0          0                 0   
                2                    -3.64%        +3.64%          0              5 
                3                      0             0          0                 0 

在过去的 5 天里,我被困在这个问题上,我在 ['fabricante']['tipo'] 和 ['local'] 中有更多的类别,因此它必须适用于每个类别中的两个以上类别。 预先感谢您的帮助,如有疑问,请随时与我联系。

请您参考如下方法:

取百分比:

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

要获取变化,请使用 diff

df_diff_percent = df_percent.groupby(level=[0,1]).diff().fillna(0) 
 
                    sum 
                    vendas_kg 
      fabricante    ASATP   TEPOS 
local   tipo    ordem        
AREA I  CAPSULA 1   0.00    0.00 
                2   0.00    0.00 
DILUIDO         1   0.00    0.00 
                2   6.67    -6.67 
AREA II CAPSULA 1   0.00    0.00 
                2   -41.67  41.67