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

# Create a synthetic banking transactions DataFrame
np.random.seed(42)  # Set seed to 42 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))

# Create a synthetic customers DataFrame
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))

# Beginner: Checking for missing values in a DataFrame
missing = df.isnull().sum()
print(missing)

# Beginner: Making data dirty intentionally to practice cleaning
df.loc[3, 'amount'] = np.nan   # Remove amount from row 4 (index starts at 0)
df.loc[5, 'customer_id'] = None   # Remove customer_id from row 6
df.loc[10, 'channel'] = 'onlINe'  # Introduce a typo for channel
print(df.head(12))

# Beginner: Identify and count dirty values by category
print('Rows with missing amount:', df['amount'].isnull().sum())
print('Rows with missing customer_id:', df['customer_id'].isnull().sum())
print('Rows with obvious typos in channel:', (df['channel'].str.lower() == 'online').sum())

# Beginner: Fill missing numeric amounts with the mean
mean_amount = df['amount'].mean()
df['amount'].fillna(mean_amount, inplace=True)
print(df.head(6))

# Beginner: Fill missing customer_id with a placeholder
df['customer_id'].fillna('UNKNOWN', inplace=True)
print(df.loc[5, ['customer_id']])

# Intermediate: Standardize text values in 'channel' to title case
df['channel'] = df['channel'].str.title()
print(df['channel'].unique())

# Intermediate: Drop rows where a critical identifier is missing
df_before = df.shape[0]
df = df[df['customer_id'] != 'UNKNOWN']
df_after = df.shape[0]
print('Rows before:', df_before, 'Rows after dropping missing customer_id:', df_after)

# Intermediate: Replace outlier values using domain rules
outlier_idx = df[df['amount'] > 1000].index
df.loc[outlier_idx, 'amount'] = 1000
print(df.loc[outlier_idx, ['amount']])

# Intermediate: Remove obvious duplicate transactions
duplicates = df.duplicated(subset=['customer_id', 'amount', 'date'])
df = df[~duplicates]
print('Remaining rows after removing duplicates:', df.shape[0])

# Advanced: Merge transactions with customer profiles for richer analysis
merged = pd.merge(df, customers, on='customer_id', how='left')
print(merged.head(3))

# Advanced: Identify transactions with no matching customer info
missing_cust = merged[merged['segment'].isnull()]
print('Transactions with unknown customer details:')
print(missing_cust)

# Advanced: Impute missing categories using similar records
mode_channel = merged['channel'].mode()[0]
merged['channel'].fillna(mode_channel, inplace=True)
print('Imputed missing channels with:', mode_channel)

# Error Handling: Try-except for risky cleaning operations
try:
    merged['amount'] = merged['amount'].astype(float)
    print('Conversion to float successful.')
except Exception as e:
    print('Failed to convert amount:', str(e))

# Error Handling: Custom function to log and drop invalid data
def drop_invalid(df, col):
    n_invalid = df[col].isnull().sum()
    print(f'Dropping {n_invalid} rows with missing', col)
    return df[df[col].notnull()]
merged = drop_invalid(merged, 'customer_id')

# Best Practice: Write a data cleaning pipeline for repeatability
def clean_transactions(df):
    df = df.copy()
    df['amount'] = df['amount'].fillna(df['amount'].median())
    df['customer_id'] = df['customer_id'].fillna('UNKNOWN')
    df['channel'] = df['channel'].str.title()
    df = df[df['customer_id'] != 'UNKNOWN']
    return df
df_clean = clean_transactions(df)
print(df_clean.head(3))

# Simulate dirty data again for end-to-end test
df2 = df.copy()
df2.loc[15, 'amount'] = -999
df2.loc[20, 'customer_id'] = None
df2.loc[30, 'channel'] = 'bRanch'
cleaned = clean_transactions(df2)
summary = cleaned.groupby('channel')['amount'].agg(['count','mean']).reset_index()
print(summary)