Python CSV File Manipulation: A Practical Guide with Examples — SitePoint

In this article, we’ll learn how to use Python to read from and write data to CSV files, and how to convert CSV files to JSON format and vice versa. We’ll explore how to use the csv module and also look at examples that help understand how it works.

A CSV (comma-separated values) file is a text file format that allows data to be saved in a tabular structure. This is a popular format used for exporting and importing data from databases and spreadsheets. As the name suggests, each piece of data in a CSV file is separated by a comma (,). Sometimes the term “CSV” can be used to describe formats with other types of separators, such as colons (:), semicolons (;) and tabs (\t). For the purposes of this article, we’ll just be dealing with CSV files that use commas as delimiters (known as RFC 4180).

When opened, the content of a CSV file looks like this:

1,Douglas,Male,8/6/1993,12:42 PM,,6.945,TRUE,Marketing
2,Thomas,Male,3/31/1996,6:53 AM,61933,4.17,,
3,Maria,Female,4/23/1993,11:17 AM,,11.858,FALSE,Finance
4,Jerry,Male,3/4/2005,1:00 PM,138705,9.34,,Finance

As seen above, the comma delimiter, ,, is used to separate each specific piece of data in the file. The first row of data may optionally serve as the header, identifying each column of data below it. CSV files are commonly saved with a .csv file extension.

The csv Module

Since spreadsheets and databases like MS SQL can be imported and exported as CSV files, it’s important to know how to handle data served in CSV format programmatically. Most programming languages like Python support handling files in CSV and also transforming them to other formats like JSON. Python provides the csv module for reading, writing and performing other forms of file handling in CSV formats. The in-built library provides functions and classes that make working with CSV files seamless.

How to Read CSV Files Using Python

The csv module has the csv.reader() function for reading CSV files. It’s used together with objects (including file objects) such as those produced with Python’s in-built open() function. Given a file object from a call to open(), csv.reader() will return a reader object. The reader object can be used to iterate over each line of CSV data, where rows are returned as a list of strings. Let’s take an example:

import csv

with open(’employees.csv’, newline=”) as file_obj:
reader_obj = csv.reader(file_obj)

for row in reader_obj:

Here’s the output of the code above:

[‘Employee Id’, ‘First Name’, ‘Gender’, ‘Start Date’, ‘Last Login Time’, ‘Salary’, ‘Bonus %’, ‘Senior Management’, ‘Team’]
[‘1’, ‘Douglas’, ‘Male’, ‘8/6/1993′, ’12:42 PM’, ”, ‘6.945’, ‘TRUE’, ‘Marketing’]
[‘2’, ‘Thomas’, ‘Male’, ‘3/31/1996’, ‘6:53 AM’, ‘61933’, ‘4.17’, ”, ”]
[‘3’, ‘Maria’, ‘Female’, ‘4/23/1993′, ’11:17 AM’, ”, ‘11.858’, ‘FALSE’, ‘Finance’]
[‘4’, ‘Jerry’, ‘Male’, ‘3/4/2005’, ‘1:00 PM’, ‘138705’, ‘9.34’, ”, ‘Finance’]
[‘5’, ‘Larry’, ‘Male’, ‘1/24/1998’, ‘4:47 PM’, ‘101004’, ‘1.389’, ‘TRUE’, ‘Client Services’]

From the first code snippet, the employees.csv file is opened, after which the csv.reader() function parses it and returns a reader object. A simple for loop is used to iterate over the reader object, which returns a list of data from each row from the employees.csv file, starting from the top.

How to Write to CSV Files Using Python

Besides reading data from CSV files, we can also write data to these files in Python. The csv.writer() function enables us to write data to CSV format. After opening the file in write mode, the csv.writer() function returns a writer object, which converts supplied data into delimited strings on the provided file object. The writer object has the writerow() method for writing a row — an iterable of strings or numbers of comma-separated values per time — while the writerows() method is used for multiple rows at once. The writerow() and writerows() methods are the only two options for writing data to a CSV file. All the list objects used in the code snippet above could be grouped into a 2D list and passed in as an argument to the writerows() method of the writer object to achieve the same result. After the with statement is executed, a CSV file (products.csv) is created in the current working directory containing these comma-separated values. Here’s an example:

import csv

with open(‘products.csv’, ‘w’, newline=”) as file_obj:
writer_obj = csv.writer(file_obj)

writer_obj.writerow([‘Product Name’, ‘Price’, ‘Quantity’, ‘SKU Number’ ])
writer_obj.writerow([‘Rice’, 80, 35, ‘RI59023’])
writer_obj.writerow([‘Curry’, 2, 200, ‘CY13890’])
writer_obj.writerow([‘Milk’, 9.5, 315, ‘MK10204′])

Here’s the output of the code above:

Product Name,Price,Quantity,SKU Number

How to Convert CSV to JSON Using Python

While performing file I/O operations, we might want to convert a CSV file to JSON format — which is popular for receiving and transmitting data between a client and a server. The csv module provides the csv.DictReader class to help us achieve this. The csv.DictReader class methods help to convert a given CSV file to a Python dictionary before applying the json module’s json.dump() function to convert the resulting Python dictionary to a JSON file. The csv.DictReader() class takes an optional fieldnames argument. When the field names are omitted, values from the first row will be mapped to the rest of the data as field names. Let’s take a look at an example:

import csv
import json

my_dict = {}

with open(’employees.csv’, newline=”) as file_obj:
reader_object = csv.DictReader(file_obj)

for row in reader_object:
key = row[‘Employee Id’]
my_dict[key] = row

with open(’employee.json’, ‘w’, encoding=’utf-8′) as file_obj:
json.dump(my_dict, file_obj, indent=4)

Here’s the output of the code above:

“1”: {
“Employee Id”: “1”,
“First Name”: “Douglas”,
“Gender”: “Male”,
“Start Date”: “8/6/1993”,
“Last Login Time”: “12:42 PM”,
“Salary”: “”,
“Bonus %”: “6.945”,
“Senior Management”: “TRUE”,
“Team”: “Marketing”
“2”: {
“Employee Id”: “2”,
“First Name”: “Thomas”,
“Gender”: “Male”,
“Start Date”: “3/31/1996”,
“Last Login Time”: “6:53 AM”,
“Salary”: “61933”,
“Bonus %”: “4.17”,
“Senior Management”: “”,
“Team”: “”


To convert a CSV file to a JSON equivalent, we applied the following steps:

1. Opened the employees.csv file in read mode
2. Created a Python dictionary from the returned file object using the csv.DictReader class
3. Opened a JSON file in write mode, such as employees.json (if no such file had existed, one would have been created)
4. Used the dump() function of the json module to convert the Python dictionary (my_dict) to a JSON file

How to Convert JSON to CSV Using Python

In this section, we’ll look at how to convert data from a JSON file to CSV format. To achieve this, we’ll use both the in-built csv and json Python modules. The json module’s json.load() function will help convert a JSON file to a Python dictionary,

Source link

Leave a Reply