How to Add a Column to a CSV

5/5 - (1 vote)

Problem Formulation and Solution Overview

In this article, you’ll learn how to add a new column to a CSV file in Python.

To make it more fun, we have the following running scenario:

The owner of the Finxter Academy has asked you to add a new column to their existing CSV file called Total_Chrgs.

💬 Question: How would we write Python code to add a new column to a CSV file?

We can accomplish this task by one of the following options:

  • Method 1: Add a Column with a Default Value
  • Method 2: Add a Column with Calculated Values
  • Method 3: Add a Column using a Lambda with Calculated Values
  • Method 4: Add a Column using assign() with Assigned Values

Preparation

Before any data manipulation can occur, one (1) new library will require installation.

  • The Pandas library enables access to/from a DataFrame.

To install this library, navigate to an IDE terminal. At the command prompt ($), execute the code below. For the terminal used in this example, the command prompt is a dollar sign ($). Your terminal prompt may be different.

$ pip install pandas

Hit the <Enter> key on the keyboard to start the installation process.

If the installation was successful, a message displays in the terminal indicating the same.


Feel free to view the PyCharm installation guide for the required library.


Add the following code to the top of each code snippet. This snippet will allow the code in this article to run error-free.

import pandas as pd

💡 Note: To follow along, click here to download the Finxter CSV file and move this file to the current working directory.


Method 1: Add a Column with a Default Value

In this example, a new column is added and assigned a single default value of zero (0).

df = pd.read_csv('finxter.csv', usecols=['FID', 'First_Name', 'Last_Name', 'Recurring', 'Taxes'])
df['Total_Chrgs'] = 0
df.to_csv('finxter1.csv', index=False)

This code reads in the existing finxter.csv file, streamlining it to a few columns for brevity, and saves it to a DataFrame.

A new DataFrame column Total_Chrgs is added and assigned a default value of 0 for each entry. The results save to finxter1.csv.

Output (snippet) with index=True

This example sets index=False to ignore the index values when writing to a CSV file. If this was set to index=True, the file displays with an index column as shown below.

Output (snippet) with index=False

Leaving this as index=False gives us the output we are looking for: no additional index column.

💡 Note: For testing purposes, may we suggest writing the contents to a new CSV file and not overwriting the original.


Method 2: Add a Column with Calculated Values

A new column is added in this example, and a set calculation is performed and assigned to each entry.

df = pd.read_csv('finxter.csv', usecols=['FID', 'First_Name', 'Last_Name', 'Recurring', 'Taxes'])
df['Total_Chrgs'] = round(df['Recurring'] * (df['Taxes']/100) + df['Recurring'], 2)
df.to_csv('finxter2.csv', index=False)

This code reads in the existing finxter.csv file, streamlining it to a few columns for brevity, and saves it to a DataFrame.

A new column Total_Chrgs is added, and calculations are performed on each entry. The results are rounded to two (2) decimal places and saved to finxter2.csv.

Output (snippet)


Method 3: Add a Column using a Lambda with Calculated Values

In this example, a new column is added, and a set calculation is performed and assigned to each entry using a Lambda!

df = pd.read_csv('finxter.csv', usecols=['FID', 'First_Name', 'Last_Name', 'Recurring', 'Taxes'])
df['Total_Chrgs'] = df.apply(lambda row: round(row['Recurring'] * (row['Taxes']/100) + row['Recurring'], 2), axis=1)
df.to_csv('finxter3.csv', index=False) 

💡A Finxter Favorite!

This code reads in the existing finxter.csv file, streamlining it to a few columns for brevity, and saves it to a DataFrame.

A new column Total_Chrgs is added, and a Lambda performs calculations on each entry, rounding to two (2) decimal places. The output saves to finxter3.csv.

Output (snippet)


Method 4: Add a Column using assign() with Assigned Values

In this example, a new column is added, sorted, and assign() is used to set new values for the top four (4) records.

df = pd.read_csv('finxter.csv', usecols=['FID', 'First_Name', 'Last_Name', 'Recurring', 'Taxes'])
df.sort_values(by=['Recurring'], ascending=False, inplace=True)
df = df.head(4)
df = df.assign(Total_Chrgs=[12.98, 12.98, 11.98, 10.98])
df.to_csv('finxter4.csv', index=False) 

This code reads in the existing finxter.csv file, streamlining it to a few columns for brevity, and saves it to a DataFrame.

Then, the DataFrame is sorted in descending order based on Recurring charges, and the top four (4) rows return (df.head(4)). These rows are assigned the values shown in the assign() List parameter. The output saves to finxter4.csv.

Output (snippet)


Summary

These four (4) methods of adding a DataFrame Column should give you enough information to select the best one for your coding requirements.

Good Luck & Happy Coding!