Reading CSV, Excel & JSON

Real data rarely starts in your code. It arrives as a CSV export from a shop's billing software, an Excel sheet from the accounts team, or JSON from a web API. pandas has a read_... function for each format and a matching to_... method to save your results.

In this lesson you will load the practice files, use the most useful options of pd.read_csv(), read JSON and Excel, and write data back out.

read_csv and its most useful options

CSV (comma-separated values) is the most common data format: plain text, one row per line, commas between values. pd.read_csv() reads it into a DataFrame and guesses each column's type.

import pandas as pd

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

(1217, 10)
order_id            str
order_date          str
city                str
state               str
category            str
product             str
quantity        float64
unit_price        int64
discount          int64
payment_mode        str
dtype: object

The guesses are mostly right: prices and discounts are integers and text columns are str. But order_date is text, not a date, and quantity is float64 even though quantities are whole numbers. That happens because a few quantity cells are empty, and missing numbers are stored as NaN, a float. You can guide the reader with options:

import pandas as pd

sales = pd.read_csv(
    "sales.csv",
    usecols=["order_id", "order_date", "city", "quantity", "unit_price"],
    parse_dates=["order_date"],
    index_col="order_id",
)
print(sales.head(3))
print(sales.dtypes)
print(sales.index[:3].tolist())

         order_date     city  quantity  unit_price
order_id                                          
ORD10001 2025-01-01   Mumbai       1.0         699
ORD10002 2025-01-01  Kolkata       1.0        1599
ORD10003 2025-01-01   Mumbai       1.0         699
order_date    datetime64[us]
city                     str
quantity             float64
unit_price             int64
dtype: object
['ORD10001', 'ORD10002', 'ORD10003']

Now order_date is a real date column, so you can extract months, filter by date ranges and resample, all covered in Dates & Time Series. The order IDs have become the index.

Telling pandas what "missing" looks like

Different sources mark missing data in different ways. In Indian school records, a student absent from an exam is often marked AB. Look at what that does to the marks columns in students.csv:

import pandas as pd

raw = pd.read_csv("students.csv")
print(raw[["maths", "science", "english"]].dtypes)

students = pd.read_csv("students.csv", na_values=["AB"])
print(students[["maths", "science", "english"]].dtypes)
print("Absent marks:", students[["maths", "science", "hindi"]].isna().sum().sum())
print("Average maths:", round(students["maths"].mean(), 2))

maths        str
science      str
english    int64
dtype: object
maths      float64
science    float64
english      int64
dtype: object
Absent marks: 9
Average maths: 58.15

Without help, a single AB turns the whole maths column into text, and you cannot calculate an average of text. With na_values=["AB"], those cells become NaN, the columns become numbers, and mean() skips the absent students. Empty cells and common markers such as NA and N/A are treated as missing automatically.

Reading text in other formats

Data does not always come as a neat file with commas. io.StringIO wraps a string so pandas can read it like a file, which is handy for small tests. This example has semicolons, Indian-style thousands separators and dates written day first.

import io
import pandas as pd

text = """shop;opened;turnover
Sharma Sweets;15-08-2019;"12,45,000"
Kochi Spice Co;02-01-2021;"8,90,500"
Pune Books;30-11-2023;"3,15,250"
"""

shops = pd.read_csv(io.StringIO(text), sep=";", thousands=",")
shops["opened"] = pd.to_datetime(shops["opened"], format="%d-%m-%Y")
print(shops)
print(shops.dtypes)

             shop     opened  turnover
0   Sharma Sweets 2019-08-15   1245000
1  Kochi Spice Co 2021-01-02    890500
2      Pune Books 2023-11-30    315250
shop                   str
opened      datetime64[us]
turnover             int64
dtype: object

thousands="," removed the commas, so 12,45,000 became the number 1245000. Dates in India are usually written day-month-year, while many tools assume month first. Giving an explicit format removes any doubt: 15-08-2019 is 15 August.

JSON from APIs

JSON is the usual format of web APIs. When the JSON is a list of flat records, pd.read_json() loads it directly. When records contain nested objects, pd.json_normalize() flattens them into columns with dotted names.

import io
import json
import pandas as pd

flat = '[{"city": "Pune", "aqi": 92}, {"city": "Delhi", "aqi": 241}, {"city": "Kochi", "aqi": 48}]'
print(pd.read_json(io.StringIO(flat)))

orders = json.loads("""[
  {"id": 1, "customer": {"name": "Isha", "city": "Lucknow"}, "total": 1299},
  {"id": 2, "customer": {"name": "Farhan", "city": "Jaipur"}, "total": 649}
]""")
print(pd.json_normalize(orders))

    city  aqi
0   Pune   92
1  Delhi  241
2  Kochi   48
   id  total customer.name customer.city
0   1   1299          Isha       Lucknow
1   2    649        Farhan        Jaipur

The nested customer object became two columns, customer.name and customer.city. Always pass JSON text through io.StringIO. Older pandas versions accepted a raw JSON string directly; pandas 3.0 treats any string as a file name or URL, so pd.read_json('[{"a": 1}]') fails with FileNotFoundError.

Excel files

pd.read_excel() reads .xlsx files. It needs the extra package openpyxl, which is not available in the browser editor, so this example is not runnable here. On your own computer, install it with uv add openpyxl or pip install openpyxl.

import pandas as pd

fees = pd.read_excel("fees_2025.xlsx", sheet_name="Class 10", skiprows=2)
all_sheets = pd.read_excel("fees_2025.xlsx", sheet_name=None)   # dict of DataFrames
print(list(all_sheets))
fees.to_excel("fees_clean.xlsx", index=False)

['Class 9', 'Class 10', 'Summary']

sheet_name picks a sheet by name or position, and sheet_name=None reads every sheet into a dictionary. skiprows skips title rows that often sit above the real table in office spreadsheets.

Writing data back out

Every reader has a writer. Save cleaned data so the next step, or the next person, does not repeat your work.

import pandas as pd

summary = pd.DataFrame({"city": ["Delhi", "Mumbai", "Kochi"], "orders": [187, 183, 73]})

summary.to_csv("city_orders.csv", index=False)
print(open("city_orders.csv", encoding="utf-8").read())

print(summary.to_json(orient="records"))
print(pd.read_csv("city_orders.csv").equals(summary))

city,orders
Delhi,187
Mumbai,183
Kochi,73

[{"city":"Delhi","orders":187},{"city":"Mumbai","orders":183},{"city":"Kochi","orders":73}]
True

index=False stops pandas from writing the 0, 1, 2 index as an extra unnamed column, the most common annoyance with CSV exports. Reading the file back gives an identical DataFrame.

Common mistakes

• Numbers read as text. One stray value such as AB, - or ₹1,299 makes the whole column str. Use na_values, thousands, or clean it as shown in Fixing Types, Duplicates & Text.
• Dates left as text. Use parse_dates or pd.to_datetime(), and give a format for day-first Indian dates.
• Extra index column. Saving without index=False adds an Unnamed: 0 column when you read the file again.
• Wrong encoding. Old Windows exports may not be UTF-8. If you see a UnicodeDecodeError, try encoding="cp1252" or ask for a UTF-8 export.

Key points

• pd.read_csv() loads CSV files; usecols, parse_dates, dtype, index_col and na_values control the result.
• One non-numeric marker turns a numeric column into text; declare it with na_values.
• io.StringIO lets pandas read a string like a file; pd.json_normalize() flattens nested JSON.
• pd.read_excel() needs openpyxl; sheet_name=None reads every sheet.
• Save with to_csv(index=False), to_json() or to_parquet().

import pandas as pd

weather = pd.read_csv("weather.csv", usecols=["date", "city", "rainfall_mm"], parse_dates=["date"])
mumbai = weather[weather["city"] == "Mumbai"]
print(len(mumbai), round(mumbai["rainfall_mm"].sum(), 1))

mumbai.to_csv("mumbai_rain.csv", index=False)
print(pd.read_csv("mumbai_rain.csv").head(3))

365 1766.3
         date    city  rainfall_mm
0  2025-01-01  Mumbai          0.0
1  2025-01-02  Mumbai          0.0
2  2025-01-03  Mumbai          0.0