Python: Add a column to an existing CSV file

In this article we will discuss how to add a column to an existing CSV file using csv.reader and csv.DictWriter classes. A part from appending the columns we will also discuss how to insert columns in between other columns of the existing CSV file.

There are no direct functions in a python to add a column in a csv file. Although in python we have a csv module that provides different classes for reading and writing csv files. All the reading and writing operations provided by these classes are row specific. But we can build our logic on top of these functions to add or append columns in a csv file. Let’s see how to do that,

Add a column with same values to an existing CSV file

Suppose we have a CSV file i.e. input.csv and its contents are,

21,Mark,Python,London,Morning
22,John,Python,Tokyo,Evening
23,Sam,Python,Paris,Morning
24,Ritika,Python,Delhi,Evening
25,Shaun,Python,Colombo,Morning

Now we want to add a column in this csv file and all the entries in this column should contain the same value i.e. ‘Some Text‘.

Let’s see how to do that,

Advertisements

csv.reader class in python’s csv module provides a mechanism to read each row in the csv file as a list. Whereas, csv.writer class in python’s csv module provides a mechanism to write a list as a row in the csv file.

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

Open ‘input.csv’ file in read mode and create csv.reader object for this csv file
Open ‘output.csv’ file in write mode and create csv.writer object for this csv file
Using reader object, read the ‘input.csv’ file line by line
- For each row (read as list ), append default text in the list.
- Write this updated list / row in the ‘output.csv’ using csv.writer object for this file.
Close both input.csv and output.csv file.

Code is as follows,

from csv import writer
from csv import reader

default_text = 'Some Text'
# Open the input_file in read mode and output_file in write mode
with open('input.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)

It appended the column in the contents of input.csv and saved the changes as output_1.csv file. Contents of output_1.csv file are,

21,Mark,Python,London,Morning,Some Text
22,John,Python,Tokyo,Evening,Some Text
23,Sam,Python,Paris,Morning,Some Text
24,Ritika,Python,Delhi,Evening,Some Text
25,Shaun,Python,Colombo,Morning,Some Text

Adding column in CSV based on custom logic

In the above example we added a column of same values in the csv file. But there can be other scenarios, like,

How to add column in csv file based on some other logic, where each value in the column is different ?
How to add a list as a column in csv file?
How to add column in csv file with header?
How to insert columns in between other columns of the csv file.

We can change the little code in the above example, for each of our scenarios but that will be too much duplication of code.

So, lets create a generic function that will perform the most common part for adding a column in csv file i.e.

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)

This function has 3 arguments,

input_file: file path / name of the input csv file, it will read the contents of this csv file
output_file: file path / name of the output csv file, it will write modified contents in this csv file
transform_row: A callback function, that receives a list and modifies that list

This function iterates over each row of the input_file and read the contents of each row as a list. Then it passes that list into a transform_row() function for modification. In side this callback we can modify the list like add an entry in it. Then it saves the modified row / list in the output_file.

In the transform_row argument of this function, we will mainly pass a lambda function in our examples.

So, let’s use this function to solve our problems,

Add a column with same values to an existing csv file using generic function & a lambda

We did the same in our first example, but let’s do it again using our generic function and a lambda i.e.

default_text = 'Some Text'

# Add column with same text in all rows
add_column_in_csv('input.csv', 'output_2.csv', lambda row, line_num: row.append(default_text))

It appended the column in the contents of input.csv and saved the changes as output_2.csv file. Contents of output_2.csv file are,

21,Mark,Python,London,Morning,Some Text
22,John,Python,Tokyo,Evening,Some Text
23,Sam,Python,Paris,Morning,Some Text
24,Ritika,Python,Delhi,Evening,Some Text
25,Shaun,Python,Colombo,Morning,Some Text

Add a column to an existing csv file, based on values from other columns

Let’s append a column in input.csv file by merging the value of first and second columns i.e.

# Add column to csv by merging contents from first & second column of csv
add_column_in_csv('input.csv', 'output_3.csv', lambda row, line_num: row.append(row[0] + '__' + row[1]))

In the lambda function we received each row as list and the line number. It then added a value in the list and the value is a merger of first and second value of list.

It appended the column in the contents of input.csv by merging value of first and second column and then saved the changes as output_3.csv file.

Contents of output_3.csv file are,

21,Mark,Python,London,Morning,21__Mark
22,John,Python,Tokyo,Evening,22__John
23,Sam,Python,Paris,Morning,23__Sam
24,Ritika,Python,Delhi,Evening,24__Ritika
25,Shaun,Python,Colombo,Morning,25__Shaun

Add a list as a column to an existing csv file

Suppose we have a list of string i.e.

list_of_str = ['First', 'Second', 'Third', 'Fourth', 'Fifth']

Let’s add this list of strings as last column in input.csv file and save its contents as output_4.csv,

# Add a list as column
add_column_in_csv('input.csv', 'output_4.csv', lambda row, line_num: row.append(list_of_str[line_num - 1]))

In the lambda function we received each row as list and the line number. It then added a value in the list and the value is an entry from our list_of_str at index line_num – 1.

Thus all the entries in the list_of_str are added as a column in the csv.

Contents of output_4.csv file are,

21,Mark,Python,London,Morning,First
22,John,Python,Tokyo,Evening,Second
23,Sam,Python,Paris,Morning,Third
24,Ritika,Python,Delhi,Evening,Fourth
25,Shaun,Python,Colombo,Morning,Fifth

Insert a column as second column with same values into an existing csv

In all the above examples we added a column in the end of csv file i.e. as last column. What if we want to insert a new column in between other columns of the csv file ? Let’s see how to do that,

Insert a column in input.csv file as the second column and save its contents to output_5.csv file,

# Insert a column in between other columns of the csv file i.e. the second column of csv
add_column_in_csv('input.csv', 'output_5.csv', lambda row, line_num: row.insert(1, row[0] + '__' + row[1]))

print('Add a column with same values to an existing csv file with header')

Contents of output_5.csv file are,

21,21__Mark,Mark,Python,London,Morning
22,22__John,John,Python,Tokyo,Evening
23,23__Sam,Sam,Python,Paris,Morning
24,24__Ritika,Ritika,Python,Delhi,Evening
25,25__Shaun,Shaun,Python,Colombo,Morning

In the lambda function we received each row as list and the line number. It then inserted the value at the end position the list.

Add a column with same values to an existing csv file with header

In all the above examples we added column in a csv file that didn’t had any header. If our csv file has a header too,

Id,Name,Course,City,Session
21,Mark,Python,London,Morning
22,John,Python,Tokyo,Evening
23,Sam,Python,Paris,Morning
24,Ritika,Python,Delhi,Evening
25,Shaun,Python,Colombo,Morning

then let’s see how to add a new column with header and values i.e.

header_of_new_col = 'Address'
default_text = 'Some_Text'
# Add the column in csv file with header
add_column_in_csv('input_with_header.csv', 'output_6.csv',
                  lambda row, line_num: row.append(header_of_new_col) if line_num == 1 else row.append(
                      default_text))

In the lambda function it receives the row as list and its line number as arguments. Then checks if line_num is 1, then it means this list contains column names and it adds the name of column in it. Whereas, if line_num is not 1 then it means its a normal row of csv file, if adds the entry of new column in it.

So, it added a new column with values ‘Same_Text’ and header ‘Address’. Then saves that content as the output_6.csv,

Contents of output_6.csv file are,

Id,Name,Course,City,Session,Address
21,Mark,Python,London,Morning,Some_Text
22,John,Python,Tokyo,Evening,Some_Text
23,Sam,Python,Paris,Morning,Some_Text
24,Ritika,Python,Delhi,Evening,Some_Text
25,Shaun,Python,Colombo,Morning,Some_Text

Use DictReader DictWriter to add a column in existing csv file

Python’s csv module provides two other class for reading and writing contents in the csv file i.e. DictReader & DictWriter. It performs all the operations using dictionaries instead of lists.

We have created a separate function to add column in csv file, its similar to previous one but it uses DictReader and DictWriter class instead,

from csv import DictReader
from csv import DictWriter

def add_column_in_csv_2(input_file, output_file, transform_row, tansform_column_names):
    """ 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 DictReader object from the input file object
        dict_reader = DictReader(read_obj)
        # Get a list of column names from the csv
        field_names = dict_reader.fieldnames
        # Call the callback function to modify column name list
        tansform_column_names(field_names)
        # Create a DictWriter object from the output file object by passing column / field names
        dict_writer = DictWriter(write_obj, field_names)
        # Write the column names in output csv file
        dict_writer.writeheader()
        # Read each row of the input csv file as dictionary
        for row in dict_reader:
            # Modify the dictionary / row by passing it to the transform function (the callback)
            transform_row(row, dict_reader.line_num)
            # Write the updated dictionary or row to the output file
            dict_writer.writerow(row)

In this function we need to pass an additional callback tansform_column_names, it receives list of column names and we can modify that based on our intent.

Let’s see how to use this function to solve our problems,

Use DictReader DictWriter to add a column with same values to an existing csv

header_of_new_col = 'Address'
default_text = 'Some_Text'
# Add a Dictionary as a column in the existing csv file using DictWriter class
add_column_in_csv_2('input_with_header.csv', 'output_7.csv',
                    lambda row, line_num: row.update({header_of_new_col: default_text}),
                    lambda field_names: field_names.append(header_of_new_col))

Contents of output_7.csv file are,

Id,Name,Course,City,Session,Address
21,Mark,Python,London,Morning,Some_Text
22,John,Python,Tokyo,Evening,Some_Text
23,Sam,Python,Paris,Morning,Some_Text
24,Ritika,Python,Delhi,Evening,Some_Text
25,Shaun,Python,Colombo,Morning,Some_Text

Use DictReader DictWriter to insert a column as second column in a csv’

header_of_new_col = 'Address'
default_text = 'Some_Text'
# Insert a Dictionary as the column in between other columns of an existing csv file (Insert as 2nd column)
add_column_in_csv_2('input_with_header.csv', 'output_8.csv',
                    lambda row, line_num: row.update({header_of_new_col: default_text}),
                    lambda field_names: field_names.insert(1, header_of_new_col))