Examples

import pandas as pd
import numpy as np

# ===== 1) Load + inspect =====
df = pd.read_csv('orders.csv',
    dtype={'status': 'category'},
    parse_dates=['created_at'],
    na_values=['', 'NA', 'null'])

df.info(memory_usage='deep')
df.describe(include='all').T
df.isna().sum().sort_values(ascending=False).head(10)

# ===== 2) Clean — types, duplicates, missing =====
df['email'] = df['email'].str.strip().str.lower()
df['name']  = df['name'].astype('string').str.title()
df['total_cents'] = df['total_cents'].fillna(0).astype('int64')

before = len(df)
df = df.drop_duplicates(subset=['order_id'])
print(f'removed {before - len(df)} duplicate orders')

# ===== 3) Join with a lookup table =====
customers = pd.read_csv('customers.csv', usecols=['id','name','tier'])
df = df.merge(customers, left_on='customer_id', right_on='id', how='left',
              suffixes=('', '_cust'), indicator=True)
print(df['_merge'].value_counts())              # ensure no unmatched rows
df = df.drop(columns=['_merge', 'id'])

# ===== 4) Aggregate — per-tier KPIs =====
kpi = (df.groupby('tier', observed=True)
         .agg(orders=('order_id', 'count'),
              revenue=('total_cents', 'sum'),
              avg_order=('total_cents', 'mean'),
              unique_customers=('customer_id', 'nunique'))
         .assign(revenue_aud=lambda x: x['revenue'] / 100))
print(kpi.sort_values('revenue', ascending=False))

# ===== 5) Time-series resample — daily revenue + 7-day moving average =====
df = df.set_index('created_at').sort_index()
daily = (df['total_cents']
           .resample('D').sum()
           .to_frame('cents'))
daily['ma_7'] = daily['cents'].rolling(7).mean()
daily['yoy']  = daily['cents'].pct_change(365)
print(daily.tail(10))

# ===== 6) Export — for downstream consumers =====
# CSV for humans / Excel
daily.to_csv('daily_revenue.csv')

# Parquet for analytics tools (10x smaller, retains dtypes)
daily.to_parquet('daily_revenue.parquet', engine='pyarrow', compression='zstd')

# Excel with multiple sheets
with pd.ExcelWriter('report.xlsx', engine='xlsxwriter') as xl:
    kpi.to_excel(xl, sheet_name='KPI')
    daily.to_excel(xl, sheet_name='Daily')

# ===== Bonus — quick visualisation for a notebook =====
import matplotlib.pyplot as plt
fig, ax = plt.subplots(figsize=(9, 3))
daily['ma_7'].plot(ax=ax, color='#2563eb')
ax.set_title('7-day rolling daily revenue')
ax.set_ylabel('cents')
plt.tight_layout(); plt.savefig('daily_rev.png'); plt.close()

# ===== Patterns to internalise =====
# - parse_dates / dtype in read_csv: do it once, save type guessing later
# - assign() in groupby chains keeps the pipeline readable
# - resample() needs a DatetimeIndex; set_index('time').sort_index() first
# - Avoid .apply for rows — vectorise or use .map for scalars
# - Always 'indicator=True' on a merge until you trust the join keys

# A typical pipeline: read CSV → clean → groupby → plot.