Learn Python Series (#29) - Handling CSV

Repository

https://github.com/python/cpython

What will I learn?

• You will learn what a CSV file containing tabular data looks like, both in a spreadsheet program, inside a CSV file using a code editor, and using a Python interpreter.
• how to use the csv module for reading, parsing and writing CSV files,
• how to handle both CSV list and dictionary elements,
• how to change the standard CSV field delimiter

Requirements

• A working modern computer running macOS, Windows or Ubuntu;
• An installed Python 3(.6) distribution, such as (for example) the Anaconda Distribution;
• The ambition to learn Python programming.

Difficulty

• Beginner

Curriculum (of the Learn Python Series):

• Learn Python Series - Intro
• Learn Python Series (#2) - Handling Strings Part 1
• Learn Python Series (#3) - Handling Strings Part 2
• Learn Python Series (#4) - Round-Up #1
• Learn Python Series (#5) - Handling Lists Part 1
• Learn Python Series (#6) - Handling Lists Part 2
• Learn Python Series (#7) - Handling Dictionaries
• Learn Python Series (#8) - Handling Tuples
• Learn Python Series (#9) - Using Import
• Learn Python Series (#10) - Matplotlib Part 1
• Learn Python Series (#11) - NumPy Part 1
• Learn Python Series (#12) - Handling Files
• Learn Python Series (#13) - Mini Project - Developing a Web Crawler Part 1
• Learn Python Series (#14) - Mini Project - Developing a Web Crawler Part 2
• Learn Python Series (#15) - Handling JSON
• Learn Python Series (#16) - Mini Project - Developing a Web Crawler Part 3
• Learn Python Series (#17) - Roundup #2 - Combining and analyzing any-to-any multi-currency historical data
• Learn Python Series (#18) - PyMongo Part 1
• Learn Python Series (#19) - PyMongo Part 2
• Learn Python Series (#20) - PyMongo Part 3
• Learn Python Series (#21) - Handling Dates and Time Part 1
• Learn Python Series (#22) - Handling Dates and Time Part 2
• Learn Python Series (#23) - Handling Regular Expressions Part 1
• Learn Python Series (#24) - Handling Regular Expressions Part 2
• Learn Python Series (#25) - Handling Regular Expressions Part 3
• Learn Python Series (#26) - pipenv & Visual Studio Code
• Learn Python Series (#27) - Handling Strings Part 3 (F-Strings)
• Learn Python Series (#28) - Using Pickle and Shelve

Additional sample code files

The full - and working! - iPython tutorial sample code file is included for you to download and run for yourself right here:
https://github.com/realScipio/learn-python-series/blob/master/csv-tut01.ipynb

GitHub Account

https://github.com/realScipio

Learn Python Series (#29) - Handling CSV

Welcome to already episode #29 of the Learn Python Series! We've already discussed handling files in general, handling JSON, in the previous epispde we talked about pickling, so we must also briefly touch upon a well-known and widely used data format: CSV.
When working with data, then CSV (Comma Separated Values) is a very commonly used format to import and export tabular data to and from spreadsheets and also databases.

However, because a well-defined CSV standard is missing, a uniform way to implement field delimiters (such as commas) is absent. These subtle differences in CSV formats make self-parsing of CSV files a bit cumbersome. Luckily most Python distributions (such as Anaconda) come with a bundled csv module help a lot with reading and writing "out of the box".

Let's find out how the csv module works!

What does a CSV file look like?

When importing / opening a CSV-file in a spreadsheet program (such as MS Excel, OpenOffice Calc), the data contained in the CSV is displayed in a tabular (table) format, like this:

ID	Name	City
1	Jack	New York
2	Paula	Dublin
3	Marly	Melbourne
4	Tony	London
5	Andrea	Rome
6	Julie	Paris
7	Bernhard	Berlin
8	Frank	San Francisco
9	Johan	Amsterdam
10	Maria	Barcelona

The underlying data structure of the above displayed CSV file, is structured in a human-readable form. It's actually just like a regular text file (you could open a CSV file with your favorite code editor as well), and each field is simply separated by a comma (hence the name CSV), like so:

ID,Name,City
1,Jack,New York
2,Paula,Dublin
3,Marly,Melbourne
4,Tony,London
5,Andrea,Rome
6,Julie,Paris
7,Bernhard,Berlin
8,Frank,San Francisco
9,Johan,Amsterdam
10,Maria,Barcelona

Reading CSV files

Let's suppose we have a file named names.csv containing the exact data as displayed above. Using Python's csv module, and the with keyword we've been using in the previous episode(s), reading a .csv file is done like so:

First import the csv module:

import csv

Then we'll open our file names.csv in read-mode, using flag r, and we'll assign it to file object f. Then we need to use the csv.reader() method and pass in our file object. Let's assign it as data:

with open('names.csv', 'r') as f:
    data = csv.reader(f)
    print(type(data))

<class '_csv.reader'>

As you can see, the CSV file data is now extracted into variable data which is a reader object, which is an iterable. So let's now first create an empty list content, then iterate over the reader object, append each data row as a list to the content list and then print it:

import pprint

with open('names.csv', 'r') as f:
    data = csv.reader(f)
    content = []
    for data_row in data:
        content.append(data_row)
    pprint.pprint(content)

[['ID', 'Name', 'City'],
 ['1', 'Jack', 'New York'],
 ['2', 'Paula', 'Dublin'],
 ['3', 'Marly', 'Melbourne'],
 ['4', 'Tony', 'London'],
 ['5', 'Andrea', 'Rome'],
 ['6', 'Julie', 'Paris'],
 ['7', 'Bernhard', 'Berlin'],
 ['8', 'Frank', 'San Francisco'],
 ['9', 'Johan', 'Amsterdam'],
 ['10', 'Maria', 'Barcelona']]

As you can see, each returned data_row is a list of 3 items.

Writing CSV files

We can apply the same technique to write data to a persistent .csv file on disk, this time using the csv.writer() method.

Let's create a list of lists containing some data, again use the with keyword and this time open a new file in write-mode (using the w, for write, flag), againi assign f as a file object, and the apply the csv.writer() method to create a writer object.

Then we'll use that writer object's method writerows() to actually write each item in the cryptos list to file, by passing in cryptos as writerows()'s argument, like so:

cryptos = [
    ['ID', 'Name', 'Abbreviation'],
    ['1', 'Bitcoin','BTC'],
    ['2', 'Litecoin','LTC'],
    ['3', 'Steem','STEEM'],
    ['4', 'Steem Backed Dollar','SBD'],
    ['5', 'IoTeX','IOTX'],
    ['6', 'Stellar','XLM'],
    ['7', 'EOS','EOS'],
    ['8', 'Ethereum','ETH'],
    ['9', 'Cardano','ADA'],
    ['10', 'Dash','DASH']
]

with open('cryptos.csv', 'w') as f:
    writer = csv.writer(f)
    print(type(writer))
    writer.writerows(cryptos)

<class '_csv.writer'>

At this point, a valid CSV file named cryptos.csv is written to disk, and to check if it's contents are valid, we can read it back in like we did before:

import pprint

with open('cryptos.csv', 'r') as f:
    data = csv.reader(f)
    content = []
    for data_row in data:
        content.append(data_row)
    pprint.pprint(content)

[['ID', 'Name', 'Abbreviation'],
 ['1', 'Bitcoin', 'BTC'],
 ['2', 'Litecoin', 'LTC'],
 ['3', 'Steem', 'STEEM'],
 ['4', 'Steem Backed Dollar', 'SBD'],
 ['5', 'IoTeX', 'IOTX'],
 ['6', 'Stellar', 'XLM'],
 ['7', 'EOS', 'EOS'],
 ['8', 'Ethereum', 'ETH'],
 ['9', 'Cardano', 'ADA'],
 ['10', 'Dash', 'DASH']]

Works like a charm!

csv's classes DictReader and DictWriter

Thus far we've been reading and writing lists with strings inside them to and from CSV files. But the csv module also contains the DictReader and DictWriter classes using Python dictionaries instead of lists containing string items.

Reading with DictReader

DictReader creates an object and maps it to a dictionary. The dictionary keys are either set using the optional fieldnames parameter, or - when not passed-in as an argument - read from the first line of the CSV file (and now you know why I have been consistently adding fieldnames in the first row of each CSV file ;-) ).

Let's now use the DictReader class to read in the names.csv file again, print a simple multiline F-String (see episode #27 on F-Strings) and please observe that I now use the same dictionary keys as contained in the first line of the names.csv file for reference.

import csv

content = []
with open('names.csv') as f:
    data = csv.DictReader(f)
    for data_row in data:
        content.append(data_row)
        print(
            f"Person #{data_row['ID']}, "
            f"{data_row['Name']}, "
            f"lives in {data_row['City']}"
        )

Person #1, Jack, lives in New York
Person #2, Paula, lives in Dublin
Person #3, Marly, lives in Melbourne
Person #4, Tony, lives in London
Person #5, Andrea, lives in Rome
Person #6, Julie, lives in Paris
Person #7, Bernhard, lives in Berlin
Person #8, Frank, lives in San Francisco
Person #9, Johan, lives in Amsterdam
Person #10, Maria, lives in Barcelona

Writing with DictWriter

Let's now try to write a CSV file using the DictWriter class, where we begin with creating a list of objects utopian_contributors (instead of a list of lists, or a list of strings). Because we're dealing with tabular data, every object inside the list has the same data structure.

We also create a list containing the fieldnames:
fieldnames = ['name', 'category']

When writing the CSV file, we first call the method writeheader(), in order to write the first row containing the fieldnames; as you may have noticed, this time I did not include the fieldnames as the first list item in the utopian_contributors data list.

And finally, we'll call the method writerows() and pass in the utopian_contributors data list.

Nota bene: it's also possible to use the method writerow(), for example in a for loop, to write each data row individually.

import csv

utopian_contributors = [
    {'name': 'scipio', 'category': 'tutorials'},
    {'name': 'rosatravels', 'category': 'tutorials'},
    {'name': 'holger80', 'category': 'development'},
    {'name': 'fabiyamada', 'category': 'graphics'}
]

with open('contributors.csv', 'w') as f:
    fieldnames = ['name', 'category']
    data = csv.DictWriter(f, fieldnames=fieldnames)    
    data.writeheader()
    data.writerows(utopian_contributors)

Changing delimiters (other than the default comma ,)

It's also possible to set an alternative field delimiter prior to executing a reader or writer method. For example you don't want to use the standard comma field delimiter, but a semi-colon, or a space.

This works as follows:

with open('contributors_semicolon.csv', 'w') as f:
    fieldnames = ['name', 'category']
    data = csv.DictWriter(f, fieldnames=fieldnames, delimiter=';')    
    data.writeheader()
    data.writerows(utopian_contributors)

Please observe the ; delimiter being applied!

This technique applies to both reading and writing CSV data.

Thank you for your time!