PYTHON / PANDAS
Reading CSV, Excel, and JSON data
Load CSV, Excel, and JSON into DataFrames and steer the parser with dtype, parse_dates, na_values, sep, sheet_name, and json_normalize.
What you will learn
- Read a CSV from a path, URL, or io.StringIO and check dtypes right after loading
- Override bad inference at parse time with dtype, parse_dates, na_values, sep and decimal
- Pull every worksheet at once with read_excel(sheet_name=None), which returns a dict
- Flatten nested JSON objects into real columns with pd.json_normalize
Understanding Reading CSV, Excel, and JSON data
The pd.read_* functions are three different front doors into the same room. read_csv sees nothing but text and has to guess what every column means; read_excel hands the file to an engine (openpyxl for .xlsx) that already knows a cell holds a number or a date, and adds a second dimension the other formats lack — worksheets; read_json starts from a tiny type system (string, number, bool, null) but allows arbitrary nesting. All of them accept a filesystem path, a URL, or any file-like object, and all of them return a DataFrame — except read_excel with sheet_name=None, which returns a dict of DataFrames.
The part worth understanding is dtype inference. read_csv reads a column as raw text, then decides: if every field parses as a whole number it becomes int64, if any field is a recognised missing marker the column widens to float64 (because NumPy int64 has no slot for NaN), and if anything at all fails to parse the whole column stays object. That single rule explains most surprises: a dash in one row turns a numeric column into strings, and an ID column of 00713 loses its zeros the moment it is read as an integer. Arguments like dtype, na_values, parse_dates and converters intervene *during* parsing, which matters because some information (leading zeros, thousands separators, the original date text) cannot be recovered afterwards.
Format-specific arguments are just dialect settings on top of that. For CSV you tell the parser about the field separator (sep), the decimal mark and thousands grouping, junk lines (skiprows, comment), and encoding. For Excel you name the sheet — a string, a zero-based index, a list, or None for all of them — and often index_col=0 if the file was written with an index. For JSON you have to match the layout: read_json infers orient for a list of records, but nested objects arrive as Python dicts sitting in an object column, so pd.json_normalize is the tool that turns {"address": {"city": ...}} into an address.city column.
import io
import pandas as pd
csv_text = """order_id,placed_on,customer,units,total
1001,2024-03-01,Ada,3,74.50
1002,2024-03-01,Grace,1,19.99
1003,2024-03-04,Ada,-,45.00
"""
orders = pd.read_csv(
io.StringIO(csv_text),
dtype={"order_id": str},
parse_dates=["placed_on"],
na_values=["-"],
)
print(orders)
print()
print(orders.dtypes)Reading a file is a parsing step you control, and the reader's dtype guesses become your data's types unless you override them at load time.
Worked examples
Nested JSON and json_normalize
Shows that read_json leaves nested objects as Python dicts, while json_normalize turns them into flat columns.
import io
import json
import pandas as pd
payload = """
[
{"id": 1, "name": "Ada", "address": {"city": "London"}},
{"id": 2, "name": "Grace", "address": {"city": "Arlington"}}
]
"""
nested = pd.read_json(io.StringIO(payload))
print(nested)
print()
print(pd.json_normalize(json.loads(payload)))Example explained
Line 1io.StringIO wraps the text because pandas 2.1+ deprecates passing a raw JSON string to read_json.
Line 2read_json recognises the list-of-records layout, but the address column holds dict objects, so you cannot filter or group on the city.
Line 3json_normalize walks the nested structure and builds one column per leaf, joining the keys with a dot into address.city.
Line 4json_normalize takes already-parsed Python objects, which is why json.loads is called first.
Every sheet of a workbook at once
Demonstrates that read_excel with sheet_name=None returns a dict keyed by sheet name rather than a DataFrame.
import pandas as pd
path = "sales.xlsx"
with pd.ExcelWriter(path) as writer:
pd.DataFrame({"region": ["north", "south"], "units": [12, 9]}).to_excel(
writer, sheet_name="2023", index=False
)
pd.DataFrame({"region": ["north", "south"], "units": [15, 11]}).to_excel(
writer, sheet_name="2024", index=False
)
books = pd.read_excel(path, sheet_name=None)
print(type(books), list(books))
print(books["2024"])Example explained
Line 1pd.ExcelWriter as a context manager keeps one workbook open so both to_excel calls land in the same file.
Line 2index=False stops the 0,1 index from being written as an unnamed first column that you would have to strip on the way back in.
Line 3sheet_name=None means "all sheets", so books is a plain dict; sheet_name="2024" or sheet_name=1 would give a single DataFrame.
Line 4Sheet names are always strings, so books[2024] with an integer key raises KeyError.
Semicolon CSV with comma decimals
Parses a European-style export where the separator, decimal mark, and thousands grouping all differ from the defaults.
import io
import pandas as pd
raw = """# exported 2024-05-01
city;population;area_km2
Berlin;3.645.000;891,7
Madrid;3.223.000;604,3
"""
df = pd.read_csv(
io.StringIO(raw),
sep=";",
skiprows=1,
thousands=".",
decimal=",",
)
print(df)
print(df.dtypes.to_dict())Example explained
Line 1skiprows=1 drops the export banner so the second physical line is used as the header row.
Line 2thousands="." strips the grouping dots before conversion, which is why population lands as int64 instead of object.
Line 3decimal="," tells the parser that 891,7 is one number, not a truncated field; without it the column would be strings.
Line 4Getting these right at read time avoids a manual str.replace pass that would have to be re-run on every new export.
Important notes
read_excel needs an engine installed: openpyxl for .xlsx/.xlsm, xlrd for legacy .xls, odfpy for .ods. Pandas raises ImportError naming the package it wants.
read_csv treats a plain string argument as a filename, so in-memory CSV text must be wrapped in io.StringIO; read_json still accepts a literal string but warns since pandas 2.1.
Common mistakes
Letting an ID column like 00713 be inferred as int64: the leading zeros are gone by the time the DataFrame exists and no later astype(str) can bring them back. Pass dtype={'id': str} while reading.
Assuming read_csv understands dates. Without parse_dates the column is object strings, so .dt accessors raise AttributeError and comparisons sort '2024-10-01' before '2024-9-01' lexicographically.
Calling .head() on the result of read_excel(path, sheet_name=None) and getting AttributeError, because that call returns a dict of DataFrames, not one DataFrame.
Try it yourself
Change, predict, then run
Wrap this text in io.StringIO and read it so member_id keeps its leading zeros and joined is a real datetime: "member_id,joined,visits\n007,2024-01-05,4\n013,2024-02-11,9". Print df.dtypes and confirm you get object, datetime64[ns], int64.
Open the Python workspaceCheck your understanding
A CSV column contains the values 3, 1 and - (a dash meaning missing). You read it with na_values=['-'] and the column comes back as float64 rather than int64. Why?
- read_csv always produces float64 for numeric columns unless you pass an explicit dtype
- The dash becomes NaN, which is a float value, and a NumPy int64 column has no way to store a missing value
- The dash character makes the parser treat the whole column as decimal text
- Pandas only infers int64 once a file has more than 1000 rows to sample
Show answer
NaN is a float64 value, so as soon as one entry is missing pandas widens the whole column from int64 to float64; if you need whole numbers plus missing values, read with dtype='Int64' (the nullable integer type). Option 0 is wrong because the same column with no missing entries is inferred as int64 — the widening is caused by the NaN, not by a blanket default.