Working with CSV and Excel in Python: pandas and openpyxl

Working with CSV and Excel in Python: pandas and openpyxl

Python has several libraries for handling spreadsheets. pandas is the powerful option for data analysis and transformation; openpyxl lets you read/write .xlsx files with formatting, styles and formulas.

1. Read and write CSV with the csv module

import csv

data = [
    {"name": "Alice", "age": 28, "city": "New York"},
    {"name": "Bob",   "age": 34, "city": "London"},
    {"name": "Carol", "age": 22, "city": "Sydney"},
]

with open("people.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.DictWriter(f, fieldnames=["name", "age", "city"])
    writer.writeheader()
    writer.writerows(data)

with open("people.csv", "r", encoding="utf-8") as f:
    reader = csv.DictReader(f)
    for row in reader:
        print(f"{row['name']} ({row['age']}) — {row['city']}")

2. pandas: read CSV and Excel

pip install pandas openpyxl xlrd

import pandas as pd

df = pd.read_csv("people.csv")
print(df.head())
print(df.dtypes)

# With options
df = pd.read_csv(
    "data.csv",
    sep=";",
    encoding="latin-1",
    parse_dates=["date"],
    index_col=0,
)

df_excel = pd.read_excel("report.xlsx", sheet_name="Sales")
print(df_excel.shape)

3. Data exploration and cleaning

import pandas as pd

df = pd.DataFrame({
    "name":   ["Alice", "Bob", "Carol", "Alice", None],
    "age":    [28, 34, 22, 28, 45],
    "city":   ["New York", "London", "Sydney", "New York", "Berlin"],
    "salary": [35000, 42000, None, 35000, 55000],
})

print(df.info())
print(df.describe())
print(df.isnull().sum())

df["salary"] = df["salary"].fillna(df["salary"].mean())
df["name"]   = df["name"].fillna("Unknown")
df_no_dup    = df.drop_duplicates()
df["age"]    = df["age"].astype(int)

4. Filter, sort and group

import pandas as pd

df = pd.read_csv("people.csv")

over_25  = df[df["age"] > 25]
london   = df[df["city"] == "London"]
result   = df.query("age > 25 and city != 'London'")

sorted_df = df.sort_values("age", ascending=False)
by_city   = df.groupby("city")["salary"].agg(["mean", "min", "max", "count"])
print(by_city)

5. Transform data

import pandas as pd

df = pd.DataFrame({
    "name":   ["Alice Smith", "Bob Jones"],
    "salary": [35000, 42000],
    "date":   ["2025-01-15", "2025-03-20"],
})

df["monthly_salary"] = df["salary"] / 12
df["last_name"]      = df["name"].str.split().str[-1]

def salary_category(s):
    if s < 30000: return "low"
    if s < 50000: return "medium"
    return "high"

df["category"]    = df["salary"].apply(salary_category)
df["date"]        = pd.to_datetime(df["date"])
df["month"]       = df["date"].dt.month
df["day_of_week"] = df["date"].dt.day_name()

6. Export with pandas

import pandas as pd

df = pd.DataFrame({"A": [1, 2, 3], "B": [4, 5, 6]})

df.to_csv("output.csv", index=False)
df.to_excel("output.xlsx", index=False, sheet_name="Data")

with pd.ExcelWriter("multi_sheet.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Sheet1", index=False)
    df.to_excel(writer, sheet_name="Sheet2", index=False)

df.to_json("output.json", orient="records", indent=2)

7. openpyxl: formatting, styles and formulas

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active
ws.title = "Sales"

headers = ["Product", "Units", "Price", "Total"]
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font      = Font(bold=True, color="FFFFFF")
    cell.fill      = PatternFill("solid", fgColor="2E7D32")
    cell.alignment = Alignment(horizontal="center")

data = [("Keyboard", 50, 25.99), ("Mouse", 80, 12.50), ("Monitor", 30, 199.99)]
for row, (prod, units, price) in enumerate(data, 2):
    ws.cell(row=row, column=1, value=prod)
    ws.cell(row=row, column=2, value=units)
    ws.cell(row=row, column=3, value=price)
    ws.cell(row=row, column=4, value=f"=B{row}*C{row}")

for col in range(1, 5):
    ws.column_dimensions[get_column_letter(col)].width = 15

wb.save("sales.xlsx")

8. openpyxl: read and modify existing files

from openpyxl import load_workbook

wb = load_workbook("sales.xlsx")
ws = wb.active

for row in ws.iter_rows(min_row=2, values_only=True):
    product, units, price, total = row
    print(f"{product}: {units} × {price:.2f}")

ws["D5"] = "=B5*C5*1.2"

ws2 = wb.create_sheet("Summary")
ws2["A1"] = "Total Revenue"
ws2["B1"] = f"=SUM(Sales!D2:D{ws.max_row})"

wb.save("sales_modified.xlsx")

9. Convert CSV to formatted Excel

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill

df = pd.read_csv("data.csv")
df.to_excel("data_formatted.xlsx", index=False)

wb = load_workbook("data_formatted.xlsx")
ws = wb.active

for cell in ws[1]:
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="1565C0")

ws.freeze_panes    = "A2"
ws.auto_filter.ref = ws.dimensions

wb.save("data_formatted.xlsx")
print("CSV converted to formatted Excel.")

10. Large files: chunked reading

import pandas as pd

total_rows  = 0
total_price = 0

for chunk in pd.read_csv("large_file.csv", chunksize=10_000):
    valid        = chunk[chunk["price"] > 0]
    total_rows  += len(valid)
    total_price += valid["price"].sum()

print(f"Valid rows : {total_rows}")
print(f"Avg price  : {total_price / total_rows:.2f}")

When to use which tool

Related conversions

Document conversions that follow this topic naturally:

• PDF to DOCX
• DOCX to PDF
• MD to PDF
• HTML to PDF
• ODT to PDF
• PDF to TXT