python脚本从另一个csv更新csv中的现有列值

Question

如果我有2个csv文件如下：

csv1.csv：

1,Bob,Bob@gmail.com,baseball
2,Tom,Tom@gmail.com.football
3,Bill,Bill@gmail.com,softball
... 

csv2.csv：

baseball, b1
football, f1
...

我正在寻找一种Python方法，将来自csv1的错误值(csv1中的第三列等于csv2中的第一列)替换为来自csv2的正确值(第二列)。

看起来应该是：

1,Bob,Bob@gmail.com,b1
2,Tom,Tom@gmail.com,f1
3,Bill,Bill@gmail.com,softball

我的代码不起作用。

import csv

table1 = r'data.csv'
table2 = r'facebook_creo.csv'

creo_desc = dict()

with open(table2) as tbl2:
    t2 = csv.reader(tbl2, delimiter=',')
    next(t2) 

    for t2row in t2:
        wrong_creo = t2row[0]
        desc = t2row[1]

        creo_desc[wrong_creo] = desc

with open(table1) as tbl1:
    t1 = csv.reader(tbl1,  delimiter=',')
    for t1row in t1:
        wrong_creo = t1row[8]

    t1.writerow(t1row[8])

熊猫版本：

import pandas as pd
data = pd.read_csv(r'data.csv')
creo = pd.read_csv(r'creo.csv')
adset = pd.read_csv(r'adset.csv')
campaign = pd.read_csv(r'campaign.csv')
CreoDict = pd.Series(creo.iloc[:,1].values,index=creo.iloc[:,0]).to_dict()
AdsetDict = pd.Series(adset.iloc[:,1].values,index=adset.iloc[:,0]).to_dict()
CampaignDict = pd.Series(adset.iloc[:,1].values,index=adset.iloc[:,0]).to_dict()
data.iloc[:,8] = data.iloc[:,8].replace(CreoDict)
data.iloc[:,6] = data.iloc[:,6].replace(AdsetDict)
data.iloc[:,4] = data.iloc[:,4].replace(CampaignDict)
data.to_csv(r'total.csv')

Answer 1

我会使用熊猫在两个表中阅读，使用第二个表作为替换值的字典，以重新映射到csv1中。

import pandas as pd

# Read in the 2 csv files
csv1 = pd.read_csv('csv1.csv')
csv2 = pd.read_csv('csv2.csv')


#Create dictionary form csv2
replaceDict = pd.Series(csv2.iloc[:,1].values,index=csv2.iloc[:,0]).to_dict()

#Use dictionary to replace values                     
csv1.iloc[:,-1] = csv1.iloc[:,-1].replace(replaceDict)

# Write to file
csv1.to_csv('csv1_new.csv')

输出：

print (csv1)
   0     1               2         3
0  1   Bob   Bob@gmail.com  baseball
1  2   Tom   Tom@gmail.com  football
2  3  Bill  Bill@gmail.com  softball

print (csv2)
          0   1
0  baseball  b1
1  football  f1

然后在替换：

print (csv1)
   0     1               2         3
0  1   Bob   Bob@gmail.com        b1
1  2   Tom   Tom@gmail.com        f1
2  3  Bill  Bill@gmail.com  softball

Answer 2

import csv

table1 = r'a.csv'
table2 = r'b.csv'

creo_desc = dict()

with open(table2) as tbl2:
  t2 = csv.reader(tbl2, delimiter=',')
  for t2row in t2:
    creo_desc[t2row[0]] = t2row[1]

print(creo_desc)

ans = []
with open(table1,'r') as tbl1:
  t1 = csv.reader(tbl1,  delimiter=',')
  for t1row in t1:
    if t1row[-1] in creo_desc:
      t1row[-1] = creo_desc[t1row[-1]]
    ans.append(t1row)

with open(table1,'w') as tbl1:
  writer = csv.writer(tbl1)
  writer.writerows(ans)

1) a.csv

1,Bob,Bob@gmail.com,baseball
2,Tom,Tom@gmail.com.football
3,Bill,Bill@gmail.com,softball

2) b.csv

baseball, b1
football, f1

Answer 3

如果您将错误消息附在一起会更好，但是，我想您应该在希望进行更改的地方使用csv.writer，而不是使用csv.reader。