How to append a new column to a CSV file using Python? [duplicate]

Question

I have stored a set of four numbers in an array which I want to add to a CSV file under the 'Score' column.

with open('Player.csv', 'ab') as csvfile:
    fieldnames = ['Score']
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)

    writer.writeheader()
    for i in range(0, l):
       writer.writerow({'Score': score[i]})

It appends to the file, but this adds a new row instead of a new column. Could someone guide into appending it into a new column?

Answer 1

Probably the simplest solution would be to use Pandas. It's overkill, but it generally is much cleaner for CSV manipulation that extends beyond straight reading/writing.

Say I have a CSV file as follows:

ASSETNUM    ASSETTAG    ASSETTYPE   AUTOWOGEN
cent45  9164        0
cent45  9164        0

Then, the relevant code to add a column would be as follows:

import pandas as pd

df = pd.read_csv('path/to/csv.csv', delimiter='\t')
# this line creates a new column, which is a Pandas series.
new_column = df['AUTOWOGEN'] + 1
# we then add the series to the dataframe, which holds our parsed CSV file
df['NewColumn'] = new_column
# save the dataframe to CSV
df.to_csv('path/to/file.csv', sep='\t')

This adds a new column, scales well, and is easy to use. The resulting CSV file then would look as follows:

    ASSETNUM    ASSETTAG    ASSETTYPE   AUTOWOGEN   NewColumn
0   cent45  9164        0   1
1   cent45  9164        0   1

Compare this to the CSV module code for the same purpose (modified from here):

with open('path/to/csv.csv', 'r') as fin:
    reader = csv.reader(fin, delimiter='\t')
    with open('new_'+csvfile, 'w') as fout:
        writer = csv.writer(fout, delimiter='\t')
        # set headers here, grabbing headers from reader first
        writer.writerow(next(reader) + ['NewColumn']

        for row in reader:
            # use whatever index for the value, or however you want to construct your new value
            new_value = reader[-1] + 1
            row.append(new_value)
            writer.writerow(row)