Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 76 ยท Getting Data

Loading Data from CSV, JSON and SQL

Load data with pandas read_csv, read_json and read_sql: dtypes and dates, encodings, chunking, SQLAlchemy with in-memory SQLite, and checks after loading.

Loading looks like the easy part: one function call and the data is in memory. In practice, loading is where silent errors begin. A zip code loses its leading zero, a date is read as text, a stray "n/a" turns a number column into strings, or a file in the wrong encoding fills your names with strange symbols. A careful load, followed by a few checks, saves hours of confusion later.

This lesson covers the three formats you will meet most: CSV, JSON and SQL databases. The pandas course has detailed lessons on reading CSV and JSON; here we focus on the habits that matter for a data science project. In the browser there are no real files, so the examples read from io.StringIO text and an in-memory SQLite database. On your own machine you would pass a file path or a connection instead; everything else is the same.

CSV: declare what you know

pd.read_csv guesses types, which is fine for a quick look but risky in a pipeline. State the types you know: identifiers as strings, dates as dates, and any special missing-value markers.

Python read_csv with explicit dtypes, dates and missing markers

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Without dtype, the zip codes would be integers without leading zeros. Without na_values, the ? would force monthly_fee to become text. When the loaded dtypes surprise you, there is usually an unexpected value in the data, and finding it is worth the effort.

Encodings and odd files

Text files are stored as bytes, and the encoding says how bytes map to characters. Today most files are UTF-8, but older exports from spreadsheets often use Latin-1 or Windows-1252. If a name like "Zoรซ" shows up as "Zoรƒยซ", the file is UTF-8 but was read as Latin-1, or vice versa. Pass encoding="utf-8" or encoding="latin-1" explicitly. You can see the effect here:

Python The same bytes, two encodings

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

If you do not know the encoding, try UTF-8 first; an error is actually a good sign, because it tells you the guess was wrong instead of quietly mangling the text.

Reading in chunks

When a file is too big for memory, chunksize= returns an iterator of DataFrames so you can aggregate piece by piece. Keep only the small running result, not the pieces:

Python Aggregating a big file chunk by chunk

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The idea is to summarize each chunk and combine the summaries. It works for sums, counts, minimums and maximums; a median cannot be combined this way without extra tricks.

JSON

JSON is the usual format of web APIs. A file with one object per line (called JSON Lines) is also common for logs. pd.read_json handles simple, table-like JSON; for nested structures, load with the json module and flatten with pd.json_normalize.

Python read_json, JSON Lines and nested records

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

json_normalize joins nested keys with the separator you pick, giving columns like address_geo_lat. Lists inside records need more work; json_normalize has a record_path argument for expanding them into rows.

SQL databases

Most business data sits in a database. To read it from Python, you need a connection and a query. SQLAlchemy provides engines for many databases (PostgreSQL, MySQL, SQLite and more); pd.read_sql runs a query and returns a DataFrame. The browser demo uses SQLite in memory, so nothing is saved, but the code for a server database differs only in the connection URL (for example postgresql+psycopg://user:pass@host/db, kept in an environment variable, never in your code).

flowchart LR
    A["Python code"] --> B["SQLAlchemy engine<br/>(connection URL)"]
    B --> C[("Database")]
    A -->|"read_sql(query)"| C
    C -->|"rows"| D["DataFrame"]

pandas sends a query through a SQLAlchemy engine and receives the rows as a DataFrame.

Python Writing to and reading from in-memory SQLite

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Two habits matter here. First, push work to the database: filter, join and aggregate in SQL so that only the needed rows travel to Python. Second, use parameters (:min_age) instead of building queries by pasting strings. Parameters prevent SQL injection and quoting bugs. The SQL tutorial and the Python lesson on SQLAlchemy cover queries and engines in depth.

Excel workbooks and MongoDB

Two more sources turn up constantly. Business teams share Excel workbooks, and many applications store their records in MongoDB, a document database. Neither fits the "one clean CSV" picture, so each needs a few habits.

Excel with read_excel

An Excel file (.xlsx) is a container of sheets, and each sheet can have titles, merged cells, blank rows and notes above the real header. pd.read_excel needs the openpyxl package for .xlsx files. The arguments you will use most are:

  • sheet_name: one name, a position, a list, or None for all sheets (returns a dictionary of DataFrames).
  • header: the row number that holds the column names, when the sheet has a title above the table.
  • usecols: pick columns by letter range ("A:D") or name.
  • skiprows, nrows: skip junk at the top, or read only a sample.
  • dtype and parse_dates: declare types, as you would for CSV, see Loading Data.

The example below creates a small workbook in memory, then reads it back, so you can see every option work without uploading a file. The in-browser runner does not ship openpyxl, so the first line installs it with micropip (an internet connection is needed on the first run).

Python Reading sheets, headers and ranges

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

On your own computer you pass a file name instead of the buffer: pd.read_excel("report.xlsx", sheet_name="Sales", header=2). Common problems:

  • Merged cells and multi-row headers: use header=[0, 1] for two header rows, and expect NaN in merged areas. Fill them with ffill().
  • Formulas: openpyxl reads the stored result of a formula. If a file was created by a script and never opened in Excel, formula cells can come back empty.
  • Dates: a column of dates can arrive as text or numbers. Check dtypes and convert with pd.to_datetime.
  • Old .xls files need a different engine (xlrd), and .ods files need odfpy.
  • Big workbooks are slow. Convert to CSV or Parquet once and analyse that.

MongoDB with pymongo

MongoDB stores documents, JSON-like objects grouped into collections. Documents in one collection need not have the same keys, and values can be nested. The pymongo package reads them: collection.find(filter, projection) returns a cursor, a lazy iterator over documents. The simplest route into pandas is pd.DataFrame(list(cursor)).

Real MongoDB needs a running server, which this page cannot start, so the connection code is read-only and the runnable example uses a small stand-in cursor that yields documents shaped like real ones.

Python
# Read-only: needs the pymongo package and a MongoDB server
from pymongo import MongoClient
import pandas as pd

client = MongoClient("mongodb://localhost:27017")
orders = client["shop"]["orders"]

cursor = orders.find(
    {"status": "paid", "total": {"$gte": 20}},      # filter
    {"_id": 0, "customer": 1, "total": 1, "items": 1},  # projection: keep only these
).limit(10_000)

df = pd.DataFrame(list(cursor))

Always filter and project in the database (find, $match, $project) so you only transfer what you need, and add limit while exploring. For a server-side summary use orders.aggregate([...]) with stages such as $group.

Python A cursor into a DataFrame (stand-in for pymongo)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

With real data remember three things. An ObjectId in _id is not a normal string, so convert it with str() as above. Documents vary, so a column may be missing for some rows and your code must tolerate NaN. And dates in MongoDB are stored as UTC datetimes, which pandas reads as time-zone-naive values: decide on a time zone deliberately, as in Time and Trend Analysis.

Check what you loaded

Whatever the source, run a few checks immediately after loading, before you trust the table:

Python A quick post-load check

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The checks found a repeated row, a missing amount and an impossible date (month 13). A full audit is the topic of Auditing Data Quality; the point here is to make a short check part of every load.

Tip: Keep the raw file untouched and do all fixes in code. Then you can always rerun from the original and explain every change.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Zip codes like 02134 lose their leading zero after loading. What should you do?

    Zip codes are labels. Reading them as strings preserves every character.
  2. 2. A name 'Zoรซ' appears as 'Zoรƒยซ' after loading. What is the likely cause?

    UTF-8 bytes read as Latin-1 produce exactly this kind of garbled text. Set the encoding explicitly.
  3. 3. Why pass parameters to read_sql instead of building the query string yourself?

    Parameters are sent separately from the SQL text, so values cannot change the meaning of the query.
  4. 4. Which is the correct way to read a big CSV without loading it all at once?

    With chunksize, read_csv yields DataFrames of limited size so you can aggregate each one and keep only the small summaries.
  5. 5. A file has one JSON object per line. Which call reads it into a DataFrame?

    JSON Lines files need lines=True in read_json. json_normalize expects already parsed Python objects.

Summary

  • Declare types when loading: identifiers as strings, dates parsed, custom missing markers listed.
  • Set the encoding explicitly when text looks garbled; UTF-8 is the common default.
  • Use chunksize for big files and combine small per-chunk summaries.
  • JSON: read_json for tabular shapes (with lines=True for JSON Lines), json_normalize for nested records.
  • SQL: use a SQLAlchemy engine, push filtering and aggregation to the database, and pass query parameters.
  • Check shape, duplicates, missing values and bad dates right after loading.

Next: Sampling and Collection Bias, because what you loaded is only a sample of reality.