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,Cat
Tyler,43,1,https://tylergarrett.com,Yes,0
Jeff,36,2,https://tylergarrett.com,No,0
Isabelle,57,3,https://tylergarrett.com,Yes,0
Mury,84,4,https://tyler-garrett.com,No,0
Meow,96,6,https://tylergarrett.com,Yes,1
Cats,25,5,https://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 https://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,Cat
Tyler,4e,1,https://tylergarrett.com,Yes,0
Jeff,e6,2,https://tylergarrett.com,No,0
Isabelle,57,e,https://tylergarrett.com,Yes,0
Mury,84,4,https://tyler-garrett.com,No,0
Meow,96,6,https://tylergarrett.com,Yes,1
Cats,25,5,https://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,https://tylergarrett.com,No,0
Isabelle,57,3,https://tylergarrett.com,Yes,0

Look at line 2 & line 3 after:

Jeff,e6,2,https://tylergarrett.com,No,0
Isabelle,57,e,https://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.

Simple python transformation.

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

→write it like this…

text = ''.join([i for i in text]).replace("3", "e")

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,Cat
1Tyler,4e,1,https://tylergarrett.com,Yes,0
1Jeff,e6,2,https://tylergarrett.com,No,0
1Isabelle,57,e,https://tylergarrett.com,Yes,0
1Mury,84,4,https://tyler-garrett.com,No,0
1Meow,96,6,https://tylergarrett.com,Yes,1
1Cats,25,5,https://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,Cat
1.......
Tyler,4e,1,https://tylergarrett.com,Yes,0
1.......
Jeff,e6,2,https://tylergarrett.com,No,0
1.......
Isabelle,57,e,https://tylergarrett.com,Yes,0
1.......
Mury,84,4,https://tyler-garrett.com,No,0
1.......
Meow,96,6,https://tylergarrett.com,Yes,1
1.......
Cats,25,5,https://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.

%d bloggers like this: