5 Best Ways to Read a CSV File in Python

by
5 Best Ways to Read a CSV File in Python

💡 Problem Formulation: Working with data often requires dealing with CSV files, which are a common format for storing tabular data. Python programmers need efficient methods to read these files and convert them into a format that’s easy to manipulate – lists, dictionaries, data frames, etc. This article demonstrates how to read a CSV file into Python using multiple methods, keeping in mind that the goal is to take a file named data.csv and transform its contents for further processing.

Method 1: Using the csv module

The built-in Python csv module provides functionality to both read from and write to CSV files. Using the module’s reader function, you can load a CSV into Python as a list of lists, with each inner list representing a row from the file.

Here’s an example:

import csv

with open('data.csv', 'r') as file:
    csv_reader = csv.reader(file)
    for row in csv_reader:
        print(row)

Output:

[['name', 'age', 'city'],
 ['John', '23', 'New York'],
 ['Anna', '28', 'Los Angeles'],
 ['Peter', '34', 'San Francisco']]

This script opens data.csv and reads its content using the csv.reader object. It iterates over each row and prints it, resulting in the output showing the data as a list of lists, where the first list contains the headers.

Method 2: Using the pandas library

The pandas library is a powerful tool that allows for easy data manipulation and analysis. Using pandas.read_csv(), one can quickly read a CSV file and store it in a DataFrame – a two-dimensional, size-mutable, and potentially heterogeneous tabular data structure with labeled axes.

Here’s an example:

import pandas as pd

df = pd.read_csv('data.csv')
print(df)

Output:

    name  age          city
0   John   23      New York
1   Anna   28   Los Angeles
2  Peter   34  San Francisco

This code snippet creates a pandas DataFrame from the CSV file, which can then be used for a plethora of data analysis tasks, like filtering, transformations, and visualizations.

Method 3: Using the csv module to read into a dictionary

For those who prefer working with dictionaries, the csv module’s DictReader function reads each row into an OrderedDict. The benefit of this approach is that you can access column values by their header names, which can make code more readable.

Here’s an example:

import csv

with open('data.csv', mode='r') as file:
    dict_reader = csv.DictReader(file)
    for row in dict_reader:
        print(row)

Output:

OrderedDict([('name', 'John'), ('age', '23'), ('city', 'New York')])
OrderedDict([('name', 'Anna'), ('age', '28'), ('city', 'Los Angeles')])
OrderedDict([('name', 'Peter'), ('age', '34'), ('city', 'San Francisco')])

In this code, each row from the CSV is converted to an OrderedDict where the keys are the column headers from the CSV file, making data access more intuitive.

Method 4: Using sqlite3 for Large CSV Files

For very large CSV files, you might want to use the sqlite3 module. This module allows you to load a CSV file into a SQL database and then execute SQL queries against the data. This approach provides powerful query capabilities and can handle large datasets efficiently.

Here’s an example:

import sqlite3
import csv

connection = sqlite3.connect(':memory:')
cursor = connection.cursor()

cursor.execute('CREATE TABLE data (name TEXT, age INTEGER, city TEXT)')
with open('data.csv', 'r') as file:
    dr = csv.DictReader(file) 
    to_db = [(i['name'], i['age'], i['city']) for i in dr]

cursor.executemany('INSERT INTO data (name, age, city) VALUES (?, ?, ?);', to_db)
connection.commit()

for row in cursor.execute('SELECT * FROM data'):
    print(row)

Output:

('John', 23, 'New York')
('Anna', 28, 'Los Angeles')
('Peter', 34, 'San Francisco')

The snippet above creates an in-memory SQL database, creates a table, and inserts the CSV data into the table. Then it queries the table and outputs the content. It’s a scalable way to handle large CSV files.

Bonus One-Liner Method 5: Using List Comprehension

For simplicity, you can use a one-liner with list comprehension and the csv module to read a CSV file into a list. However, this method reads the entire file into memory, so it may not be suitable for very large files.

Here’s an example:

csv_rows = [row for row in csv.reader(open('data.csv'))]
print(csv_rows)

Output:

[['name', 'age', 'city'],
 ['John', '23', 'New York'],
 ['Anna', '28', 'Los Angeles'],
 ['Peter', '34', 'San Francisco']]

This one-liner reads the data.csv file into a list of lists containing all the rows in the CSV file.

Summary/Discussion

  • Method 1: csv module. Great for basic CSV file operations. Provides a reader that is useful for small to medium-sized files. It can be slightly verbose for large datasets or when advanced data manipulation is needed.
  • Method 2: pandas library. Extremely versatile for data analysis. Ideal for complex operations and large datasets. Requires additional memory for creating DataFrames and may not be necessary for very simple file reads.
  • Method 3: csv with DictReader. Convenient when you need to reference data by column names. Introduces overhead of creating OrderedDictionaries and may be slower than simple list operations for large datasets.
  • Method 4: sqlite3 module. Offers an alternative approach for large or very large files, leveraging the efficiency of SQL databases. Requires understanding of SQL language which might not be suitable for beginners.
  • Method 5: List Comprehension. Super simple and quick for one-off scripts with small files. Not memory-efficient for large files and lacks the additional functionalities provided by the csv module.