Moderate Project — Sales & Customers Dashboard

Goal

Build a Sales & Customers Dashboard combining: Orders CSV + Customers JSON + (optional) MySQL. Join datasets, compute revenue KPIs, visualize revenue by city, and export to CSV/JSON/PNG/MySQL.

Difficulty: Moderate • Learn joins + KPIs + database export.

Data entry sources

📥 Read orders (CSV) + customers (JSON)

import pandas as pd

orders = pd.read_csv("../data/orders.csv", parse_dates=["order_date"])
customers = pd.read_json("../data/customers.json")

print(orders.head())
print(customers.head())

Pandas processing

Compute revenue, join customers, filter paid+shipped, then summarize.

🧠 Join + KPIs

import pandas as pd

orders = pd.read_csv("../data/orders.csv", parse_dates=["order_date"])
customers = pd.read_json("../data/customers.json")

orders["revenue"] = pd.to_numeric(orders["qty"], errors="coerce").fillna(0) * pd.to_numeric(orders["unit_price"], errors="coerce").fillna(0)
df = orders.merge(customers, on="customer_id", how="left")
df = df[df["status"].isin(["PAID","SHIPPED"])]

revenue_by_city = df.groupby("city")["revenue"].sum().sort_values(ascending=False)
top_products = df.groupby("product")["revenue"].sum().sort_values(ascending=False).head(10)

print(revenue_by_city)
print(top_products)

Charts (Matplotlib) → PNG

📊 Revenue by city

import pandas as pd
import matplotlib.pyplot as plt
import os

os.makedirs("../output", exist_ok=True)

orders = pd.read_csv("../data/orders.csv")
customers = pd.read_json("../data/customers.json")
orders["revenue"] = pd.to_numeric(orders["qty"], errors="coerce").fillna(0) * pd.to_numeric(orders["unit_price"], errors="coerce").fillna(0)

df = orders.merge(customers, on="customer_id", how="left")
df = df[df["status"].isin(["PAID","SHIPPED"])]

rev = df.groupby("city")["revenue"].sum().sort_values(ascending=False)

plt.figure()
plt.bar(rev.index, rev.values)
plt.title("Revenue by City (PAID + SHIPPED)")
plt.xlabel("City")
plt.ylabel("Revenue")
plt.tight_layout()
plt.savefig("../output/moderate_revenue_by_city.png", dpi=200)
plt.show()

Exports to CSV/JSON/MySQL

Use SQLAlchemy to push a DataFrame into MySQL.

🗄 Export to MySQL

import pandas as pd
from sqlalchemy import create_engine

MYSQL_URL = "mysql+pymysql://USER:PASSWORD@localhost:3306/DBNAME"

orders = pd.read_csv("../data/orders.csv")
customers = pd.read_json("../data/customers.json")
orders["revenue"] = pd.to_numeric(orders["qty"], errors="coerce").fillna(0) * pd.to_numeric(orders["unit_price"], errors="coerce").fillna(0)

df = orders.merge(customers, on="customer_id", how="left")
df = df[df["status"].isin(["PAID","SHIPPED"])]

revenue_by_city = df.groupby("city")["revenue"].sum().reset_index()

engine = create_engine(MYSQL_URL, pool_pre_ping=True)
revenue_by_city.to_sql("pp_city_revenue", con=engine, if_exists="replace", index=False)

print("MySQL export done ✅")

💾 Export CSV + JSON

import pandas as pd
import os

os.makedirs("../output", exist_ok=True)

orders = pd.read_csv("../data/orders.csv")
customers = pd.read_json("../data/customers.json")
orders["revenue"] = pd.to_numeric(orders["qty"], errors="coerce").fillna(0) * pd.to_numeric(orders["unit_price"], errors="coerce").fillna(0)

df = orders.merge(customers, on="customer_id", how="left")
df = df[df["status"].isin(["PAID","SHIPPED"])]

revenue_by_city = df.groupby("city")["revenue"].sum().sort_values(ascending=False)
top_products = df.groupby("product")["revenue"].sum().sort_values(ascending=False).head(10)

revenue_by_city.to_csv("../output/moderate_city_revenue.csv")
top_products.reset_index().to_json("../output/moderate_top_products.json", orient="records", indent=2)

Run steps

Install deps: pip install -r requirements.txt
Run: python moderate_sales_customers_dashboard.py
(Optional) Edit MySQL URL in script and rerun for DB export
Check output/ for CSV/JSON/PNG