These are the notes for Data Analytics with Python, episode 13. In episode 12 we typed DataFrames in by hand. Real data lives in files, so here we load the common file formats into DataFrames and save results back: CSV with read_csv and its most useful options (sep, header, names, usecols, dtype, na_values, parse_dates, nrows, chunksize, encoding), Excel (read_excel, sheet_name, to_excel, the openpyxl engine), JSON (read_json, json_normalize), Parquet (read_parquet, to_parquet) and a preview of read_sql. Every term is defined the first time it appears and collected in the Glossary. Every program below was run for real (Python 3.13, pandas 3.0.5, openpyxl 3.1.5, pyarrow 26.0.0); outputs are unedited.
Why the programs create their own files. The SyntaxPilot compiler starts every run in an empty folder, so each example first writes the small file it then reads, exactly as a billing app would export it. On your own computer you would simply point
read_csvat a file that already exists.
What is a file format?
A file is a named chunk of data saved on a disk. A file format is the agreed way data is written inside it. Think of the different ways a bill can be written:
| Real life | Format | What it is |
|---|---|---|
| A plain paper slip, values separated by commas | CSV (Comma-Separated Values) | Plain text, one row per line, values split by a delimiter (usually a comma) |
| A ledger with a tab for each month | Excel .xlsx |
A workbook (the whole file) containing sheets (the tabs) |
| An order from a delivery app, with fields inside fields | JSON (JavaScript Object Notation) | Text made of key-value pairs and lists, which can be nested |
| A sealed box, packed tight, contents listed on the label | Parquet | A binary (not human-readable) file that stores every column's type and compresses the data |
The first line of a CSV usually holds the column names. That line is called the header.
The problem: splitting a CSV by hand
Ramesh's new billing app exports sales.csv. One item is called masala, chai, with a comma inside the name, so the app wraps it in quotes. The obvious plan, using open() and split() from episode 7, is to split every line on commas:
open("sales.csv", "w").write("""day,item,cups,price
Mon,chai,320,10
Tue,"masala, chai",280,15
Wed,chai,410,10
""")
for line in open("sales.csv"):
print(line.strip().split(","))
['day', 'item', 'cups', 'price']
['Mon', 'chai', '320', '10']
['Tue', '"masala', ' chai"', '280', '15']
['Wed', 'chai', '410', '10']
Two things went wrong:
- Quotes ignored. Tuesday's row has five pieces instead of four:
"masalaandchai". The quotes meant "this comma is part of the value", andsplitdoesn't know that. - No types.
'320'is text, not a number. Every value would need converting by hand.
Turning raw text into structured data is called parsing. Writing a correct CSV parser yourself is surprisingly hard, and you'd have to do it for every file. So nobody does.
read_csv: the fix
import pandas as pd
df = pd.read_csv("sales.csv")
print(df)
print(df.dtypes)
print(df["cups"].sum())
day item cups price
0 Mon chai 320 10
1 Tue masala, chai 280 15
2 Wed chai 410 10
day str
item str
cups int64
price int64
dtype: object
1010
pd.read_csv(path) reads a CSV file and returns a DataFrame:
- it understands quotes, so
masala, chaistays one value; - the header line becomes the column names;
- a default RangeIndex (0, 1, 2, ...) labels the rows;
- it infers (guesses) each column's type by looking at the values:
cupsandpriceareint64, so maths works (1010).
A path is the location of a file. "sales.csv" is a relative path: it is looked up in the current working directory (the folder your program or notebook runs in). A full path such as "C:/data/sales.csv" or "/home/asha/data/sales.csv" is an absolute path.
Messy files: sep, header, names, usecols
A second shop's app exports this (no header line, semicolons, and an extra note column):
2026-10-01;0042;chai;320;ok
2026-10-02;0042;samosa;-;ok
2026-10-03;0107;chai;410;late
What a plain read_csv does
open("export.csv", "w").write("""2026-10-01;0042;chai;320;ok
2026-10-02;0042;samosa;-;ok
2026-10-03;0107;chai;410;late
""")
import pandas as pd
df = pd.read_csv("export.csv")
print(df)
print(df.shape)
2026-10-01;0042;chai;320;ok
0 2026-10-02;0042;samosa;-;ok
1 2026-10-03;0107;chai;410;late
(2, 1)
No error, wrong result. read_csv looked for commas, found none, and made each whole line one value. It also treated the first data line as the header, so the 1 October sale became a column name and disappeared from the data.
Describing the file with parameters
A parameter is a named option a function accepts (episode 7). These four describe the file's layout:
| Parameter | Meaning | Here |
|---|---|---|
sep |
The separator (delimiter) between values | sep=";" (others: "\t" for tab, "\|" for pipe) |
header |
Which line holds the column names; None = there is no header line |
header=None |
names |
Column names to use | names=["date", "code", "item", "cups", "note"] |
usecols |
Keep only these columns (by name or position) | drop note |
df = pd.read_csv("export.csv", sep=";",
header=None,
names=["date", "code", "item", "cups", "note"],
usecols=["date", "code", "item", "cups"])
print(df)
print(df.dtypes)
print(df["cups"].sum())
date code item cups
0 2026-10-01 42 chai 320
1 2026-10-02 42 samosa -
2 2026-10-03 107 chai 410
date str
code int64
item str
cups str
dtype: object
320-410
The shape is right now, but three problems remain, and none raises an error:
code0042 became 42.read_csvguessed "number" and dropped the leading zeros. A code, a phone number or a roll number is a label, not an amount.- The dash
-(meaning "missing") made the wholecupscolumn text (str). - So
sum()joined the text:"320" + "-" + "410"=320-410. A silent wrong answer. dateis plain text, so you can't ask for its month or weekday.
dtype, na_values, parse_dates
| Parameter | Meaning | Example |
|---|---|---|
dtype |
Force a column's data type instead of letting pandas guess | dtype={"code": str} keeps 0042 |
na_values |
Extra strings that mean missing in this file; they become NaN | na_values=["-", "n/a"] |
parse_dates |
Convert these columns from text to real dates (datetime) | parse_dates=["date"] |
df = pd.read_csv("export.csv", sep=";",
header=None,
names=["date", "code", "item", "cups", "note"],
usecols=["date", "code", "item", "cups"],
dtype={"code": str},
na_values=["-"],
parse_dates=["date"])
print(df)
print(df.dtypes)
print(df["cups"].sum())
date code item cups
0 2026-10-01 0042 chai 320.0
1 2026-10-02 0042 samosa NaN
2 2026-10-03 0107 chai 410.0
date datetime64[us]
code str
item str
cups float64
dtype: object
730.0
codekeeps its zeros (str).- The dash became NaN (Not a Number, pandas' mark for a missing value).
cupsis now numeric,float64because NaN is a float (episode 12). dateisdatetime64[us]: a real date-and-time type (us= microsecond precision). Dates get a whole episode (22).sum()skips NaN by default:320 + 410 = 730.0.
Rule of thumb: tell read_csv what you know about the file. Don't let it guess.
Big files: nrows and chunksize
nrows=nreads only the first n rows: a cheap way to peek at a huge file, like tasting one spoon from the pot.chunksize=nmakesread_csvreturn an iterator (something you loop over) that yields the file in pieces of n rows, called chunks. Each chunk is an ordinary DataFrame. You process each one and keep a running result, so the whole file never has to fit in memory (RAM, the computer's short-term working space) at once.
import pandas as pd
rows = [f"{d},{d % 500}" for d in range(1, 100001)]
open("big.csv", "w").write("order,cups\n" + "\n".join(rows))
print(pd.read_csv("big.csv", nrows=3))
total = 0
for chunk in pd.read_csv("big.csv", chunksize=40000):
print(len(chunk), chunk["cups"].sum())
total += chunk["cups"].sum()
print("total:", total)
order cups
0 1 1
1 2 2
2 3 3
40000 9980000
40000 9980000
20000 4990000
total: 24950000
100,000 rows came in three chunks: 40,000 + 40,000 + 20,000. Sums, counts, minimums and maximums combine easily across chunks; an average needs the total sum and total count, not an average of averages.
Encoding: UnicodeDecodeError and encoding=
Computers store text as numbers. Each storage unit is a byte, a value from 0 to 255. An encoding is the code book that maps bytes to characters. A codec (coder-decoder) is the machine that applies one encoding.
- UTF-8 is today's standard encoding. It covers every language (Hindi, Tamil, emoji, everything) and is pandas' default.
- latin-1 (ISO-8859-1) and cp1252 (the old Windows Western encoding) are older one-byte encodings, still produced by some old programs and Excel exports.
- Unicode is the universal list of characters; UTF-8 is one way of writing Unicode as bytes.
Read latin-1 bytes with the UTF-8 code book and some bytes make no sense:
import sys; sys.tracebacklimit = 0
open("menu.csv", "w", encoding="latin-1").write(
"item,price\ncafé latte,90\nchai,10\n")
import pandas as pd
df = pd.read_csv("menu.csv")
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe9 in position 14: invalid continuation byte
(sys.tracebacklimit = 0 only hides pandas' long internal traceback, the list of function calls Python prints before an error, so the error line itself is easy to read.)
Byte 0xe9 (233 in hexadecimal) is how latin-1 writes é. In UTF-8 that byte must be followed by special "continuation" bytes, so the UTF-8 codec gives up. The fix is to name the right encoding:
df = pd.read_csv("menu.csv", encoding="latin-1")
print(df)
print(open("menu.csv", "rb").read())
item price
0 café latte 90
1 chai 10
b'item,price\ncaf\xe9 latte,90\nchai,10\n'
Opening with mode "rb" (read binary) shows the raw bytes: \xe9 is the é. If you don't know a file's encoding, try "utf-8", then "cp1252", then "latin-1". (latin-1 never fails, because every byte means some character, but if it's the wrong guess some characters come out wrong, so check the text.) Always save your own files as UTF-8.
Saving: to_csv and index=False
Every read_* function has a matching to_* method: to_csv, to_excel, to_json, to_parquet.
import pandas as pd
df = pd.DataFrame({"day": ["Mon", "Tue"], "cups": [320, 280]})
df.to_csv("out.csv")
print(open("out.csv").read())
print(pd.read_csv("out.csv"))
,day,cups
0,Mon,320
1,Tue,280
Unnamed: 0 day cups
0 0 Mon 320
1 1 Tue 280
By default to_csv writes the index as an unnamed first column. Read it back and it becomes Unnamed: 0. Save and reload a few times and these columns pile up. Fix:
df.to_csv("out.csv", index=False)
day,cups
Mon,320
Tue,280
day cups
0 Mon 320
1 Tue 280
Keep the index only when it carries real information (for example day after set_index("day")); then read it back with index_col="day".
Excel: read_excel, sheet_name, to_excel, openpyxl
pandas doesn't parse Excel files itself. It hands the job to another library, called an engine. For modern .xlsx files the engine is openpyxl; install it once with pip install openpyxl (episode 3 covered pip).
ExcelWriter opens one workbook so you can write several DataFrames into it, each as its own sheet. with (episode 7) makes sure the file is saved and closed.
import pandas as pd
oct_sales = pd.DataFrame({"day": ["Mon", "Tue"], "cups": [320, 280]})
nov_sales = pd.DataFrame({"day": ["Mon", "Tue"], "cups": [350, 300]})
with pd.ExcelWriter("sales.xlsx") as xl:
oct_sales.to_excel(xl, sheet_name="Oct", index=False)
nov_sales.to_excel(xl, sheet_name="Nov", index=False)
print(pd.read_excel("sales.xlsx"))
print(pd.read_excel("sales.xlsx", sheet_name="Nov"))
sheets = pd.read_excel("sales.xlsx", sheet_name=None)
print(list(sheets))
day cups
0 Mon 320
1 Tue 280
day cups
0 Mon 350
1 Tue 300
['Oct', 'Nov']
sheet_name= |
Returns |
|---|---|
omitted (default 0) |
The first sheet, as a DataFrame |
"Nov" |
The sheet with that name |
1 |
The sheet at position 1 (the second) |
None |
Every sheet, as a dict: sheet name → DataFrame |
A sheet that doesn't exist:
import sys; sys.tracebacklimit = 0
print(pd.read_excel("sales.xlsx", sheet_name="Dec"))
ValueError: Worksheet named 'Dec' not found
read_excel also accepts usecols, nrows, header, names, dtype and na_values, just like read_csv.
Limit to know: according to Microsoft's "Excel specifications and limits" page, a worksheet holds at most 1,048,576 rows by 16,384 columns. Excel is great for sharing reports with people; it is not a store for big data.
JSON: read_json and json_normalize
JSON is the text format apps and websites use to send data. It is built from objects { "key": value } (like Python dicts) and arrays [ ... ] (like Python lists). A list of records reads straight into a DataFrame:
open("orders.json", "w").write("""[
{"id": 1, "item": "chai", "qty": 2},
{"id": 2, "item": "bun", "qty": 1}
]""")
import pandas as pd
print(pd.read_json("orders.json"))
id item qty
0 1 chai 2
1 2 bun 1
Nested JSON
Nested data is data inside data: here each order has a customer, which is itself an object.
import pandas as pd
orders = [
{"id": 1, "item": "chai",
"customer": {"name": "Asha", "city": "Pune"}},
{"id": 2, "item": "bun",
"customer": {"name": "Vikram", "city": "Agra"}},
]
print(pd.DataFrame(orders))
print(pd.json_normalize(orders))
id item customer
0 1 chai {'name': 'Asha', 'city': 'Pune'}
1 2 bun {'name': 'Vikram', 'city': 'Agra'}
id item customer.name customer.city
0 1 chai Asha Pune
1 2 bun Vikram Agra
A plain DataFrame stuffs the whole dict into one cell, which you can't filter or group by. pd.json_normalize flattens nested fields into ordinary columns named parent.child. ("Normalize" here means "flatten into a plain table".) When you load JSON from a file, use json.load (episode 7) to get Python objects, then json_normalize.
To write JSON, df.to_json("file.json", orient="records") writes one object per row.
Parquet: to_parquet and read_parquet
CSV stores only text. Save dates and zero-padded codes to CSV and their types are lost:
import pandas as pd
df = pd.DataFrame({
"date": pd.to_datetime(["2026-10-01", "2026-10-02"]),
"code": ["0042", "0107"],
"cups": [320, 280],
})
df.to_csv("s.csv", index=False)
df.to_parquet("s.parquet")
print(pd.read_csv("s.csv").dtypes)
print(pd.read_parquet("s.parquet").dtypes)
date str
code int64
cups int64
dtype: object
date datetime64[us]
code str
cups int64
dtype: object
The Apache Parquet project describes Parquet as "an open source, column-oriented data file format designed for efficient data storage and retrieval". In practice:
- It keeps types. Dates come back as dates and
0042stays text, with no options needed. The file stores a schema: the list of column names and their types. - It is columnar. Values are stored column by column, so reading one column doesn't touch the others (
columns=[...]). - It is compressed: squeezed into fewer bytes.
pandas reads and writes Parquet through the pyarrow library (pip install pyarrow).
import pandas as pd, numpy as np, os
rng = np.random.default_rng(7)
n = 1_000_000
df = pd.DataFrame({
"item": rng.choice(["chai", "coffee", "samosa", "bun"], n),
"cups": rng.integers(1, 6, n),
})
df.to_csv("big.csv", index=False)
df.to_parquet("big.parquet")
print(os.path.getsize("big.csv") // 1024, "KB csv")
print(os.path.getsize("big.parquet") // 1024, "KB parquet")
print(pd.read_parquet("big.parquet", columns=["cups"]).shape)
7568 KB csv
622 KB parquet
(1000000, 1)
For a million random orders the Parquet file is about 12 times smaller than the CSV (the exact ratio depends on the data). os.path.getsize returns a file's size in bytes; // 1024 converts to kilobytes.
Which format should I use?
| Situation | Format |
|---|---|
| Sharing with anyone, any tool, small to medium data | CSV (UTF-8, index=False) |
| A report for people who use Excel | Excel .xlsx |
| Data from an app or web API | JSON (then json_normalize) |
| Your own saved, cleaned data, especially large | Parquet |
A preview: read_sql
A database is an organised store of tables, managed by software you talk to in SQL (Structured Query Language). A query is a question written in SQL. pd.read_sql(query, connection) runs a query and returns the answer as a DataFrame. Here a tiny database lives in memory, using Python's built-in sqlite3 module:
import sqlite3
import pandas as pd
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE sales (day TEXT, cups INT)")
con.execute("INSERT INTO sales VALUES ('Mon', 320), ('Tue', 280)")
df = pd.read_sql("SELECT * FROM sales WHERE cups > 300", con)
print(df)
day cups
0 Mon 320
A connection (con) is an open line to the database. SQL and databases have their own course on this channel; in this course we'll use read_sql again when we combine pandas with databases.
Mini project: load a messy POS export, save it clean
A POS (point-of-sale) system is the shop's billing machine. Its export is pipe-separated, uses - for a missing amount, and has a Cashier column we don't need:
open("pos.csv", "w").write("""Date|Item|Qty|Amount|Cashier
2026-10-01|chai|2|20|R
2026-10-01|samosa|3|-|R
2026-10-02|chai|4|40|S
2026-10-02|bun|2|30|S
""")
import pandas as pd
df = pd.read_csv("pos.csv", sep="|",
usecols=["Date", "Item", "Qty", "Amount"],
na_values=["-"], parse_dates=["Date"])
print(df)
df.info()
df.to_parquet("pos_clean.parquet")
print(pd.read_parquet("pos_clean.parquet")["Amount"].sum())
Date Item Qty Amount
0 2026-10-01 chai 2 20.0
1 2026-10-01 samosa 3 NaN
2 2026-10-02 chai 4 40.0
3 2026-10-02 bun 2 30.0
<class 'pandas.DataFrame'>
RangeIndex: 4 entries, 0 to 3
Data columns (total 4 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 Date 4 non-null datetime64[us]
1 Item 4 non-null str
2 Qty 4 non-null int64
3 Amount 3 non-null float64
dtypes: datetime64[us](1), float64(1), int64(1), str(1)
memory usage: 277.0 bytes
90.0
One call loaded it with the right types; info() immediately flags the missing amount (3 non-null of 4); the clean table is saved as Parquet and reads back with its types intact. Load messy, save clean is the daily rhythm of analysis.
Common mistakes
| Code | Real error / symptom | Why | Fix |
|---|---|---|---|
pd.read_csv("sales.csv") from the wrong folder |
FileNotFoundError: [Errno 2] No such file or directory: 'sales.csv' |
Relative paths are looked up in the current working directory | Check the folder; use an absolute path |
line.split(",") on a CSV |
(no error: values cut at quoted commas, all text) | split ignores quotes and types |
pd.read_csv |
read_csv on a ; file |
(no error: shape (2, 1)) |
Default separator is a comma | sep=";" |
| File with no header line | (no error: first row becomes column names) | Default header=0 |
header=None, names=[...] |
Codes like 0042 |
(no error: become 42) |
Type inference picked int | dtype={"code": str} |
- or n/a for missing |
(no error: column becomes str; sum() gives 320-410) |
Unknown missing markers are text | na_values=["-", "n/a"] |
| latin-1 file | UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe9 in position 14: invalid continuation byte |
Wrong encoding | encoding="latin-1" or "cp1252" |
df.to_csv("out.csv") |
(no error: Unnamed: 0 column when read back) |
The index was written | index=False |
read_excel(..., sheet_name="Dec") |
ValueError: Worksheet named 'Dec' not found |
Sheet names must match exactly | sheet_name=None to list them |
| Expecting dates from a CSV | (no error: date is str) |
CSV stores text only | parse_dates=[...], or save as Parquet |
Practice
1. A tab-separated file marks.txt has no header: name, marks, roll number. Missing marks are written -, and roll numbers have leading zeros. Load it correctly and print the average marks.
Solution
import pandas as pd
open("marks.txt", "w").write("""Asha\t81\t07
Ravi\t-\t12
Meena\t92\t03
""")
df = pd.read_csv("marks.txt", sep="\t", header=None,
names=["name", "marks", "roll"],
dtype={"roll": str}, na_values=["-"])
print(df)
print(df.dtypes)
print(df["marks"].mean())
name marks roll
0 Asha 81.0 07
1 Ravi NaN 12
2 Meena 92.0 03
name str
marks float64
roll str
dtype: object
86.5
"\t" is the tab character. mean() skips the NaN: (81 + 92) / 2 = 86.5.
2. Save a small DataFrame as CSV without the index and as JSON records, print both files, and read the JSON back.
Solution
import pandas as pd
df = pd.DataFrame({"city": ["Pune", "Agra"], "orders": [12, 7]})
df.to_csv("cities.csv", index=False)
df.to_json("cities.json", orient="records")
print(open("cities.csv").read())
print(open("cities.json").read())
print(pd.read_json("cities.json"))
city,orders
Pune,12
Agra,7
[{"city":"Pune","orders":12},{"city":"Agra","orders":7}]
city orders
0 Pune 12
1 Agra 7
3. Process a 50,000-row file in chunks of 20,000. Count the rows and find the largest visits value without loading the whole file at once.
Solution
import pandas as pd
rows = [f"{i},{i % 7}" for i in range(1, 50001)]
open("visits.csv", "w").write("id,visits\n" + "\n".join(rows))
biggest = 0
count = 0
for chunk in pd.read_csv("visits.csv", chunksize=20000):
count += len(chunk)
biggest = max(biggest, chunk["visits"].max())
print(count, biggest)
50000 6
4. Flatten users with a nested address into plain columns, and list the column names.
Solution
import pandas as pd
users = [
{"id": 1, "name": "Asha", "address": {"city": "Pune", "pin": "411001"}},
{"id": 2, "name": "Ravi", "address": {"city": "Agra", "pin": "282001"}},
]
flat = pd.json_normalize(users)
print(flat)
print(flat.columns.tolist())
id name address.city address.pin
0 1 Asha Pune 411001
1 2 Ravi Agra 282001
['id', 'name', 'address.city', 'address.pin']
5. Write two DataFrames into one workbook as sheets A and B, then read every sheet and print each sheet's name and row count.
Solution
import pandas as pd
a = pd.DataFrame({"x": [1, 2]})
b = pd.DataFrame({"x": [3]})
with pd.ExcelWriter("book.xlsx") as xl:
a.to_excel(xl, sheet_name="A", index=False)
b.to_excel(xl, sheet_name="B", index=False)
sheets = pd.read_excel("book.xlsx", sheet_name=None)
for name, df in sheets.items():
print(name, len(df))
A 2
B 1
sheet_name=None returns a dict, so .items() (episode 5) gives name and DataFrame pairs.
Quick quiz
# file a.csv contains:
# id;qty
# 007;5
df = pd.read_csv("a.csv", sep=";")
print(df["id"][0])
A. 7 B. 007 C. ParserError D. id;qty
Answer
A. 7. read_csv inferred that id is a number and dropped the leading zeros. dtype={"id": str} keeps 007.
Interview questions
- How do you read a CSV that uses semicolons and has no header?
pd.read_csv(path, sep=";", header=None, names=[...]). - Why might a numeric column load as text, and how do you fix it? A non-numeric marker for missing values (
-,n/a,?) or stray text; passna_values=[...](or clean it later withpd.to_numeric(..., errors="coerce"), episode 20). - How do you keep leading zeros in IDs or PIN-like codes? Read the column as text:
dtype={"col": str}. - How do you handle a file larger than memory?
chunksize=to process it in pieces,usecols=anddtype=to load less, or convert it once to Parquet and read only the needed columns. - What does
UnicodeDecodeError: 'utf-8' codec can't decode byte ...mean? The file isn't UTF-8; pass the rightencoding(oftencp1252orlatin-1for old Windows/Excel exports). - Why does
Unnamed: 0appear? The index was saved byto_csvand read back as a column; save withindex=False(or read withindex_col=0). - CSV vs Parquet? CSV is plain text, universal, human-readable, but stores no types and is large. Parquet is binary, columnar, compressed and keeps the schema, so it's smaller and faster to read; you need pyarrow and a tool that understands it.
- How do you read all sheets of an Excel file?
pd.read_excel(path, sheet_name=None)returns a dict of DataFrames. - How do you turn nested JSON into a table?
pd.json_normalize(records); nested keys becomeparent.childcolumns. - What does
parse_datesdo? Converts the listed columns from text todatetime64while reading, so date operations work.
Glossary
| Term | Meaning | Example |
|---|---|---|
| File | A named chunk of data saved on disk | sales.csv |
| File format | The agreed way data is written inside a file | CSV, JSON, Parquet |
| CSV | Comma-Separated Values: plain text, one row per line | Mon,chai,320 |
Delimiter / separator (sep) |
The character between values | , ; \t \| |
| Header | The line holding the column names | day,item,cups |
| Parsing | Reading text and turning it into structured data | read_csv parses CSV |
| Path | A file's location; relative (from the current folder) or absolute (full) | "data/sales.csv" |
| Current working directory | The folder a program runs in; relative paths start here | your notebook's folder |
| Parameter | A named option a function accepts | sep=";" |
read_csv |
Read a CSV file into a DataFrame | pd.read_csv("sales.csv") |
header=None |
The file has no header line | with names=[...] |
names |
Column names to use when reading | names=["day", "cups"] |
usecols |
Keep only these columns | usecols=["day", "cups"] |
dtype (parameter) |
Force column types instead of guessing | dtype={"code": str} |
| Type inference | pandas guessing a column's type from its values | "320" → int64 |
| Leading zeros | Zeros at the start of a code, lost if read as a number | 0042 |
na_values |
Strings that mean "missing" in this file | na_values=["-"] |
| NaN | Not a Number: pandas' missing-value marker | the dash after na_values |
parse_dates |
Convert text columns to dates while reading | parse_dates=["date"] |
datetime / datetime64 |
pandas' date-and-time type | 2026-10-01 |
nrows |
Read only the first n rows | nrows=3 |
chunksize |
Read a file in pieces of n rows | chunksize=40000 |
| Chunk | One piece of a file, as a DataFrame | 40,000 rows |
| Iterator | Something you loop over that hands out items one at a time | the chunk reader |
| Memory (RAM) | The computer's short-term working space | a file "too big for memory" |
| Byte | A storage unit holding a number from 0 to 255 | 0xe9 |
| Encoding | The code book mapping bytes to characters | UTF-8 |
| Codec | The coder-decoder that applies one encoding | 'utf-8' codec |
| Unicode | The universal list of characters in every language | é, अ, 😊 |
| UTF-8 | The standard encoding of Unicode; pandas' default | encoding="utf-8" |
| latin-1 / cp1252 | Older one-byte encodings from old programs | encoding="latin-1" |
UnicodeDecodeError |
Bytes that don't fit the encoding used to read them | reading latin-1 as UTF-8 |
Binary mode "rb" |
Open a file as raw bytes | open(f, "rb") |
| Traceback | The list of calls Python prints before an error | hidden with sys.tracebacklimit = 0 |
to_csv |
Save a DataFrame as CSV | df.to_csv("out.csv", index=False) |
index=False |
Don't write the index when saving | avoids Unnamed: 0 |
Unnamed: 0 |
The name pandas gives a saved, nameless index column | after to_csv without index=False |
| Excel workbook | A whole .xlsx file |
sales.xlsx |
| Sheet / worksheet | One tab inside a workbook | Oct |
read_excel |
Read a sheet (or all sheets) of an Excel file | pd.read_excel(f, sheet_name="Nov") |
sheet_name |
Which sheet to read; None = all, as a dict |
sheet_name=None |
to_excel / ExcelWriter |
Write DataFrames to Excel sheets | with pd.ExcelWriter(f) as xl: |
| Engine | A library pandas uses to do a job | openpyxl for .xlsx |
| openpyxl | The library that reads and writes .xlsx files |
pip install openpyxl |
| JSON | JavaScript Object Notation: keys, values and lists as text | {"id": 1} |
read_json |
Read a JSON file into a DataFrame | pd.read_json("orders.json") |
| Nested data | Data inside data | customer: {name, city} |
json_normalize |
Flatten nested records into plain columns | customer.city |
| Parquet | Open-source, columnar, compressed binary format that keeps types | df.to_parquet(f) |
| Binary file | A file of raw bytes, not readable text | .parquet |
| Columnar | Stored column by column, so one column can be read alone | columns=["cups"] |
| Compression | Storing data in fewer bytes | 7568 KB → 622 KB |
| Schema | The list of columns and their types stored with the data | Parquet's metadata |
| pyarrow | The library pandas uses for Parquet | pip install pyarrow |
read_parquet / to_parquet |
Read / write Parquet files | pd.read_parquet(f) |
| Database | An organised store of tables | SQLite, PostgreSQL |
| SQL / query | The language of databases / a question in it | SELECT * FROM sales |
| sqlite3 | Python's built-in module for small SQLite databases | sqlite3.connect(":memory:") |
| Connection | An open line to a database | con |
read_sql |
Run a SQL query and get a DataFrame | pd.read_sql(q, con) |
| POS | Point-of-sale: a shop's billing system | the POS export |
Summary
- Never split CSV by hand:
read_csvhandles quotes, headers and types. - Describe messy files with
sep,header=None,names,usecols; stop guessing withdtype,na_valuesandparse_dates. Many mistakes give wrong results with no error: checkdtypesafter loading. nrowspeeks;chunksizeprocesses files bigger than memory in pieces.UnicodeDecodeErrormeans the wrong encoding: trycp1252orlatin-1; save your own files as UTF-8.- Save with
to_csv(..., index=False)to avoidUnnamed: 0. - Excel:
read_excel/to_excel,sheet_name(None= all sheets), engine openpyxl, at most 1,048,576 rows per sheet. - JSON:
read_jsonfor records,json_normalizefor nested data. - Parquet keeps types, is columnar and compressed (about 12x smaller than CSV in our test): the best format for your own saved data.
read_sqlturns a SQL query into a DataFrame.- Next episode: pandas 4: selecting with
[],locandiloc.