<jupyter_start><jupyter_text>Visual Data Analysis of Fraudulent TransactionsYour CFO has also requested detailed trends data on specific card holders. Use the starter notebook to query your database and generate visualizations that supply the requested information as follows, then add your visualizations and observations to your markdown report.<jupyter_code># Initial imports
import pandas as pd
import calendar
import datetime
import plotly.express as px
import hvplot.pandas
from sqlalchemy import create_engine
# Create a connection to the database
engine = create_engine("postgresql://postgres:postgres@localhost:5432/fraud_detection")<jupyter_output><empty_output><jupyter_text>Data Analysis Question 1The two most important customers of the firm may have been hacked. Verify if there are any fraudulent transactions in their history. For privacy reasons, you only know that their cardholder IDs are 2 and 18.* Using hvPlot, create a line plot representing the time series of transactions over the course of the year for each cardholder separately. * Next, to better compare their patterns, create a single line plot that containins both card holders' trend data.  * What difference do you observe between the consumption patterns? Does the difference suggest a fraudulent transaction? Explain your rationale in the markdown report.<jupyter_code># loading data for card holder 2 and 18 from the database
# Write the query
query = """ 
        SELECT card_holder.id, transaction.date, transaction.amount
        FROM card_holder
        INNER JOIN credit_card
        ON card_holder.id = credit_card.cardholder_id 
        INNER JOIN transaction 
        ON credit_card.card = transaction.card 
        WHERE card_holder.id = 2 OR card_holder.id = 18
        """
# Create a DataFrame from the query result. HINT: Use pd.read_sql(query, engine)
transactions_df = pd.read_sql(query, engine)
transactions_df.head()
# Plot for cardholder 2
card_holder2 = transactions_df.loc[transactions_df["id"] == 2].sort_values(by = "date")
card_holder2.hvplot.line(x = "date", y = "amount", rot = 45)
# Plot for cardholder 18
card_holder18 = transactions_df.loc[transactions_df["id"] == 18].sort_values(by = "date")
card_holder18.hvplot.line(x = "date", y = "amount", rot = 45)
# Combined plot for card holders 2 and 18
card_holder18.hvplot.line(x = "date", y = "amount", rot = 45) * card_holder2.hvplot.line(x = "date", y = "amount", rot = 45)<jupyter_output><empty_output><jupyter_text>Data Analysis Question 2The CEO of the biggest customer of the firm suspects that someone has used her corporate credit card without authorization in the first quarter of 2018 to pay quite expensive restaurant bills. Again, for privacy reasons, you know only that the cardholder ID in question is 25.* Using Plotly Express, create a box plot, representing the expenditure data from January 2018 to June 2018 for cardholder ID 25.* Are there any outliers for cardholder ID 25? How many outliers are there per month?* Do you notice any anomalies? Describe your observations and conclusions in your markdown report.<jupyter_code># loading data of daily transactions from jan to jun 2018 for card holder 25
# Write the query
query = """
        SELECT EXTRACT (MONTH FROM DATE) AS month, EXTRACT (DAY FROM DATE) AS day, amount
        FROM card_holder
        INNER JOIN credit_card 
        ON card_holder.id = credit_card.cardholder_id 
        INNER JOIN transaction 
        ON credit_card.card = transaction.card 
        WHERE cardholder_id = 25 AND
        EXTRACT (YEAR FROM DATE) = 2018 AND
        EXTRACT (MONTH FROM DATE) >= 1 AND
        EXTRACT (MONTH FROM DATE) <= 6
        """
# Create a DataFrame from the query result. HINT: Use pd.read_sql(query, engine)
card_holder_25 = pd.read_sql(query, engine)
card_holder_25.head()
# loop to change the numeric month to month names
card_holder_25.month = card_holder_25.month.astype(int)

for i in range(0, len(card_holder_25)):
    month_number = str(card_holder_25.loc[i, "month"])
    datetime_object = datetime.datetime.strptime(month_number, "%m")
    full_month_name = datetime_object.strftime("%B")
    card_holder_25.loc[i, "month"] = full_month_name

card_holder_25.head()
# Creating the six box plots using plotly express
px.box(
    card_holder_25, 
    x = "month", y = "amount", 
    color = "month", 
    labels = {"month": "Month", "amount": "Transaction Amount"}, 
    title = "Monthly Transactions for Card Holder 25")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07401644809957768, 'fraction_numerical': 0.020893531895976883, 'mean_word_length': 4.376344086021505, 'pattern_counts': {'":': 2, '<': 14, '<?xml version=': 0, '>': 14, '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': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '536005', 'license': 'MIT', 'n_tokens_mistral': 1356, 'n_tokens_neox': 1227, 'n_words': 514, 'path': 'visual_data_analysis.ipynb', 'repo_name': 'GuilleMGN/SuspiciousTransactions', 'size': '4499'}