pandas applied: groupby, joins, pivots and time series

What you will learn

Turn row-level sales into reconciled regional and monthly reports, validating joins before they duplicate revenue.

Before you start

You need basic variables, lists and function calls, not advanced Python algorithms. If these are new, visit Python foundations first.

1. Create a local environment: python -m venv .venv. Activate it for your OS and install python -m pip install jupyterlab numpy pandas matplotlib seaborn plotly.
2. Download the 12-row sales CSV into your notebook folder. Keep its filename unchanged.
3. Start Jupyter with jupyter lab. Run the setup below in a fresh notebook before every workshop.

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import plotly.express as px
raw = pd.read_csv('data-science-sales.csv', parse_dates=['date'])
df = raw.copy()
df['revenue'] = df['units'] * df['unit_price']
net = df.loc[df['returned'].eq(0)].copy()

Data dictionary: each row is one order for one product; units is a positive count; unit_price is INR per unit; returned is 0 or 1. Revenue is units × unit_price. For this workshop, net sales exclude the entire returned order (not partial returns). The CSV has 12 rows, 4950 gross revenue, 4450 net revenue and 35 net units. This small fictional sample teaches technique; it does not justify population or causal claims.

Save your notebook and written interpretation. There is no browser code judge and no automatic grading of visual design.

Official pandas documentation

Learn the idea

groupby sets the result grain. agg names the metrics; pivot_table reshapes summaries. merge(validate='many_to_one') catches duplicate lookup keys before they multiply facts. Use indicator=True to inspect unmatched rows. Resample requires a datetime index. For monthly reporting, to_period('M') makes a readable year-month key and avoids reliance on a changing month-end alias. Reconcile every report back to 4450 net revenue.

Follow a worked example

regional=net.groupby('region',as_index=False).agg(revenue=('revenue','sum'))
print(regional.sort_values('region').to_string(index=False))

Expected result

North 1350, South 1700, West 1400. Total 4450.

Skill-development workshop

These are practical assignments, not MCQs or code-judge puzzles. Work in your own notebook or MySQL client. Complete the levels in order; if a task feels difficult, follow the hint and compare with the reference only after making an attempt. Keep your result and a short explanation of what it means.

Easy: Summarise net revenue by product.

Success criteria: Lamp 2400, Mug 750, Notebook 900, Pen 400.

product=net.groupby('product')['revenue'].sum().sort_index()
print(product)
assert product.sum()==4450

Medium: Create month-by-region net revenue table.

Success criteria: January 1100, February 1550, March 1800; grand total 4450.

monthly=net.assign(month=net['date'].dt.to_period('M').astype(str))
pivot=monthly.pivot_table(index='month',columns='region',values='revenue',aggfunc='sum',fill_value=0)
print(pivot)
assert pivot.sum(axis=1).tolist()==[1100,1550,1800]

Hard: Attach product categories without multiplying order rows.

Success criteria: 11 net rows remain and total is 4450; every product matches.

lookup=pd.DataFrame({'product':['Notebook','Pen','Lamp','Mug'],'category':['Stationery','Stationery','Electronics','Home']})
joined=net.merge(lookup,on='product',how='left',validate='many_to_one',indicator=True)
assert len(joined)==11 and joined['revenue'].sum()==4450
assert joined['_merge'].eq('both').all()

Professional: Export a finance-ready monthly table and reconcile returns.

Success criteria: Gross 4950 minus returned 500 equals net 4450. Export monthly-revenue.csv and document INR and full-order exclusion.

assert df['revenue'].sum()-df.loc[df['returned'].eq(1),'revenue'].sum()==net['revenue'].sum()==4450
report=net.assign(month=net['date'].dt.to_period('M').astype(str)).groupby('month',as_index=False).agg(net_revenue_inr=('revenue','sum'))
report.to_csv('monthly-revenue.csv',index=False)
print(report)

Reflection and submission

Save your queries/notebook and outputs. Write three sentences: what you changed, how you checked it, and one limitation. A correct chart or query without a clear interpretation is not a completed professional assignment.