Read CSV, Excel, and JSON

Download the sample files from the banner, then in the workbench click Add files and attach them. read_csv looks in /uploads.

See what is attached

import os

print(os.listdir("/uploads"))

If the list is empty, the files are not attached yet. Download from this page, then Add files.

Basic CSV

df = pd.read_csv("sales.csv")
print(df)
print()
print(df.dtypes)

sales.csv already includes revenue. sales_log.csv does not — you will compute it.

Useful read_csv options

log = pd.read_csv(
    "sales_log.csv",
    parse_dates=["date"],
    usecols=["date", "city", "product", "units", "price"],
)
print(log.head())
print(log.dtypes)
print("rows:", len(log))

Other options worth knowing:

• sep=";" — European CSVs
• na_values=["n/a", "NA", ""] — extra missing tokens
• dtype={"product": "string"} — force a type
• nrows=5 — peek without loading a giant file
• encoding="utf-8" (default) or "latin-1" if you see decode errors

Both sales_log.csv and /uploads/sales_log.csv work.

Dirty CSV

messy = pd.read_csv("messy.csv")
print(messy)
print()
print(messy.dtypes)
print(messy["name"].tolist())

Names have padding; joined is still text; score has NA. Cleaning is the String, Dates, and Missing chapters — you will finish it in Practice.

Lookups

products = pd.read_csv("products.csv")
regions = pd.read_csv("regions.csv")
print(products)
print()
print(regions)

Excel

After openpyxl is ready and you have attached a .xlsx:

# xl = pd.read_excel("data.xlsx")
# xl = pd.read_excel("data.xlsx", sheet_name="Sales")
print("pd.read_excel works the same as read_csv once the file is in /uploads.")

JSON

payload = [{"city": "Nairobi", "units": 12}, {"city": "Mombasa", "units": 9}]
import json

# If you uploaded data.json:
# print(pd.read_json("data.json"))
print(pd.DataFrame(payload))
print(pd.read_json(json.dumps(payload)))

Records-oriented JSON becomes one row per object.