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,
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,
Frequently Asked:
- 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))
Contents of output_7.csv file are,
Id,Address,Name,Course,City,Session 21,Some_Text,Python,London,Morning, 22,Some_Text,Python,Tokyo,Evening, 23,Some_Text,Python,Paris,Morning, 24,Some_Text,Python,Delhi,Evening, 25,Some_Text,Python,Colombo,Morning,
The complete example is as follows,
from csv import writer from csv import reader from csv import DictReader from csv import DictWriter 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) 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) def main(): print('Add a column with same values to an existing csv file') 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) print('Add a column with same values to an existing csv file using generic function & a lambda') 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)) print('Add a column to an existing csv file, based on values from other column ') # 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])) print('Add a list as a column to an existing csv file') list_of_str = ['First', 'Second', 'Third', 'Fourth', 'Fifth'] # 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])) print('Insert a column as second column with same values into an existing csv') # 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') 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)) print('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)) print('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)) if __name__ == '__main__': main()
This is the best site ever !!!!!¡!!!!!!!!!!!!!!!!!!!!!!
Thanks for everything!!!!!!??
Hi there !
I am having a CSV file, I want to read it and re write on it by replacing one cell in the CSV.
Please I couldn’t find any tutorial here.
Thanks I will wait for your update
Hi Ayobmai,
Thanks for connecting with us.
We have created a new tutorial for changing cell values in a csv file –> Edit Cell Values in CSV files using Pandas in Python
Please let us know if you have any other query.
Thanks,
Varun
Hi,
In excel, if the number length is 15 and more – it will take as scientific notation(1.2e+15). Using python how to have the exact number instead of scientific notation. Could you please help me to solve this?