根据熊猫数据框中的多行添加列

Question

如何根据对另一个数据框多行中的值的操作在数据框中添加一列？所以这是我最初的数据框示例。我想要输出如下在哪里例子到目前为止，我尝试使用 unique(ord_date、crt_code 和 del_date 组合) 添加一个新数据框，然后尝试计算每一行的分数，但我不知道如何设置 if 条件。df2['score'][(df2['ord_date']==xxxx)&(df2['crt_code']==xxxx)&(df2['del_date']==xxxx)] = if(df['val1'][(df['slb_qty']==2)&(df['ord_date']==xxxx)&(df['crt_code']==xxxx)&(df['del_date']==xxxx)] + df['val1'][(df['slb_qty']==12)&(df['ord_date']==xxxx)&(df['crt_code']==xxxx)&(df['del_date']==xxxx)] >=80 ) then 200加上这将成为一个非常大的语句来检查所有 4 个难以阅读的条件。谁能建议如何以更清洁/简单的方式实现我想要的输出？

Answer 1

1. 你需要收集独特的价值

2. 每个唯一值的总和数量

3. 为他们计算分数

下次将数据作为文本而不是图像发布。

我的代码与描述：

=^..^=

import pandas as pd

from io import StringIO

data = StringIO("""

ord_date crt_code del_date slb_qty val1

01/01/2019 125 10/01/2019 2 38

01/01/2019 125 10/01/2019 4 27

01/01/2019 125 10/01/2019 12 35

01/01/2019 128 10/01/2019 2 45

01/01/2019 128 10/01/2019 4 21

01/01/2019 128 10/01/2019 12 23

01/01/2019 128 10/01/2019 14 24

02/01/2019 125 10/01/2019 2 37

02/01/2019 125 10/01/2019 12 30

02/01/2019 125 10/01/2019 4 29

02/01/2019 128 10/01/2019 14 22

02/01/2019 128 10/01/2019 4 26

02/01/2019 128 10/01/2019 12 21

02/01/2019 128 10/01/2019 2 29

""")

# load data

df = pd.read_csv(data, sep=" ")

# get unique values

df_unique = df.groupby(['ord_date', 'crt_code', 'del_date']).size().reset_index()

# drop last column

df_unique = df_unique.drop([0], axis=1)

# sum quantity values

slb_qty_2_12 = []

slb_qty_4_14 = []

for index, row in df_unique.iterrows():

    # select row range from raw data

    selected_rows = df[(df['ord_date'] == row['ord_date']) & (df['crt_code'] == row['crt_code']) & (df['del_date'] == row['del_date'])]

    # find 2 and 12 qty

    rows_2_12 = selected_rows[(selected_rows['slb_qty'] == 2) | (selected_rows['slb_qty'] == 12)]

    # sum values

    values_sum = rows_2_12['val1'].sum()

    # collect data

    slb_qty_2_12.append(values_sum)

    # find 4 and 14 qty

    rows_4_14 = selected_rows[(selected_rows['slb_qty'] == 4) | (selected_rows['slb_qty'] == 14)]

    # sum values

    values_sum = rows_4_14['val1'].sum()

    # collect data

    slb_qty_4_14.append(values_sum)

# add calculated values to data frame

df_unique['slb_qty_2_12'] = slb_qty_2_12

df_unique['slb_qty_4_14'] = slb_qty_4_14

# calculate score

score = []

for index, row in df_unique.iterrows():

    if row['slb_qty_4_14'] >= 80:

        score.append(300)

    elif 80 > row['slb_qty_4_14'] >= 60:

        score.append(150)

    elif row['slb_qty_2_12'] >= 80:

        score.append(200)

    elif 80 > row['slb_qty_2_12'] >= 60:

        score.append(100)

    else:

        score.append(0)

# drop used columns

df_unique = df_unique.drop(['slb_qty_2_12', 'slb_qty_4_14'], axis=1)

# add score

df_unique['Score'] = score

输出：

     ord_date  crt_code    del_date  Score

0  01/01/2019       125  10/01/2019    100

1  01/01/2019       128  10/01/2019    100

2  02/01/2019       125  10/01/2019    100

3  02/01/2019       128  10/01/2019      0