Data ScienceIntermediate14 min readUpdated March 2025

Data Collection & Wrangling

Real data is messy. This article covers loading data from common sources and the wrangling techniques — cleaning, reshaping and merging — that turn raw inputs into an analysis-ready dataset.

Where Data Comes From

Data typically arrives from several kinds of sources, each with its own quirks:

Files — CSV, Excel, JSON and Parquet exports.
Databases — SQL queries against production or warehouse systems.
APIs — JSON over HTTP, often paginated and rate limited.
Web scraping — Extracting from HTML when no API exists (check terms of use).

Loading Data

pandas can read most formats directly. Always specify types and parse dates explicitly to avoid silent errors:

python

import pandas as pd
import requests

# CSV with explicit types and dates
orders = pd.read_csv(
    "orders.csv",
    dtype={"order_id": "string", "customer_id": "string"},
    parse_dates=["order_date"],
)

# JSON from an API
resp = requests.get("https://api.example.com/v1/products", timeout=10)
resp.raise_for_status()
products = pd.json_normalize(resp.json()["items"])

print(orders.info())

Cleaning: Missing Values and Duplicates

Decide deliberately how to treat missing data — dropping, filling, or flagging each have trade-offs:

python

# Inspect
print(orders.isna().sum())

# Remove exact duplicates
orders = orders.drop_duplicates(subset="order_id")

# Fill numeric gaps with the median, categorical with a label
orders["amount"] = orders["amount"].fillna(orders["amount"].median())
orders["channel"] = orders["channel"].fillna("unknown")

# Standardize text
orders["channel"] = orders["channel"].str.strip().str.lower()

Reshaping and Merging

Combine tables with `merge` and reshape between wide and long formats with `pivot` and `melt`:

python

# Join orders with customers (left join keeps all orders)
data = orders.merge(customers, on="customer_id", how="left", validate="m:1")

# Monthly revenue by channel (long -> wide)
monthly = (
    data.assign(month=data["order_date"].dt.to_period("M"))
        .pivot_table(index="month", columns="channel",
                     values="amount", aggfunc="sum", fill_value=0)
)
print(monthly.head())

Key Takeaways

Specify types and date parsing when loading data.
Choose a missing-value strategy deliberately and document it.
Use validate= on merges to catch unexpected duplicates.
Wrangling is repeatable code, not manual spreadsheet edits.

Contact Us

Have a question or feedback? Fill out the form below or reach us directly at support@nvaitraining.com