Python: Add a Column to an Existing CSV File

• Method 1-Add a column with the same values to an existing CSV file

In this, we see how we make one column and add it to our CSV file but all the values in this column are the same.

Steps will be to append a column in CSV file are,

1. Open ‘input.csv’ file in read mode and create csv.reader object for this CSV file
2. Open ‘output.csv’ file in write mode and create csv.writer object for this CSV file
3. Using reader object, read the ‘input.csv’ file line by line
4. For each row (read like a list ), append default text in the list.
5. Write this updated list / row in the ‘output.csv’ using csv.writer object for this file.
6. Close both input.csv and output.csv file.

Let see this with the help of an example

from csv import writer
from csv import reader
default_text = 'New column'
# Open the input_file in read mode and output_file in write mode
with open('example1.csv', 'r') as read_obj, \
        open('output_1.csv', 'w', newline='') as write_obj:
    # Create a csv.reader object from the input file object
    csv_reader = reader(read_obj)
    # Create a csv.writer object from the output file object
    csv_writer = writer(write_obj)
    # Read each row of the input csv file as list
    for row in csv_reader:
        # Append the default text in the row / list
        row.append(default_text)
        # Add the updated row / list to the output file
        csv_writer.writerow(row)
output_data=pd.read_csv('output_1.csv')
output_data.head()

Output

Here we see that new column is added but all value in this column is same.

Now we see how we can add different values in the column.

•  Method 2-Add a column to an existing CSV file, based on values from other columns

In this method how we can make a new column but in this column the value we add will be a combination of two or more columns. As we know there is no direct function to achieve so we have to write our own function to achieve this task. Let see the code for this.

from csv import writer
from csv import reader
def add_column_in_csv(input_file, output_file, transform_row):
    """ Append a column in existing csv using csv.reader / csv.writer classes"""
    # Open the input_file in read mode and output_file in write mode
    with open(input_file, 'r') as read_obj, \
            open(output_file, 'w', newline='') as write_obj:
        # Create a csv.reader object from the input file object
        csv_reader = reader(read_obj)
        # Create a csv.writer object from the output file object
        csv_writer = writer(write_obj)
        # Read each row of the input csv file as list
        for row in csv_reader:
            # Pass the list / row in the transform function to add column text for this row
            transform_row(row, csv_reader.line_num)
            # Write the updated row / list to the output file
            csv_writer.writerow(row)
add_column_in_csv('example1.csv', 'output_2.csv', lambda row, line_num: row.append(row[0] + '__' + row[1]))
output_data=pd.read_csv('output_2.csv')
output_data.head()

Output

Here we see the new column is formed as the combination of the values of the 1st and 2nd column.

Explanation:

In the Lambda function, we received each row as a list and the line number. It then added a value in the list and the value is a merger of the first and second value of the list. It appended the column in the contents of example1.csv by merging values of the first and second columns and then saved the changes as output_2.csv files.

• Method 3-Add a list as a column to an existing csv file

In this method, we will add our own value in the column by making a list of our values and pass this into the function that we will make. Let see the code for this.

from csv import writer
from csv import reader
def add_column_in_csv(input_file, output_file, transform_row):
    """ Append a column in existing csv using csv.reader / csv.writer classes"""
    # Open the input_file in read mode and output_file in write mode
    with open(input_file, 'r') as read_obj, \
            open(output_file, 'w', newline='') as write_obj:
        # Create a csv.reader object from the input file object
        csv_reader = reader(read_obj)
        # Create a csv.writer object from the output file object
        csv_writer = writer(write_obj)
        # Read each row of the input csv file as list
        for row in csv_reader:
            # Pass the list / row in the transform function to add column text for this row
            transform_row(row, csv_reader.line_num)
            # Write the updated row / list to the output file
            csv_writer.writerow(row)
l=[]
l.append("New Column")
rows = len(data.axes[0])
for i in range(rows):
    val=i+1
    l.append(val)
add_column_in_csv('example1.csv', 'output_3.csv', lambda row, line_num: row.append(l[line_num - 1]))
output_data=pd.read_csv('output_3.csv')
output_data.head()

Output

Explanation

In the Lambda function, we received each row as a list and the line number. It then added a value in the list and the value is an entry from our list l at index  line_num – 1.Thus all the entries in the list l are added as a column in the CSV.

So these are some of the methods to add new column in csv.