View Discussion
Improve Article
Save Article
View Discussion
Improve Article
Save Article
Let us see how we can replace the column value of a CSV file in Python. CSV file is nothing but a comma-delimited file.
Method 1: Using Native Python way
Using replace() method, we can replace easily a text into another text. In the below code, let us have an input CSV file as “csvfile.csv” and be opened in “read” mode. The join() method takes all lines of a CSV file in an iterable and joins them into one string. Then, we can use replace() method on the entire string and can perform single/multiple replacements. In the entire string, the given text is searched and replaced with the specified text.
Example:
The input file will be:
Python3
text = open("csvfile.csv", "r")
text = ''.join([i for i in text])
text = text.replace("EmployeeName", "EmpName")
text = text.replace("EmployeeNumber", "EmpNumber")
text = text.replace("EmployeeDepartment", "EmpDepartment")
text = text.replace("lined", "linked")
x = open("output.csv","w")
x.writelines(text)
x.close()
Output:
Method 2: Using Pandas DataFrame
We can read the CSV file as a DataFrame and then apply the replace() method.
Python3
import pandas as pd
dataframe = pd.read_csv("csvfile1.csv")
dataframe.replace(to_replace ="Fashion",
value = "Fashion industry",
inplace = True)
dataframe.replace(to_replace ="Food",
value = "Food Industry",
inplace = True)
dataframe.replace(to_replace ="IT",
value = "IT Industry",
inplace = True)
dataframe.to_csv('outputfile.csv',
index = False)
Output:
Below is a quick tutorial on using a type of “find and replace” across a CSV file or you could do this find and replace on a TXT file too. I’m not a developer, just another blogger, blogging about learning python. I’m building use cases that are similar to a typical business analyst or data analyst.
You get a CSV file or TEXT file and want to scrape over the entire file, and output it with your modifications. Find and replace is the term I would think you would google if you wanted to do a find and replace in python over a CSV.
Here’s a typical CSV file. Delimited by a comma.
Name,Number,Rank,Website,Flag,CatTyler,43,1,//tylergarrett.com,Yes,0
Jeff,36,2,//tylergarrett.com,No,0
Isabelle,57,3,//tylergarrett.com,Yes,0
Mury,84,4,//tyler-garrett.com,No,0
Meow,96,6,//tylergarrett.com,Yes,1
Cats,25,5,//tylergarr3tt.com,no,1
→ Save file as input.csv
Python, without any downloaded libraries, will do a find and replace on the data above. You can copy and paste the data above into a .txt file, and change the extension to .csv — if you don’t have an excel software on your computer, or you can use Google sheets and make your own data problem.
Our goal is to clean the 3.
The 3 in the //tylergarr3tt.com link because it’s not accurate.
For whatever reason this is going to be used by a developer and they are asking me to find and replace the “errors.” Errors in the sample data generate a use case to learn how to do a find and replace on a CSV file, which taught me that using the a previous “text file” tutorial, I was able to handle the same ETL like solution, with simple python code, and no odd libraries to be imported.
Our use case will generate a full “find and replace python solution” and a few more obvious data issues. We will remove “3” and replace it with “e” in python below, to help us move down a path of learning and solving your use case today.
The code to find and replace anything on a CSV using python
text = open("input.csv", "r")text = ''.join([i for i in text]) \
.replace("3", "e")
x = open("output.csv","w")
x.writelines(text)
x.close()
We are not using any “imports” because this is all native python capability.
Let’s see what the output looks like.
Name,Number,Rank,Website,Flag,CatTyler,4e,1,//tylergarrett.com,Yes,0
Jeff,e6,2,//tylergarrett.com,No,0
Isabelle,57,e,//tylergarrett.com,Yes,0
Mury,84,4,//tyler-garrett.com,No,0
Meow,96,6,//tylergarrett.com,Yes,1
Cats,25,5,//tylergarrett.com,no,1
Successfully cleaning the “3” and also adding an “e” where our “3” used to be.
Look at line 2 & line 3 before:
Jeff,36,2,//tylergarrett.com,No,0Isabelle,57,3,//tylergarrett.com,Yes,0
Look at line 2 & line 3 after:
Jeff,e6,2,//tylergarrett.com,No,0Isabelle,57,e,//tylergarrett.com,Yes,0
The small portion of code:
text = ''.join([i for i in text]) \.replace("3", "e")
Allows you to “find and replace” using one line of code.
It looks like 2 lines of code because python lets you put a space and a “\” in front of a long line of code. The space + “\” lets me see what I’m doing easier that writing one wrapping line of code.
An example that may require a “ \” in front of your code, looks something like…because it’s easier to read.
Above image of code is an exaggeration to show you that python lets you break up your find and replace or you can do it in one line. I do it because I would go absolutely crazy if it wasn’t for that little feature, so if you’re like me, woop woop. This is a big help, otherwise
text = ''.join([i for i in text]).replace("3", "e")→write it like this…
Above code lets us quickly replace “3”’s with “e”’s and lets us heal our link column in the csv file. Find and replace may require a more granular approach which will be covered in another lesson.
Final code to find and replace text in a CSV with python
Find and replace text or letters in a csv with python is simple, super fast, faster than any SQL stuff I’ve played with in the past, so — if you need to bulk process a CSV or TEXT file — python is a great direction because it’s easy to understand.
text = open("input.csv", "r")text = ''.join([i for i in text]).replace("3", "e")
x = open("output.csv","w")
x.writelines(text)
x.close()
‘’.join is cool because it allows you to add anything, to each row of data.
Notice how…
text = '1'.join([i for i in text]) \Adds a “1” to each row of data…
Name,Number,Rank,Website,Flag,Cat1Tyler,4e,1,//tylergarrett.com,Yes,0
1Jeff,e6,2,//tylergarrett.com,No,0
1Isabelle,57,e,//tylergarrett.com,Yes,0
1Mury,84,4,//tyler-garrett.com,No,0
1Meow,96,6,//tylergarrett.com,Yes,1
1Cats,25,5,//tylergarrett.com,no,1
How exciting…
text = open("input.csv", "r")text = '1.......+'.join([i for i in text]) \
.replace("3", "e") \
.replace('+','+\n')
x = open("output.csv","w")
x.writelines(text)
x.close()
Generates…
Name,Number,Rank,Website,Flag,Cat1.......
Tyler,4e,1,//tylergarrett.com,Yes,0
1.......
Jeff,e6,2,//tylergarrett.com,No,0
1.......
Isabelle,57,e,//tylergarrett.com,Yes,0
1.......
Mury,84,4,//tyler-garrett.com,No,0
1.......
Meow,96,6,//tylergarrett.com,Yes,1
1.......
Cats,25,5,//tylergarrett.com,no,1
Find and replace in python, using the basics. You can use a .txt file too, for both input and output.
Later you will learn split() is a lot of fun to use.
By Tyler Garrett
Built a Tableau Consulting thing and now i do other stuff.