Link Search Menu Expand Document
Data Focused Python

Working with CSV files and JSON data

Source

The CSV Module 

import csv

exampleFile = open('../automate_online-materials/example.csv')

reader Objects 

exampleReader = csv.reader(exampleFile)

exampleData = list(exampleReader)

exampleData

[['4/5/2014 13:34', 'Apples', '73'],
 ['4/5/2014 3:41', 'Cherries', '85'],
 ['4/6/2014 12:46', 'Pears', '14'],
 ['4/8/2014 8:59', 'Oranges', '52'],
 ['4/10/2014 2:07', 'Apples', '152'],
 ['4/10/2014 18:10', 'Bananas', '23'],
 ['4/10/2014 2:40', 'Strawberries', '98']]

exampleData[2][1]

'Pears'

with open('../automate_online-materials/example.csv', 'r') as f:
    lines = []
    for row in f:
        cols = row.split(',')
        lines.append(cols)
print(lines)

[['4/5/2014 13:34', 'Apples', '73\n'], ['4/5/2014 3:41', 'Cherries', '85\n'], ['4/6/2014 12:46', 'Pears', '14\n'], ['4/8/2014 8:59', 'Oranges', '52\n'], ['4/10/2014 2:07', 'Apples', '152\n'], ['4/10/2014 18:10', 'Bananas', '23\n'], ['4/10/2014 2:40', 'Strawberries', '98\n']]

exampleData[0][1]

'Apples'

Reading data from reader Objects in a for Loop 

exampleReader = csv.reader(exampleFile)

for row in exampleReader:
    print(f"Row #{exampleReader.line_num} {row}")

writer Objects 

outputFile = open('output.csv', 'w', newline='')

outputWriter = csv.writer(outputFile)

outputFile.write?

Signature: outputFile.write(text, /)
Docstring:
Write string to stream.
Returns the number of characters written (which is always equal to
the length of the string).
Type:      builtin_function_or_method

outputWriter.writerow('spam')

9

outputWriter.writerow(['spam', 'eggs', 'bacon', 'ham'])

21

outputWriter.writerow(['Hello, world!', 'eggs', 'bacon', 'ham'])

32

outputFile.close()

!cat output.csv

s,p,a,m
s,p,a,m
spam,eggs,bacon,ham
"Hello, world!",eggs,bacon,ham

The delimiter and lineterminator Keyword Arguments 

csvFile = open('example.tsv', 'w', newline='')

csvWriter = csv.writer(csvFile, delimiter='\t', lineterminator='\n\n')

csvWriter.writerow(['apples', 'oranges', 'grapes'])

23

csvWriter.writerow(['eggs', 'bacon', 'ham'])

16

outputFile.close()

!cat example.tsv

apples	oranges	grapes

eggs	bacon	ham

DictReader and DictWriter CSV Objects 

exampleFile = open('../automate_online-materials/exampleWithHeader.csv')

exampleDictReader = csv.DictReader(exampleFile)

for row in exampleDictReader:
    print(row['Timestamp'], row['Fruit'], row['Quantity'])

4/5/2014 13:34 Apples 73
4/5/2014 3:41 Cherries 85
4/6/2014 12:46 Pears 14
4/8/2014 8:59 Oranges 52
4/10/2014 2:07 Apples 152
4/10/2014 18:10 Bananas 23
4/10/2014 2:40 Strawberries 98

If you tried to use DictReader objects with example.csv, which doesn’t have column headers in the first row, the DictReader object would use ‘4/5/2015 13:34’, ‘Apples’, and ‘73’ as the dictionary keys. To avoid this, you can supply the DictReader() function with a second argument containing made-up header names:

exampleFile = open('../automate_online-materials/example.csv')
exampleDictReader = csv.DictReader(exampleFile, ['time', 'name', 'amount'])
for row in exampleDictReader:
    # print(row['time'], row['name'], row['amount'])
    print(row['name'])

Apples
Cherries
Pears
Oranges
Apples
Bananas
Strawberries

DictWriter objects use dictionaries to create CSV files.

outputFile = open('output.csv', 'w', newline='')
outputDictWriter = csv.DictWriter(outputFile, ['Name', 'Pet', 'Phone'])

outputDictWriter.writeheader()

16

outputDictWriter.writerow({'Name': 'Alice', 'Pet': 'cat', 'Phone': '555-1234'})
outputDictWriter.writerow({'Name': 'Bob', 'Phone': '555-9999'})
outputDictWriter.writerow({'Phone': '555-5555', 'Name': 'Carol', 'Pet': 'dog'})

20

outputFile.close()

!cat output.csv

Name,Pet,Phone
Alice,cat,555-1234
Bob,,555-9999
Carol,dog,555-5555

The JSON Module

Reading JSON with the loads() Function

To translate a string containing JSON data into a Python value, pass it to the json.loads() function. (The name means “load string,” not “loads.”) Enter the following into the interactive shell:

import json

stringOfJsonData = '{"name": "Zophie", "isCat": true, "miceCaught": 0, "felineIQ": null}'

jsonDataAsPythonValue = json.loads(stringOfJsonData)
jsonDataAsPythonValue

{'isCat': True, 'miceCaught': 0, 'name': 'Zophie', 'felineIQ': None}

Writing JSON with the dumps() Function

The json.dumps() function (which means “dump string,” not “dumps”) will translate a Python value into a string of JSON-formatted data. Enter the following into the interactive shell:

pythonValue = {'isCat': True, 'miceCaught': 0, 'name': 'Zophie', 'felineIQ': None}

stringOfJsonData = json.dumps(pythonValue)
stringOfJsonData

'{"isCat": true, "miceCaught": 0, "name": "Zophie", "felineIQ": null}'