replacing the first line of a csv file python

Question

I need to replace the column headings in a CSV file, where I don't know the order or number of columns. For example the file could look like this:

DN  cn  sn  Name    mail    depart  mobile
data1   data2   data3   data4   data5   data6   data7

or this

DN  cn  depart  mobile  sn
data1   data2   data3   data4   data5

AND I need to copy one column and create a new heading, so the final output would look something like this (notice data1 is repeated):

user    email   phone   depar   mobile  sn
data1   data1   data2   data3   data4   data5

I am going 'round in circles with this one. Using

import csv

with open('test.csv', 'rb') as csvfile:
    r = csv.reader(csvfile, delimiter=',', quotechar='"')
    headers=r.next()
    for index, heading in enumerate(headers):
        if heading == 'mail':
            headers[index] = "Username"

I can change the column headings fine, and either write it to a new file or to the existing one, but then how do I add the extra column?

SOLUTION

Thanks to abarnert's answer below I am now using this code which works like a charm:

import csv

with open('test.csv', 'rb') as infile, open('out.csv', 'wb') as outfile:
    r = csv.reader(infile, delimiter=',', quotechar='"')
    headers=r.next()
    for index, heading in enumerate(headers):
        if heading == 'mail':
            headers[index] = "WorkEmail"
            username_index = index

    headers.insert(username_index, 'Username')

    w = csv.writer(outfile, delimiter=',', quotechar='"')
    w.writerow(headers)
    for row in r:
    if len(row) >= username_index:
            row.insert(username_index, row[username_index])
        w.writerow(row)

abarnert · Accepted Answer · 2013-06-27 23:47:39Z

4

All you need to do is keep track of where you found the column to be copied, then add it. For example:

# ...
# for ...

    if heading == 'mail':
        headers[index] = "Username"
    elif heading == 'data1':
        data_1_index = index

headers.insert(data_1_index, 'data1')

And I assume you'll also want to copy the value in each row, too. But that's just as easy.

for row in r:
    row.insert(data_1_index, row[data_1_index])
    w.writerow(row)

Putting it all together, here's a complete program that does what (I think) you want:

import csv

with open('test.csv', 'rb') as infile, open('out.csv', 'wb') as outfile:
    r = csv.reader(infile, delimiter=',', quotechar='"')
    headers=r.next()
    for index, heading in enumerate(headers):
        if heading == 'mail':
            headers[index] = "Username"
        elif heading == 'data1':
            data_1_index = index
    headers.insert(data_1_index, 'data1')

    w = csv.writer(outfile, delimiter=',', quotechar='"')
    w.writerow(headers)
    for row in r:
        row.insert(data_1_index, row[data_1_index])
        w.writerow(row)

answered Jun 27, 2013 at 23:47

abarnert

368k54 gold badges626 silver badges692 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Teg Ryan Over a year ago

Hi abarnert, thank you for the answer. I'm trying to make sure I understand this code, and I don't get how it copies the data in the column and not just the column heading. Maybe it's the use of "data1" as a heading, which is actually a data field that is throwing me off. Would this copy a column containing email addresses to a new column under the heading "usernames"?

Teg Ryan Over a year ago

oh, haha I got kind of focused on the header part, and tuned out the row writing part. I'll try this and see how it goes, thanks!

Collectives™ on Stack Overflow

replacing the first line of a csv file python

SOLUTION

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

SOLUTION

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related