具有多个条件和计算的 Pandas 数据框

Question

我有以下 df.当满足 2 个条件时，需要将“数值”的值替换为“类型_1 的 4%”的计算:类型 = 类型_2 且数值 > 0

df_in = pd.DataFrame([['Jan-2021','P_1','Type_1','5'],['Jan-2021','P_1','Type_2','10'],['Jan-2021','P_1','Type_3','15'],
              ['Feb-2021','P_1','Type_1','7'],['Feb-2021','P_1','Type_2','0'],['Feb-2021','P_1','Type_3','21'],
                 ['Mar-2021','P_2','Type_1','3'],['Mar-2021','P_2','Type_2','6'],['Mar-2021','P_2','Type_3','9']],
                 columns = ['Month-Yr','Product','Type','Numerical'])

   Month-Yr Product Type    Numerical
0   Jan-2021    P_1 Type_1  5
1   **Jan-2021  P_1 Type_2  10**
2   Jan-2021    P_1 Type_3  15
3   Feb-2021    P_1 Type_1  7
4   **Feb-2021  P_1 Type_2  0**
5   Feb-2021    P_1 Type_3  21
6   Mar-2021    P_2 Type_1  3
7   **Mar-2021  P_2 Type_2  6**
8   Mar-2021    P_2 Type_3  9

预期结果:

     Month-Yr   Product Type    Numerical
0   Jan-2021    P_1 Type_1  5
1   **Jan-2021  P_1 Type_2  0.2**
2   Jan-2021    P_1 Type_3  15
3   Feb-2021    P_1 Type_1  7
4   Feb-2021    P_1 Type_2  0
5   Feb-2021    P_1 Type_3  21
6   Mar-2021    P_2 Type_1  3
7   **Mar-2021  P_2 Type_2  0.12**
8   Mar-2021    P_2 Type_3  9

Answer 1

一种选择是将数据翻转为水平形式，然后再计算条件，最后翻转回垂直形式；这应该比 for 循环更快，因为它是矢量化的并且依赖于 Pandas 方法:

# you can ignore this,
# if you are not fuzzy on the order
from pandas.api.types import CategoricalDtype
categories = CategoricalDtype(categories = df_in['Month-Yr'].unique(), 
                              ordered = True)
temp = (df_in
         # need the column as numbers
        .astype({'Numerical': int, 'Month-Yr':categories})
        .pivot(index=['Month-Yr', 'Product'], 
               columns='Type', 
               values='Numerical')
                # conditional statement here,
                # similar to python's if-else statement                 
        .assign(Type_2 = lambda df: np.where(df.Type_2 > 0,
                                             df.Type_1 * 0.04,
                                             df.Type_2))
        .stack()
        .reset_index(name='NUmerical')
      )


   Month-Yr Product    Type  Numerical
0  Jan-2021     P_1  Type_1       5.00
1  Jan-2021     P_1  Type_2       0.20
2  Jan-2021     P_1  Type_3      15.00
3  Feb-2021     P_1  Type_1       7.00
4  Feb-2021     P_1  Type_2       0.00
5  Feb-2021     P_1  Type_3      21.00
6  Mar-2021     P_2  Type_1       3.00
7  Mar-2021     P_2  Type_2       0.12
8  Mar-2021     P_2  Type_3       9.00