import pandas as pd
import numpy as np
import warnings
warnings.filterwarnings('ignore')

# Create synthetic banking transactions data
np.random.seed(42)  # Set seed for reproducibility
n_transactions = 1000
n_customers = 200
df = pd.DataFrame({
    'transaction_id': range(1, n_transactions + 1),
    'customer_id': np.random.choice([f'CUST_{i:04d}' for i in range(1, n_customers + 1)], n_transactions),
    'amount': np.round(np.random.normal(150, 60, n_transactions), 2),
    'transaction_type': np.random.choice(['Debit', 'Credit'], n_transactions),
    'channel': np.random.choice(['ATM', 'Online', 'Branch', 'POS'], n_transactions),
    'date': pd.date_range(start='2024-01-01', periods=n_transactions, freq='h')
})
print(df.shape)
print(df.head(3))

# Convert the date column to datetime, if it is not already
df['date'] = pd.to_datetime(df['date'])
print(df['date'].dtype)

# Beginner Example 1: Count transactions per day
daily_counts = df.groupby(df['date'].dt.date).size()
print(daily_counts.head())

# Beginner Example 2: Calculate total transaction amounts for each day
daily_totals = df.groupby(df['date'].dt.date)['amount'].sum()
print(daily_totals.head())

# Beginner Example 3: Calculate average transaction amount per day
daily_avg = df.groupby(df['date'].dt.date)['amount'].mean()
print(daily_avg.head())

# Intermediate Example 1: Get monthly totals using the pandas period
monthly_totals = df.groupby(df['date'].dt.to_period('M'))['amount'].sum()
print(monthly_totals)

# Intermediate Example 2: Find peak transaction days each month
peak_days = df.groupby(df['date'].dt.to_period('M')).apply(lambda g: g.loc[g['amount'].idxmax()][['date', 'amount']])
print(peak_days)

# Intermediate Example 3: Daily totals as a time series DataFrame
df_daily = df.set_index('date').resample('D')['amount'].sum().to_frame('daily_total')
print(df_daily.head())

# Advanced Example 1: Aggregations by customer and month
customer_monthly = df.groupby([df['customer_id'], df['date'].dt.to_period('M')])['amount'].sum().unstack()
print(customer_monthly.head())

# Advanced Example 2: Rolling monthly moving average
df_monthly = df.set_index('date').resample('M')['amount'].sum().to_frame('monthly_total')
df_monthly['rolling_3_month_avg'] = df_monthly['monthly_total'].rolling(window=3, min_periods=1).mean()
print(df_monthly)

# Advanced Example 3: Aggregating different transaction types (debit vs credit) per month
monthly_types = df.groupby([df['date'].dt.to_period('M'), 'transaction_type'])['amount'].sum().unstack(fill_value=0)
print(monthly_types)

# Error Handling Example: Handling missing or corrupted date values
df_bad = df.copy()
df_bad.loc[0, 'date'] = 'not_a_date'
try:
    df_bad['date'] = pd.to_datetime(df_bad['date'], errors='raise')
except Exception as e:
    print('Error:', e)
    df_bad['date'] = pd.to_datetime(df_bad['date'], errors='coerce')
    print('Missing dates:', df_bad['date'].isna().sum())

# Debugging Example: Sanity check for negative transaction amounts
neg_amounts = df[df['amount'] < 0]
print(f'Negative transactions: {neg_amounts.shape[0]}')
print(neg_amounts.head())

# Common pattern: Chain grouping and summary operations
daily_summary = (df.groupby(df['date'].dt.date)
                 .agg(total_amount=('amount', 'sum'),
                      avg_amount=('amount', 'mean'),
                      num_transactions=('transaction_id', 'count')))
print(daily_summary.head())

# End-to-end example: Find customer with monthly highest debit total, then plot trend
monthly_debits = df[df['transaction_type'] == 'Debit'].groupby(['customer_id', df['date'].dt.to_period('M')])['amount'].sum().unstack(fill_value=0)
top_cust = monthly_debits.sum(axis=1).idxmax()
top_cust_trend = monthly_debits.loc[top_cust]
print(f'Customer with highest total debit: {top_cust}')
print(top_cust_trend)

# Save daily summary to CSV for reporting
daily_summary.to_csv('daily_summary.csv')
print('Daily summary saved to daily_summary.csv')