import pandas as pd
import numpy as np
from sklearn.cluster import KMeans

general_info = pd.read_csv('./cc_info.csv')
transaction = pd.read_csv('./transactions.csv',parse_dates=['date'])

general_info.head()

transaction['month'] = pd.to_datetime(transaction['date'],format = '%Y%m%d')

transaction['month'] = transaction['month'].dt.month

transaction.head()

## collect monthly amount for each card holder

credit = pd.merge(general_info,transaction,on = ['credit_card'])

credit.head()

task1 = credit.groupby(['month','credit_card']).agg({'credit_card_limit':'min','transaction_dollar_amount':'sum'})

task1.reset_index(inplace = True)

task1 = task1.loc[(task1['transaction_dollar_amount']<=task1['credit_card_limit'])]

over_limit_user = task1['credit_card'].unique()

### Ratio of identified card holders

over_limit_user.size/credit['credit_card'].unique().size

### details for over limit holders monthly transaction which are over amount

print(task1)

def credit_alarm(date):
    month = date.month
    print(month)
    task2 = credit.groupby(['month','credit_card']).agg({'credit_card_limit':'min','transaction_dollar_amount':'sum'})
    task2.reset_index(inplace = True)
    users = task2.loc[(task2['month'] == month) & (task2['transaction_dollar_amount']>task2['credit_card_limit'])]['credit_card']
    return users.unique()

# test with example: 2015-10-29 18:23:04
date = pd.to_datetime(['2015-10-29 18:23:04'])
credit_alarm(date[0])

### 1. transform register zip code to [longitude,latitude]

from geopy.geocoders import Nominatim

credit['register_place'] = credit['city']+','+credit['state']

credit['trans_place'] = credit[['Lat','Long']].values.tolist()

geolocator = Nominatim(user_agent="custering")

#adding longituede,latitude information to cities
#since we have over 290000 records but only have 124 distinct cities.

cities = credit['register_place'].unique()

df = pd.DataFrame({'name': cities})

from geopy.geocoders import Nominatim
geolocator = Nominatim(user_agent="clustering")

from geopy.extra.rate_limiter import RateLimiter
geocode = RateLimiter(geolocator.geocode, min_delay_seconds=1)
df['location'] = df['name'].apply(geocode)

df['point'] = df['location'].apply(lambda x:[x.latitude,x.longitude] if x else None)

df.head()

#merging back to credit and remove duplicate grographic columns
df = pd.merge(credit[['credit_card','credit_card_limit','transaction_dollar_amount','register_place','trans_place']],df[['name','point']],left_on = ['register_place'],right_on = ['name'])

df['register_place'] = df['point']

df

### 2. calculate the distance between resgiered place and transaction place

from geopy.distance import geodesic
df['distance'] = df.apply(lambda x: geodesic(x['register_place'], x['trans_place']).km, axis=1)

df.head()

def stst_percentile(x):
    ps = [25,50,75]
    res = np.percentile(x,ps)
    return pd.Series(res,index = ['{}%'.format(p) for p in ps])

percent = credit.groupby(['credit_card'])['transaction_dollar_amount'].apply(stst_percentile).unstack()

percent.head()

credit_df = pd.merge(df,percent,on= ['credit_card'])

credit_df.to_csv('./credit_df.csv',index = False)



### 1. Clustering: PCA+ k-means

credit_df.head()

from sklearn.preprocessing import scale
#standarization
feature = ['credit_card_limit','transaction_dollar_amount','distance','25%','50%','75%']
X = credit_df[feature]
X = scale(X)

import matplotlib.pyplot as plt
from sklearn.decomposition import PCA
pca = PCA(n_components=2)
PrincipleComp = pca.fit_transform(X)
x2d = pd.DataFrame(PrincipleComp,columns=['pc1','pc2'])
plt.scatter(x2d.pc1,x2d.pc2,alpha = 0.1)

from mpl_toolkits.mplot3d import Axes3D
pca = PCA(n_components=3)
PrincipleComp = pca.fit_transform(X)
x3d = pd.DataFrame(PrincipleComp,columns=['pc1','pc2','pc3'])
fig = plt.figure()
ax = Axes3D(fig, rect=[0, 0, 1, 1], elev=30, azim=20)
plt.scatter(x3d['pc1'], x3d['pc2'], x3d['pc3'],marker='o')

#hyperparameter k selection
inertia = []
for k in range(1, 8):
    kmeans = KMeans(n_clusters=k, random_state=1).fit(X)
    inertia.append(np.sqrt(kmeans.inertia_))

plt.plot(range(1, 8), inertia, marker='s');
plt.xlabel('$k$')
plt.ylabel('$J(C_k)$');

## K-means to cluster


n_clusters = 6
kmeans = KMeans(n_clusters = n_clusters).fit(x3d)

x3d['label'] = kmeans.labels_

x3d['label'].value_counts()

import itertools
colors = itertools.cycle( ['r','g','b','c','m','y','k'] )

fig = plt.figure(figsize = (12,10) )
ax = Axes3D(fig, rect=[0, 0, 1, 1], elev=30, azim=20)
ax.set_xlim3d(-5, 10)
for label in range(n_clusters) :
    temp = x3d.loc[x3d.label == label,:]
    plt.scatter(temp.pc1,temp.pc2,temp.pc3, c=next(colors),label=label,alpha=0.3,marker='o')

plt.legend(loc='best')
plt.show()

## check the distribution of x3d labels

x3d.label.value_counts()

suspicious_label=4

suspect = credit_df.loc[x3d.label ==4]

suspect.to_csv('./suspect',index = False)

cols = ["transaction_dollar_amount",'75%']
plt.hist(suspect.loc[:,cols].values,bins=50,label=cols)
plt.legend(loc='best')
plt.show()

unsuspect = credit_df.loc[x3d.label !=3]

plt.hist(suspect.loc[:,['distance']].values,bins=50,density = True,label = 'suspect')
plt.hist(unsuspect.loc[:,['distance']].values,bins=50,density = True,label = 'unsuspect')
plt.legend(loc='best')
plt.show()

### deleting 0 distance and replot

unsuspect = unsuspect.loc[unsuspect.distance>100]

plt.hist(suspect.loc[:,['distance']].values,bins=50,density = True,label = 'suspect')
plt.hist(unsuspect.loc[:,['distance']].values,bins=50,density = True,label = 'unsuspect')
plt.legend(loc='best')
plt.show()

#ratio of suspectious fraud transactions
suspect.shape[0]/credit_df.shape[0]*100

#total amount of suspectious fraud transactions
suspect['transaction_dollar_amount'].sum()

#for diatance
dis_x = suspect['distance']
dis_y = unsuspect['distance']
#z-test
from statsmodels.stats.weightstats import ztest
r1 = ztest(dis_x,dis_y)
print(r1)
#Mann Whitney U Test
from scipy.stats import mannwhitneyu
r2 = mannwhitneyu(dis_x,dis_y)
print(r2)

#for total_amount
dis_x = suspect['transaction_dollar_amount']
dis_y = unsuspect['transaction_dollar_amount']
#z-test
from statsmodels.stats.weightstats import ztest
r1 = ztest(dis_x,dis_y)
print(r1)
#Mann Whitney U Test
from scipy.stats import mannwhitneyu
r2= mannwhitneyu(dis_x,dis_y)
print(r2)