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:
An alternative to the accepted answer is to:
- Use
fileinputwithinplace=Trueto modify the file in-place - Use
csv.DictReaderto access the column via header instead of indices [This only works if the CSV has headers]
Test CSV:
Ip,Sites
127.0.0.1,10
127.0.0.2,23
127.0.0.3,50
Test Code:
import fileinput
with fileinput.input[files=['test.csv'], inplace=True, mode='r'] as f:
reader = csv.DictReader[f]
print[",".join[reader.fieldnames]] # print back the headers
for row in reader:
if row["Ip"] == "127.0.0.2":
row["Sites"] = "30"
print[",".join[[row["Ip"], row["Sites"]]]]
The main difference is that you don't have to manually open the input file and create the output file, as inplace=True already does that behind-the-scenes:
Optional in-place filtering: if the keyword argument
inplace=Trueis passed tofileinput.input[]or to theFileInputconstructor, the file is moved to a backup file and standard output is directed to the input file [if a file of the same name as the backup file already exists, it will be replaced silently]. This makes it possible to write a filter that rewrites its input file in place.
The loop goes over the CSV row-by-row [except for the header row], so you can do whatever processing you need on each row.
If you still want to retain the original, you could pass in a backup=".backup" so that fileinput creates a test.csv.backup file.
Also,
note that with in-place editing, "standard output is directed to the input file", so print[..] prints it out to the file instead of to the command line. If you want to actually print to the console, you need to specify stderr as in print[..., file=sys.stderr].