Merging two csv files where common column matches

Question

I have a csv of users, and a csv of virtual machines, and i need to merge the users into their vms only where their id match.

But all im getting is a huge file containing everything.

file_names = ['vms.csv', 'users.csv']


o_data = []


for afile in file_names:
    file_h = open(afile)
    a_list = []
    a_list.append(afile)
    csv_reader = csv.reader(file_h, delimiter=';')
    for row in csv_reader:
        a_list.append(row[0])

    o_data.append((n for n in a_list))
    file_h.close()

with open('output.csv', 'w') as op_file:
    csv_writer = csv.writer(op_file, delimiter=';')
    for row in list(zip(*o_data)):
        csv_writer.writerow(row)
op_file.close()

Im relatively new to python, am i missing something?

Answer 1

I've always found pandas really helpful for tasks like this. You can simply load the datasets into pandas data frames and then use the merge function to merge them where the values in a column are same.

    import pandas
    vms = pandas.read_csv('vms.csv')
    users = pandas.read_csv('users.csv')

    output = pandas.merge(vms, users)
    output.to_csv('output.tsv')

You can find the documentation for the different options at http://pandas.pydata.org/pandas-docs/stable/merging.html