如果列匹配，则合并两个csv文件，然后覆盖

Question

示例我有这两个csv，如何覆盖列类型的值在a.csv或替换，如果它匹配列水果中的字符串在a.csv和b.csv

a.csv

fruit,name,type
apple,anna,A
banana,lisa,A
orange,red,A
pine,tin,A

b.csv

fruit,type
banana,B
apple,B

如何输出：或者如何覆盖

fruit,name,type
apple,anna,B
banana,lisa,B
orange,red,A
pine,tin,A

我正在尝试使用熊猫，但我不知道接下来会发生什么

df1=pd.read_csv("sha1_vsdt.csv",delimiter=",",error_bad_lines=False,engine = 'python',quoting=3)
df2=pd.read_csv("final.csv",delimiter=",",error_bad_lines=False,engine = 'python',quoting=3)

df = pd.merge(df1, df2, on='SHA-1', how='outer')

Answer 1

您可以对齐索引，更新索引，然后重置索引：

# align indices
df1 = pd.read_csv(s1).set_index('fruit')
df2 = pd.read_csv(s2).set_index('fruit')

# update
df1.update(df2)

# reset index
res = df1.reset_index()

print(res)

    fruit  name type
0   apple  anna    B
1  banana  lisa    B
2  orange   red    A
3    pine   tin    A

设置

from io import StringIO

s1 = StringIO("""fruit,name,type
apple,anna,A
banana,lisa,A
orange,red,A
pine,tin,A""")

s2 = StringIO("""fruit,type
banana,B
apple,B""")

Answer 2

您不需要合并，这可以通过一个简单的来实现。loc（位置）：

df2.set_index('fruit', inplace=True)
mask = df1.fruit.isin(df2.index)
df1.loc[mask, 'type'] = df2.loc[df1.loc[mask, 'fruit'], 'type'].values


    fruit   name    type
0   apple   anna    B
1   banana  lisa    B
2   orange  red     A
3   pine    tin     A

Answer 3

根据你的意见

import pandas as pd
df1=pd.read_csv("a.csv")
df2=pd.read_csv("b.csv")
df = pd.merge(df1, df2, on='fruit', how='outer')
df['type_x'] = df['type_y'].combine_first(df['type_x'])
del df["type_y"]
df = df[pd.notnull(df['name'])]

输入df1

    fruit   name    type
0   apple   anna    A
1   banana  lisa    A
2   orange  red     A
3   pine    tin     A

输入df2

    fruit   type
0   banana  B
1   lemon   B

输出

   fruit    name    type_x
0   apple   anna    A
1   banana  lisa    B
2   orange  red     A
3   pine    tin     A

如果您有具有不同列名的不同文件

import pandas as pd
df1=pd.read_csv("a.csv")
df2=pd.read_csv("b.csv")
df = pd.merge(df1, df2, on='fruit', how='outer')
df[df.columns[2]] = df[df.columns[3]].combine_first(df[df.columns[2]])
del df[df.columns[3]]
df = df[pd.notnull(df[df.columns[1]])]