CODEWITHSUNDEEP
pythoncsv
K3rn3l5
K3rn3l5

Reputation: 361

Merge multiple csv files and add a new column

I have a bunch of csv files that i need to merge into one file but with an additional date column

xxxxx20150216.csv

xxxxx20130802.csv

xxxxx20130803.csv

xxxxx20130804.csv

I am using the following code from (http://cbrownley.wordpress.com/2014/03/09/pythons-voracious-glob-module/) to merge them

import csv
import glob
import os
import sys

data_path = ""
outfile_path = "alldata.csv"
filewriter = csv.writer(open(outfile_path,'wb'))
file_counter = 0
for input_file in glob.glob(os.path.join(data_path,'*.csv')):
        with open(input_file,'rU') as csv_file:
                filereader = csv.reader(csv_file)
                if file_counter < 1:
                        for row in filereader:
                                filewriter.writerow(row)
                else:
                        header = next(filereader,None)
                        for row in filereader:
                                filewriter.writerow(row)
        file_counter += 1

But now I need to extract the date from the filename and add it as column along with the other rows. What could be the easiest way to accomplish this?

Upvotes: 1

Views: 2516

Answers (1)

Alex Martelli
Alex Martelli

Reputation: 881575

What about...:

    with open(input_file,'rU') as csv_file:
        filereader = csv.reader(csv_file)
        name, ext = os.path.splitext(input_file)
        date = name[-8:]
        if file_counter < 1:
            for i, row in enumerate(filereader):
                if i==0: row.append('Date')
                else: row.append(date)
                filewriter.writerow(row)
            else:
                header = next(filereader,None)
                for row in filereader:
                    row.append(date)
                    filewriter.writerow(row)

The only tricky part is taking the headers from the first CSV file!-)

Upvotes: 4

Related Questions