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

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 个难以阅读的条件。

谁能建议如何以更清洁/简单的方式实现我想要的输出？

标签： pythonpandas

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