python - 如果找到匹配，则比较两个不同数据框中的列将电子邮件从 df2 复制到 df1

Question

我有两个列名不同的数据框，每个数据框有 10 行。我想要做的是比较列值，如果它们匹配，则将电子邮件地址从 df2 复制到 df1。我看过这个例子，但我的列名不同 How to join (merge) data frames (inner, outer, left, right)? .我看过this example以及 np.where 那里使用了多个条件但是当我这样做时它会给我以下错误:

ValueError: Wrong number of items passed 2, placement implies 1

我想做什么:

我想做的是比较 df1 的第一行 2 列(first，last_huge)和 df2 列的所有行(first_small，last_small)，如果找到匹配项，则从 df2 中的特定列获取电子邮件地址并分配它到 df1 中的新列。任何人都可以帮我解决这个问题吗？我只复制了下面的相关代码，只是向 new_email 添加了 5 条新记录，它并没有完全正常工作。

最初我所做的是比较 df1['first'] 和 df2['first']

data1 = {"first":["alice", "bob", "carol"],
         "last_huge":["foo", "bar", "baz"],
         "street_huge": ["Jaifo Road", "Wetib Ridge", "Ucagi View"],
         "city_huge": ["Egviniw", "Manbaali", "Ismazdan"],
         "age_huge": ["23", "30", "36"],
         "state_huge": ["MA", "LA", "CA"],
         "zip_huge": ["89899", "78788", "58999"]}

df1 = pd.DataFrame(data1)

data2 = {"first_small":["alice", "bob", "carol"],
         "last_small":["foo", "bar", "baz"],
         "street_small": ["Jsdffo Road", "sdf Ridge", "sdfff View"],
         "city_huge": ["paris", "london", "rome"],
         "age_huge": ["28", "40", "56"],
         "state_huge": ["GA", "EA", "BA"],
         "zip_huge": ["89859", "78728", "56999"],
         "email_small":["alice@xyz.com", "bob@abc.com", "carol@jkl.com"],
         "dob": ["31051989", "31051980", "31051981"],
         "country": ["UK", "US", "IT"],
         "company": ["microsoft", "apple", "google"],
         "source": ["bing", "yahoo", "google"]}

df2 = pd.DataFrame(data2)

df1['new_email'] = np.where((df1[['first']] == df2[['first_small']]), df2[['email_small']], np.nan)

现在它只向 new_email 添加 5 条记录，其余的都是 nan。并向我显示此错误:

ValueError: Can only compare identically-labeled Series objects

Answer 1

尝试合并:

(df1.merge(df2[["first_small", "last_small", "email_small"]], 
           how="left", 
           left_on=["first", "last_huge"], 
           right_on=["first_small", "last_small"])
    .drop(['first_small','last_small'], 1))

例子:

data1 = {"first":["alice", "bob", "carol"], 
         "last_huge":["foo", "bar", "baz"]}
df1 = pd.DataFrame(data1)

data2 = {"first_small":["alice", "bob", "carol"], 
         "last_small":["foo", "bar", "baz"],
         "email_small":["alice@xyz.com", "bob@abc.com", "carol@jkl.com"]}
df2 = pd.DataFrame(data2)

(df1.merge(df2[["first_small", "last_small", "email_small"]], 
           how="left", 
           left_on=["first", "last_huge"], 
           right_on=["first_small", "last_small"])
    .drop(['first_small','last_small'], 1))

输出:

   first last_huge    email_small
0  alice       foo  alice@xyz.com
1    bob       bar    bob@abc.com
2  carol       baz  carol@jkl.com