Merge 2 csv file with one unique column but different header

Question

I want to merge 2 csv file using some scripting language (like bash script or python).

1st.csv (this data is from mysql query)

member_id,name,email,desc
03141,ej,ej@domain.com,cool
00002,jes,jes@domain.com,good
00002,charmie,charm@domain.com,sweet

2nd.csv (from mongodb query)

id,address,create_date
00002,someCity,20150825
00003,newCity,20140102
11111,,20150808

The examples are not the actual, though i know that some of the member_id from qsl and the id from mongodb are the same. (*and i wish my output will be something like this)

desiredoutput.csv

meber_id,name,email,desc,address,create_date
03141,ej,ej@domain.com,cool,,
00002,jes,jes@domain.com,good,someCity,20150825
00002,charmie,charm@domain.com,sweet,
11111,,,,20150808

help will be much appreciated. thanks in advance

#########################################################################

#!/usr/bin/python
import csv
import itertools as IT

filenames = ['1st.csv', '2nd.csv']
handles = [open(filename, 'rb') for filename in filenames]
readers = [csv.reader(f, delimiter=',') for f in handles]

with  open('desiredoutput.csv', 'wb') as h:
    writer = csv.writer(h, delimiter=',', lineterminator='\n', )
    for rows in IT.izip_longest(*readers, fillvalue=['']*2):
        combined_row = []
        for row in rows:
            row = row[:1] # column where 1 know there are identical data
            if len(row) == 1:
                combined_row.extend(row)
            else:
                combined_row.extend(['']*1)
        writer.writerow(combined_row)

for f in handles:
    f.close()

#########################################################################

just read and tried this code(manipulate) in this site too

Answer 1

Since you haven't posted an attempt, I'll give you a general answer (using Python) to get you started.

• Create a dict, d
• Iterate over all the rows of the first file, convert each row into a list and store it in d using meber_id as the key and the list as the value.
• Iterate over all the rows of the second file, convert each row into a list leaving out the id column and update the list under d[id] with the new list if d[id] exists, otherwise store the new list under d[id].
• Finally, iterate over the values in d and print them out comma separated to a file.

Edit

In your attempt, you are trying to use izip_longest to iterate over the rows of both files at the same time. But this would work only if there were an equal number of rows in both files and they were in the same order.

Anyhow, here is one way of doing it.

Note: This is using the Python 3.4+ csv module. For 2.7 it might look a little different.

import csv

d = {}

with open("file1.csv", newline="") as f:
  for row in csv.reader(f):
    d.setdefault(row[0], []).append(row + [""] * 3)

with open("file2.csv", newline="") as f:
  for row in csv.reader(f):
    old_row = d.setdefault(row[0][0], [row[0], "", "", ""])
    old_row[4:] = row[1:]

with open("out.csv", "w", newline="") as f:
  writer = csv.writer(f)
  for rows in d.values():
    writer.writerows(rows)

Answer 2

Here goes a suggestion using pandas I've got from this answer and pandas doc about merging.

import pandas as pd
first = pd.read_csv('1st.csv')
second = pd.read_csv('2nd.csv')
merged = pd.concat([first, second], axis=1)

This will output:

meber_id  name     email            desc    id      address     create_date
3141      ej       ej@domain.com    cool    2       someCity    20150825
2         jes      jes@domain.com   good    11      newCity     20140102
11        charmie  charm@domain.com sweet   11111   NaN         20150808