Blog
pandasCSVJSON

How to Use Pandas for CSV and JSON Data Processing

August 29, 2026 ·

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 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"])

For one-off summaries without a notebook, use SQL aggregates in DB in Browser.

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: load files as separate tables in DB in Browser, then use UNION ALL with matching column lists.

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:

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 .

Written by

The RividTech Experts Team builds and maintains RividTech's data tools and writes its guides. Every guide is checked against the tools' actual behavior and updated when that behavior changes.

Spotted an error? Email info@rividtech.online.