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.