Loading and Storing Data in pandas: CSV, Excel, JSON and SQL

A CSV file is plain text organized into records and fields. Commas usually separate fields, and the first record often supplies column names. The file contains characters, not a pandas schema, so reading it also means deciding what those characters represent.

Load and inspect a small file

Suppose a file contains:

order_id,date,region,units,price
1001,2026-03-02,North,3,4.50
1002,2026-03-02,South,1,12.00
1003,2026-03-03,North,2,7.25

Read it and inspect the result:

import pandas as pd

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

The shape is (3, 5): three data rows and five columns. pandas infers int64 for order_id and units, and float64 for price. The date column still contains text. In pandas 3, inferred text columns normally show str; older pandas versions often show object.

Each inspection answers a different question. head() shows whether fields line up with their labels. shape exposes missing rows or an unexpected extra column. dtypes checks interpretation, and info() adds non-null counts. A column intended for numbers that loads as text deserves a look at the raw values: a unit, a placeholder or a different decimal convention can stop numeric inference.

Match labels to records

For a headerless file whose first line is 1001,North,3, use:

df = pd.read_csv(
    "orders.txt",
    header=None,
    names=["id", "region", "units"]
)

Without that choice, pandas takes the first record as the header. Its values become column names and the record disappears from the data. Use index_col="order_id" when an existing column should label rows. Use usecols=["region", "units"] to read just those columns, or nrows=1000 to inspect the first 1,000 data rows before loading a large file.

Let the CSV reader handle quotes

The record "Smith, Jane",Boston,10 has three fields. The comma inside the quoted name belongs to that name. Two double quotes inside a quoted field represent one literal double quote: "O'Neil ""Red""" becomes O'Neil "Red". A quoted field can even contain a line break, so records and physical lines do not always match.

Calling line.split(",") cuts the name apart and cannot handle these rules. read_csv handles them as part of parsing. pd.read_table uses tab as its default separator; pd.read_fwf handles fields defined by character positions.

Find the file Python is looking for

A relative path is resolved from the program's current working directory. If "data/sales.csv" cannot be found, inspect it with Path.cwd() after from pathlib import Path. An absolute path, a Path object, a URL or an in-memory text file such as StringIO can also be passed to read_csv. Check the location before changing parsing options: a missing file and a badly parsed file are different problems.

A delimiter separates fields; a decimal mark separates the whole and fractional parts of a number. A thousands separator groups digits. These three jobs need separate settings, even when the same symbol is used for different jobs in different files.

Inspect characters before choosing options

Look at a few raw lines in an editor. A .csv extension does not guarantee commas, and .txt does not identify a layout. Common settings are:

You can inspect the beginning without reading the whole file:

with open("products.csv", encoding="utf-8") as f:
    print(f.readline())
    print(f.readline())

Work through a regional export

Consider this text:

product;price;qty
Tea;3,50;1.200
Coffee;12,90;850

The semicolons divide fields, the comma is the decimal mark, and the point groups thousands. Read all three conventions explicitly:

df = pd.read_csv(
    "products.csv",
    sep=";",
    decimal=",",
    thousands="."
)

The prices become 3.5 and 12.9. The quantities become 1200 and 850. With the default comma separator, this header does not describe three comma-separated columns. Even after choosing sep=";", the job is unfinished: the prices remain text and "1.200" can become the decimal 1.2. That last result is a valid number with the wrong meaning, so no parsing exception is needed for a serious error.

Compare a few loaded values with their source text and inspect describe() for implausible ranges. A quantity expected in the thousands that appears near one points toward a number-convention problem. Changing the delimiter alone will not repair it.

Keep field boundaries separate from number grouping

In a comma-delimited file, a grouped number such as "1,234.50" must be quoted. Its internal comma is part of the number. With thousands=",", pandas can read the quoted field as 1234.5. Without quotes, the comma also creates a field boundary; thousands cannot restore the lost record structure.

Choose a compatible parsing engine

The default C engine handles common layouts quickly. The Python engine supports separator detection with sep=None, regular-expression separators and skipfooter. Detection examines a sample and can choose badly on an irregular report, so state a known separator directly.

A separator longer than one character is generally treated as a regular expression and requires the Python engine; r"\s+" is a special case also supported by the C engine. The PyArrow engine can read with parallel threads, but supports a different set of options. For example, chunksize is unsupported with engine="pyarrow". Choose the engine for the options your file needs, then check the result's structure and values.

Type inference cannot know whether 999 is a measurement or a code for an unknown value. It also cannot know whether leading zeros carry meaning. Use the data's definitions to set reading options before inference changes those values.

Declare missing markers where they apply

pandas recognizes empty fields and built-in markers such as "NA", "N/A", "n/a", "NULL", "NaN" and "None". A source can use additional markers. Suppose score contains 88, "n/a", "-" and 999, with 999 defined as unknown:

df = pd.read_csv(
    "scores.csv",
    na_values={"score": ["-", "999"]}
)

The score column now contains 88.0 and three missing values. Without declaring the markers, "-" can keep the column as text; an undeclared numeric sentinel can enter calculations. The mean of 88 and 999 is 543.5, although the only known score is 88.

A dictionary confines a marker to its column. A file-wide na_values=["999"] would also remove a real product code 999. If "NA" is a real label, use keep_default_na=False to turn off the built-in marker list and explicitly list the markers that do mean missing. With that setting, include "" if empty fields must still count as missing.

Preserve values according to their role

Read identifiers as text:

df = pd.read_csv(
    "visits.csv",
    dtype={"site_code": str, "visits": "Int64"}
)

A code such as "00501" must stay five characters long. Reading it as an integer gives 501; converting that integer to text later produces "501", not the original code. Use a numeric type for values you calculate with.

Ordinary NumPy integer columns cannot hold NaN, so a count column with a gap often becomes float64. pandas's nullable "Int64", with a capital I, keeps integer values and represents a gap with <NA>. dtype_backend="numpy_nullable" requests nullable types across the result. For a small repeated set of labels, dtype={"region": "category"} stores the labels as categories and can reduce memory use.

Specify how dates are written

Date-looking text remains text until parsed. For day-first dates such as "03/04/2026" and "13/04/2026", use:

df = pd.read_csv(
    "events.csv",
    parse_dates=["day"],
    date_format="%d/%m/%Y"
)

These values become April 3 and April 13. Without a format, a month-first guess can either swap valid days and months or fail to convert the column. dayfirst=True expresses a preference; an explicit format states the expected structure. ISO dates such as "2026-04-03" avoid day/month ambiguity.

After reading, inspect dtypes, isna().sum() and the row count. Check a date whose interpretation distinguishes the conventions. A column still showing text means parsing did not produce the intended datetime values; a missing count that rises unexpectedly can reveal a real label mistaken for a marker.

A report can mix a table with titles, blank lines, comments, totals and source notes. First locate the header and the data boundaries. Otherwise a reader can mistake a title for column names or include a total as an ordinary observation.

Read only the table

A report contains these nine physical lines:

Monthly sales report
Exported 2026-04-01

region,jan,feb
North,120,135
# test row below
South,98,101
Total,218,236
Source: internal system

The first three lines precede the header. The last two are not observations. Read it with:

df = pd.read_csv(
    "report.csv",
    skiprows=3,
    comment="#",
    skipfooter=2,
    engine="python"
)

The result has two rows. January totals 218 from North and South. Keeping the total row would add another 218 and report 436. With the defaults, the one-field title can instead become the header and lead to a parsing error when three-field records arrive.

skiprows=3 counts the first three physical lines, including the blank line. A list such as skiprows=[0, 2] removes particular zero-based line positions. Blank lines are skipped by default, and comment="#" removes comment text. skipfooter needs the Python engine. Match these settings to the actual layout; a fixed skip count can stop being correct when an exporter adds a title line.

Investigate malformed records

A record with too many fields can trigger on_bad_lines="error", the default. "warn" skips it with a warning, while "skip" drops it silently. Before choosing either, inspect the raw record. An unquoted comma inside a name or a wrong delimiter can create extra fields; skipping then loses a valid observation instead of repairing the cause.

Too few fields behave differently. Under id,name,score, the record 2,Ben loads with a missing score. It need not raise an error. Check missing counts as well as row counts, and compare the kept records with the source when excluding malformed lines.

Read stacked headers or character positions

Two rows of column labels can form a MultiIndex. With group labels region,sales,sales above ,2025,2026, header=[0, 1] produces labels such as ("sales", "2025"). If flat labels suit the analysis better, skip both header rows and supply names=["region", "sales_2025", "sales_2026"].

Fixed-width text has no delimiter. Column boundaries are character positions:

df = pd.read_fwf(
    "readings.txt",
    colspecs=[(0, 4), (4, 11), (11, 16)]
)

These half-open ranges include each start and exclude its end. The first field occupies positions 0 through 3, and the next starts at 4. widths=[4, 7, 5] describes the same layout. Inference is convenient for regular samples; explicit boundaries prevent a short early value from defining the wrong width.

A file stores bytes, each with a value from 0 to 255. An encoding defines how characters are represented by those bytes. Encoding converts Python text to bytes; decoding converts bytes back to text. The same bytes can mean different characters under different decoding rules.

Count characters and bytes separately

text = "café"
raw = text.encode("utf-8")
print(len(text))  # 4 characters
print(len(raw))   # 5 bytes
print(raw.decode("utf-8"))  # café

The accent does not make a second visible character here. The letter "é" needs two bytes in UTF-8, so four characters occupy five bytes. A byte count and a character count answer different questions.

ASCII defines 128 characters, including basic English letters, digits, punctuation and control codes. Their byte values agree across ASCII, UTF-8, Latin-1 and Windows-1252. That shared beginning explains why an English-only sample can look correct even when the encoding setting is wrong.

Recognize the main encoding families

UTF-8 represents every Unicode character and is the usual choice for exchanged text. Latin-1 and Windows-1252 represent a smaller set of characters. They agree on many accented letters but differ in part of the byte range: Windows-1252 uses byte 0x80 for "€", where Latin-1 has a control character. They are not interchangeable names for one encoding.

Read byte order marks correctly

A byte order mark, or BOM, is a marker at the start of some files. UTF-16 can begin with FF FE for little-endian order or FE FF for big-endian order. encoding="utf-16" uses the BOM to select the order. Without a BOM, specify "utf-16-le" or "utf-16-be" according to the source.

A UTF-8 BOM is EF BB BF. Python's open(..., encoding="utf-8-sig") removes it while decoding. Plain "utf-8" in open() retains it as \ufeff, which can unexpectedly become part of the first header name. pandas's CSV reader also handles a leading UTF-8 BOM, but choosing utf-8-sig makes the intended file convention clear.

Make the encoding explicit

with open("notes.txt", encoding="utf-8") as f:
    text = f.read()

The default of open() depends on Python's version and UTF-8 mode. Python 3.11–3.14 can use the locale's encoding; Python 3.15 enables UTF-8 mode by default, with an opt-out. An explicit encoding keeps the script tied to the file's actual format across computers. It also prevents a platform default from silently changing how the same bytes are read.

A wrong encoding can fail loudly with UnicodeDecodeError, or quietly produce readable-looking nonsense called mojibake. Both problems start with interpreting bytes under the wrong rule. Keep the original file so you can decode it again.

Diagnose an error from the source

A CSV saved as Windows-1252 contains city names including "Zürich" and "Málaga". Reading it as UTF-8 can fail at byte 0xfc, used for "ü" in Windows-1252. Once the source's export settings confirm its encoding, read it with:

df = pd.read_csv("cities.csv", encoding="cp1252")
print(df["city"].head())

Inspect names containing accents or symbols. A sample made entirely of ASCII characters cannot distinguish these encodings. A particular invalid UTF-8 byte suggests a possible legacy encoding; it does not uniquely identify which legacy encoding produced the file.

Recognize an error that did not raise

UTF-8 bytes decoded as Windows-1252 can turn "café" into "café" and "São Paulo" into "São Paulo". The reader has produced characters successfully, but they are the wrong characters. Read the original bytes again using UTF-8:

df = pd.read_csv("cities.csv", encoding="utf-8")

Search-and-replace repairs one visible symptom at a time and can miss other damaged letters. It also risks changing a genuine occurrence of a character. Correct decoding addresses the cause for the whole file.

Gather evidence for the encoding

Start with the supplier's documentation or export settings. Inspect a small byte sample if needed:

with open("cities.csv", "rb") as f:
    sample = f.read(200)
print(sample)

Binary mode preserves the bytes without decoding. A BOM can give a direct clue. A detector such as charset-normalizer estimates a likely encoding, but a short sample may fit several candidates. Test the result against known accented names and symbols.

Latin-1 assigns a character to every byte, so it can decode any byte sequence without an error. That success alone says nothing about whether the characters are right. Windows-1252 also uses single-byte characters but leaves a few byte values undefined and can raise a decoding error.

Avoid discarding the evidence

encoding_errors="replace" substitutes "�" for bytes the decoder cannot read. encoding_errors="ignore" drops them. These options can help inspect damaged input, but they lose information and should not stand in for finding the correct encoding. A name changed to "Zrich" can break a match as surely as visibly garbled text.

For output, UTF-8 handles the widest range of characters. Some spreadsheet programs identify it more reliably when a BOM is present:

df.to_csv("cities_export.csv", index=False, encoding="utf-8-sig")

Reopen the export in the receiving program and inspect the same accented values. The goal is the correct text at both ends of the exchange.

The file's size on disk is not its memory requirement. pandas builds arrays, indexes and text values in memory, and their types affect the space needed. Measure with df.memory_usage(deep=True) or df.info(memory_usage="deep") before deciding how to load more data.

Reduce the data you bring into memory

Read selected columns with usecols, inspect an initial sample with nrows, and choose suitable types with dtype. A category can store repeated region names compactly. float32 uses less space than float64, but offers about seven significant digits, so choose it only when that precision fits the measurements. A small preview helps identify types; it does not establish that every later row follows the same pattern.

Process chunks and keep a small result

chunksize changes the return value from a DataFrame to an iterator of DataFrames. For a total and a mean:

total = 0.0
count = 0
with pd.read_csv(
    "measurements.csv",
    usecols=["amount"],
    chunksize=100_000
) as reader:
    for chunk in reader:
        total += chunk["amount"].sum()
        count += chunk["amount"].count()
mean = total / count if count else float("nan")

The loop keeps a running sum and count of nonmissing values. Each chunk can be released after use. Collecting all chunks in a list and concatenating them later recreates the full memory requirement; chunking saves memory only when the work keeps a reduced result or writes processed pieces out.

A ten-row file read with chunksize=4 gives pieces of 4, 4 and 2 rows. For amounts 1 through 10 with regions cycling N, S, E, add partial regional totals to obtain N 22, S 15 and E 18. Their combined total is 55.

Combine the right quantities

Sums and counts add. Minimums combine by taking the smallest partial minimum; maximums combine by taking the largest partial maximum. Means need weights because chunks can contain different numbers of valid observations.

For values 1, 2 and 3 in one chunk and 10 in another, the chunk means are 2 and 10. Averaging those means gives 6, treating the one-value chunk as equal in weight to the three-value chunk. Add the sums and counts instead: total 16 across four observations gives mean 4. Chunk medians do not contain enough information to reconstruct an overall median.

Read compressed files directly

read_csv normally infers compression from recognized extensions such as .gz, .bz2, .zip, .xz and .zst. It decompresses while reading:

with pd.read_csv("measurements.csv.gz", chunksize=100_000) as reader:
    for chunk in reader:
        print(len(chunk))

A ZIP archive used this way must contain exactly one data file. df.to_csv("out.csv.gz", index=False) writes gzip-compressed CSV. Compression reduces disk space and transfer size; it does not remove the memory used by the decoded table. For repeated analysis, converting a checked dataset to Parquet also avoids reparsing text and restores its stored column types.

An Excel workbook can contain several sheets, titles and calculated totals. Cells have stored values and types as well as display formatting. Reading a worksheet means locating the data table and understanding which parts of the workbook become DataFrame values.

Choose the sheet and its boundaries

df = pd.read_excel(
    "quarterly.xlsx",
    sheet_name="Q1",
    skiprows=2,
    usecols="A:C",
    nrows=2
)

Suppose row 1 is a merged title, row 2 is blank, row 3 holds region, date and sales, and rows 4–5 hold the observations. skiprows=2 makes row 3 the header; nrows=2 stops before the total in row 6. For North 1200 and South 950, the two observations sum to 2150. Including a cached total of 2150 as a third observation would double that amount.

Excel displays row numbers starting at 1; pandas row positions start at 0. header=2 also selects Excel row 3 as the header. usecols="A,C,E:F" selects separate columns and a range. The default sheet_name=0 reads the first sheet; a name selects a specific sheet, and sheet_name=1 selects the second.

sheet_name=None returns a dictionary of sheet names to DataFrames. Select a key or loop over .items(); the dictionary itself is not one DataFrame. For several reads from one workbook:

with pd.ExcelFile("quarterly.xlsx") as xf:
    print(xf.sheet_names)
    q1 = pd.read_excel(xf, sheet_name="Q1")

Know what the cell really stores

A percentage displayed as "15%" stores 0.15. A number displayed with five digits may store 2139, even though the screen shows "02139". No reading option can recover a zero that exists only in formatting. Store identifiers as text in the workbook and also pass dtype={"zip": str} when reading; pandas can otherwise infer integers even from digit-only text cells.

Excel numeric precision is limited to 15 significant digits. A 16-digit identifier entered as a number can already be damaged before Python reads it. Store such values as text. A worksheet has at most 1,048,576 rows and 16,384 columns, so a very large dataset needs another format.

Treat formulas and merged cells carefully

pandas does not calculate formulas. It reads cached results stored in the workbook. A spreadsheet application can save current results; a script-generated file may have missing, placeholder or stale caches. openpyxl does not calculate a result, and XlsxWriter normally writes a zero placeholder unless given one. Recalculate important values in pandas or open and save the workbook in a spreadsheet program before relying on its cached totals.

Only the top-left cell of a merged range stores its label. A merged country label can therefore leave blanks in the rows below. When the layout confirms that the label applies to those rows, df["country"] = df["country"].ffill() carries it down. Inspect the table boundary before filling.

Reading requires an engine library. openpyxl handles .xlsx and .xlsm; xlrd handles .xls, pyxlsb handles .xlsb, odf handles .ods, and calamine supports several spreadsheet formats. A missing engine raises an ImportError naming the dependency. Colors, comments and other layout styling are not loaded as DataFrame data.

Excel is useful when people need to open and inspect a report in a spreadsheet program. A DataFrame can become one worksheet, or several DataFrames can share a workbook. Choose the destination path and sheet names before writing so each table goes to the intended place.

Write one table or several

weather.to_excel(
    "weather.xlsx",
    sheet_name="Weather",
    index=False
)

index=False keeps the default row numbers out of the worksheet. Without it, they appear as an extra first column and can come back as "Unnamed: 0" on a later read. Keep an index only when it contains meaningful labels that belong in the report.

A path-based to_excel call writes a workbook at that path, replacing an existing file. Calling it once for Weather and again for Counts at the same path can leave only the second table. Use one writer for both:

with pd.ExcelWriter("report.xlsx") as writer:
    weather.to_excel(writer, sheet_name="Weather", index=False)
    counts.to_excel(writer, sheet_name="Counts", index=False)

The context manager closes and finishes the workbook when the block ends. Reading it with pd.read_excel("report.xlsx", sheet_name=None) returns a dictionary containing both sheet names. Writing .xlsx requires openpyxl or XlsxWriter.

Update a sheet in an existing workbook

To preserve other sheets while replacing one, use an append-mode writer:

with pd.ExcelWriter(
    "report.xlsx",
    mode="a",
    engine="openpyxl",
    if_sheet_exists="replace"
) as writer:
    counts.to_excel(writer, sheet_name="Counts", index=False)

if_sheet_exists controls a name collision. "error" stops, "new" creates another sheet, "replace" replaces that sheet, and "overlay" writes into its existing cells. Overlay does not clear old cells outside the new table, so a shorter update can leave old rows behind. Decide whether the report needs a complete replacement of the sheet or a carefully positioned overlay.

Make the output easy to reopen

Sheet names have a 31-character limit and cannot contain :, \, /, ?, *, [ or ]. A short name such as "Q1 Summary" works; "Q1/Q2" contains a forbidden slash. Some engines warn instead of stopping for an overlong name, so choose compatible names yourself.

startrow and startcol position a table using zero-based offsets. na_rep gives missing values a visible marker. float_format="%.2f" rounds written numbers, and autofilter=True adds header filter buttons in pandas 3. Datetime columns are written as Excel dates.

Rounding on output changes stored numbers, not just their display. Keep a full-precision analysis file separately when a rounded report is needed. Reopen the workbook and check the sheet list, table dimensions and representative values. Excel reading is relatively slow and a round trip can change pandas dtypes, so use Parquet for intermediate analysis tables when type preservation matters.

JSON stores nested data as text. Objects hold name-value pairs, arrays hold ordered values, and strings use double quotes. Its other values are numbers, true, false and null. Python's json module converts this syntax to Python objects before you build a table.

Parse and write valid JSON

import json

text = '{"station":"Oslo","active":true,"temp_c":[3.5,null,4.0]}'
data = json.loads(text)
print(data["active"])   # True
print(data["temp_c"])   # [3.5, None, 4.0]

Objects become dictionaries, arrays become lists, and null becomes None. json.loads parses a string; json.load reads an open file. The writing functions reverse that direction: json.dumps returns text and json.dump writes to a file.

with open("station.json", "w", encoding="utf-8") as f:
    json.dump(data, f, ensure_ascii=False, indent=2)

ensure_ascii=False leaves characters such as "ü" readable. The default can write the same character as \u00fc; both forms represent the same string. A printed Python dictionary is not JSON: single quotes, True, None and trailing commas are invalid JSON syntax. Standard JSON also has no NaN. Python's encoder permits it by default, but allow_nan=False rejects it; pandas writes missing values as null.

Match the table's layout

A JSON table needs an orientation, the arrangement of rows, labels and values. orient="records" gives one object per row:

[{"city":"Lyon","temp_c":21.5},{"city":"Osaka","temp_c":null}]

The default DataFrame orientation, "columns", groups values under column names and then row labels. "split" stores columns, index and data separately. Specify an orientation that matches the receiving program instead of assuming every JSON table has the same shape.

from io import StringIO
import pandas as pd

df = pd.read_json(
    StringIO('[{"code":"0042","amount":9.5}]'),
    orient="records",
    dtype={"code": str}
)

In pandas 3, a plain string passed to read_json is a path, so wrap actual JSON text in StringIO. Even JSON strings containing only digits can be inferred as numbers; the explicit dtype preserves "0042".

Use JSON Lines for sequential records

JSON Lines has one complete JSON object per line, without an enclosing array or commas between records:

{"id":1,"amount":9.5}
{"id":2,"amount":12.0}

Read it with pd.read_json(path, lines=True) and write it with df.to_json(path, orient="records", lines=True). The reader's chunksize and nrows options require lines=True, which makes individual records available sequentially.

When exporting dates, set date_format="iso" for readable ISO timestamps. pandas 3.1 still defaults to epoch milliseconds for ordinary DataFrame JSON output, but that default is changing. Explicit date output avoids relying on a reader to guess what a large integer represents.

Nested JSON can describe an order, its customer and several items in one object. Before flattening it, decide the grain: what one row represents. The grain determines which amounts can be summed.

Flatten nested objects first

Consider two orders. Order 501 belongs to Ana and has items A1 (quantity 2, price 3.5) and B7 (quantity 1, price 10.0). Order 502 belongs to Ben and has A1 (quantity 4, price 3.5). Their customer details are nested objects and their items are lists.

orders = [
    {"order_id": 501,
     "customer": {"name": "Ana", "country": "PT"},
     "items": [{"sku": "A1", "qty": 2, "price": 3.5},
               {"sku": "B7", "qty": 1, "price": 10.0}]},
    {"order_id": 502,
     "customer": {"name": "Ben", "country": "CA"},
     "items": [{"sku": "A1", "qty": 4, "price": 3.5}]}
]
order_rows = pd.json_normalize(orders)

This produces two rows and four columns: order_id, items, customer.name and customer.country. The nested customer fields become columns with dotted names. The item lists remain in cells; normalization does not automatically turn every list into rows.

Expand a list with parent information

items = pd.json_normalize(
    orders,
    record_path="items",
    meta=["order_id", ["customer", "name"]]
)

record_path chooses the list whose entries become rows. meta copies parent values onto each row, with a list of keys for a nested path. The result has three item rows: two for Ana's order and one for Ben's. The revenue is quantity times price: 7.0, 10.0 and 14.0, totaling 31.0.

If an order-level shipping charge is 4.00, it repeats on both item rows for order 501. Summing those repeated charges gives 8.00 for one order. Keep order-level amounts in a separate orders table when you also need an item table; retain order_id to connect them.

Control names and missing fields

sep="_" creates names such as customer_name. record_prefix="item_" distinguishes item fields from parent fields with the same name. max_level=0 leaves nested dictionaries intact.

An ordinary nested field absent from one record becomes missing in its output column. A missing field requested through meta raises KeyError by default; errors="ignore" allows missing metadata and fills it with NaN. A missing list named by record_path still needs to be corrected.

Unwrap API responses before flattening

An API can return an envelope containing count, next and results. Pass data["results"] to pd.json_normalize to flatten the records. Passing the envelope itself normalizes one wrapper object, leaving its results list packed into a cell. Keep pagination metadata available for fetching later pages, but build the analysis table from the actual record list.

A web API accepts an HTTP request at an endpoint and returns a status code, headers and a body. Many APIs use JSON bodies, but a response arriving successfully does not mean it contains the data or all the records you need.

Send parameters and check the response

import requests

resp = requests.get(
    url,
    params={"city": "Lima", "units": "metric"},
    timeout=10
)
resp.raise_for_status()
data = resp.json()
df = pd.json_normalize(data["results"])

Here url is the API's documented endpoint. params encodes the query string, so spaces and special characters in values are handled correctly. Requests has no timeout by default. A single timeout value controls connection and read waiting; it is not a deadline for the entire response download.

Call raise_for_status() before using the body. It raises an HTTP error for 4xx and 5xx responses. A JSON error message can parse successfully while still describing a failed request; conversely, a 200 response can contain non-JSON text, making resp.json() fail. Inspect resp.text when the response format is unexpected.

Keep credentials separate

Use the authentication method the API documents. For a bearer token, read a key from an environment variable and send it in a header:

import os

key = os.environ["WEATHER_API_KEY"]
headers = {"Authorization": "Bearer " + key}

Keep keys out of saved code and notebooks. If a key has been shared or committed, revoke and replace it. A key grants access; hiding a displayed value later does not undo its exposure.

Continue until all pages are read

Follow the API's documented next-page mechanism, whether it uses page numbers, offsets or a next URL. Collect each page's records and stop at its stated end condition. A total of 230 records with 100 per page requires pages of 100, 100 and 30. The first page can be perfectly valid while the dataset is incomplete.

Compare the collected count with a reported total when available. Respect rate limits: 429 calls for waiting before another request, and Retry-After can specify the delay. That header can contain seconds or an HTTP date. Save raw responses for reproducible analysis and to avoid downloading the same data on every rerun.

Recognize other web table formats

pd.read_html(url) returns a list of DataFrames, one for each matching HTML table. match="Population" restricts tables by their text. It needs an HTML parsing dependency such as lxml or BeautifulSoup with html5lib. pd.read_xml reads repeated XML elements as rows, using lxml or parser="etree". Check a site's terms before scraping and prefer an official download or API when available.

A CSV export should preserve the values the recipient needs and make its conventions clear. Choose whether the row index is data, how missing values appear, and whether output is for precise analysis or a rounded display.

Choose what becomes a column

df.to_csv("weather_processed.csv", index=False)

The default is to write the index. For a table with an ordinary numbered index, this adds an unnamed first field; a later read_csv can show it as "Unnamed: 0". index=False leaves those row numbers out. When the index contains meaningful labels, keep it and read it back with index_col=0.

Missing values normally become empty fields. na_rep="NA" makes them visible, but the receiving reader must interpret that marker correctly. In a city table with Lyon at 21.5 °C and Osaka at 18.25 °C, a missing rainfall value for Osaka should stay distinct from zero rainfall.

Match the recipient's text convention

df.to_csv(
    "weather_export.csv",
    index=False,
    sep=";",
    decimal=",",
    na_rep="NA",
    date_format="%Y-%m-%d",
    encoding="utf-8"
)

The separator and decimal options produce values such as Lyon;21,5;0,0. ISO date output keeps day and month unambiguous. Use encoding="utf-8-sig" when a receiving spreadsheet program needs a BOM to recognize UTF-8.

float_format="%.1f" writes one decimal place. The value 18.25 becomes "18.2" under this formatting, and the discarded precision cannot be recovered from the CSV. Keep full precision for analysis data; create a separate rounded report when needed.

Check what survives a round trip

CSV stores characters without pandas dtype metadata. Consider four columns: an identifier "02139", a datetime, an ordered category A < B < C, and nullable integers with a gap. A default CSV read can return integer 2139, date text, plain category labels without their order, and floating-point numbers for the counts.

A read that completes successfully has therefore not necessarily restored the original DataFrame. Keep the options needed to reconstruct the data, including text identifiers, date parsing and missing markers. Category order also needs to be defined again. For repeated analysis where these types matter, Parquet can preserve them directly.

Append records and protect raw input

df.to_csv(
    "daily_results.csv",
    mode="a",
    header=False,
    index=False
)

This adds rows to an existing file. Keep column order and conventions consistent with that file. With header=True, an append writes another header into the middle, and the next reader can treat it as a record. For a new file, write the header once before later appends.

Keep received or downloaded files unchanged in a raw-data location. Write derived results separately under descriptive names. Reopening a processed export should check its row count, columns, missing values and representative values, including leading zeros and dates.

The best storage format depends on who reads the data next and what must survive the exchange. A table sent to a colleague, a nested API response and a typed analysis dataset have different needs.

Compare text and binary storage

Text formats encode visible characters. CSV is easy to inspect and widely supported; JSON naturally represents nested objects and arrays. CSV has no column-type schema, and ordinary JSON value types do not preserve every pandas dtype. Re-reading them requires parsing and can need explicit type choices.

Binary formats encode data for software to read directly. Formats such as Parquet and Feather store a schema describing columns and types, reducing the need to infer types again. Type support still depends on the format and reader: arbitrary Python objects do not become portable merely because the file is binary.

A row-oriented layout keeps a record's fields together. A columnar layout keeps values from the same column together. Analysis often needs a few columns from many rows, so columnar readers can skip unneeded columns. Similar adjacent values also compress well. File size and speed depend on the data; a compression result for one table is not a fixed ratio for all tables.

Match each format to a job

Parquet is an open format used by pandas, R, Spark and DuckDB. Feather uses Arrow IPC and is useful for fast exchange between Python and R or between local steps. to_feather and read_feather provide that path. HDF5 groups several datasets in one file; pandas's to_hdf requires PyTables.

Understand pickle's trust boundary

pickle can save Python objects beyond ordinary tables, but loading it can execute code. Never load a pickle from an untrusted source. Use it for your own short-lived objects, and expect compatibility limits as Python and libraries change. For example, pandas 3 no longer supports reading pickles made with pandas versions before 1.0.

Decide from the required outcome

If a recipient needs to inspect a small table without special tools, CSV is practical. If they need named workbook sheets, use Excel. If you need to preserve identifiers, categories and nullable types between analysis sessions, choose Parquet. If several programs need to query and update shared records, a database fits the job better than passing around copies of a file. The format choice follows the next use, not the extension of the original input.

Parquet stores a table with its column types in an open, columnar format. It is useful when a checked DataFrame will be loaded repeatedly or passed to other analysis tools. pandas uses PyArrow for the examples below.

Write and verify a typed round trip

df.to_parquet("sales.parquet", engine="pyarrow")
restored = pd.read_parquet("sales.parquet", engine="pyarrow")
print(restored.dtypes)

A text identifier "02139", a datetime column, an ordered category and nullable integers can all survive this pandas-to-PyArrow-to-pandas round trip with their types. Unlike a CSV read, it does not need to guess the identifier's role or re-create the category order. Inspect types and representative values when introducing a new writer or reader.

The default index=None stores a RangeIndex compactly as metadata. Another index is written as columns and restored on reading. Use index=False when the index is not needed. That choice concerns row labels independently of the table's ordinary columns.

Read less and choose compression

small = pd.read_parquet(
    "sales.parquet",
    columns=["zip", "amount"]
)

The reader can retrieve only those columns from storage. This is different from loading the full table and selecting afterward, which has already paid the full reading cost. Column selection is especially useful for wide tables.

Snappy is the default compression codec. compression="zstd" or "gzip" often trades more writing work for smaller storage, while compression=None writes uncompressed data. Measure the tradeoff with representative data; repeated labels and random measurements do not compress alike. Install PyArrow; the alternative fastparquet engine is deprecated in pandas 3.1.

Organize a dataset into partitions

df.to_parquet(
    "sales_dataset",
    engine="pyarrow",
    partition_cols=["year"],
    index=False
)
recent = pd.read_parquet(
    "sales_dataset",
    filters=[("year", "==", 2026)]
)

The destination is a directory, with subdirectories such as year=2025 and year=2026. Reading the directory loads the dataset; a filter can avoid unrelated partitions. With this layout, PyArrow derives the year values from directory names and pandas commonly receives a categorical partition column.

Choose a partition column with a small set of useful values that you often filter on. A unique record ID makes thousands of tiny partitions, increasing file and directory work. Year or a small region set often offers a more practical split.

Add data without corrupting the dataset

A single Parquet file does not support CSV-style row appending through pandas. Write new files or partitions to the dataset directory, or read, combine and rewrite a single file. Files sharing a dataset need compatible schemas. If one stores a code as text and another as an integer, loading can fail or convert values. Set consistent dtypes before writing each part and check that an added partition does not create overlapping copies of the same records.

A relational database stores records in tables with named columns. A program sends a SQL query over a connection and receives a result that pandas can turn into a DataFrame. The database can select just the data needed before it travels to Python.

Start with a SQLite file

import sqlite3
import pandas as pd

con = sqlite3.connect("shop.db")
try:
    df = pd.read_sql_query(
        "SELECT region, amount FROM sales", con
    )
finally:
    con.close()

SQLite runs without a separate server and stores a database in a file. Connecting creates the file if it does not exist, so a mistyped path can open a new, empty database instead of the intended one. ":memory:" creates a temporary in-memory database. The query above returns the region and amount columns from an existing sales table; its result column names become DataFrame labels.

A SQLite connection's with block manages a transaction, not closure. Explicitly call close() when finished. A try/finally block closes the connection even if reading raises an error.

Use an engine for a server database

SQLAlchemy provides engines for systems such as PostgreSQL, MySQL and SQL Server, with a matching driver installed. A connection URL identifies the dialect, driver, server and database. Keep it in an environment variable:

import os
from sqlalchemy import create_engine

engine = create_engine(os.environ["ANALYSIS_DB_URL"])
try:
    with engine.connect() as conn:
        df = pd.read_sql_query(
            "SELECT region, amount FROM sales", conn
        )
finally:
    engine.dispose()

The connection context returns the connection to the engine's pool. dispose() releases pooled resources when the engine is no longer needed. Use a read-only account for analysis and keep credentials out of notebooks and version control. An engine URL for a local SQLite file can be "sqlite:///shop.db".

Pick the reader that matches the input

With a plain sqlite3 connection, pass a complete query. pd.read_sql("sales", con) sends sales as SQL and fails; use pd.read_sql_query("SELECT * FROM sales", con) instead. pandas supports SQLAlchemy, ADBC and SQLite connections; an arbitrary raw driver connection is not a supported substitute just because the driver can execute SQL.

Move the result you need

A query returning selected rows and columns transfers less data than reading a whole table and discarding most of it in pandas. The given query SELECT SUM(amount) AS total FROM sales returns one aggregate value, labeled total. Reading a query result does not change the stored table. After loading, check row count and types just as you would for a file; the database's declared types and the returned pandas types are related but not identical.

Changing values belong in query parameters. The SQL statement defines the operation; bound parameters supply the region, threshold or other value used for this run. This preserves the query structure even when a value contains a quote.

Bind values with the connection's syntax

For SQLite:

query = "SELECT region, amount, sold_on FROM sales WHERE amount > ? ORDER BY amount DESC"
df = pd.read_sql_query(query, con, params=(70,))

For the sales amounts 120.0, 75.5, 60.0 and 210.0, this returns 210.0, 120.0 and 75.5 in descending order. The tuple (70,) has one element; (70) is just the number 70. SQLite also accepts named placeholders, for example WHERE region = :r with params={"r": "North"}.

Placeholder rules depend on the interface. psycopg uses %s or %(name)s. With SQLAlchemy, wrap a statement in text and use named parameters:

from sqlalchemy import text

query = text("SELECT region, amount FROM sales WHERE amount >= :m")
with engine.connect() as conn:
    df = pd.read_sql_query(query, conn, params={"m": 100})

Pass values through params; do not apply Python formatting to insert them into the statement.

Understand what parameterization protects

Suppose code builds a condition using an f-string and an input "x' OR '1'='1". Pasting it inside quotes changes the condition to region = 'x' OR '1'='1', which matches every row. This is SQL injection: a value has become executable query structure.

A bound parameter is compared as one value, so that same string matches only a region literally having that text. Binding also handles ordinary names containing apostrophes without hand-built quote escaping. The rule applies to inputs from files as well as user forms.

Parameters represent values, not table or column names. A parameter in place of a table name does not select that table. When a program needs a variable identifier, choose it from a fixed list controlled by the code.

Control types in the result

SQLite commonly stores dates as text. Ask pandas to parse them:

df = pd.read_sql_query(
    "SELECT sold_on, visits FROM observations", con,
    parse_dates=["sold_on"],
    dtype={"visits": "Int64"}
)

The date column becomes datetime values, and a count with SQL NULL uses nullable integers instead of becoming floating-point solely to hold gaps. Inspect the result to check conversion.

Distinguish chunks from driver buffering

chunksize=1000 returns an iterator of DataFrames containing at most 1,000 rows each. A four-row result with chunksize=3 gives pieces of three and one. Keep the connection open while consuming them. Some database drivers still buffer the entire result before pandas yields chunks; a server-side cursor or streaming configuration may also be needed to limit memory across the full connection path.

to_sql creates a database table from a DataFrame or writes rows into an existing table. Its options decide whether existing data survives, whether the index becomes a column and how a group of writes is committed.

Choose the existing-table behavior

df.to_sql("sales", con, index=False)

The default if_exists="fail" stops when sales already exists. Choose another mode only when its effect matches the intended load:

delete_rows is available in pandas 3. Replacing a table removes its old definition, including keys, indexes and constraints; database-specific permissions can be affected too. Deleting rows preserves the definition but still erases every existing record. Use it for an intended full reload, not when the DataFrame contains only a new batch.

Set columns and write in a transaction

The default writes the DataFrame index as a column. An unnamed index becomes index; a named index keeps its name. index_label gives it an explicit name, and index=False excludes it. If an ID is already an ordinary column, writing default row numbers usually adds no useful information.

With SQLAlchemy, dtype can choose database types:

from sqlalchemy import types

with engine.begin() as conn:
    df.to_sql(
        "sales", conn,
        if_exists="append",
        index=False,
        dtype={"sold_on": types.Date()},
        chunksize=1000
    )

engine.begin() commits on successful exit and rolls back on an exception. For an existing table, the stored schema still governs what values can be inserted; dtype is not a command to redesign its columns. Transaction guarantees also depend on the database's support for the operations used. Appending inside a SQLAlchemy transaction keeps the example's insert load together.

chunksize groups rows into batches. method="multi" puts several rows in one INSERT statement where supported. It can improve or reduce performance depending on the database and batch size, so measure it with representative data.

Verify growth, not just completion

check = pd.read_sql_query("SELECT COUNT(*) AS n FROM sales", con)
print(check["n"].iloc[0])

For a new or fully reloaded table, compare the count with len(df). For an append, compare the increase: a table with four rows receiving two rows should contain six. The row count returned by to_sql comes from the driver's reporting; verify the stored data directly.

Running an append twice repeats the records unless the database rejects duplicates or the program selects only new records. Decide whether the operation is a full reload or a new-record append before running it again. A transaction prevents a partial load on failure; it does not make repeated successful appends unique.