SyntaxPilot
Courses/Data Analytics with Python/Lesson 13

Data Analytics with Python · Lesson 13 of 90 · 17 min

pandas 3: reading and writing CSV, Excel, JSON and Parquet

read_csv (sep, header, names, usecols, dtype, parse_dates, encoding, nrows, na_values)chunksizeread_excelsheet_nameopenpyxlread_jsonjson_normalizeread_parquetread_sql (preview)to_csvto_excelto_parquetindex=Falseencoding issues (UTF-8, latin-1)

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_csv at 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:

  1. Quotes ignored. Tuesday's row has five pieces instead of four: "masala and chai". The quotes meant "this comma is part of the value", and split doesn't know that.
  2. 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, chai stays 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: cups and price are int64, 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:

  1. code 0042 became 42. read_csv guessed "number" and dropped the leading zeros. A code, a phone number or a roll number is a label, not an amount.
  2. The dash - (meaning "missing") made the whole cups column text (str).
  3. So sum() joined the text: "320" + "-" + "410" = 320-410. A silent wrong answer.
  4. date is 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
  • code keeps its zeros (str).
  • The dash became NaN (Not a Number, pandas' mark for a missing value). cups is now numeric, float64 because NaN is a float (episode 12).
  • date is datetime64[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=n reads only the first n rows: a cheap way to peek at a huge file, like tasting one spoon from the pot.
  • chunksize=n makes read_csv return 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 0042 stays 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

  1. How do you read a CSV that uses semicolons and has no header? pd.read_csv(path, sep=";", header=None, names=[...]).
  2. 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; pass na_values=[...] (or clean it later with pd.to_numeric(..., errors="coerce"), episode 20).
  3. How do you keep leading zeros in IDs or PIN-like codes? Read the column as text: dtype={"col": str}.
  4. How do you handle a file larger than memory? chunksize= to process it in pieces, usecols= and dtype= to load less, or convert it once to Parquet and read only the needed columns.
  5. What does UnicodeDecodeError: 'utf-8' codec can't decode byte ... mean? The file isn't UTF-8; pass the right encoding (often cp1252 or latin-1 for old Windows/Excel exports).
  6. Why does Unnamed: 0 appear? The index was saved by to_csv and read back as a column; save with index=False (or read with index_col=0).
  7. 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.
  8. How do you read all sheets of an Excel file? pd.read_excel(path, sheet_name=None) returns a dict of DataFrames.
  9. How do you turn nested JSON into a table? pd.json_normalize(records); nested keys become parent.child columns.
  10. What does parse_dates do? Converts the listed columns from text to datetime64 while 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_csv handles quotes, headers and types.
  • Describe messy files with sep, header=None, names, usecols; stop guessing with dtype, na_values and parse_dates. Many mistakes give wrong results with no error: check dtypes after loading.
  • nrows peeks; chunksize processes files bigger than memory in pieces.
  • UnicodeDecodeError means the wrong encoding: try cp1252 or latin-1; save your own files as UTF-8.
  • Save with to_csv(..., index=False) to avoid Unnamed: 0.
  • Excel: read_excel / to_excel, sheet_name (None = all sheets), engine openpyxl, at most 1,048,576 rows per sheet.
  • JSON: read_json for records, json_normalize for 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_sql turns a SQL query into a DataFrame.
  • Next episode: pandas 4: selecting with [], loc and iloc.