Select a Subtopic

Day 15: Working with CSV and Excel Files

Let's dive into **Day 15** of the Python learning schedule, where we focus on **Working with CSV and Excel Files**. We'll break down the concepts, exercises, and some interactive examples.

Topics:

Reading and Writing CSV Files
Working with Excel Files (using `openpyxl` and `pandas`)

Exercise 1: Read and Display Data from a CSV File

Let's start by writing a program to read a CSV file and display its contents. Here's an example CSV file (`students.csv`):

import csv

# Read data from a CSV file
with open('students.csv', mode='r') as file:
    csv_reader = csv.reader(file)
    next(csv_reader)  # Skip the header row
    
    for row in csv_reader:
        print(row)

- **What this does**: It opens the `students.csv` file for reading, parses the file with `csv.reader()`, and prints the contents.

Exercise 2: Write Data to a CSV File

Next, we'll write some data to a new CSV file. Let's create a new file and write the same data as the previous example.

import csv

# Data to be written to the CSV file
data = [
    ["Name", "Age", "Grade"],
    ["Alice", 20, "A"],
    ["Bob", 22, "B"],
    ["Charlie", 23, "A"],
    ["David", 21, "C"]
]

# Write data to a new CSV file
with open('new_students.csv', mode='w', newline='') as file:
    csv_writer = csv.writer(file)
    csv_writer.writerows(data)

print("Data has been written to 'new_students.csv'")

- **What this does**: It opens a new CSV file (`new_students.csv`) for writing, writes the header and data rows into the file, and confirms the creation of the file.

Exercise 3: Working with Excel Files (Using openpyxl)

Now, let's look at how to work with Excel files using the `openpyxl` library. If you don't have it installed, use the following command:

pip install openpyxl

Here's an example of how to create and write to an Excel file using `openpyxl`:

from openpyxl import Workbook

# Create a new workbook and activate it
wb = Workbook()
ws = wb.active
ws.title = "Students"

# Add data to the Excel sheet
ws.append(["Name", "Age", "Grade"])
ws.append(["Alice", 20, "A"])
ws.append(["Bob", 22, "B"])
ws.append(["Charlie", 23, "A"])
ws.append(["David", 21, "C"])

# Save the workbook to a file
wb.save("students.xlsx")
print("Excel file 'students.xlsx' created.")

- **What this does**: Creates a new Excel workbook, adds data to a sheet titled "Students", and saves the file as `students.xlsx`.

Exercise 4: Using Pandas for Excel File Handling

The `pandas` library provides a more powerful way to handle both CSV and Excel files with just a few lines of code.

pip install pandas openpyxl

Here's how you can read and write Excel files using `pandas`:

import pandas as pd

# Load the Excel file into a DataFrame
df = pd.read_excel('students.xlsx')

# Display the DataFrame
print(df)

- **What this does**: Loads the `students.xlsx` file into a pandas DataFrame and displays it as a table.

To write data to an Excel file, use the following:

import pandas as pd

# Create a DataFrame
data = {
    "Name": ["Alice", "Bob", "Charlie", "David"],
    "Age": [20, 22, 23, 21],
    "Grade": ["A", "B", "A", "C"]
}

df = pd.DataFrame(data)

# Write the DataFrame to an Excel file
df.to_excel('students_pandas.xlsx', index=False)
print("Data has been written to 'students_pandas.xlsx'")

- **What this does**: Creates a pandas DataFrame and writes it to an Excel file called `students_pandas.xlsx`.

Summary

CSV Handling using Python's built-in `csv` module.
Excel File Handling using `openpyxl` and `pandas` libraries.
Advanced Excel Handling with `pandas` for easier data manipulation.