Mathew K AnalyticsLesson 7 · Pandas Full Course
Read and Write CSV, Excel, JSON and SQL in Pandas (No More File Errors) | Pandas Course #7
In this lesson you'll be able to: Read CSV files with the right columns and real dates Save results to CSV and Excel, and read them back safely Turn a…
- CoursePandas Full Course
- Lesson7 of 65
- FormatJupyter notebook · 38 code cells
- Data1 dataset
What you'll learn
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
- superstore.csv preview1.7 MB
📓 Full notebook
Download .ipynbPandas 07: Importing and Exporting Data (CSV, Excel, JSON, SQL)#
In this lesson you'll be able to:
- Read CSV files with the right columns and real dates
- Save results to CSV and Excel, and read them back safely
- Turn a nested API-style JSON response into a flat table
- Query a SQL database straight into a DataFrame
- Fix file encoding errors when text looks broken Dataset: Superstore orders (data/pandas/superstore.csv)
Warm-up: load and explore#
Lesson 06 created and explored DataFrames. Same moves, real file.
import pandas as pd
sales = pd.read_csv("data/pandas/superstore.csv")
print(sales.shape)
sales[["Order ID", "Order Date", "Region", "Sales"]].head(3)
sales.dtypes.value_counts()
Read CSV files the smart way#
Problem: load only what you need, with dates as real dates.
print(sales["Order Date"].dtype)
print(sales["Order Date"].head(3).tolist())
path = "data/pandas/superstore.csv"
cols = ["Order ID", "Order Date", "Region", "Customer Segment",
"Sales", "Profit"]
orders = pd.read_csv(path, usecols=cols,
parse_dates=["Order Date"],
date_format="%m/%d/%Y")
orders.dtypes
full_mb = sales.memory_usage(deep=True).sum() / 1e6
slim_mb = orders.memory_usage(deep=True).sum() / 1e6
print(f"all columns: {full_mb:.1f} MB, six columns: {slim_mb:.1f} MB")
peek = pd.read_csv(path, nrows=5)
print(peek.shape)
Quick check: what date range do these orders cover?
print(orders["Order Date"].min())
print(orders["Order Date"].max())
Common mistake: a file path that doesn't match the folder
wrong = "data/superstore.csv"
pd.read_csv(wrong)
import os
print(os.path.exists(wrong))
print(os.path.exists(path))
print(sorted(os.listdir("data/pandas"))[:4])
Save results to CSV and read them back#
Problem: share a result as a file that opens cleanly anywhere.
os.makedirs("output/pandas", exist_ok=True)
top = orders.nlargest(5, "Sales")
top.to_csv("output/pandas/l07_top_orders.csv")
top
back = pd.read_csv("output/pandas/l07_top_orders.csv")
print(back.columns.tolist())
Quick check: where did the extra Unnamed: 0 column come from?
top.to_csv("output/pandas/l07_top_orders.csv", index=False)
back = pd.read_csv("output/pandas/l07_top_orders.csv",
parse_dates=["Order Date"])
print(back.columns.tolist())
print(back.equals(top.reset_index(drop=True)))
Your turn#
Save the 5 orders with the biggest losses to l07_losses.csv, without the index. Read it back and print its shape.
losses = orders.nsmallest(5, "Profit")
losses.to_csv("output/pandas/l07_losses.csv", index=False)
check = pd.read_csv("output/pandas/l07_losses.csv")
print(check.shape)
check[["Order ID", "Region", "Profit"]]
Write and read Excel workbooks#
Problem: one file with several sheets, for spreadsheet users.
xl_path = "output/pandas/l07_report.xlsx"
with pd.ExcelWriter(xl_path) as writer:
top.to_excel(writer, sheet_name="Top sales", index=False)
losses.to_excel(writer, sheet_name="Losses", index=False)
print("saved", os.path.getsize(xl_path), "bytes")
book = pd.read_excel(xl_path, sheet_name=None)
for name, sheet in book.items():
print(name, sheet.shape)
lost = pd.read_excel(xl_path, sheet_name="Losses")
lost[["Order ID", "Order Date", "Profit"]].head(3)
Common mistake: asking for a sheet name that isn't there
pd.read_excel(xl_path, sheet_name="losses")
print(pd.ExcelFile(xl_path).sheet_names)
pd.read_excel(xl_path, sheet_name="Losses").shape
Flatten JSON from an API#
Problem: web services send nested JSON, but pandas wants a flat table.
two = orders[["Order ID", "Region", "Sales"]].head(2)
print(two.to_json(orient="records", indent=2))
json_path = "output/pandas/l07_orders.json"
orders.to_json(json_path, orient="records", date_format="iso")
from_json = pd.read_json(json_path)
print(from_json.shape)
response = {"status": "ok", "orders": [
{"order_id": 3,
"customer": {"name": "Muhammed MacIntyre",
"segment": "Small Business"},
"items": [{"qty": 6, "sales": 261.54}]},
{"order_id": 293,
"customer": {"name": "Barry French", "segment": "Consumer"},
"items": [{"qty": 49, "sales": 10123.02},
{"qty": 27, "sales": 244.57}]},
]}
import json
api_path = "output/pandas/l07_api_response.json"
with open(api_path, "w") as f:
json.dump(response, f, indent=2)
pd.read_json(api_path)
with open(api_path) as f:
data = json.load(f)
pd.json_normalize(data["orders"])
Quick check: how can we get one row per item, with its order ID?
items = pd.json_normalize(data["orders"], record_path="items",
meta=["order_id", ["customer", "name"]])
items
Query a SQL database into pandas#
Problem: company data lives in databases, and SQL fetches it.
import sqlite3
conn = sqlite3.connect(":memory:")
rows = orders.to_sql("orders", conn, index=False)
print("rows written:", rows)
query = """
SELECT Region, ROUND(SUM(Sales)) AS total_sales
FROM orders
GROUP BY Region
ORDER BY total_sales DESC
"""
pd.read_sql(query, conn)
q = ("SELECT COUNT(*) AS n, AVG(Profit) AS avg_profit "
"FROM orders WHERE Region = ?")
pd.read_sql(q, conn, params=("Ontario",)).round(2)
Common mistake: a column name with a space, left unquoted
pd.read_sql("SELECT Customer Segment FROM orders LIMIT 3", conn)
fixed = 'SELECT "Customer Segment" FROM orders LIMIT 3'
pd.read_sql(fixed, conn)
Your turn#
Use SQL to count the orders in each customer segment, largest first.
seg = """
SELECT "Customer Segment" AS segment, COUNT(*) AS n_orders
FROM orders GROUP BY segment ORDER BY n_orders DESC"""
pd.read_sql(seg, conn)
Fix file encoding errors#
Problem: some files are saved in older encodings, like Latin-1.
names = sales[["Product Name"]].head(3)
old_path = "output/pandas/l07_latin1.csv"
names.to_csv(old_path, index=False, encoding="latin-1")
names
Common mistake: opening a Latin-1 file with the default UTF-8
pd.read_csv(old_path)
fixed_names = pd.read_csv(old_path, encoding="latin-1")
print(fixed_names.loc[2, "Product Name"])
Mini-project: a regional profit report#
Question: which region earns the least profit per dollar sold?
Plan: CSV in, SQL summary, Excel and JSON out, verify, conclude.
report_sql = """
SELECT Region, COUNT(*) AS orders,
ROUND(SUM(Sales)) AS sales,
ROUND(SUM(Profit)) AS profit,
ROUND(100 * SUM(Profit) / SUM(Sales), 1) AS margin_pct
FROM orders GROUP BY Region ORDER BY margin_pct
"""
report = pd.read_sql(report_sql, conn)
report
out_xl = "output/pandas/l07_region_report.xlsx"
report.to_excel(out_xl, sheet_name="Regions", index=False)
report.to_json("output/pandas/l07_region_report.json",
orient="records")
conn.close()
check = pd.read_excel(out_xl, sheet_name="Regions")
print(check.dtypes)
print((check == report).all().all())
import matplotlib.pyplot as plt
ax = check.plot(kind="barh", x="Region", y="margin_pct",
legend=False, figsize=(7, 4))
ax.set_title("Profit margin by region")
ax.set_xlabel("Profit as % of sales")
ax.set_ylabel("Region")
plt.show()
worst = check.iloc[0]
best = check.iloc[-1]
print(f"Weakest: {worst.Region} at {worst.margin_pct}% margin")
print(f"Strongest: {best.Region} at {best.margin_pct}% margin")
Recap#
- read_csv: usecols, parse_dates and nrows load exactly what you need
- to_csv(index=False), then always read the file back to check
- ExcelWriter writes many sheets; sheet_name=None reads them all
- json_normalize flattens nested API data; read_sql runs SQL
- A UnicodeDecodeError means: pass the right encoding
Challenges#
- Load only Province and Profit, then save the 10 biggest profits to Excel.
- Add a third sheet with the orders from Nunavut, and read all sheets back.
- Use SQL with a ? placeholder to find the total sales of one province.
What's next#
Lesson 08: Selecting Rows, Columns, and Subsets of Data Pick exactly the rows and columns you need with [], loc and iloc.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.
Related lessons
17 minLesson 7
How to Import and Export Data in Pandas: CSV, Excel, JSON, and SQL Explained
59 minLesson 5
Python Debugging for Beginners: Read Errors, Fix Bugs, Write Tests (Beginner Python Course, Part 5)
11 minLesson 6
How to Import and Export CSV, Excel, and JSON Files in Python for Data Management
18 minLesson 8