使用 pandas/python 合并/合并两个 csv
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41669299/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
combine/merge two csv using pandas/python
提问by Madhura Mhatre
I have two csvs, I want to combine or merge these csvs as left join... my key column is "id", I have same non-key column as "result" in both csvs, but I want to override "result" column if any value exists in "result" column of 2nd CSV . How can I achieve that using pandas or any scripting lang. Please see my final expected output.
我有两个 csvs，我想合并或合并这些 csvs 作为左连接......我的关键列是“id”，我在两个 csvs 中有与“result”相同的非关键列，但我想覆盖“result”如果第二个 CSV 的“结果”列中存在任何值，则为列。我如何使用 Pandas 或任何脚本语言来实现这一点。请查看我的最终预期输出。
Input
输入
input.csv:
输入.csv：
id,scenario,data1,data2,result
1,s1,300,400,"{s1,not added}"
2,s2,500,101,"{s2 added}"
3,s3,600,202,
output.csv:
输出.csv：
id,result
1,"{s1,added}"
3,"{s3,added}"
Expected Output
预期产出
final_output.csv
final_output.csv
id,scenario,data1,data2,result
1,s1,300,400,"{s1,added}"
2,s2,500,101,"{s2 added}"
3,s3,600,202,"{s3,added}"
Current Code:
当前代码：
import pandas as pd
a = pd.read_csv("input.csv")
b = pd.read_csv("output.csv")
merged = a.merge(b, on='test_id',how='left')
merged.to_csv("final_output.csv", index=False)
Question:
题：
Using this code I am getting the result column twice. I want only once and it should override if value exists in that column. How do I get a single result column?
使用此代码，我获得了两次结果列。我只想要一次，如果该列中存在值，它应该覆盖。如何获得单个结果列？
采纳答案by Mahesh
try this, this works as well
试试这个，这也有效
import pandas as pd
import numpy as np
c=pd.merge(a,b,on='id',how='left')
lst=[]
for i in c.index:
if(c.iloc[i]['result_x']!=''):
lst.append(c.iloc[i]['result_x'])
else:
lst.append(c.iloc[i]['result_y'])
c['result']=pd.Series(lst)
del c['result_x']
del c['result_y']
回答by Stephen Rauch
This will combine the columns as desired:
这将根据需要组合列：
import pandas as pd
a = pd.read_csv("input.csv")
b = pd.read_csv("output.csv")
merged = a.merge(b, on='id', how='outer')
def merge_results(row):
y = row['result_y']
return row['result_x'] if isinstance(y, float) else y
merged['result'] = merged.apply(merge_results, axis=1)
del merged['result_x']
del merged['result_y']
merged.to_csv("final_output.csv", index=False)
回答by Legolas
You can also use concatas below.
您也可以使用concat，如下所示。
import pandas as pd
a = pd.read_csv("input.csv")
b = pd.read_csv("output.csv")
frames=[a,b]
mergedFrames=pd.DataFrame()
mergedFrames=pd.concat(frames, sort=True)
mergedFrames.to_csv(path/to/location)
NOTE: The sort=Trueis added to avoid some warnings
注意：sort=True添加是为了避免一些警告
