基于密钥的CSV连接

Question

这可能是一个简单/重复的问题，但我可以找到/想出如何做。在

我有两个csv文件：

信息.csv：

"Last Name", First Name, ID, phone, adress, age X [Total age: 100] |009076

abc, xyz, 1234, 982-128-0000, pqt,

bcd, uvw, 3124, 813-222-1111, tre, 

poi, ccc, 9087, 123-45607890, weq,

然后呢

年龄.csv：

^{pr2}$

我想根据中的“id”列比较这两个csv文件信息.csv和“student_id”来自年龄.csv取相应的“age_1”数据并将其放入信息.csv。在

因此，最终输出应为：

信息.csv：

"Last Name", First Name, ID, phone, adress, age X [Total age: 100] |009076
 abc, xyz, 1234, 982-128-0000, pqt,45
 bcd, uvw, 3124, 813-222-1111, tre,20
 poi, ccc, 9087, 123-45607890, weq,21

我可以简单地将基于键的表连接到新建.csv，但无法将数据放入列标题“age”。我用“csvkit”来做这个。在

我用的是：

csvjoin -c 3,1 info.csv age.csv > new.csv

Answer 1

您可以使用Pandas并使用age数据更新{}。通过将两个数据帧的索引分别设置为ID和{}，然后更新{}中的age列。之后，重新设置索引，使ID再次成为列。在

from StringIO import StringIO
import pandas as pd

info = StringIO("""Last Name,First Name,ID,phone,adress,age X [Total age: 100] |009076
abc, xyz, 1234, 982-128-0000, pqt,
bcd, uvw, 3124, 813-222-1111, tre, 
poi, ccc, 9087, 123-45607890, weq,""")


age = StringIO("""student_id,age_1
3124,20
9087,21
1234,45""")

info_df = pd.read_csv(info, sep=",", engine='python')
age_df = pd.read_csv(age, sep=",", engine='python')

info_df = info_df.set_index('ID')
age_df = age_df.set_index('student_id')
info_df['age X [Total age: 100] |009076'].update(age_df.age_1)
info_df.reset_index(level=0, inplace=True)
info_df

输出：

^{pr2}$

Answer 2

试试这个。。。在

import csv

info = list(csv.reader(open("info.csv", 'rb')))
age = list(csv.reader(open("age.csv", 'rb')))

def copyCSV(age, info, outFileName = 'out.csv'):
    # put age into dict, indexed by ID
    # assumes no duplicate entries

    # 1 - build a dict ageDict to represent data
    ageDict = dict([(entry[0].replace(' ',''), entry[1]) for entry in age[1:] if entry != []])

    # 2 - setup output
    with open(outFileName, 'wb') as outFile:
        outwriter = csv.writer(outFile)
        # 3 - run through info and slot in ages and write to output
        # nb: had to use .replace(' ','') to strip out whitespaces - these may not be in original .csv
        outwriter.writerow(info[0])
        for entry in info[1:]:
            if entry != []:
                key = entry[2].replace(' ','')
                if key in ageDict: # checks that you have data from age.csv
                    entry[5] = ageDict[key]
            outwriter.writerow(entry)

copyCSV(age, info)

如果有什么不清楚的地方，请告诉我。我使用dict是因为如果你的文件非常大，它应该更快，因为你只需要在年龄.csv一次。在

也许有一种更简单的方法/一些已经实现的东西…但这应该能做到。在