import pandas as pd
import numpy as np
import warnings
warnings.filterwarnings('ignore')
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))

customer_ids = [f'CUST_{i:04d}' for i in range(1, 201)]

customers = pd.DataFrame({
    'customer_id': customer_ids,
    'segment': ['Retail'] * 150 + ['Business'] * 50,
    'region': ['Metro'] * 100 + ['Regional'] * 100
})
print(customers.shape)
print(customers.head(3))

accounts = pd.DataFrame({
    'account_id': [f'ACC_{i:05d}' for i in range(1, 201)],
    'customer_id': customer_ids,
    'account_type': np.random.choice(['Savings', 'Cheque', 'Credit'], size=200),
    'open_date': pd.date_range(start='2015-01-01', periods=200, freq='30D')
})
print(accounts.shape)
print(accounts.head(3))

print(df['channel'].value_counts())

print(df.groupby('transaction_type')['amount'].mean())

df['amount'].hist(bins=30)

daily_totals = df.groupby(df['date'].dt.date)['amount'].sum()
print(daily_totals.head())

channel_crosstab = pd.crosstab(df['channel'], df['transaction_type'])
print(channel_crosstab)

merged = df.merge(customers, on='customer_id', how='left')
channel_by_seg = merged.groupby(['segment', 'channel']).size().unstack()
print(channel_by_seg)

avg_amount_region = merged.groupby('region')['amount'].mean()
print(avg_amount_region)

pivot = merged.pivot_table(index='region', columns='transaction_type', values='amount', aggfunc='sum')
print(pivot)

accounts_per_region = accounts.merge(customers, on='customer_id').groupby('region')['account_id'].count()
print(accounts_per_region)

activity_per_customer = df.groupby('customer_id').size()
top10_customers = activity_per_customer.sort_values(ascending=False).head(10)
print(top10_customers)

print(df[df['amount'] <= 0].head())

print(merged[merged['customer_id'].isnull()].head())

merged.to_csv('customer_transactions.csv', index=False)
print('Saved cleaned customer transaction dataset.')

# End-to-end Example: Calculate total monthly debit value by customer segment
df['month'] = df['date'].dt.to_period('M')
enriched = df.merge(customers, on='customer_id', how='left')
monthly_debit = enriched[enriched['transaction_type'] == 'Debit'].groupby(['month', 'segment'])['amount'].sum().unstack()
print(monthly_debit.head())

# Final Practice: Calculate share of business customers by region with at least one credit transaction
business_credits = merged[(merged['segment'] == 'Business') & (merged['transaction_type'] == 'Credit')]
business_region_counts = business_credits.groupby('region')['customer_id'].nunique()
all_business_counts = customers[customers['segment'] == 'Business'].groupby('region')['customer_id'].nunique()
share = (business_region_counts / all_business_counts * 100).round(2)
print(share)