How to Use Pandas for CSV and JSON Data Processing

pandas is the default Python library for tabular data. CSV exports, API JSON, and spreadsheet dumps all become a DataFrame you can filter, join, aggregate, and plot in a few lines.

This guide covers the operations you will use most: reading CSV and JSON, cleaning messy columns, reshaping nested data, exporting results, and building charts with matplotlib. When you only need a quick convert or preview, browser tools can skip the install entirely.

Install

pip install pandas matplotlib
# optional: openpyxl for Excel, pyarrow for faster parquet/json

Start every script the same way:

import pandas as pd
import matplotlib.pyplot as plt

Read CSV

read_csv handles delimiters, headers, types, and large files:

df = pd.read_csv("orders.csv")

# Common options
df = pd.read_csv(
    "orders.csv",
    sep=",",              # or "\t" for TSV
    encoding="utf-8",
    parse_dates=["created_at"],
    dtype={"user_id": "string"},
    na_values=["", "NA", "n/a"],
    usecols=["id", "status", "amount", "created_at"],  # load only what you need
)

# Peek
df.head()
df.info()
df.shape          # (rows, columns)
df.columns.tolist()

For multi-GB files that do not fit in memory, read in chunks:

chunks = []
for chunk in pd.read_csv("huge.csv", chunksize=100_000):
    filtered = chunk[chunk["status"] == "paid"]
    chunks.append(filtered)
df = pd.concat(chunks, ignore_index=True)

Quick inspect without Python: CSV Preview. For shell-only workflows, see Tools to Process CSV Files in the Terminal.

Read JSON

Flat JSON arrays of objects map cleanly to rows. Nested payloads need json_normalize or explode.

# Array of objects → one row per object
df = pd.read_json("users.json")

# Lines of JSON (NDJSON / JSONL)
df = pd.read_json("events.jsonl", lines=True)

# Nested structure from a Python dict or loaded file
import json
with open("response.json") as f:
    data = json.load(f)

# Flatten nested dicts; expand a list of records under a key
df = pd.json_normalize(data["results"])
df = pd.json_normalize(
    data["results"],
    record_path="orders",          # list to expand into rows
    meta=["user_id", "email"],     # parent fields to keep
    sep=".",
)

# List column → one row per element
df = df.explode("tags")

Prefer a visual flatten when exploring unfamiliar APIs: Flatten JSON, then JSON to CSV. Deeper background: Flatten Nested JSON for Spreadsheets and CSV.

Select, filter, and sort

# Columns
df[["id", "name", "email"]]
df.loc[:, "id":"email"]           # inclusive slice by label
df.filter(regex="^user_")         # columns matching a pattern

# Rows
df[df["amount"] > 100]
df[(df["status"] == "open") & (df["priority"] >= 2)]
df.query("status == 'open' and amount > 100")

# String filters
df[df["email"].str.endswith("@example.com", na=False)]
df[df["city"].str.contains("york", case=False, na=False)]

# Sort and rank
df.sort_values(["created_at", "amount"], ascending=[True, False])
df.nlargest(10, "amount")

Clean messy data

# Missing values
df.isna().sum()
df = df.dropna(subset=["email"])           # drop rows missing email
df["nickname"] = df["nickname"].fillna(df["name"])
df = df.fillna({"status": "unknown", "amount": 0})

# Duplicates
df.duplicated(subset=["email"]).sum()
df = df.drop_duplicates(subset=["email"], keep="last")

# Types and text
df["amount"] = pd.to_numeric(df["amount"], errors="coerce")
df["created_at"] = pd.to_datetime(df["created_at"], errors="coerce")
df["email"] = df["email"].str.strip().str.lower()
df.columns = df.columns.str.strip().str.lower().str.replace(" ", "_")

# Replace values
df["status"] = df["status"].replace({"Open": "open", "CLOSED": "closed"})

For a no-code cleanup pass first, try Remove Duplicates, Validate CSV, or the workflow in How to Clean Messy CSV Files.

Aggregate, group, and pivot

# Group by
df.groupby("city")["amount"].sum()
df.groupby(["status", "region"], as_index=False).agg(
    orders=("id", "count"),
    revenue=("amount", "sum"),
    avg_amount=("amount", "mean"),
)

# Value counts
df["status"].value_counts()
df["status"].value_counts(normalize=True)

# Pivot tables
pd.pivot_table(
    df,
    index="region",
    columns="status",
    values="amount",
    aggfunc="sum",
    fill_value=0,
    margins=True,
)

# Crosstab
pd.crosstab(df["region"], df["status"])

One-off pivots without a notebook: Pivot Table. Quick distributions: Data Stats.

Merge and concatenate

users = pd.read_csv("users.csv")
orders = pd.read_csv("orders.csv")

# SQL-style joins
merged = orders.merge(users, on="user_id", how="left")
merged = orders.merge(
    users,
    left_on="customer_id",
    right_on="id",
    how="inner",
    suffixes=("_order", "_user"),
)

# Stack files with the same columns
all_df = pd.concat(
    [pd.read_csv(f) for f in ["jan.csv", "feb.csv", "mar.csv"]],
    ignore_index=True,
)

Browser alternative for simple stacks: Merge CSV.

Export results

df.to_csv("clean_orders.csv", index=False)
df.to_csv("clean_orders.tsv", sep="\t", index=False)
df.to_json("clean_orders.json", orient="records", indent=2)
df.to_json("events.jsonl", orient="records", lines=True)
df.to_excel("report.xlsx", index=False, sheet_name="Orders")
df.to_parquet("orders.parquet", index=False)  # needs pyarrow or fastparquet

# Useful JSON orients: records | index | values | columns

Safe one-click converts: CSV to JSON, JSON to CSV, CSV to Excel. Privacy notes: How to Convert CSV to JSON Safely.

Create charts with matplotlib

pandas plots through matplotlib. Call .plot on a Series or DataFrame, then plt.savefig or plt.show.

Bar chart — counts or totals

counts = df["status"].value_counts()

ax = counts.plot(kind="bar", color="#4f46e5", figsize=(8, 4))
ax.set_title("Orders by status")
ax.set_xlabel("Status")
ax.set_ylabel("Count")
plt.xticks(rotation=0)
plt.tight_layout()
plt.savefig("orders_by_status.png", dpi=150)
plt.show()

Horizontal bar — ranked categories

revenue = (
    df.groupby("city")["amount"]
    .sum()
    .sort_values(ascending=True)
    .tail(10)
)

ax = revenue.plot(kind="barh", color="#0d9488", figsize=(8, 5))
ax.set_title("Top 10 cities by revenue")
ax.set_xlabel("Revenue")
plt.tight_layout()
plt.savefig("top_cities.png", dpi=150)

Line chart — time series

daily = (
    df.set_index("created_at")
    .resample("D")["amount"]
    .sum()
)

ax = daily.plot(kind="line", figsize=(10, 4), color="#4f46e5")
ax.set_title("Daily revenue")
ax.set_xlabel("Date")
ax.set_ylabel("Amount")
plt.tight_layout()
plt.savefig("daily_revenue.png", dpi=150)

Histogram and box plot — distributions

fig, axes = plt.subplots(1, 2, figsize=(10, 4))

df["amount"].plot(kind="hist", bins=30, ax=axes[0], color="#4f46e5", edgecolor="white")
axes[0].set_title("Amount distribution")
axes[0].set_xlabel("Amount")

df.boxplot(column="amount", by="status", ax=axes[1])
axes[1].set_title("Amount by status")
axes[1].set_xlabel("Status")
plt.suptitle("")  # remove default boxplot super-title
plt.tight_layout()
plt.savefig("amount_dist.png", dpi=150)

Scatter — two numeric columns

ax = df.plot.scatter(x="qty", y="amount", alpha=0.5, figsize=(6, 5), color="#4f46e5")
ax.set_title("Quantity vs amount")
plt.tight_layout()
plt.savefig("qty_vs_amount.png", dpi=150)

Pie chart — share of whole

share = df["region"].value_counts()

ax = share.plot(
    kind="pie",
    autopct="%1.1f%%",
    figsize=(6, 6),
    ylabel="",
)
ax.set_title("Orders by region")
plt.tight_layout()
plt.savefig("region_share.png", dpi=150)

Grouped bars from a pivot

table = pd.pivot_table(
    df,
    index="region",
    columns="status",
    values="amount",
    aggfunc="sum",
    fill_value=0,
)

ax = table.plot(kind="bar", figsize=(9, 5))
ax.set_title("Revenue by region and status")
ax.set_xlabel("Region")
ax.set_ylabel("Revenue")
ax.legend(title="Status")
plt.xticks(rotation=0)
plt.tight_layout()
plt.savefig("region_status.png", dpi=150)

Chart tips that save time

• Always set figsize and call tight_layout() before saving so labels are not clipped.
• Use dpi=150 (or higher) on savefig for crisp PNGs in docs and slides.
• Prefer bar/line over pie once you have more than ~5 categories.
• For many related plots, loop categories or use df.groupby(...).plot sparingly; explicit subplots is clearer.
• Seaborn (pip install seaborn) sits on matplotlib if you want nicer defaults: sns.barplot, sns.histplot, sns.lineplot.

import seaborn as sns
sns.set_theme(style="whitegrid")

sns.barplot(data=df, x="status", y="amount", estimator="sum", errorbar=None)
plt.title("Total amount by status")
plt.tight_layout()
plt.savefig("seaborn_status.png", dpi=150)

End-to-end mini workflow

import pandas as pd
import matplotlib.pyplot as plt

df = pd.read_csv("orders.csv", parse_dates=["created_at"])
df = df.dropna(subset=["amount", "status"])
df["amount"] = pd.to_numeric(df["amount"], errors="coerce")
df = df[df["amount"].notna() & (df["amount"] > 0)]

summary = (
    df.groupby("status", as_index=False)
    .agg(orders=("id", "count"), revenue=("amount", "sum"))
    .sort_values("revenue", ascending=False)
)
summary.to_csv("status_summary.csv", index=False)

ax = summary.plot(
    kind="bar",
    x="status",
    y="revenue",
    legend=False,
    color="#4f46e5",
    figsize=(8, 4),
)
ax.set_title("Revenue by status")
ax.set_xlabel("Status")
ax.set_ylabel("Revenue")
plt.xticks(rotation=0)
plt.tight_layout()
plt.savefig("revenue_by_status.png", dpi=150)

When browser tools are enough

pandas wins for repeatable scripts, larger joins, and custom charts. For a one-time look or convert — especially with sensitive files you do not want in a random notebook cloud — browser-only tools are enough:

• CSV Preview / JSON Preview
• CSV to JSON / JSON to CSV
• Data Process / Column Tools
• Pivot Table / Data Stats

RividTech runs those steps on your device only. See Why Browser-Only CSV Tools Are Safer for Your Data. For JSON in the shell without Python, use jq for JSON Processing from the Terminal.

Getting started

Install pandas and matplotlib, load a sample CSV with read_csv, run head / info, then try one filter and one groupby. Add a bar chart from value_counts() and save a PNG. When you need a table or convert for someone who will not run Python, finish with CSV to Excel or CSV Preview.