Python - Working with CSV Files

Working with CSV Files in Python

Introduction

CSV (Comma-Separated Values) files are widely used for storing tabular data such as spreadsheets and databases in a simple, human-readable format. These files use commas to separate values and are supported by many applications including Microsoft Excel, Google Sheets, and most database software.

In Python, CSV files are handled efficiently using the built-in csv module. In addition, third-party libraries like pandas offer powerful tools for advanced CSV manipulation. This document provides an in-depth look at reading, writing, and processing CSV files in Python, with detailed examples and best practices.

Understanding CSV Files

Structure of a CSV File

A CSV file consists of rows and columns of data. Each line in the file represents a single row, and values are separated by commas (or other delimiters like semicolons or tabs).

Name, Age, City
Alice, 30, New York
Bob, 25, Los Angeles
Charlie, 35, Chicago

Common Uses of CSV Files

• Storing data exported from spreadsheets
• Exchanging information between different software
• Reading and writing configuration or log data

Working with the csv Module

Importing the Module

import csv

Reading CSV Files

Reading with csv.reader()

The csv.reader() function returns a reader object which can be iterated over to read lines in a CSV file.

import csv

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

Handling Headers

Often, CSV files include a header row. You can skip the header using next().

with open('data.csv', 'r') as file:
    reader = csv.reader(file)
    header = next(reader)  # Skip header
    for row in reader:
        print(row)

Specifying a Delimiter

You can specify a different delimiter (e.g., semicolon) using the delimiter parameter.

with open('semicolon_data.csv', 'r') as file:
    reader = csv.reader(file, delimiter=';')
    for row in reader:
        print(row)

Writing to CSV Files

Writing with csv.writer()

with open('output.csv', 'w', newline='') as file:
    writer = csv.writer(file)
    writer.writerow(['Name', 'Age', 'City'])
    writer.writerow(['Alice', 30, 'New York'])

Writing Multiple Rows

data = [
    ['Name', 'Age', 'City'],
    ['Bob', 25, 'Los Angeles'],
    ['Charlie', 35, 'Chicago']
]

with open('output.csv', 'w', newline='') as file:
    writer = csv.writer(file)
    writer.writerows(data)

Reading and Writing Using Dictionaries

csv.DictReader

DictReader reads CSV data into a dictionary where keys are from the header row.

with open('data.csv', 'r') as file:
    reader = csv.DictReader(file)
    for row in reader:
        print(row['Name'], row['Age'])

csv.DictWriter

DictWriter writes dictionaries to a CSV file using specified fieldnames.

data = [
    {'Name': 'Alice', 'Age': 30, 'City': 'New York'},
    {'Name': 'Bob', 'Age': 25, 'City': 'Los Angeles'}
]

with open('output.csv', 'w', newline='') as file:
    fieldnames = ['Name', 'Age', 'City']
    writer = csv.DictWriter(file, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerows(data)

Advanced CSV Operations

Appending Data

To append data without overwriting existing content, use mode 'a'.

with open('output.csv', 'a', newline='') as file:
    writer = csv.writer(file)
    writer.writerow(['David', 40, 'Miami'])

Filtering Data

with open('data.csv', 'r') as file:
    reader = csv.DictReader(file)
    for row in reader:
        if int(row['Age']) > 30:
            print(row)

Sorting CSV Data

import csv

with open('data.csv', 'r') as file:
    reader = list(csv.DictReader(file))
    sorted_data = sorted(reader, key=lambda x: int(x['Age']))

for row in sorted_data:
    print(row)

Modifying Data

with open('data.csv', 'r') as file:
    rows = list(csv.DictReader(file))

for row in rows:
    if row['Name'] == 'Alice':
        row['Age'] = '31'

with open('data.csv', 'w', newline='') as file:
    fieldnames = rows[0].keys()
    writer = csv.DictWriter(file, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerows(rows)

Using pandas for CSV Files

Introduction to pandas 

pandas is a powerful data analysis library in Python. It provides built-in functions for reading and writing CSV files in a tabular format.

Reading CSV with pandas.read_csv() 

import pandas as pd

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

Writing CSV with DataFrame.to_csv()

df.to_csv('output.csv', index=False)

Filtering and Analyzing with pandas

filtered_df = df[df['Age'] > 30]
print(filtered_df)

Sorting with pandas

sorted_df = df.sort_values('Age')
print(sorted_df)

Handling Special Cases

Different Delimiters

For CSV files with tabs or semicolons:

reader = csv.reader(file, delimiter=';')

df = pd.read_csv('data.tsv', delimiter='\t')

Quoting and Escaping

writer = csv.writer(file, quoting=csv.QUOTE_ALL)

Encoding Issues

Handle encoding using the encoding parameter:

with open('data.csv', 'r', encoding='utf-8') as file:
    reader = csv.reader(file)

Error Handling and Best Practices

Try-Except Blocks

try:
    with open('data.csv', 'r') as file:
        reader = csv.reader(file)
        for row in reader:
            print(row)
except FileNotFoundError:
    print("File not found.")

Using with Statement

Always use with  for proper file closing and error handling.

Real-World Applications

Storing User Data

CSV files are often used to store user registration or login info for small-scale applications.

Exporting Reports

Automated scripts often generate CSV reports for analysis.

Machine Learning Datasets

Many datasets for ML come in CSV format and are loaded using pandas.

Log Analysis

CSV logs are analyzed to monitor system behavior or user activity.

Comparison with Other Formats

CSV vs Excel

• CSV is plain text, Excel uses binary (XLSX)
• CSV is faster and more portable

CSV vs JSON

• CSV is flat, JSON supports nested structures
• JSON is more suited for APIs

Working with CSV files in Python is a fundamental skill for developers, analysts, and data scientists. Python's built-in csv module makes it easy to handle structured data, while third-party tools like pandas add advanced capabilities for data analysis and transformation.

Whether you're storing logs, processing spreadsheets, or analyzing big datasets, knowing how to read, write, filter, and modify CSV files effectively is an essential part of Python programming. With the knowledge gained from this guide, you should be well-equipped to tackle real-world tasks involving CSV file manipulation.