<jupyter_start><jupyter_text>Load Data<jupyter_code>df = pd.read_csv('data/training.csv')<jupyter_output><empty_output><jupyter_text>Save to CSV<jupyter_code>#df_step2_2.to_csv("data/value_df.csv")<jupyter_output><empty_output><jupyter_text>**EDA (exploratory data analysis ) --------------------------------------**<jupyter_code>df.head()
df.describe()
df.info()
df.columns
df["CustomerId"].nunique()
df["TransactionId"].nunique()
print(df["FraudResult"].value_counts().to_frame())<jupyter_output>FraudResult
0        95469
1          193<jupyter_text>Removing string and change to integer<jupyter_code>columns = ['TransactionId', 'BatchId', 'AccountId', 'SubscriptionId', 
           'CustomerId', 'ProviderId', 'ProductId', 'ChannelId']

import re

for col in columns:
    df[col] = df[col].str.replace('\D', '')
    df[col] = df[col].apply(np.int64)<jupyter_output><empty_output><jupyter_text>Changing the Datetime- and create new columns for **weekday, hour, day**<jupyter_code>df.TransactionStartTime = pd.to_datetime(df.TransactionStartTime)
df['Date'] = df['TransactionStartTime'].apply(lambda x: pd.datetime.strftime(x, '%Y-%m-%d' ))
# .weekday() 
df['weekday'] = df['TransactionStartTime'].dt.weekday
df["weekday"].nunique()
df['hour'] = df['TransactionStartTime'].dt.hour
df["hour"].nunique()
df['day'] = df['TransactionStartTime'].dt.day
df["day"].nunique()<jupyter_output><empty_output><jupyter_text>Drop useless Columns- CurrencyCode, CountryCode<jupyter_code>cols = ['CurrencyCode', 'CountryCode']
df.drop(cols, axis = 1, inplace = True)<jupyter_output><empty_output><jupyter_text>Export Data for EDA After did some cleaning above<jupyter_code>#df.to_csv("data/data_eda.csv")<jupyter_output><empty_output><jupyter_text>Looking at the Data with first Plots **ProductCategory**<jupyter_code>plt.rcParams['font.size'] = 14
plt.figure(figsize=(10,8))
sns.set_style('whitegrid')
sns.countplot(x='ProductCategory',data=df[df["FraudResult"] == 1], palette='ocean')
plt.yticks(size=16)
plt.xticks(size=16)
plt.show()<jupyter_output><empty_output><jupyter_text>**ProductId**<jupyter_code>plt.figure(figsize=(10,6))
sns.set_style('whitegrid')
sns.countplot(x='ProductId',data=df[df["FraudResult"] == 1], palette='ocean')
plt.yticks(size=16)
plt.xticks(size=16)
plt.show()

#plt.savefig("ProductId.png")<jupyter_output><empty_output><jupyter_text>**ProviderId**<jupyter_code>plt.figure(figsize=(12,8))
sns.set_style('whitegrid')
sns.countplot(x='ProviderId',data=df[df["FraudResult"] == 1], palette='ocean')
plt.yticks(size=16)
plt.xticks(size=16)
plt.show()<jupyter_output><empty_output><jupyter_text>**ChannelId**- Fraudulent transactions mostly happen on one specific channel.<jupyter_code>plt.figure(figsize=(10,6))
sns.set_style('whitegrid')
sns.countplot(x='ChannelId',data=df[df["FraudResult"] == 1], palette='ocean')
plt.yticks(size=16)
plt.xticks(size=16)
plt.show()<jupyter_output><empty_output><jupyter_text>**PricingStrategy**<jupyter_code>plt.figure(figsize=(10,8))
sns.set_style('whitegrid')
sns.countplot(x='PricingStrategy',data=df[df["FraudResult"] == 1], palette='ocean')
plt.yticks(size=16)
plt.xticks(size=16)
plt.show()<jupyter_output><empty_output><jupyter_text>**Hour**<jupyter_code>fig, ax = plt.subplots(figsize=(8,7))
sns.set_style('whitegrid')
sns.countplot(x='hour',data=df[df["FraudResult"] == 1], palette='ocean')
plt.yticks(size=16)
plt.xticks(size=16)
for p in ax.patches:
    ax.annotate(str(format(int(p.get_height()), ',d')), (p.get_x(), p.get_height()*1.01))<jupyter_output><empty_output><jupyter_text>**weekday**<jupyter_code>plt.figure(figsize=(10,8))
sns.set_style('whitegrid')
sns.countplot(x='weekday',data=df[df["FraudResult"] == 1], palette='ocean')
plt.yticks(size=16)
plt.xticks(size=16)
plt.show()<jupyter_output><empty_output><jupyter_text>**Feature Engineering (aggregation)**  Example of **.unstack()**- https://stackoverflow.com/questions/61052816/how-to-filter-dataframe-on-two-columns-and-output-cumulative-sum - Looking at customer Ids<jupyter_code>cf = df.groupby(["CustomerId", "Date"]).agg({"TransactionId": "count", "ProductId": "max",
                                      "ChannelId": "max", "Amount" : "mean",  "FraudResult": "sum"}).sort_values("CustomerId", ascending=False) 
display(cf.index[0])
display(cf["Amount"])
cf #.columns<jupyter_output><empty_output><jupyter_text>**Create new Dataset for aggregation** - We take df and **create dummies** with drop_first=False<jupyter_code>df_dummy = pd.get_dummies(df, columns=["ProviderId", "ProductId", "ChannelId", "PricingStrategy"], drop_first=False)
df_dummy.columns
np.sort(df_dummy[df_dummy["CustomerId"] == 334]["Date"].unique())<jupyter_output><empty_output><jupyter_text>**Step 1: Create again new columns based on the old columns**  - **fraud_cumsum:** shows us the count of fraud up to every timestamp by id<jupyter_code>df_step1 = df_dummy.sort_values('TransactionStartTime', ascending=True)
df_step1["fraud_cumsum"] = df_dummy.groupby('CustomerId')['FraudResult'].cumsum()<jupyter_output><empty_output><jupyter_text>- **value_cumsum:** shows us the sum of Amount up to every timestamp by id<jupyter_code>df_step1["value_cumsum"] = df_dummy.groupby('CustomerId')['Value'].cumsum()<jupyter_output><empty_output><jupyter_text>- **transaction_cumsum:** shows us the sum of Transactions up to every timestamp by id<jupyter_code>df_step1["transaction_cumsum"] = df_dummy.groupby('CustomerId')['TransactionId'].cumcount()+1<jupyter_output><empty_output><jupyter_text>- **value_mean:** shows us the mean of the Amount by transactions up to every timestamp by id<jupyter_code>df_step1["value_mean"] = df_step1["value_cumsum"]/df_step1["transaction_cumsum"]<jupyter_output><empty_output><jupyter_text>**Step 2.1: Create again new columns based on the new Dummies (mean by transaction of each column and id up to every timestamp)**<jupyter_code>group = ['ProviderId_1', 'ProviderId_2', 'ProviderId_3', 'ProviderId_4', 
         'ProviderId_5', 'ProviderId_6', 'ProductId_1', 'ProductId_2',
           'ProductId_3', 'ProductId_4', 'ProductId_5', 'ProductId_6',
           'ProductId_7', 'ProductId_8', 'ProductId_9', 'ProductId_10',
           'ProductId_11', 'ProductId_12', 'ProductId_13', 'ProductId_14',
           'ProductId_15', 'ProductId_16', 'ProductId_19', 'ProductId_20',
           'ProductId_21', 'ProductId_22', 'ProductId_23', 'ProductId_24',
           'ProductId_27', 'ChannelId_1', 'ChannelId_2', 'ChannelId_3', 'ChannelId_5',
           'PricingStrategy_0', 'PricingStrategy_1', 'PricingStrategy_2', 'PricingStrategy_4']

df_step2_1 = df_step1.sort_values('TransactionStartTime', ascending=True).copy()
for g in group:
    df_step2_1[g] = df_dummy.groupby('CustomerId')[g].cumsum()/df_step1["transaction_cumsum"]<jupyter_output><empty_output><jupyter_text>**Step 2.2: Create new DF and new columns based on the new Dummies (mean by transaction of each column and id)**<jupyter_code>df_step2_2 = df_step1.sort_values('TransactionStartTime', ascending=True).copy()
for g in group:
    gb = df_dummy.groupby('CustomerId').agg({f: "sum","TransactionId": "count"}) 
    for g in gb.index:
        df_step2_2.loc[df_dummy['CustomerId'] == g, f] = gb[f][g]/gb["TransactionId"][g]<jupyter_output><empty_output><jupyter_text>Creating df_plot (based on original Dataframe):<jupyter_code>df_plot = df.sort_values('TransactionStartTime', ascending=True).copy()
df_plot["fraud_cumsum"] = df.groupby('CustomerId')['FraudResult'].cumsum() 
df_plot["value_cumsum"] = df.groupby('CustomerId')['Value'].cumsum()
df_plot["transaction_cumsum"] = df.groupby('CustomerId')['TransactionId'].cumcount()+1
df_plot["value_mean"] = df_plot["value_cumsum"]/df_plot["transaction_cumsum"]
df_plot = df_plot.sort_values('TransactionStartTime', ascending=True).copy()
df_plot["fraud_total"] = 0
df_plot["Value_total"] = 0
df_plot["fraud"] = 0
df_plot["fraud_history"] = 0

gb = df_step2_2.groupby('CustomerId').agg({"Amount": "sum","FraudResult": "sum", "Value": "sum"})

for g in gb.index:
    if gb["FraudResult"][g] == 0:
        frauds = "No Frauds"
    elif gb["FraudResult"][g] == 1:
        frauds = "one Fraud"
    elif gb["FraudResult"][g] > 1:
        frauds = "several Frauds"
    
    df_plot.loc[df_plot['CustomerId'] == g, "fraud_total"] = gb["FraudResult"][g]
    df_plot.loc[df_plot['CustomerId'] == g, "Value_total"] = gb["Value"][g]
    df_plot.loc[df_plot['CustomerId'] == g, "fraud"] = 1 if gb["FraudResult"][g] > 0 else 0
    df_plot.loc[df_plot['CustomerId'] == g, "fraud_history"] = frauds  
df_plot.columns<jupyter_output><empty_output><jupyter_text>Export df_plot to csv<jupyter_code>#df_plot.to_csv("data/df_plot.csv")<jupyter_output><empty_output><jupyter_text>**Plots with the new variables from the Feature Engineering** **The higher the transaction-volume the higher the probability to be fraudulent.**<jupyter_code>sns.set_theme(style="white")
sns.relplot(x="Value", y="fraud_total", hue="fraud_history", size="Value",
            sizes=(50, 500), alpha=.5, palette="muted",
            height=6, data=df_plot)
plt.yticks(size=16)
plt.xticks(size=16)
plt.show()<jupyter_output><empty_output><jupyter_text>**Value distribution of no frauds, one fraud and several frauds within a week**<jupyter_code>sns.set(style="whitegrid")
fig, ax = plt.subplots(figsize=(18,10))
sns.barplot(x="weekday", y="Value", hue="fraud_history", data=df_plot, ci=None);
for p in ax.patches:
    ax.annotate(str(format(int(p.get_height()), ',d')), (p.get_x(), p.get_height()*1.01))
plt.yticks(size=16)
plt.xticks(size=16)
plt.show()<jupyter_output><empty_output><jupyter_text>**Value distribution of no frauds, one fraud and several frauds within a day**<jupyter_code>sns.set(style="darkgrid")
plt.figure(figsize=(18,10))
sns.barplot(x="hour", y="Value", hue="fraud_history", data=df_plot, ci=None);
plt.yticks(size=16)
plt.xticks(size=16)
plt.show()<jupyter_output><empty_output><jupyter_text>**Value distribution of no frauds, one fraud and several frauds within the ProviderId**<jupyter_code>sns.set(style="darkgrid")
plt.figure(figsize=(18,10))
sns.barplot(x="ProviderId", y="Value", hue="fraud_history", data=df_plot, ci=None);
plt.yticks(size=16)
plt.xticks(size=16)
plt.show()<jupyter_output><empty_output><jupyter_text>**Value distribution of no frauds, one fraud and several frauds within the ProductId**<jupyter_code>sns.set(style="darkgrid")
plt.figure(figsize=(20,10))
sns.barplot(x="ProductId", y="Value", hue="fraud_history", data=df_plot, ci=None);
plt.yticks(size=16)
plt.xticks(size=16)
plt.show()<jupyter_output><empty_output><jupyter_text>**Value distribution of no frauds, one fraud and several frauds**<jupyter_code>sns.set(style="darkgrid")
plt.figure(figsize=(10,10))
sns.barplot(x="fraud_history", y="Value", data=df_plot, palette="Blues_d")<jupyter_output><empty_output><jupyter_text>**Pieplots**<jupyter_code>df_plot["fraud_history"].value_counts().to_frame()
df_plot.groupby('FraudResult').agg({"Value": "mean","CustomerId": "count", "TransactionId": "count"})<jupyter_output><empty_output><jupyter_text>**0.2% of all transactions are fraud.**<jupyter_code>names='No Frauds (96.3%)', 'one Fraud (2%)', 'several Frauds (1.7%)'
values=[92091,1968 + 1603]
names='No Frauds (99.8%)', 'Fraud (0.2%)'
values=[99.8,0.2]
plt.figure(figsize=(12,10))
colors = ['#4F6272', '#B7C3F3', '#DD7596', '#8EB897']
plt.pie(values, labels=names, labeldistance=1.15, wedgeprops = { 'linewidth' : 2, 'edgecolor' : 'white' }, colors=colors);<jupyter_output><empty_output><jupyter_text>**32% of the overall transaction value is fraud.**<jupyter_code>names='No Frauds (55.2%)', 'one Fraud (4.3%)', 'several Frauds (40.5%)'
values=[522512742,40620263,383976656]
names='No Frauds (68%)', 'Fraud (32%)'
values=[68,32]
plt.figure(figsize=(12,10))
colors = ['#4F6272', '#B7C3F3', '#DD7596', '#8EB897']
plt.pie(values, labels=names, labeldistance=1.15, wedgeprops = { 'linewidth' : 2, 'edgecolor' : 'white' }, colors=colors);<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.18270758424617442, 'fraction_numerical': 0.029099423028681327, 'mean_word_length': 8.006024096385541, 'pattern_counts': {'":': 11, '<': 132, '<?xml version=': 0, '>': 134, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '33', 'dir': 'jupyter-structured-clean-dedup', 'id': '265616', 'license': 'MIT', 'n_tokens_mistral': 4793, 'n_tokens_neox': 4472, 'n_words': 685, 'path': 'notebooks/EDA_feature_engineering.ipynb', 'repo_name': 'mariaulf4h/dash-fraud-detection', 'size': '11959'}