This article will discuss how to change a cell value in a CSV file using the Pandas library in Python.
Table Of Contents
- Change cell value of a CSV file by row/column labels
- Change cell value of a CSV file by row/column number
Suppose we have a CSV file like this,
Id,Name,Age,City,Experience a,John,49,London,15 b,Mark,44,New York,13 c,Joseph,48,Tokyo,14 d,Ritika,41,Delhi,11 e,Vinod,43,Mumbai,13 f,Saurav,51,Sydney,13 g,Lucy,52,Paris,13
We can modify this CSV file using Pandas by changing some cell values. For that, we need to import a CSV file to a DataFrame first. Then we can change cell values by selecting them either by row/column labels or by index positions.
For example, we can change the value of the cell at row ‘c’ and column ‘Age’ to 56 i.e.
Id,Name,Age,City,Experience a,John,49,London,15 b,Mark,44,New York,13 c,Joseph,56,Tokyo,14 d,Ritika,41,Delhi,11 e,Vinod,43,Mumbai,13 f,Saurav,51,Sydney,13 g,Lucy,52,Paris,13
or we can change the value of the cell at row number 5 and column number 2 to 55 i.e.
Id,Name,Age,City,Experience a,John,49,London,15 b,Mark,44,New York,13 c,Joseph,56,Tokyo,14 d,Ritika,41,Delhi,11 e,Vinod,55,Mumbai,13 f,Saurav,51,Sydney,13 g,Lucy,52,Paris,13
Let’s see how to do that.
Change cell value of a CSV file by row/column labels
First, we need to import the CSV file to a Pandas DataFrame using the read_csv() function. Then we can change the value of a cell by using the loc[] attribute. In the loc attribute, we need to pass the row index label and column name like this,
df.loc[row_label, column name]
It returns a reference of the specified cell from DataFrame. We can assign a new value to it, and the cell value in DataFrame will be modified i.e
df.loc[row_label, column name] = new_value
Once the DataFrame is modified, we can save the DataFrame to the same CSV file using the to_csv() function, and the cell value in the CSV file will be updated.
Let’s see the practical example,
Contents of the CSV file employees.csv file are,
Id,Name,Age,City,Experience a,John,49,London,15 b,Mark,44,New York,13 c,Joseph,48,Tokyo,14 d,Ritika,41,Delhi,11 e,Vinod,43,Mumbai,13 f,Saurav,51,Sydney,13 g,Lucy,52,Paris,13
Now let’s change the value of a cell in row ‘c’ and column ‘Age’ to 56,
import pandas as pd df = pd.read_csv('employees.csv', index_col='Id') # Set cell value at row 'c' and column 'Age' df.loc['c', 'Age'] = 56 # Write DataFrame to CSV file df.to_csv('employees.csv')
The content of the employees.csv file mist be like this now,
Id,Name,Age,City,Experience a,John,49,London,15 b,Mark,44,New York,13 c,Joseph,56,Tokyo,14 d,Ritika,41,Delhi,11 e,Vinod,43,Mumbai,13 f,Saurav,51,Sydney,13 g,Lucy,52,Paris,13
The value of cell at [‘c’, ‘Age’] is now 56.
Change cell value of a CSV file by row/column number
First, we will import the CSV file to a Pandas DataFrame using the read_csv() function. Then we can change the value of a cell by row and column number using the iloc[] attribute. In the iloc[] attribute, we need to pass the row and column index number like this,
df.iloc[row_index, column_index]
As the index number starts from 0, to select the cell at Nth row and Mth column, pass N-1 and M-1. It returns a reference of the specified cell from DataFrame. We can assign a new value to it, and the selected cell value in DataFrame will be modified i.e
df.iloc[N-1, M-1] = new_value
It changes the value of the cell at row number N and column number M. Once the DataFrame is modified, we can save the DataFrame to the same CSV file using the to_csv() function cell value in the CSV file will be updated.
Let’s see the practical example,
Contents of the CSV file employees.csv file are,
Id,Name,Age,City,Experience a,John,49,London,15 b,Mark,44,New York,13 c,Joseph,48,Tokyo,14 d,Ritika,41,Delhi,11 e,Vinod,43,Mumbai,13 f,Saurav,51,Sydney,13 g,Lucy,52,Paris,13
Now let’s change the cell value at row number 5 and column number 2. So we need to row index value 4 and column index number 1 in the iloc[] i.e.
import pandas as pd df = pd.read_csv('employees.csv', index_col='Id') # Set cell value at 5th row and 2nd column df.iloc[4 , 1] = 55 # Write DataFrame to CSV file df.to_csv('employees.csv')
The content of the employees.csv file mist be like this now,
Id,Name,Age,City,Experience a,John,49,London,15 b,Mark,44,New York,13 c,Joseph,48,Tokyo,14 d,Ritika,41,Delhi,11 e,Vinod,55,Mumbai,13 f,Saurav,51,Sydney,13 g,Lucy,52,Paris,13
The value of cell at [‘c’, ‘Age’] is now 56.
Summary:
We learned how to use Pandas to change a cell value in a CSV file.
Pandas Tutorials -Learn Data Analysis with Python
-
Pandas Tutorial Part #1 - Introduction to Data Analysis with Python
-
Pandas Tutorial Part #2 - Basics of Pandas Series
-
Pandas Tutorial Part #3 - Get & Set Series values
-
Pandas Tutorial Part #4 - Attributes & methods of Pandas Series
-
Pandas Tutorial Part #5 - Add or Remove Pandas Series elements
-
Pandas Tutorial Part #6 - Introduction to DataFrame
-
Pandas Tutorial Part #7 - DataFrame.loc[] - Select Rows / Columns by Indexing
-
Pandas Tutorial Part #8 - DataFrame.iloc[] - Select Rows / Columns by Label Names
-
Pandas Tutorial Part #9 - Filter DataFrame Rows
-
Pandas Tutorial Part #10 - Add/Remove DataFrame Rows & Columns
-
Pandas Tutorial Part #11 - DataFrame attributes & methods
-
Pandas Tutorial Part #12 - Handling Missing Data or NaN values
-
Pandas Tutorial Part #13 - Iterate over Rows & Columns of DataFrame
-
Pandas Tutorial Part #14 - Sorting DataFrame by Rows or Columns
-
Pandas Tutorial Part #15 - Merging or Concatenating DataFrames
-
Pandas Tutorial Part #16 - DataFrame GroupBy explained with examples
Are you looking to make a career in Data Science with Python?
Data Science is the future, and the future is here now. Data Scientists are now the most sought-after professionals today. To become a good Data Scientist or to make a career switch in Data Science one must possess the right skill set. We have curated a list of Best Professional Certificate in Data Science with Python. These courses will teach you the programming tools for Data Science like Pandas, NumPy, Matplotlib, Seaborn and how to use these libraries to implement Machine learning models.
Checkout the Detailed Review of Best Professional Certificate in Data Science with Python.
Remember, Data Science requires a lot of patience, persistence, and practice. So, start learning today.