Reading Data: Files, APIs, Databases, and the Web
The first step in any data project is getting data into memory. Data sources differ in latency, schema enforcement, and reliability — choosing the right ingestion path determines how much wrangling follows.
Flat Files: CSV and TSV
CSV remains the universal interchange format despite its many ambiguities (quoting rules, encodings, line endings). pandas and readr both handle the common cases well, but you must be explicit about encoding and type hints for large or non-ASCII files.
import pandas as pd
# Specify encoding and parse dates at read time
df = pd.read_csv(
"sales_2024.csv",
encoding="utf-8",
parse_dates=["order_date"],
dtype={"product_id": str}, # prevent integer coercion
na_values=["", "NA", "N/A", "null", "-"],
)
print(df.dtypes)
print(df.shape)library(readr)
library(dplyr)
df <- read_csv(
"sales_2024.csv",
col_types = cols(
order_date = col_date(format = "%Y-%m-%d"),
product_id = col_character(),
revenue = col_double()
),
na = c("", "NA", "N/A", "null", "-")
)
glimpse(df)Pitfall: pd.read_csv infers dtypes from the first few thousand rows. A column that looks like integers may contain "N/A" on row 500 000. Always inspect df.dtypes immediately after loading.
Parquet: Columnar Storage for Larger Data
Parquet stores data in a columnar, compressed binary format. Reading a Parquet file is typically 5–20x faster than an equivalent CSV for analytical queries because only requested columns are decompressed.
import pyarrow.parquet as pq
# Read only needed columns — IO and memory savings
table = pq.read_table("events.parquet", columns=["user_id", "event_ts", "event_type"])
df = table.to_pandas()library(arrow)
df <- read_parquet("events.parquet", col_select = c(user_id, event_ts, event_type))JSON: Nested and Semi-Structured Data
JSON is ubiquitous in API responses. The challenge is that JSON allows nesting and arrays where a data frame expects flat, rectangular data.
import json
with open("products.json") as f:
raw = json.load(f)
# json_normalize flattens nested keys with a separator
df = pd.json_normalize(raw["items"], sep="_")
# nested lists become columns of lists — explode them
df = df.explode("tags").reset_index(drop=True)library(jsonlite)
library(tidyr)
raw <- fromJSON("products.json", flatten = TRUE)
df <- as_tibble(raw$items) |>
unnest_longer(tags)REST APIs
Most modern data sources expose a REST API. A disciplined pattern handles pagination and HTTP errors cleanly.
import requests
import time
def fetch_all_pages(base_url: str, params: dict, page_key="page") -> list:
results = []
page = 1
while True:
params[page_key] = page
r = requests.get(base_url, params=params, timeout=10)
r.raise_for_status() # raises on 4xx/5xx
data = r.json()
if not data.get("items"):
break
results.extend(data["items"])
page += 1
time.sleep(0.2) # be polite to the server
return results
records = fetch_all_pages("https://api.example.com/orders", {"per_page": 100})
df = pd.DataFrame(records)Web Scraping: HTML Tables
For data published as HTML (government portals, Wikipedia, legacy dashboards), scraping with BeautifulSoup or rvest is often the only option.
import requests
from bs4 import BeautifulSoup
html = requests.get("https://en.wikipedia.org/wiki/List_of_countries_by_GDP").text
soup = BeautifulSoup(html, "lxml")
# pandas can parse the list of DataFrames that read_html returns
tables = pd.read_html(html) # returns list of DataFrames
gdp_df = tables[1] # inspect index manuallylibrary(rvest)
page <- read_html("https://en.wikipedia.org/wiki/List_of_countries_by_GDP")
table <- page |> html_element("table.wikitable") |> html_table()SQL Extraction
Pushing aggregation and filtering into the database before pulling data into memory is almost always the right approach — the database engine is optimized for this work.
from sqlalchemy import create_engine
import pandas as pd
engine = create_engine("postgresql+psycopg2://user:pass@localhost/analytics")
query = """
SELECT
customer_id,
DATE_TRUNC('month', order_date) AS month,
SUM(revenue) AS monthly_revenue
FROM orders
WHERE order_date >= '2023-01-01'
GROUP BY 1, 2
ORDER BY 1, 2
"""
df = pd.read_sql(query, engine)library(DBI)
library(RSQLite)
con <- dbConnect(RSQLite::SQLite(), "analytics.sqlite")
df <- dbGetQuery(con, "
SELECT customer_id,
strftime('%Y-%m', order_date) AS month,
SUM(revenue) AS monthly_revenue
FROM orders
WHERE order_date >= '2023-01-01'
GROUP BY 1, 2
ORDER BY 1, 2
")
dbDisconnect(con)Why it matters: A table with 500 million rows might aggregate to 10 000 rows for your analysis. Pulling all 500 million rows into Python/R memory first is a common, costly mistake.