<jupyter_start><jupyter_text>ChallengeAnother approach to identifying fraudulent transactions is to look for outliers in the data. Standard deviation or quartiles are often used to detect outliers. Using this starter notebook, code two Python functions:* One that uses standard deviation to identify anomalies for any cardholder.* Another that uses interquartile range to identify anomalies for any cardholder. Identifying Outliers using Standard Deviation<jupyter_code># Initial imports
import pandas as pd
import numpy as np
import random
import os
from sqlalchemy import create_engine


username = "Randolph"
password = "<PASSWORD>"
host     = "pg-335df34c-rpatronage-18ac.aivencloud.com"
port     = 19786
database = "Randolph"

connection_str = f"postgresql://{username}:{password}@{host}:{port}/{database}"

print(connection_str)
# Create a connection to the database
engine = create_engine("postgresql://Randolph:s0grycfkrbz6bjlz@pg-335df34c-rpatronage-18ac.aivencloud.com:19786/Randolph")

query = "select a.id, c.date, c.amount \
from card_holder a \
inner join credit_card b \
on a.id = b.cardholder_id \
inner join transaction c \
on b.card = c.card"

df = pd.read_sql(query, engine)
df.head()

# Write function that locates outliers using standard deviation
def card_transaction(input_id):
    return df.loc[df['id']==input_id, 'amount']

def outliers(input_id):
    df1 =card_transaction(input_id)
    return pd.DataFrame(df1[df1> df1.mean()+3*df1.std()])

# Find anomalous transactions for 3 random card holders
rand_card_holders = np.random.randint(1,25,3)

for id in rand_card_holders:
    if len(outliers(id)) == 0:
        print(f"Card holder {id} has no outlier transactions.")
    else:
        print(f"Card holder {id} has outlier transactions as below:\n{outliers(id)}.")<jupyter_output>Card holder 9 has outlier transactions as below:
      amount
613   1534.0
1578  1795.0
3389  1724.0.
Card holder 10 has no outlier transactions.
Card holder 19 has no outlier transactions.<jupyter_text>Identifying Outliers Using Interquartile Range<jupyter_code># Write a function that locates outliers using interquartile range
def outliers_iqr(input_id):
    df1 =card_transaction(input_id)
    IQR_threshold = np.quantile(df1, .75)+(np.quantile(df1, .75)-np.quantile(df1, .25))*1.5
    return pd.DataFrame(df1[df1> IQR_threshold])
# Find anomalous transactions for 3 random card holders
for id in rand_card_holders:
    if len(outliers_iqr(id)) == 0:
        print(f"Card holder {id} has no outlier transactions.")
    else:
        print(f"Card holder {id} has outlier transactions as below:\n{outliers_iqr(id)}.")<jupyter_output>Card holder 9 has outlier transactions as below:
      amount
613   1534.0
852   1009.0
1001   325.0
1466   245.0
1578  1795.0
1632   691.0
1909   267.0
2575  1095.0
2703  1179.0
3251    57.0
3389  1724.0.
Card holder 10 has no outlier transactions.
Card holder 19 has no outlier transactions.
{'fraction_non_alphanumeric': 0.09196185286103542, 'fraction_numerical': 0.06164850136239782, 'mean_word_length': 4.551984877126654, 'pattern_counts': {'":': 0, '<': 8, '<?xml version=': 0, '>': 10, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, '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': '389246', 'license': 'PostgreSQL', 'n_tokens_mistral': 1095, 'n_tokens_neox': 943, 'n_words': 305, 'path': 'Starter_Files/challenge.ipynb', 'repo_name': 'jrandolph13/Looking-for-Suspicious-Transactions', 'size': '2942'}