Short for comma-separated values, CSV files are a fading relic of a simpler time. Yet, they are still a necessity and admittedly can be quite convenient.
The challenge in working with CSV files is that our data is often stored in a different format internally within Python memory. Python dictionaries are one of these common structures.
It’s possible to write your own custom CSV export script; however, for simple exports this is not necessary. The csv
library that is native to Python and only needs a simple import has a class called DictWriter, which will allow you to export dictionary data to CSV in five lines of code.
We’ll start with a working solution and then circle back through all the specifics of using DictWriter.
Our Sample Script
Let’s use some data generated from the site Mockaroo. We’ll use their default setup minus ip address for a total of five fields. After setting the format to JSON and the number of rows to 3, here’s the output stored to a variable named data:
data = [{
"id": 1,
"first_name": "Giuditta",
"last_name": "Garland",
"email": "ggarland0@blogspot.com",
"gender": "Female"
}, {
"id": 2,
"first_name": "Ginger",
"last_name": "Franzewitch",
"email": "gfranzewitch1@furl.net",
"gender": "Male"
}, {
"id": 3,
"first_name": "Ellwood",
"last_name": "Shanahan",
"email": "eshanahan2@ebay.com",
"gender": "Male"
}]
Now, above our variable definition we’ll add an import statement.
import csv
Below the variable definition we’ll write the commands to export as CSV.
with open("data.csv", "w", newline="") as csv_file:
cols = ["id","first_name","last_name","email","gender"]
writer = csv.DictWriter(csv_file, fieldnames=cols)
writer.writeheader()
writer.writerows(data)
That’s it. Five lines, as promised. Run the script from your command-line and you should see a file named data.csv
in the same directory.
Solution Breakdown
Okay, let’s break down exactly what happened with this concise script.
Check out A Beginner’s Guide to Importing in Python for more information on importing libraries.
Since the solution is so brief, here is a line-by-line breakdown.
We open a file called
test.csv
with thew
attribute for writing and set the newline character equal to an empty string — this prevents compatibility issues on various operating systems. The created file pointer is stored in the variablecsv_file
.Next, we set the order that we want the fields exported in our CSV file. In the example, the fields remain in the same order as the dictionary, but they can be rearranged as desired.
We create an instance of the DictWriter class and store it in
writer
. The class requires both a CSV file pointer and our fields passed to thefieldnames
attribute.The
.writeheader()
method takes the list stored infieldnames
and writes it to the CSV file.The
.writerows()
method takes our variable data and writes each item in the list as a row in the CSV file.
Frequently Asked Questions & Troubleshooting
At this point you should be ready to try out DictWriter on your own. But just in case things don’t go smoothly, here are the answers to some common pitfalls that you may encounter.
What if I miss a key in the fieldnames?
Missing a key in the fieldnames list that is passed to DictWriter can result in an exception being thrown. If we take our solution code and remove id
from the list, here is what happens:
with open("data.csv", "w", newline="") as csv_file:
cols = ["first_name","last_name","email","gender"]
writer = csv.DictWriter(csv_file, fieldnames=cols)
writer.writeheader()
writer.writerows(data)
# ValueError: dict contains fields not in fieldnames: 'id'
There are two ways to resolve this. Either add the key to fieldnames
or change the behavior by setting the extrasaction
value to ignore
when creating our DictWriter object.
with open("data.csv", "w", newline="") as csv_file:
cols = ["first_name","last_name","email","gender"]
writer = csv.DictWriter(csv_file,
fieldnames=cols,
extrasaction='ignore')
writer.writeheader()
writer.writerows(data)
What if my dictionary has nested data?
When your dictionary has a nested structure, the value is stringified to preserve a flat file structure.
If we modify our sample data to include a list of job titles and re-run the script, you can expect the following output.
# Sample Data
{
"id": 3,
"first_name": "Jon",
"last_name": "Smith",
"email": "jsmith@wisc.edu",
"gender": "Male",
"jobs": ["Advisor", "Pharmacist"]
}
# Output
id, first_name, last_name, email, gender, jobs
3, Jon, Smith, jsmith@wisc.edu, Male, ["Advisor", "Pharmacist"]
What if a field is not in the dictionary?
When a key is specified in the fieldnames
that does not exist in the dictionary, it will be written as an empty string by default. If you wish to set a different default value, use the restval
attribute when creating the DictWriter object.
writer = csv.DictWriter(csv_file,
fieldnames=["id","first_name", "car"],
restval='MISSING',
extrasaction='ignore')
The above code snippet asks for the fields id, first_name, and car while setting the restval equal to “MISSING”. Let’s see how our sample data would turn out.
# Sample Data
{
"id": 3,
"first_name": "Jon",
"last_name": "Smith",
"email": "jsmith@wisc.edu",
"gender": "Male",
"jobs": ["Advisor", "Pharmacist"]
}
# Output
id, first_name, car
3, Jon, MISSING
Thanks for reading. Please share this if you found it helpful and visit our archives for more content.