import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import squarify
import seaborn as sns
 
# Load Data
orders = pd.read_csv('src_orders.csv', parse_dates=['created_at'])
items = pd.read_csv('src_order_items.csv')
products = pd.read_csv('src_products.csv')
 
# Merge for Analysis
df_raw = orders.merge(items, on='order_id').merge(products, on='product_id')

SELECT
    p.category,
    SUM(oi.sale_price) as total_revenue,
    COUNT(DISTINCT o.order_id) as order_count
FROM src_orders o
JOIN src_order_items oi ON o.order_id = oi.order_id
JOIN src_products p ON oi.product_id = p.product_id
WHERE EXTRACT(YEAR FROM o.created_at) = 2023
GROUP BY category
ORDER BY total_revenue DESC

# Aggregate Data (Mimicking SQL)
df = df_raw.groupby('category')['sale_price'].sum().reset_index(name='total_revenue')
 
# Top 15 Categories
df_top = df.nlargest(15, 'total_revenue')
 
# Treemap Visualization
plt.figure(figsize=(14, 10))
 
# Colors
colors = plt.cm.Spectral(np.linspace(0, 1, len(df_top)))
 
# Labels
labels = [f"{row['category']}\n${row['total_revenue']:,.0f}"
          for _, row in df_top.iterrows()]
 
squarify.plot(
    sizes=df_top['total_revenue'],
    label=labels,
    color=colors,
    alpha=0.8,
    text_kwargs={'fontsize': 10, 'fontweight': 'bold'}
)
 
plt.title('Sales by Category (Top 15)', fontsize=16, fontweight='bold')
plt.axis('off')
plt.tight_layout()
plt.show()
 
# Insight
print(f"📊 Total Revenue: ${df_top['total_revenue'].sum():,.0f}")
print(f"📊 Top 1: {df_top.iloc[0]['category']} (${df_top.iloc[0]['total_revenue']:,.0f})")

SELECT
    p.department,
    p.category,
    SUM(oi.sale_price) as revenue
FROM src_orders o
JOIN src_order_items oi ON o.order_id = oi.order_id
JOIN src_products p ON oi.product_id = p.product_id
WHERE EXTRACT(YEAR FROM o.created_at) = 2023
GROUP BY department, category
ORDER BY department, revenue DESC

```python
# Aggregate Data
df = df_raw.groupby(['department', 'category'])['sale_price'].sum().reset_index(name='revenue')
 
# Department Colors
departments = df['department'].unique()
dept_colors = plt.cm.Set3(np.linspace(0, 1, len(departments)))
dept_color_map = dict(zip(departments, dept_colors))
 
# Colors & Labels
colors = [dept_color_map[dept] for dept in df['department']]
labels = [f"{row['category']}\n${row['revenue']:,.0f}"
          for _, row in df.iterrows()]
 
# Treemap
plt.figure(figsize=(16, 10))
squarify.plot(
    sizes=df['revenue'],
    label=labels,
    color=colors,
    alpha=0.7,
    text_kwargs={'fontsize': 8}
)
 
plt.title('Department > Category Treemap', fontsize=16, fontweight='bold')
plt.axis('off')
 
# Legend
for dept, color in dept_color_map.items():
    plt.scatter([], [], c=[color], s=100, label=dept)
## 2. Hierarchical Treemap
 
Sub-categories within categories can also be represented.
 
```python
# (Example code shows only visualization logic, execution results are replaced)
# For actual hierarchical data visualization, interactive tools like Plotly are more advantageous,
# but here we substitute with a static image example.
# ... (previous code omitted) ...

import plotly.express as px
 
# Prepare Data
df = df_raw.groupby(['department', 'category'])['sale_price'].sum().reset_index(name='revenue')
df['growth_rate'] = np.random.uniform(-30, 50, len(df))  # Mock data
 
# Basic Treemap
fig = px.treemap(
    df,
    path=['department', 'category'],  # Hierarchy
    values='revenue',                  # Size
    color='growth_rate',               # Color
    color_continuous_scale='RdYlGn',   # Palette
    title='Department > Category Treemap (Interactive)'
)
 
fig.update_layout(
    font=dict(size=14),
    margin=dict(t=50, l=25, r=25, b=25)
)
 
fig.show()

fig = px.treemap(
    df,
    path=['department', 'category'],
    values='revenue',
    color='growth_rate',
    color_continuous_scale='RdYlGn',
    hover_data={
        'revenue': ':$,.0f',
        'growth_rate': ':.1f%',
    },
    title='Interactive Treemap with Custom Hover'
)
 
fig.update_traces(
    hovertemplate='<b>%{label}</b><br>Revenue: %{value:$,.0f}<br>Growth: %{color:.1f}%<extra></extra>'
)
 
fig.show()