Using Python CSV #2— Basic Writing

Python’s csv module allows you to work with csv files (e.g., Excel spreadsheets). In a previous article, we looked at how to read csv files using Python (Using Python CSV#1 — Basic Reading). Now, let’s focus on the absolute basics of writing csv files using this module. In particular, we’ll use csv.writer , which converts data into delimited strings in a file (.csv). This is useful if we want to create a spreadsheet from a list or create one spreadsheet from another spreadsheet.

Write list to a row

I have a list of football players and their overall quality. I want to create a .csv file from this list. I can use write this list to a csv row using writerow

import csv 

my_list = ['Messi', 'Pele', 'Henderson']
with open('best_players.csv', 'w') as outfile:
    writer = csv.writer(outfile)
    writer.writerow(my_list)  

In the above 'w' specifies that I’m writing the file. If best_players.csv does not exist, then Python will create it. Here is the result:

Messi,Pele,Henderson

Write list to rows

You can also write each item in the list to its own row by looping over the items and the list and returning list items. More concretely, let’s say I want the names each one of my players on their own row.

my_list = ['Messi', 'Pele', 'Henderson']
with open('best_players.csv', 'w', newline="") as outfile:
    writer = csv.writer(outfile)
    for item in my_list:
        writer.writerow([item]) # Notice [item] rather than item

I’m using newline='' because it will create a blank line between each entry if I don’t. Result:

Messi
Pele
Henderson

Write list of lists using writerows

These lists are nice but suppose I have the names of football players and a rating of their quality. For each row, I want to write the football player’s name and their rating. If I have a list and the items in the list are lists that contain the name and the rating, then I can use writerows:

my_list = [['Messi', 'A'], ['Pele', 'A'], ['Henderson', 'C']]
with open('best_players.csv', 'w', newline='') as outfile:
    writer = csv.writer(outfile)
    writer.writerows(my_list)

Result:

Messi,A
Pele,A
Henderson,C

Adding Headers using writerow

In the previous example, we created a csv where each row has a player’s name and their rating. Let’s add a header to make sense of this information. To include a header, we can use writerow and before we write all of our data using writerows.

my_list = [['Messi', 'A'], ['Pele', 'A'], ['Henderson', 'C']]
with open('best_players.csv', 'w', newline='') as outfile:
    writer = csv.writer(outfile)
    writer.writerow(['Player', 'Rating']) # THE HEADER!
    writer.writerows(my_list)

Result:

Player,Rating
Messi,A
Pele,A
Henderson,C

Specifying a delimiter

A delimiter can be specified as a parameter in the csv.writer function (the default is the comma). A common delimiter is '\t' (tabbed). Let’s use one involving the "pipe|:

my_list = [['Messi', 'A'], ['Pele', 'A'], ['Henderson', 'C']]
with open('best_players.csv', 'w', newline='') as outfile:
    writer = csv.writer(outfile, delimiter='|') # DELIMITER!
    writer.writerow(['Player', 'Rating']) 
    writer.writerows(my_list)

Result:

Player|Rating
Messi|A
Pele|A
Henderson|C

Create csv from two lists (adding a column)

Suppose I have a list of the names of football players (Messi, Pele, Henderson) and a list of their ages. Two lists! I can create a csv of the names (one column) or a csv of the ages (one column), but how do I create a csv of both the names and ages? One option is to zip the lists together using the zip function and then turn the result back into a list using list(). The result will be a row with the names (column 1) and ages (column 2).

names = ['Messi', 'Pele', 'Henderson']
age = [35, 82, 32]
combined = list(zip(names, age))
with open('best_players.csv', 'w', newline='') as outfile:
    writer = csv.writer(outfile)
    writer.writerows(combined)

Result

Messi,35
Pele,82
Henderson,32

Write csv from a csv

Another common usage of csv.writer is to take data from one csv file and write it to another csv file. For example, I might export a spreadsheet that contains student names, grades, emails, id numbers, etc. I want to use this spreadsheet to create a spreadsheet that only contains their names.

with open ('grades_simple.csv', 'r') as infile:
    reader = csv.reader(infile)
    with open('names.csv', 'w', newline='') as outfile:        
        next(reader)
        writer = csv.writer(outfile)
        for row in reader:
            writer.writerow([row[0]])

In this example, we open the larger spreadsheet grades_simple.csv as a read file. With this open, we open names.csv as our write file. We use next(reader) to skip the header. Then, we go row by row through our read file, writing the first item (the student name) in each row. The result is a csv file that contains student names on each row.

"Soryu, Asuka Langley"
"Ikari, Shinji"
"Ayanami, Rei"

Create csv from multiple csvs

Suppose I have multiple spreadsheets that I wish to combine into a single spreadsheet. For example, suppose I have two csv files from two different classes that I teach. To combine them, first, I’ll put the file names in a list, then I’ll loop through the list. I’ll open the first file (class1.csv), then write each row to a csv file called combined_classes.csv. In this case, I want to use the append mode (use 'a' instead of read 'r' or write 'w' ) because if I’m in write mode, when I write the data from the second file (class2.csv), I’ll just write over the data from the first file.

csv_list = ['class1.csv', 'class2.csv']

for i in csv_list:
    with open (i, 'r') as infile:
        reader = csv.reader(infile)
        with open('combined_classes.csv', 'a', newline='') as outfile:
            next(reader)
            writer = csv.writer(outfile)
            for row in reader:
                writer.writerows([row])

Resources

• CSV File Reading and Writing — Python CSV Documentation