Python CSV: Read and Write CSV files

Why This Matters

CSV (Comma Separated Values) is a common file format used to store tabular data, such as lists of products or student grades. In this tutorial, we'll learn how to read and write CSV files using Python. By the end, you'll be able to handle real-world tasks like importing and exporting data from Excel, debugging common issues, and answering interview questions.

Why This Matters

CSV files are widely used in various domains, including data analysis, web development, and education. Knowing how to read and write CSV files allows you to:

1. Import data from external sources for further processing or analysis.
2. Export your Python programs' results as a CSV file for easy sharing or import into other software like Excel or SQL databases.
3. Debug common issues that might arise when reading or writing CSV files, such as handling missing values or incorrect data types.
4. Demonstrate proficiency in Python during interviews by answering questions related to CSV I/O.

Prerequisites

To follow this tutorial, you should be familiar with the following:

1. Basic Python syntax and data structures (variables, lists, dictionaries)
2. Control flow statements (if-else, for loops, while loops)
3. Error handling using try-except blocks

Core Concept

Python provides several libraries to handle CSV files, but the most common one is csv. Let's explore how to read and write CSV files using this library.

Reading CSV files

To read a CSV file, you can use the csv.reader() function, which returns an iterator yielding rows from the CSV file as lists. Here's an example:

import csv

with open('example.csv', newline='') as csvfile:
reader = csv.reader(csvfile)
for row in reader:
print(row)

In this code, we first import the csv module. Then, we open the CSV file named 'example.csv' using the built-in open() function. The newline='' argument is necessary to prevent extra newlines from being added between rows. Next, we create a reader object by calling csv.reader(csvfile). Finally, we loop through each row in the CSV file and print it.

Writing CSV files

To write a CSV file, you can use the csv.writer() function, which returns a writer object that can be used to write rows to the CSV file. Here's an example:

import csv

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

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

In this code, we first create a list of lists called data, containing the header row and some sample data rows. Then, we open a new CSV file named 'output.csv' in write mode using the built-in open() function. We use the writerows() method of the writer object to write the entire data list to the CSV file.

Handling missing values and data types

When reading a CSV file, the csv.reader() function attempts to convert each field to the appropriate Python data type based on its content. However, it may encounter missing or malformed values that can cause issues. To handle these cases, you can use the next(csvfile) method to read and discard the first row (assuming it contains column headers).

import csv

with open('example.csv', newline='') as csvfile:
header = next(csvfile) # Discard the header row
reader = csv.reader(csvfile)
for row in reader:
try:
name, age, city = row
print(f'Name: {name}, Age: {age}, City: {city}')
except ValueError:
print('Invalid data format')

In this code, we first discard the header row using next(csvfile). Then, we loop through each row and attempt to unpack the fields into variables. If the unpacking fails (due to missing or malformed values), we catch the resulting ValueError exception and print an error message.

Worked Example

Let's read a CSV file containing student grades, process the data, and write the results to another CSV file.

import csv

def read_grades(filename):
with open(filename, newline='') as csvfile:
reader = csv.reader(csvfile)
header = next(csvfile) # Discard the header row
grades = []
for row in reader:
try:
name, grade = row
float_grade = float(grade)
if 0 < float_grade <= 100:
grades.append((name, float_grade))
else:
print(f'Invalid grade for {name}: {grade}')
except ValueError:
print(f'Invalid data format for {name}')
return grades

def write_grades(filename, grades):
with open(filename, 'w', newline='') as csvfile:
writer = csv.writer(csvfile)
writer.writerow(['Name', 'Grade'])
for name, grade in grades:
writer.writerow([name, grade])

grades_data = read_grades('student_grades.csv')
write_grades('processed_grades.csv', grades_data)

In this example, we define two functions: read_grades() and write_grades(). The read_grades() function reads a CSV file containing student names and grades, processes the data by validating the grade values, and returns a list of tuples containing valid entries. The write_grades() function writes a list of tuples to a new CSV file.

Common Mistakes

1. Forgetting to discard the header row when reading a CSV file: This can lead to unpredictable results, such as trying to access non-existent fields or encountering missing values where headers should be.
2. Not handling missing or malformed values appropriately: Failing to handle these cases can cause errors during data processing or result in incorrect data being written to the output file.
3. Ignoring the newline='' argument when opening CSV files: This can lead to extra newlines being added between rows, which may cause issues when reading or writing the file.
4. Not validating input data types: Failing to validate input data types can result in errors during processing, such as attempting to convert a non-numeric value to a float.
5. Not using try-except blocks to handle exceptions: This can make your code more robust and easier to debug when dealing with CSV files that may contain invalid or malformed data.

Practice Questions

1. Write a Python script that reads a CSV file containing employee names, salaries, and departments, and calculates the average salary for each department. Store the results in a new CSV file named 'department_averages.csv'.
2. Given the following CSV data:

Name, Age, City
Alice, 25, New York
Bob, 30, Los Angeles
Charlie, , San Francisco
David, 28, New York
Eve, ,
Frank, 35, Los Angeles

Write a Python script that reads this data and prints the names of employees who are missing either their age or city.

FAQ

Q: What should I do if my CSV file contains headers with commas?

A: You can use a different delimiter when reading the CSV file by passing it as an argument to csv.reader(). For example, to use a semicolon as the delimiter, you can call csv.reader(csvfile, delimiter=';').

Q: How can I read and write CSV files with quotes around fields that contain commas?

A: To handle fields containing commas, you can set the quoting argument of csv.reader() to csv.QUOTE_ALL or csv.QUOTE_MINIMAL. Similarly, when writing CSV files with quotes around fields, you can set the quoting argument of csv.writer() to one of these values.

Q: How can I read and write CSV files with different line endings (Windows vs. Unix)?

A: To handle CSV files with different line endings, you can pass the universal_newlines=True argument when opening the file using open(). This will ensure that all line endings are converted to Unix-style newline characters (\n) during reading and writing.

Q: How can I read a CSV file with a custom delimiter?

A: To read a CSV file with a custom delimiter, you can create a custom csv.reader() function that uses your desired delimiter instead of the default comma. Here's an example using a tab character as the delimiter:

def tab_delimited_reader(csvfile):
reader = csv.reader(csvfile, delimiter='\t')
return reader

Then, you can use this custom reader function instead of csv.reader() when reading the CSV file.