Add a Column to an existing CSV file with Python

Question

I'm trying to add a new column header and values to an existing csv file with python. Every thing I've looked up appends the header to the last row of the last column. This is what I want my results to be essentially.

Header Header2 Header3 NewHeader Value Value2 Value3 NewValue

What I'm currently getting is this:

Header   Header2   Header3
Value    Value2    Value3**NewHeader
NewValue`

This is my code:

import csv
  with open('filename.csv', 'a') as csvfile:
  fieldnames = ['pageviewid']
  writer = csv.DictWriter(csvfile, fieldnames=fieldnames)

  writer.writeheader()
  writer.writerow({'pageviewid': 'Baked'})
  writer.writerow({'pageviewid': 'Lovely'})
  writer.writerow({'pageviewid': 'Wonderful'})

Solution 1

If using pandas is an option:

import pandas as pd
df = pd.read_csv('filename.csv')
new_column = pd.DataFrame({'new_header': ['new_value_1', 'new_value_2', 'new_value_3']})
df = df.merge(new_column, left_index = True, right_index = True)
df.to_csv('filename.csv', index = False)

Solution 2

Easiest way would be to rewrite to an output file

import csv
reader = csv.reader(open('filename.csv', 'r'))
writer = csv.writer(open('output.csv', 'w'))
headers = reader.next()
headers.append("Brand New Awesome Header")
writer.writerow(headers)
for row in reader:
    row.append(new data for my new header)
    writer.writerow(row)

Comments

[WombatPM]

You don't need to read a text file in binary mode. Is there another reason for the 'rb' mode? Otherwise a good answer.

[oba2311]

@Chris Kenyon I'd assume you meant writer.writerow rather than writer.write.

[duldi]

Using this method, a new column is added to the left of my data. The rest of the code works, do you know how I could get rid of this column? It starts from 0 to the end of my list so its probably enumerating the index of the list.