In my previous post, we have seen how to read CSV files https://tekcookie.com/read-csv-file-using-python/ and how to write data to text files https://tekcookie.com/write-to-a-text-file-using-python/
This article is about how to modify or edit multiple CSV files using Python.
Suppose we have the daily sale data of apples as shown below. Our task is to add one column to the right and calculate the total sale in each week.
Source Data
Required Result
Source content in raw csv
Weeks,Monday,Tuesday,Wednesday,Thursday,Friday Week-1,20,41,22,65,42 Week-2,23,23,56,44,85 Week-3,54,54,68,56,44 Week-4,12,89,32,23,65
Logic
- Open the CSV file and read the data
- Iterate through each row and calculate the sum of values for each week.
- Append the calculated data to the row
- Overwrite to the existing file
Python code to edit CSV file
import csv
# function to write the list to csv file
def write_csv(data_list, file_path):
with open(file_path, 'w', newline='') as file:
writer = csv.writer(file)
writer.writerows(data_list)
# function to read CSV to list
def read_csv(file_path):
csv_data_list = []
source_file = open(file_path)
csv_data = csv.reader(source_file)
for data in csv_data:
# converting the data to list
csv_data_list += [data]
source_file.close()
return csv_data_list
# main function
def edit_csv_main(file_path):
csv_data = read_csv(file_path)
final_list = []
for row_num in range(0, len(csv_data)):
# assigning the list to a variable for readability
data = csv_data[row_num]
total = 0
# adding heading to the first row
if row_num == 0:
data.append("Total")
else:
# total of each item in the row except the first item as it is a text
for numbers in data[1:len(data)]:
total += int(numbers)
# append the result to end of the list
data.append(total)
# append the result to final list
final_list += [data]
# calling csv write function
write_csv(final_list, file_path)
# calling the main function with path to the source file
edit_csv_main(r'D:\BLOG\Python\WriteCSV\File-17-04-2020.csv')
Running the above code will give us the required result.
If there are multiple files in which the data has to be modified, a small change to the above code can address it.
Python code to edit multiple CSV file
Suppose all the CSV files are kept at “D:\BLOG\Python\WriteCSV\” and the file names are File-<date>.csv
The way is to get path of the file of type CSV having names starting with File* and pass it to the code (Line# 53 in the code below).
We use glob function to find the file names in the folder (Line# 27 in the code below).
import csv
import glob
# function to write the list to csv file
def write_csv(data_list, file_path):
with open(file_path, 'w', newline='') as file:
writer = csv.writer(file)
writer.writerows(data_list)
#for data in data_list:
# writer.writerow(data)
# function to read CSV to list
def read_csv(file_path):
csv_data_list = []
source_file = open(file_path)
csv_data = csv.reader(source_file)
for data in csv_data:
# converting the data to list
csv_data_list += [data]
source_file.close()
return csv_data_list
# main function
def edit_csv_main(file_path):
file_names = glob.glob(file_path)
for file_path in file_names:
csv_data = read_csv(file_path)
final_list = []
for row_num in range(0, len(csv_data)):
# assigning the list to a variable for readability
data = csv_data[row_num]
total = 0
# adding heading to the first row
if row_num == 0:
data.append("Total")
else:
# total of each item in the row except the first item as it is a text
for numbers in data[1:len(data)]:
total += int(numbers)
# append the result to end of the list
data.append(total)
# append the result to final list
final_list += [data]
# calling csv write function
write_csv(final_list, file_path)
# calling the main function
# here the
edit_csv_main(r'D:\BLOG\Python\WriteCSV\File*.csv')Running the above code will edit all the CSV files in the mentioned location having their names starting with File*
Thank you for reading my post. Hope this is informative for you.
Recent Comments