Advanced Pandas Operations

import pandas as pd
import numpy as np

# Create MultiIndex DataFrame
data = {
    'Revenue': [12000, 8000, 15000, 9500, 14000, 7800, 13200, 8900],
    'Units':   [10, 8, 12, 9, 11, 7, 13, 8]
}
index = pd.MultiIndex.from_tuples([
    ('2024 Q1', 'Laptop'), ('2024 Q1', 'Phone'),
    ('2024 Q2', 'Laptop'), ('2024 Q2', 'Phone'),
    ('2024 Q3', 'Laptop'), ('2024 Q3', 'Phone'),
    ('2024 Q4', 'Laptop'), ('2024 Q4', 'Phone')
], names=['Quarter', 'Product'])

df = pd.DataFrame(data, index=index)
print(df)

# Access levels
print(df.loc['2024 Q1'])              # All Q1 data
print(df.loc[('2024 Q1', 'Laptop')]) # Specific: Q1 Laptop
print(df.loc['2024 Q1':'2024 Q2'])   # Q1 and Q2 slice

# Stack/Unstack — pivot between index and columns
print(df['Revenue'].unstack())       # Products as columns
print(df.unstack().stack())          # Round trip

# Reset to flat index
df_flat = df.reset_index()

np.random.seed(42)
df_ts = pd.DataFrame({
    'Date': pd.date_range('2024-01', periods=60, freq='D'),
    'Sales': np.random.randint(500, 2000, 60) + np.linspace(0, 500, 60)
}).set_index('Date')

# Rolling windows
df_ts['MA_7']   = df_ts['Sales'].rolling(7).mean()    # 7-day moving average
df_ts['MA_30']  = df_ts['Sales'].rolling(30).mean()   # 30-day moving average
df_ts['STD_7']  = df_ts['Sales'].rolling(7).std()     # 7-day volatility
df_ts['MAX_7']  = df_ts['Sales'].rolling(7).max()     # 7-day rolling max

# Bollinger Bands (financial analysis)
df_ts['Upper_Band'] = df_ts['MA_7'] + 2 * df_ts['STD_7']
df_ts['Lower_Band'] = df_ts['MA_7'] - 2 * df_ts['STD_7']

# Expanding windows (cumulative from start)
df_ts['Cum_Mean'] = df_ts['Sales'].expanding().mean()
df_ts['Cum_Max']  = df_ts['Sales'].expanding().max()

# EWM — Exponentially Weighted Moving Average (recent data weighted more)
df_ts['EWM_7'] = df_ts['Sales'].ewm(span=7, adjust=False).mean()

print(df_ts[['Sales', 'MA_7', 'MA_30', 'EWM_7']].head(15))

# Categorical dtype — significant memory and speed benefits for repeated strings
df = pd.DataFrame({
    'Dept': ['Engineering', 'Marketing', 'Engineering', 'Sales', 'HR',
             'Engineering', 'Marketing', 'Sales', 'HR', 'Engineering'] * 1000,
    'Salary': np.random.randint(50000, 100000, 10000)
})

# Compare memory
print(f"String:   {df['Dept'].memory_usage(deep=True):,} bytes")
df['Dept'] = df['Dept'].astype('category')
print(f"Category: {df['Dept'].memory_usage(deep=True):,} bytes")  # Much smaller!

# Ordered categories (for comparison)
satisfaction = pd.Categorical(
    ['High', 'Low', 'Medium', 'High', 'Medium'],
    categories=['Low', 'Medium', 'High'],
    ordered=True
)
s = pd.Series(satisfaction)
print(s > 'Low')   # [True, False, True, True, True]
print(s.max())     # 'High'

# Use with groupby — faster than string groupby
summary = df.groupby('Dept', observed=True)['Salary'].mean()
print(summary)

df_survey = pd.DataFrame({
    'Gender': np.random.choice(['Male', 'Female'], 200),
    'Dept': np.random.choice(['Eng', 'Mkt', 'Sales'], 200),
    'Salary': np.random.normal(70000, 20000, 200).astype(int),
    'Rating': np.random.choice(['A', 'B', 'C'], 200)
})

# Cross tabulation
ct = pd.crosstab(df_survey['Dept'], df_survey['Gender'])
print("Count by Dept and Gender:")
print(ct)

ct_pct = pd.crosstab(df_survey['Dept'], df_survey['Gender'], normalize='index') * 100
print("\nPercentage:")
print(ct_pct.round(1))

# cut — bin continuous data
df_survey['Salary_Band'] = pd.cut(
    df_survey['Salary'],
    bins=[0, 50000, 70000, 90000, float('inf')],
    labels=['Low', 'Mid', 'High', 'Very High']
)

# qcut — equal-frequency bins (quantile-based)
df_survey['Salary_Quartile'] = pd.qcut(df_survey['Salary'], q=4,
                                        labels=['Q1', 'Q2', 'Q3', 'Q4'])
print("\nSalary Band distribution:")
print(df_survey['Salary_Band'].value_counts().sort_index())