<jupyter_start><jupyter_text>Challenge   Identifying Outliers using Standard Deviation<jupyter_code># initial imports
import pandas as pd
import numpy as np
import random
from sqlalchemy import create_engine
import os

POSTGRES_KEY= os.getenv('POSTGRES_KEY')
# create a connection to the database
engine = create_engine(f"postgresql://postgres:{POSTGRES_KEY}@localhost:5432/Credit_Card_Transactions")

query = "SELECT t.id, t.date, t.amount, t.card, t.id_merchant, cc.cardholder_id FROM transaction t JOIN credit_card cc ON (t.card = cc.card)"

transactions_df = pd.read_sql(query, engine)

transactions_df.head()
# code a function to identify outliers based on standard deviation

def outliners_std (client_id):
    # outliners_std function receives cardholder id as parameter and returns
    # all the transactions_df rows with amounts > (mean + 1std) and amounts < (mean - 1std)
    
    transactions_client_df = transactions_df[transactions_df['cardholder_id'] == int(client_id)]
    client_std = transactions_client_df['amount'].std()
    client_mean = transactions_client_df['amount'].mean()
    outliners_df = transactions_client_df[(transactions_client_df['amount'] > (client_mean+client_std)) + (transactions_client_df['amount'] < (client_mean-client_std))]
    return outliners_df
# find anomalous transactions for 3 random card holders

outliners_2 = outliners_std(2)
outliners_25 = outliners_std(25)
outliners_18 = outliners_std(18)

print("========================================")
print("Example of Outliners for Card Holder #2:")
print(outliners_2[['id', 'date', 'amount','card','id_merchant']].head())
print(f"Total Outliners : {outliners_2['amount'].count()}")
print("========================================")
print("Example of Outliners for Card Holder #25:")
print(outliners_25[['id', 'date', 'amount','card','id_merchant']].head())
print(f"Total Outliners : {outliners_25['amount'].count()}")
print("========================================")
print("Example of Outliners for Card Holder #18:")
print(outliners_18[['id', 'date', 'amount','card','id_merchant']].head())
print(f"Total Outliners : {outliners_18['amount'].count()}")
print("========================================")<jupyter_output>========================================
Example of Outliners for Card Holder #2:
       id                date  amount                 card  id_merchant
44   2439 2018-01-06 02:16:41    1.33  4866761290278198714          127
57   3028 2018-01-07 15:10:27   17.29  4866761290278198714          126
141  2655 2018-01-16 06:29:35   17.64         675911140852          136
333  3395 2018-02-03 18:05:39    1.41  4866761290278198714           65
369  2878 2018-02-08 05:12:18   18.32  4866761290278198714           57
Total Outliners : 45
========================================
Example of Outliners for Card Holder #25:
        id                date  amount           card  id_merchant
296   1415 2018-01-30 18:31:00  1177.0  4319653513507           64
636   2840 2018-03-06 07:18:09  1334.0  4319653513507           87
960   1341 2018-04-08 06:03:50  1063.0  4319653513507           16
1306  1377 2018-05-13 06:31:20  1046.0  4319653513507           48
1510  1790 2018-06-04 03:46:15  1162.0  4319653[...]<jupyter_text>Identifying Outliers Using Interquartile Range<jupyter_code># code a function to identify outliers based on interquartile range

def outliners_iqr (client_id):
    # outliners_iqr function receives cardholder id as parameter and returns
    # all the transactions_df rows with amounts > (percentile 75) and amounts < (percentile 25)
    
    transactions_client_df = transactions_df[transactions_df['cardholder_id'] == int(client_id)]
    #client_std = transactions_client_df['amount'].std()
    #client_mean = transactions_client_df['amount'].mean()
    client_q75, client_q25 = np.percentile(transactions_client_df['amount'], [75 ,25])
    outliners_df = transactions_client_df[(transactions_client_df['amount'] > client_q75) + (transactions_client_df['amount'] < client_q25)]
    return outliners_df
# find anomalous transactions for 3 random card holders
outliners_2 = outliners_iqr(2)
outliners_25 = outliners_iqr(25)
outliners_18 = outliners_iqr(18)

print("========================================")
print("Example of Outliners for Card Holder #2:")
print(outliners_2[['id', 'date', 'amount','card','id_merchant']].head())
print(f"Total Outliners : {outliners_2['amount'].count()}")
print("========================================")
print("Example of Outliners for Card Holder #25:")
print(outliners_25[['id', 'date', 'amount','card','id_merchant']].head())
print(f"Total Outliners : {outliners_25['amount'].count()}")
print("========================================")
print("Example of Outliners for Card Holder #18:")
print(outliners_18[['id', 'date', 'amount','card','id_merchant']].head())
print(f"Total Outliners : {outliners_18['amount'].count()}")
print("========================================")<jupyter_output>========================================
Example of Outliners for Card Holder #2:
       id                date  amount                 card  id_merchant
44   2439 2018-01-06 02:16:41    1.33  4866761290278198714          127
57   3028 2018-01-07 15:10:27   17.29  4866761290278198714          126
141  2655 2018-01-16 06:29:35   17.64         675911140852          136
333  3395 2018-02-03 18:05:39    1.41  4866761290278198714           65
369  2878 2018-02-08 05:12:18   18.32  4866761290278198714           57
Total Outliners : 50
========================================
Example of Outliners for Card Holder #25:
       id                date  amount             card  id_merchant
6    2083 2018-01-02 02:06:21    1.46    4319653513507           93
56   2108 2018-01-07 14:57:23    2.93    4319653513507          137
79    754 2018-01-10 00:25:40    1.39  372414832802279           50
120  3023 2018-01-14 05:02:22   17.84  372414832802279           52
138  3333 2018-01-16 02:26:16    1.65  372[...]
{'fraction_non_alphanumeric': 0.1860231271995978, 'fraction_numerical': 0.1526730350259762, 'mean_word_length': 4.26278659611993, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 11, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '360387', 'license': 'PostgreSQL', 'n_tokens_mistral': 2498, 'n_tokens_neox': 1899, 'n_words': 466, 'path': 'Starter_Files/challenge.ipynb', 'repo_name': 'amejiali/sql-homework', 'size': '5967'}