基于迭代方式匹配两个DataFrame用户并添加标签与新套餐列

现有两个Pandas DataFrame：

• DataFrame A：记录2023年4月10日Bronze套餐停售前购买该套餐的用户
• DataFrame B：记录2023年4月10日后仍继续购买产品的用户

需要完成：从DataFrame A中筛选出后续有购买行为的用户，并获取他们的新套餐信息。要求不能使用np.where方法，必须采用迭代方式——提取DataFrame A的Identity字段遍历DataFrame B进行匹配，为DataFrame A新增Label（标记是否有后续购买）和New Package（后续购买的套餐名称）两列。

DataFrame A 代码 #

import pandas as pd

data_a = {
    "Identity": ["A", "B", "C", "D", "E", "F", "X", "Y", "Z"],
    "Last Purchasing Date": ["20201224", "20220418", "20230312", "20230414", "20230618", "20230417", "20230417", "20230417", "20230416"],
    "Package Name": ["Platinum", "Gold", "Bronze", "Red", "Green", "Bronze", "Bronze", "Bronze", "Bronze"],
    "Country": ["Ghana", "Ghana", "Kenya", "Mozambique", "Astria", "Australia", "Egypt", "South Africa", "Uganda"],
    "Price_USD": [50, 30, 20, 15, 10, 20, 20, 20, 20],
    "TransactionID": ["xxcxcjjjkhsdg", "uyerygbfjh", "hjvfbjhsbdf", "ureybjsdfsk", "qwqtvjdbcj", "pioerybhjb", "lkjkfnksfuh", "yeuwtevjfdsf", "qwiqeubkd"]
}

df_a = pd.DataFrame(data_a)
print("DataFrame A:")
print(df_a)

DataFrame B 代码 #

import pandas as pd

data_b = {
    "Identity": ["X", "Y", "Z", "C", "oi", "po", "as", "vvc", "mn", "kml", "oiu"],
    "Last Purchasing Date": ["20230510", "20230630", "20230701", "20230524", "20230618", "20230103", "20230709", "20230323", "20230222", "20230613", "20230629"],
    "Package Name": ["Platinum", "Gold", "Gold", "Red", "Green", "Platinum", "Gold", "Platinum", "Gold", "Red", "Red"],
    "Country": ["Egypt", "South Africa", "Uganda", "Kenya", "Astria", "Australia", "Egypt", "South Africa", "Uganda", "Tanzania", "Zimbabwe"],
    "Price_USD": [50, 30, 30, 20, 10, 50, 30, 50, 30, 15, 15],
    "TransactionID": ["xxcxcjjjkhsdgkkits", "uyerygbfjhyutrev", "hjvfbjhsbdfqwoierb", "ureybjsdfskmncxy", "qwqtvjdbcjapiev", "ttccljqoeuhadl", "lkjkfnksfuhiyewl", "yeuwtevjfdsfawqwutvssl", "qwiqeubkdqweoipmn", "ieyrjbsdfkbkqwpeoi", "poierbsdjfbdflioewww"]
}

df_b = pd.DataFrame(data_b, columns=["Identity", "Last Purchasing Date", "Package Name", "Country", "Price_USD", "TransactionID"])
print("\nDataFrame B:")
print(df_b.to_string())

import pandas as pd

# 初始化DataFrame A
data_a = {
    "Identity": ["A", "B", "C", "D", "E", "F", "X", "Y", "Z"],
    "Last Purchasing Date": ["20201224", "20220418", "20230312", "20230414", "20230618", "20230417", "20230417", "20230417", "20230416"],
    "Package Name": ["Platinum", "Gold", "Bronze", "Red", "Green", "Bronze", "Bronze", "Bronze", "Bronze"],
    "Country": ["Ghana", "Ghana", "Kenya", "Mozambique", "Astria", "Australia", "Egypt", "South Africa", "Uganda"],
    "Price_USD": [50, 30, 20, 15, 10, 20, 20, 20, 20],
    "TransactionID": ["xxcxcjjjkhsdg", "uyerygbfjh", "hjvfbjhsbdf", "ureybjsdfsk", "qwqtvjdbcj", "pioerybhjb", "lkjkfnksfuh", "yeuwtevjfdsf", "qwiqeubkd"]
}
df_a = pd.DataFrame(data_a)

# 初始化DataFrame B
data_b = {
    "Identity": ["X", "Y", "Z", "C", "oi", "po", "as", "vvc", "mn", "kml", "oiu"],
    "Last Purchasing Date": ["20230510", "20230630", "20230701", "20230524", "20230618", "20230103", "20230709", "20230323", "20230222", "20230613", "20230629"],
    "Package Name": ["Platinum", "Gold", "Gold", "Red", "Green", "Platinum", "Gold", "Platinum", "Gold", "Red", "Red"],
    "Country": ["Egypt", "South Africa", "Uganda", "Kenya", "Astria", "Australia", "Egypt", "South Africa", "Uganda", "Tanzania", "Zimbabwe"],
    "Price_USD": [50, 30, 30, 20, 10, 50, 30, 50, 30, 15, 15],
    "TransactionID": ["xxcxcjjjkhsdgkkits", "uyerygbfjhyutrev", "hjvfbjhsbdfqwoierb", "ureybjsdfskmncxy", "qwqtvjdbcjapiev", "ttccljqoeuhadl", "lkjkfnksfuhiyewl", "yeuwtevjfdsfawqwutvssl", "qwiqeubkdqweoipmn", "ieyrjbsdfkbkqwpeoi", "poierbsdjfbdflioewww"]
}
df_b = pd.DataFrame(data_b, columns=["Identity", "Last Purchasing Date", "Package Name", "Country", "Price_USD", "TransactionID"])

# 重命名原套餐列为Old Package
df_a.rename(columns={"Package Name": "Old Package"}, inplace=True)

# 初始化新增列
df_a["Label"] = "No"
df_a["New Package"] = "None"

# 迭代遍历DataFrame A的每个用户
for idx, row in df_a.iterrows():
    user_id = row["Identity"]
    # 在DataFrame B中匹配用户
    match_row = df_b[df_b["Identity"] == user_id]
    if not match_row.empty:
        df_a.at[idx, "Label"] = "Yes"
        # 取匹配到的第一个套餐（若有多个可按需调整）
        df_a.at[idx, "New Package"] = match_row.iloc[0]["Package Name"]

# 调整列顺序，与期望输出一致
df_a = df_a[["Identity", "Last Purchasing Date", "Old Package", "Country", "Price_USD", "Label", "New Package"]]

# 打印结果
print("处理后DataFrame A:")
print(df_a.to_string())

运行上述代码后，得到的结果与期望输出一致：

处理后DataFrame A:
  Identity Last Purchasing Date Old Package      Country  Price_USD Label New Package
0        A             20201224   Platinum        Ghana         50    No    Platinum
1        B             20220418        Gold        Ghana         30    No        Gold
2        C             20230312      Bronze         Kenya         20   Yes          Red
3        D             20230414        Red  Mozambique         15    No          Red
4        E             20230618       Green       Astria         10    No         None
5        F             20230417      Bronze    Australia         20    No         None
6        X             20230417      Bronze        Egypt         20   Yes    Platinum
7        Y             20230417      Bronze  South Africa         20   Yes        Gold
8        Z             20230416      Bronze        Uganda         20   Yes        Gold

内容的提问来源于stack exchange，提问作者Mwai.John