-
Notifications
You must be signed in to change notification settings - Fork 0
/
Updated Code
79 lines (66 loc) · 3.35 KB
/
Updated Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
import pandas as pd
# Load the CSV files
behavior_df = pd.read_csv('/path/to/behavior.csv')
atm_df = pd.read_csv('/path/to/atm.csv')
# Ensure correct data types
behavior_df['Customer Number'] = behavior_df['Customer Number'].astype(int)
atm_df['Customer Number'] = atm_df['Customer Number'].astype(int)
# Initialize lists to store final table data
final_data = {
'Customer Number': [],
'ATM Deposit Normal Volume': [],
'ATM Deposit Excessive Volume': [],
'ATM Withdrawal Normal Volume': [],
'ATM Withdrawal Excessive Volume': [],
'Total ATM Deposit': [],
'Average ATM Deposit': [],
'STD ATM Deposit': [],
'Total ATM Withdrawal': [],
'Average ATM Withdrawal': [],
'STD ATM Withdrawal': [],
'Action for ATM Deposit': [],
'Action for ATM Withdrawal': []
}
# Iterate through each unique customer in behavior_df
for index, row in behavior_df.iterrows():
customer_number = row['Customer Number']
customer_behavior = row
# Filter the ATM transactions for the current customer
customer_atm = atm_df[atm_df['Customer Number'] == customer_number]
deposit_df = customer_atm[customer_atm['Transaction Type'] == 'Deposit'][['Jan-24', 'Feb-24', 'Mar-24']]
withdrawal_df = customer_atm[customer_atm['Transaction Type'] == 'Withdrawal'][['Jan-24', 'Feb-24', 'Mar-24']]
total_atm_deposit = deposit_df.sum(axis=1).sum()
average_atm_deposit = deposit_df.mean(axis=1).mean()
std_atm_deposit = deposit_df.std(axis=1).std()
total_atm_withdrawal = withdrawal_df.sum(axis=1).sum()
average_atm_withdrawal = withdrawal_df.mean(axis=1).mean()
std_atm_withdrawal = withdrawal_df.std(axis=1).std()
if average_atm_deposit > customer_behavior['ATM Deposit Excessive Volume']:
action_for_atm_deposit = 'Increase'
elif average_atm_deposit < customer_behavior['ATM Deposit Normal Volume']:
action_for_atm_deposit = 'Decrease'
else:
action_for_atm_deposit = 'No Action'
if average_atm_withdrawal > customer_behavior['ATM Withdrawal Excessive Volume']:
action_for_atm_withdrawal = 'Increase'
elif average_atm_withdrawal < customer_behavior['ATM Withdrawal Normal Volume']:
action_for_atm_withdrawal = 'Decrease'
else:
action_for_atm_withdrawal = 'No Action'
final_data['Customer Number'].append(customer_number)
final_data['ATM Deposit Normal Volume'].append(customer_behavior['ATM Deposit Normal Volume'])
final_data['ATM Deposit Excessive Volume'].append(customer_behavior['ATM Deposit Excessive Volume'])
final_data['ATM Withdrawal Normal Volume'].append(customer_behavior['ATM Withdrawal Normal Volume'])
final_data['ATM Withdrawal Excessive Volume'].append(customer_behavior['ATM Withdrawal Excessive Volume'])
final_data['Total ATM Deposit'].append(total_atm_deposit)
final_data['Average ATM Deposit'].append(average_atm_deposit)
final_data['STD ATM Deposit'].append(std_atm_deposit)
final_data['Total ATM Withdrawal'].append(total_atm_withdrawal)
final_data['Average ATM Withdrawal'].append(average_atm_withdrawal)
final_data['STD ATM Withdrawal'].append(std_atm_withdrawal)
final_data['Action for ATM Deposit'].append(action_for_atm_deposit)
final_data['Action for ATM Withdrawal'].append(action_for_atm_withdrawal)
# Create the final table
final_df = pd.DataFrame(final_data)
# Display the final table
print(final_df)