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

# Beginner Example 1: Creating synthetic banking transactions data
np.random.seed(42)
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))

# Beginner Example 2: Checking for missing or duplicate transaction_id
print('Missing values:', df.isnull().sum().sum())
duplicates = df['transaction_id'].duplicated().sum()
print('Duplicate transaction_id:', duplicates)

# Beginner Example 3: Exploring basic statistics for transaction amounts
print(df['amount'].describe())

# Beginner Example 4: Count of transactions by type
print(df['transaction_type'].value_counts())

# Beginner Example 5: Count transactions by channel
print(df['channel'].value_counts())

# Intermediate Example 1: Find transactions with negative or zero amounts
negatives = df[df['amount'] <= 0]
print('Number of negative or zero transactions:', negatives.shape[0])
print(negatives[['transaction_id', 'amount']].head(2))

# Intermediate Example 2: Top five transactions by amount
top5 = df.nlargest(5, 'amount')
print(top5[['transaction_id', 'customer_id', 'amount']])

# Intermediate Example 3: Number of transactions per customer
count_per_customer = df['customer_id'].value_counts()
print(count_per_customer.head())

# Intermediate Example 4: Group transactions by date and sum amounts
daily_totals = df.groupby(df['date'].dt.date)['amount'].sum()
print(daily_totals.head())

# Intermediate Example 5: Detect possible suspicious patterns (very high transactions)
threshold = df['amount'].mean() + 3 * df['amount'].std()
suspicious = df[df['amount'] > threshold]
print('Transactions flagged as suspicious:', suspicious.shape[0])
print(suspicious[['transaction_id', 'customer_id', 'amount']].head())

# Intermediate Example 6: Add a day_of_week column for analysis
df['day_of_week'] = df['date'].dt.day_name()
print(df[['date', 'day_of_week']].head(3))

# Advanced Example 1: Top three customers by total spend
total_spend = df.groupby('customer_id')['amount'].sum()
top_customers = total_spend.nlargest(3)
print(top_customers)

# Advanced Example 2: Analyze average debit vs credit amount
means = df.groupby('transaction_type')['amount'].mean()
print(means)

# Advanced Example 3: Find customers with high frequency of small transactions
small_tx = df[df['amount'] < 20]
small_count = small_tx['customer_id'].value_counts()
print(small_count.head())

# Advanced Example 4: Time gap between consecutive customer transactions
df_sorted = df.sort_values(['customer_id', 'date'])
df_sorted['prev_date'] = df_sorted.groupby('customer_id')['date'].shift(1)
df_sorted['gap_hours'] = (df_sorted['date'] - df_sorted['prev_date']).dt.total_seconds() / 3600
print(df_sorted[['customer_id', 'date', 'gap_hours']].dropna().head())

# Error Handling: What if the 'amount' column is missing?
try:
    print(df['amount'].head())
except KeyError:
    print('Column amount is missing! Please check your data source.')

# Error Handling: Detect if any date values are missing or out of order
if df['date'].isnull().sum() > 0:
    print('There are missing dates!')
elif not df['date'].is_monotonic_increasing:
    print('Dates are not in order!')
else:
    print('Dates are OK!')

# Best Practice: Function to summarize key quality checks for transaction DataFrame
def transaction_qc(df):
    print('Missing:', df.isnull().sum().sum())
    print('Duplicates:', df.duplicated().sum())
    print('Negative Amounts:', (df['amount'] < 0).sum())
    print('Out-of-order Dates:', not df['date'].is_monotonic_increasing)

transaction_qc(df)

# End-to-End: Sort, compute rolling mean, and detect surges
df_sorted = df.sort_values(['customer_id', 'date'])
df_sorted['rolling_avg'] = df_sorted.groupby('customer_id')['amount'].rolling(window=10, min_periods=5).mean().reset_index(0,drop=True)
df_sorted['spending_surge'] = df_sorted['amount'] > df_sorted['rolling_avg'] * 2
surge_cases = df_sorted[df_sorted['spending_surge']]
print('Customers with surges:', len(surge_cases['customer_id'].unique()))
print(surge_cases[['customer_id', 'amount', 'rolling_avg', 'date']].head())