Pandas列，每个值都取决于另一个数据框查询

Question

Pandas列，每个值都取决于另一个数据框查询

pythonpython-3.xpandasdataframeaggregate

3

我面临一个复杂的问题。我有一个第一个数据框，其中我有客户（请注意，ClientID不是唯一的，您可以将相同的ClientID与不同的TestDate相关联）：

df1 ：

ClientID  TestDate
1A        2019-12-24
1B        2019-08-26
1B        2020-01-12

我有另一个数据框 "operations"，其中包含日期和涉及的客户信息。

df2：

LineNumber  ClientID  Date          Amount
1           1A        2020-01-12    50
2           1A        2019-09-24    15
3           1A        2019-12-25    20
4           1A        2018-12-30    30
5           1B        2018-12-30    60
6           1B        2019-12-12    40

我想要的是在df1中添加一列，其中包含平均金额和行数，但仅考虑Date < TestDate的df2行。

例如，对于客户1A，我只会选择LineNumber 2和4（因为第1行和第3行的日期晚于TestDate），然后得到df1的以下输出：

ClientID  TestDate      NumberOp  MeanOp
1A        2019-12-24    2         22.5
1B        2019-08-26    1         60
1B        2020-01-12    2         50

注意：由于第一行的 TestDate 是 2019-08-26，因此只看到一次操作（LineNumber 6 的操作是在 2019-12-12 完成的，所以在测试日期之后执行，我不考虑它）。

我已经有了一个代码来实现，但我必须在我的 df1 上使用 iterrows，这需要很长时间： 当前的代码（能用但很耗时）：

for index, row in df1.iterrows():
    id = row['ClientID']
    date = row['TestDate']
    df2_known = df2.loc[df2['ClientID'] == id]
    df2_known = df2_known.loc[df2_known['Date'] < date]
    df1.loc[index, 'NumberOp'] = df2_known.shape[0]
    df1.loc[index, 'MeanOp'] = df2_known['Amount'].mean()

我想使用聚合操作和诸如mean和count的命令，但是每行都必须按日期进行过滤的事实是一个我无法解决的巨大问题。非常感谢您的帮助。

编辑：剩余问题：

答案的编辑中给出的解决方案（“如果您要保留df2中缺失的匹配键”）与我的问题不相符。

事实上，如果在df2中没有可用于计算平均值和计数的操作，我想避免丢失df1的等价行。我将用一个示例说明问题：

df = df2.merge(df1, on=['ClientID'], how='right')
print(df[df['ClientID'] == '5C'])

Output :
ClientID  TestDate    Date          Amount
5C        2019-12-12  2020-01-12    50

如果我按照答案中给出的groupby和transform的方法执行，那么我的输出将不会有任何一行满足条件 CliendID == '5C'，因为Date < TestDate和Date is null这两个条件永远不会成立，所以当我执行df = df[(df['Date']<df['TestDate']) | (df['Date'].isnull())]时，这行数据就会被丢失。我希望在最终的输出结果中能够包含CliendID =='5C'，如下所示：

ClientID  TestDate      NumberOp  MeanOp
5C        2019-12-12    0         NaN

- Adept

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Ehsan · Accepted Answer

您可以合并和转换：

df = df2.merge(df1, on=['ClientID'])
#filter based on condition
df = df[df['Date']<df['TestDate']]
#get the mean and count into new columns
df['MeanOp'] = df.groupby(['ClientID'])['Amount'].transform('mean')
df['NumberOp'] = df.groupby(['ClientID'])['Amount'].transform('count')
#drop duplicates and irrelevant columns
df = df.drop(['Amount','Date','LineNumber'],1).drop_duplicates()

输出：

  ClientID    TestDate  MeanOp  NumberOp
1       1A  2019-12-24    22.5         2
4       1B  2019-08-26    70.0         1

编辑：如果您想保留df2中缺失匹配键：

df = df2.merge(df1, on=['ClientID'], how='right')
df = df[(df['Date']<df['TestDate']) | (df['Date'].isnull())]
df['MeanOp'] = df.groupby(['ClientID'])['Amount'].transform('mean')
df['NumberOp'] = df.groupby(['ClientID'])['Amount'].transform('count')
df = df.drop(['Amount','Date','LineNumber'],1).drop_duplicates()

例子：

df1:

  ClientID    TestDate
0       1A  2019-12-24
1       1B  2019-08-26
2       1C  2019-08-26

output:

  ClientID    TestDate  MeanOp  NumberOp
1       1A  2019-12-24    22.5         2
4       1B  2019-08-26    70.0         1
5       1C  2019-08-26     NaN         0

更新：根据对帖子的编辑，如果您想按(客户端ID、测试日期)进行分组：

df = df2.merge(df1, on=['ClientID'], how='right')
df = df[(df['Date']<df['TestDate']) | (df['Date'].isnull())]
df['MeanOp'] = df.groupby(['ClientID','TestDate'])['Amount'].transform('mean')
df['NumberOp'] = df.groupby(['ClientID','TestDate'])['Amount'].transform('count')
df = df.drop(['Amount','Date','LineNumber'],1).drop_duplicates()

输出：

df1
  ClientID    TestDate
0       1A  2019-12-24
1       1B  2019-08-26
2       1B  2020-01-12
3       1C  2019-08-26

df2
   LineNumber ClientID        Date  Amount
0           1       1A  2020-01-12      50
1           2       1A  2019-09-24      15
2           3       1A  2019-12-25      20
3           4       1A  2018-12-30      30
4           5       1B  2018-12-30      60
5           6       1B  2019-12-12      40

df
  ClientID    TestDate  MeanOp  NumberOp
1       1A  2019-12-24    22.5         2
4       1B  2019-08-26    60.0         1
6       1B  2020-01-12    50.0         2
8       1C  2019-08-26     NaN         0