🎯 Lesson Objective
To learn how to read, write, and manipulate Excel and CSV files using Python, enabling data handling for real-life applications.
🧩 1. Introduction
- CSV (
.csv) → Comma Separated Values, simple text format - Excel (
.xlsx) → Microsoft Excel workbook - Python libraries:
csv→ built-in module for CSVopenpyxl→ read/write Excel (.xlsx)pandas→ powerful library for both Excel and CSV
⚙️ 2. Working with CSV Files
Example 1 — Reading a CSV File
import csv
with open("data.csv", newline='') as csvfile:
reader = csv.reader(csvfile)
for row in reader:
print(row)
CSV Example (data.csv):
Name,Age,Grade
Ali,20,A
Sara,21,B
Output:
['Name', 'Age', 'Grade']
['Ali', '20', 'A']
['Sara', '21', 'B']
Example 2 — Writing to a CSV File
import csv
data = [
["Name", "Age", "Grade"],
["Ali", 20, "A"],
["Sara", 21, "B"]
]
with open("output.csv", "w", newline='') as csvfile:
writer = csv.writer(csvfile)
writer.writerows(data)
✅ Creates output.csv with the data.
Example 3 — Using DictReader and DictWriter
import csv
# Reading as dictionary
with open("data.csv") as csvfile:
reader = csv.DictReader(csvfile)
for row in reader:
print(row["Name"], row["Grade"])
# Writing as dictionary
with open("output_dict.csv", "w", newline='') as csvfile:
fieldnames = ["Name", "Age", "Grade"]
writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
writer.writeheader()
writer.writerow({"Name": "Ali", "Age": 20, "Grade": "A"})
🔹 3. Working with Excel Files using openpyxl
Example 4 — Reading an Excel File
from openpyxl import load_workbook
wb = load_workbook("students.xlsx")
sheet = wb.active
for row in sheet.iter_rows(values_only=True):
print(row)
Example 5 — Writing to an Excel File
from openpyxl import Workbook
wb = Workbook()
sheet = wb.active
sheet.title = "Students"
data = [
["Name", "Age", "Grade"],
["Ali", 20, "A"],
["Sara", 21, "B"]
]
for row in data:
sheet.append(row)
wb.save("students.xlsx")
Example 6 — Modifying Excel Data
from openpyxl import load_workbook
wb = load_workbook("students.xlsx")
sheet = wb.active
# Change grade of Ali
for row in sheet.iter_rows(min_row=2):
if row[0].value == "Ali":
row[2].value = "A+"
wb.save("students.xlsx")
🔹 4. Using pandas for Excel & CSV
Example 7 — Reading CSV with Pandas
import pandas as pd
df = pd.read_csv("data.csv")
print(df)
Output:
Name Age Grade
0 Ali 20 A
1 Sara 21 B
Example 8 — Writing CSV with Pandas
df.to_csv("output_pandas.csv", index=False)
Example 9 — Reading Excel with Pandas
df = pd.read_excel("students.xlsx", sheet_name="Students")
print(df)
Example 10 — Writing Excel with Pandas
df.to_excel("students_output.xlsx", index=False, sheet_name="Students")
🔹 5. Real-Life Example — Student Score Report
import pandas as pd
# Read student marks
df = pd.read_csv("marks.csv")
# Calculate total and grade
df["Total"] = df["Math"] + df["Science"] + df["English"]
df["Grade"] = df["Total"].apply(lambda x: "A" if x >= 270 else "B" if x >= 240 else "C")
# Save to Excel
df.to_excel("student_report.xlsx", index=False)
✅ Generates a ready-to-use Excel report with totals and grades.
Leave a Reply