Joining 2 CSV files, one with a variable number of columns

Question

I have a problem similar to the one mentioned here:

How to join two CSV files?

I awant to join the file, using the first field of both files as the join key.

The difference is that in one of the two files, the number of columns may vary from one record to another (it contains a traceroute, one ip per column, so the length may change.)

(The join key is the IP address, that is the first column in both files.)

Answer 1

Dont know I have read your question right. I would like to use the excellent csv module from python to do this.

Example:

file a

a,x
b,y
c,z

file b

l,m
n,p
k,m

Code - join.py

import csv
source1= csv.reader( open("a","rb") )
source2= csv.reader( open("b","rb") )
dest= csv.writer( open("c","wb") )
for row in source1:
    result=row[1]
    dest.writerow(result)
for row in source2:
    result=row[1]
    dest.writerow(result)

Answer 2

I would suggest to use SQLite to do this operation. Installing SQLite is very easy, no root permission is required, works well on all platforms.

Steps:

1. Import both the CSV files into SQLite DB.
2. Use SQL to make the join.
3. Save the output as CSV file.

sqlite3

.separator ","

create table test1 (col1 text, col2 text);

.import test1.csv test1

create table test2 (col1 text, col2 text);

.import test3.csv test1

.mode csv

.output outfile.csv

select a.col1,a.col2,b.col2 from test1 a join test2 b on a.col1=b.col1;

.output stdout

Answer 3

I would really use a database for this. You could try using MS Access or OpenOffice Base. You may need to use a spreadsheet (eg Excel) temporarily to help import the CSV files.