Use Python for Data Analysis: Build a Reproducible Workflow

pandas DataFrames, backed by NumPy and visualized with Matplotlib or Seaborn, give you a direct path from raw records to trustworthy insights without manual, error-prone steps. The approach below prioritizes a single, reproducible pipeline you can re-run on new data and share with teammates.

Prerequisites

• Python 3.9+ installed via Anaconda or your system package manager.
• JupyterLab or Jupyter Notebook for iterative analysis.
• Libraries: pandas, numpy, matplotlib, seaborn, scikit-learn; plus drivers for any external sources you use (for example, pyodbc or psycopg2).

Method 1 — Build a Reproducible pandas Pipeline (Jupyter)

conda create -n data-pipeline python=3.11 -y
conda activate data-pipeline
# or with pip + venv:
python -m venv .venv
source .venv/bin/activate  # Windows: .venv\Scripts\activate

python -m pip install pandas numpy matplotlib seaborn scikit-learn pyarrow openpyxl lxml

jupyter lab
# Run cells with 

Shift+Enter executes the current cell and moves to the next.

import pandas as pd

df = pd.read_csv("data.csv").convert_dtypes()
df.head()

df.columns = (
    df.columns
      .str.strip()
      .str.lower()
      .str.replace(r"[^0-9a-zA-Z]+", "_", regex=True)
      .str.replace(r"_+", "_", regex=True)
      .str.removesuffix("_")
)

df.info()
df.isna().sum()

# Example: fill specific cells by index using combine_first
patch = pd.DataFrame({"score_a": {42: 7.1}, "score_b": {42: 6.8}})
df = df.combine_first(patch)

df = df.assign(
    revenue_usd=lambda d: d["revenue_usd"]
        .astype("string")
        .str.replace(r"[$,]", "", regex=True)
        .astype("Float64"),
    budget_usd=lambda d: d["budget_usd"]
        .astype("string")
        .str.replace(r"[$,]", "", regex=True)
        .astype("Float64"),
)

df = df.assign(
    duration_min=lambda d: d["duration_min"]
        .astype("string")
        .str.replace(" mins", "", regex=False)
        .astype("Int64")
)

df = df.assign(
    release_date=lambda d: pd.to_datetime(d["release_date"], format="%B, %Y")
)

df = df.assign(release_year=lambda d: d["release_date"].dt.year.astype("Int64"))

df = df.assign(
    lead_actor=lambda d: d["lead_actor"]
        .str.replace(r"^Shawn", "Sean", regex=True)
        .str.replace("MOORE", "Moore"),
    car_brand=lambda d: d["car_brand"].str.replace("Astin", "Aston"),
)

df[["duration_min", "martinis"]].describe()

df = df.assign(
    duration_min=lambda d: d["duration_min"].replace({1200: 120}),
    martinis=lambda d: d["martinis"].replace({-6: 6})
)

df = df.drop_duplicates(ignore_index=True)

# Parquet keeps types and compresses well
df.to_parquet("clean.parquet", index=False)
# CSV is broadly compatible
df.to_csv("clean.csv", index=False)

Why this method first: a single, chained pipeline reduces manual steps, prevents hidden Excel edits, and lets teammates reproduce results on demand. It also scales better than spreadsheets when your dataset grows beyond a few hundred thousand rows.

---

Method 2 — Query Data Directly from a Database Into pandas

# SQL Server example
python -m pip install pyodbc sqlalchemy
# PostgreSQL example
python -m pip install psycopg2-binary sqlalchemy

from sqlalchemy import create_engine

# Example for SQL Server with ODBC Driver 17:
engine = create_engine(
    "mssql+pyodbc://username:password@SERVER/DB?driver=ODBC+Driver+17+for+SQL+Server",
    fast_executemany=True
)

import pandas as pd

sql = "SELECT col_a, col_b, created_at FROM schema.table WHERE created_at >= '2024-01-01';"
df = pd.read_sql(sql, engine).convert_dtypes()

from sqlalchemy import text

stmt = text("SELECT * FROM sales WHERE region = :region AND dt >= :start")
df = pd.read_sql(stmt, engine, params={"region": "EMEA", "start": "2025-01-01"})

engine.dispose()

This approach eliminates manual exports, supports scheduled jobs, and keeps source-of-truth logic in SQL where appropriate. It’s ideal when your data already lives in relational systems used by BI tools.

---

Method 3 — Read From Common File and Web Sources

df = pd.read_parquet("data.parquet").convert_dtypes()

df = pd.read_excel("workbook.xlsx", sheet_name="Sheet1").convert_dtypes()

df = pd.read_json("records.json").convert_dtypes()

tables = pd.read_html("https://example.com/tables-page")
df = tables[0].convert_dtypes()

Tip: Prefer Parquet for intermediate storage because it compresses and retains types; if you must share with tools lacking Parquet support, export CSV as a fallback.

---

Method 4 — Analyze, Visualize, and Model

df.describe(numeric_only=True)

import matplotlib.pyplot as plt

fig, ax = plt.subplots()
ax.scatter(df["metric_x"], df["metric_y"], alpha=0.7)
ax.set_title("Metric Y vs Metric X")
ax.set_xlabel("Metric X")
ax.set_ylabel("Metric Y")
plt.show()

from sklearn.linear_model import LinearRegression
import numpy as np

X = df[["metric_x"]].to_numpy()
y = df["metric_y"].to_numpy()
model = LinearRegression().fit(X, y)
r2 = model.score(X, y)
y_pred = model.predict(X)

fig, ax = plt.subplots()
ax.scatter(X, y, alpha=0.7)
ax.plot(X, y_pred, color="red")
ax.set_title(f"Linear Fit (R²={r2:.2f})")
ax.set_xlabel("Metric X")
ax.set_ylabel("Metric Y")
plt.show()

counts = df["duration_min"].value_counts(bins=7).sort_index()
ax = counts.plot.bar(title="Duration Distribution", xlabel="Minutes (bins)", ylabel="Count")
plt.show()

(df.groupby("release_year")["revenue_usd"]
   .agg(["count", "mean", "sum"])
   .sort_index())

Reading the plots: a visible upward trend in the scatter indicates a positive relationship, while a cloud with no slope suggests little to no linear relationship. Use R² to quantify how well the regression line explains variance in the data.

---

Operational Tips and Cautions

• Automate recurring work by keeping everything in one notebook or script and running it on a schedule with your orchestrator of choice.
• Use method chaining (for example, df.assign(...).drop_duplicates(...)) to keep code readable and to avoid accidental intermediate edits.
• Prefer typed integers (Int64) and floats (Float64) for math; strings won’t sum or average correctly.
• Validate at each stage with df.info(), df.head(), and spot-checks to catch mistakes early.
• When datasets exceed Excel comfort limits, pandas typically loads, filters, and groups millions of rows faster and with fewer crashes.

---

By centering your work in a single pandas pipeline, you cut busywork, speed up repeat runs, and make results easier to audit and reuse. Add direct SQL reads when available, and keep Parquet snapshots to move data through your workflow quickly.