Python Pandas map CSV 文件

Question

我想“合并”两个 CSV 文件。我想映射文件 1 中的电子邮件并从文件 2 中获取它们各自的 userId，然后我想将其分配给文件 1 的相应电子邮件

例子:

文件 1

name, userId, email
john, null, john@a.com
alex, null, alex@a.com
micheal, null, mike@a.com
alex, null, alex@a.com
john, null, john@a.com

文件 2

name, userId, email
alex, 5, alex@a.com
micheal, 10, mike@a.com
john, 12, john@a.com

输出文件

name, userId, email
john, 12, john@a.com
alex, 5, alex@a.com
micheal, 10, mike@a.com
alex, 5, alex@a.com
john, 12, john@a.com

这是我的代码，但不会分配相应电子邮件的用户 ID，因为电子邮件未排序

import pandas as pd

df1 = pd.read_csv("file1.csv", sep=",")
df2 = pd.read_csv("file2.csv", sep=",", index_col=0)

df1["userId"] = df2["userId"].values

df1.to_csv("output.csv", sep=";")

谁能帮帮我？

Answer 1

Dataframe.merge

df1 = pd.read_csv("file1.csv", sep=",")
df1.columns = ['name', 'userid', 'email']
df2 = pd.read_csv("file2.csv", sep=",", index_col=0)
df1 = df1.drop(['userId'], axis=1)

result = pd.merge(df1, df2, on=['name','email'], how='right')

result.to_csv("output.csv", sep=";")

我是如何测试的:

import pandas as pd

df1 = pd.DataFrame({'name': ['john', 'alex', 'michael', 'alex', 'john'],
                    'userId': ['null', 'null', 'null', 'null', 'null'],
                    'email': ['john@a.com', 'alex@a.com', 'mike@a.com', 'alex@a.com', 'john@a.com']
                    }, columns=['name','userId','email'])

df2 = pd.DataFrame({'name': ['alex', 'michael', 'john'],
                    'userId': ['5', '10', '12'],
                    'email': ['alex@a.com', 'mike@a.com', 'john@a.com']
                    })

df1 = df1.drop(['userId'], axis=1)

result = pd.merge(df1, df2, on=['name','email'], how='right')

print(df1)
print(df2)

print(result)