Python 根据另一个数据帧中的匹配行将部分行移动到上一行

Question

我有两个数据框df1:

    Name Month  Amount  Status
0   Bill   Apr       0           
1   Bill   May       0           
2   Bill   Jun     100  member
3  Sally   Apr       0           
4  Sally   May       0           
5  Sally   Jun     200  member
6    Tom   Apr       0           
7    Tom   May     300  member
8    Tom   Jun       0           

和df2:

   Name Month
0  Bill   Jun
1   Tom   May

只要 df2 中的名称和月份匹配，我就会更新 df1，并将金额和状态移至上个月。可以假设每个人总是有一个上个月要移动到，并且行已经按姓名和月份按正确的顺序排列。预期结果如下:

    Name Month  Amount  Status
0   Bill   Apr       0           
1   Bill   May     100  member           
2   Bill   Jun       0
3  Sally   Apr       0           
4  Sally   May       0           
5  Sally   Jun     200  member
6    Tom   Apr     300  member          
7    Tom   May       0
8    Tom   Jun       0           

我相信有一种方法可以使用 itterows() 来做到这一点，但是这里有更直接的方法吗？

Answer 1

尝试:

# merge to find rows with Name, Month in df2
exist = df1[['Name','Month']].merge(df2.assign(exist=1), how='left')['exist'].notna()

# find the previous rows
prev_rows = exist.groupby(df1['Name']).shift(-1, fill_value=False)

# fill the previous rows
df1.loc[prev_rows, ['Amount','Status']] = df1.loc[exist, ['Amount','Status']].to_numpy()

# remove the given rows
df1.loc[exist, ['Amount','Status']] = [0, None]

输出:

    Name Month  Amount  Status
0   Bill   Apr       0    None
1   Bill   May     100  member
2   Bill   Jun       0    None
3  Sally   Apr       0    None
4  Sally   May       0    None
5  Sally   Jun     200  member
6    Tom   Apr     300  member
7    Tom   May       0    None
8    Tom   Jun       0    None