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))

print('Amount column types:', df['amount'].dtype)
print('First 5 amounts:', df['amount'].head().values)

print('Transaction type unique values:', df['transaction_type'].unique())
print('Channel unique values:', df['channel'].unique())

# Convert numeric amounts to formatted strings with two decimal places
df['amount_str'] = df['amount'].apply(lambda x: f'${x:,.2f}')
print(df[['amount', 'amount_str']].head(3))

# Convert string amounts back to float
amount_float = df['amount_str'].apply(lambda s: float(s.replace('$','').replace(',','')))
print('First 3 reconverted amounts:', amount_float.head(3).values)

# Example: simple sum of all transaction amounts
total_sum = df['amount'].sum()
print(f'Total value of all transactions: ${total_sum:,.2f}')

# Example: count number of credit vs debit transactions
count_types = df['transaction_type'].value_counts()
print(count_types)

# Intermediate: Find and correct negative transaction amounts (not allowed)
negatives = df[df['amount'] < 0]
if not negatives.empty:
    print(f'Found {len(negatives)} negative amounts. Setting to zero.')
    df.loc[df['amount'] < 0, 'amount'] = 0
else:
    print('No negative amounts found.')

# Intermediate: extract numbers from messy text descriptions
examples = ['Paid $512.23 by EFT', 'ATM withdrawal $100', 'Salary $3000 for Feb', 'Refund of $54.9']
import re
amounts = [float(re.search(r'\$([0-9,.]+)', s).group(1).replace(',','')) for s in examples]
print('Extracted amounts:', amounts)

# Intermediate: Round all amounts to nearest dollar (bank rounding)
df['amount_rounded'] = df['amount'].round(0)
print(df[['amount', 'amount_rounded']].head())

# Intermediate: create summary string for reporting
row = df.iloc[0]
summary = f"{row['date'].strftime('%Y-%m-%d')} | {row['transaction_type']} | {row['channel']} | {row['amount_str']}"
print('Example summary:', summary)

# Advanced: Calculate rolling balance for a single customer by transaction date
single_customer = df[df['customer_id'] == df['customer_id'].iloc[0]].sort_values('date')
balance = single_customer['amount'].cumsum()
print('First 5 balances:', balance.head().values)

# Advanced: Flag suspicious large transactions
high_threshold = 500
df['flagged'] = df['amount'] > high_threshold
print(df[['amount', 'flagged']].head())

# Advanced: Parse and split strings into structured columns
messy = pd.Series(['2024/01/01:ATM:$100', '2024/02/05:Online:$250', '2024/03/15:POS:$64.00'])
split_data = messy.str.split(':', expand=True)
split_data.columns = ['date', 'channel', 'amount_str']
split_data['amount'] = split_data['amount_str'].apply(lambda x: float(x.replace('$','')))
print(split_data)

# Error handling: Try converting an invalid string to float
try:
    bad_value = float('one hundred dollars')
except ValueError as e:
    print('Error:', e)

# Error handling: Removing currency words and extra spaces before float conversion
sample = ' USD  150.75 '
try:
    cleaned = sample.replace('USD','').strip()
    value = float(cleaned)
    print(value)
except ValueError:
    print('Cannot convert to float')

# End-to-end: From raw strings to account summary
raw = ['2024-04-01:ATM:USD 100.50', '2024-04-02:Online:USD 200.00', '2024-04-05:POS:USD 50.75']
parts = [r.split(':') for r in raw]
df_e2e = pd.DataFrame(parts, columns=['date','channel','amount_str'])
df_e2e['amount'] = df_e2e['amount_str'].str.replace('USD','').astype(float)
balance = df_e2e['amount'].sum()
print('Account balance from all transactions:', balance)