<jupyter_start><jupyter_text>Reading and exploting the raw data As our data scientist wants to start working on this project they initially obtain a dump of transactions with raw features from AWS Redshift! Their initial goal is to explore the data and discover features that may prove as indicative for fraud.The data scientists use pandas installed on AWS SageMaker notebook to read the file to the memory and start creating visualizations of the raw data with common python data science libraries such as matplotlib and seaborn.<jupyter_code>engine = get_engine()
df = pd.read_sql('select * FROM raw_data', con=engine)
# Showing a sample of rows
df.sample(5)
# Showing some stats of the dataset
df.describe()
# Looking for missing values
df.isnull().any()
# Wow no missing values here!<jupyter_output><empty_output><jupyter_text>After identifying that the label that they are after is called "isFraud" the data science team would like to understand what is the scope of the fraud. They ask the following questions:How many fraudulent transactions are we facing?&nbsp;What is the percentage of fraud and does it trend over time?How much money was involved in fraudulent transactions and what is it&rsquo;s portion of the entire turnout?<jupyter_code># what is the portion of fraudulent transaction? 
df['isfraud'].value_counts().plot(kind='bar', cmap='Dark2')
plt.yscale('log')
plt.show()
print(f"The portion of fraud transactions of all tranasctions is {round(df['isfraud'].mean()*100,2)}%")<jupyter_output><empty_output><jupyter_text>Does fraud trend over time?<jupyter_code>df_ts = df.groupby('step').agg({'isfraud':['sum','count']}).sort_index()
fig, ax1 = plt.subplots(figsize=(15,6))

color = 'tab:red'
ax1.set_xlabel('time (step)')
ax1.set_ylabel('fraud cases', color=color)
ax1.plot(df_ts.index, df_ts[('isfraud', 'sum')], color=color)
ax1.tick_params(axis='y', labelcolor=color)

ax2 = ax1.twinx()  # instantiate a second axes that shares the same x-axis

color = 'tab:blue'
ax2.set_ylabel('total cases', color=color)  # we already handled the x-label with ax1
ax2.plot(df_ts.index, df_ts[('isfraud', 'count')], color=color)
ax2.tick_params(axis='y', labelcolor=color)

fig.tight_layout()  # otherwise the right y-label is slightly clipped
plt.show()<jupyter_output><empty_output><jupyter_text>No, it looks like the number of fraud cases doesn't increase over timehowever...&nbsp;The number of all transactions does decrease resulting in a higher rate of fraud!<jupyter_code>series_fraud_date = 100 * df_ts['isfraud']['sum'] /  df_ts['isfraud']['count']
series_fraud_date.rolling(10, min_periods=1).mean().plot(figsize=(15,4), cmap='Dark2')
plt.show()<jupyter_output><empty_output><jupyter_text>OK, wow. While the general fraud rate is around 0.13%, during some &ldquo;steps&rdquo; it can reach up to 100% of the transactions...  IsFlaggedFraud  Our data scientists also notice that in the raw data there is a column named "isFlaggedFraud". They wonder of its about and learn that the company is using a 3rd party service that taggs suspected transactions. They are happy because now they have a benchmark to beat :)<jupyter_code>df[['isflaggedfraud', 'isfraud']].value_counts().reset_index()
df[['isflaggedfraud', 'isfraud']].corr()
# Clearly the company wastes money on this service as it doesn't seem to correlate well with our target variable<jupyter_output><empty_output><jupyter_text>Fraud by Transaction Type Off they go to start analyzing each feature individually. They start with the "Transaction Type" and quickly learn that there are some transaction types that are much more prone to fraud.<jupyter_code># fraud by transaction type
df.groupby('type').agg({'isfraud':'sum', 'isflaggedfraud':'sum'}).plot(kind='bar')
plt.yscale('log')
plt.show()<jupyter_output><empty_output><jupyter_text>Origin and destination accounts Obtaining access to a financial account requires going through authorization, making it an expensive process. Therefore we would like to check whether once an account (either destination or source) is involved with fraud it will be reused. On the other hand, a criminal that comes back to the crime scene isn&rsquo;t a smart criminal.<jupyter_code>df_origin_cases = df.groupby('nameorig', as_index=False).agg({'isfraud':'sum'}).sort_values('isfraud', ascending=False)
df_dest_cases =  df.groupby('namedest', as_index=False).agg({'isfraud':'sum'}).sort_values('isfraud', ascending=False)
fig, ax = plt.subplots(nrows=1, ncols=2, figsize=(20,6))

ylab = 'Fraud cases'
xlab = 'Count'
df_dest_cases['isfraud'].value_counts().plot(kind='bar', ax=ax[0],
                                             title='Destination fraud counts',
                                             xlabel=xlab, ylabel=ylab, cmap='Dark2')

df_origin_cases['isfraud'].value_counts().plot(kind='bar', ax=ax[1],
                                               title='Origin fraud counts',
                                               xlabel=xlab, ylabel=ylab, cmap='coolwarm')

ax[0].set_yscale('log')
ax[1].set_yscale('log')

plt.show()<jupyter_output><empty_output><jupyter_text>It looks like for origin accounts there is not a single "second fraud case", destination accounts do have "second cases" but they are very rare, most destination accounts with fraud have only one case.  Fraud by transaction amount  The data science team thinks of what other features may be relevant for their model. They would like to analyze the transaction amount by plotting the histograms of fraud and non-fraud transaction amount over each other.&nbsp;<jupyter_code>fig, ax = plt.subplots(nrows=1, ncols=2, figsize=(20,6))


ax[0].hist(df['amount'][df['isfraud']==True], bins=range(0,int(2e5),2000), density=True, label='Fraud')
ax[0].hist(df['amount'][df['isfraud']==False], bins=range(0,int(2e5),2000), alpha=0.5, density=True, label='Not Fraud')
ax[0].set_title('Distribution of transactions by fraud class')
ax[0].legend()
ax[0].set_xlabel("Transaction amount (USD)")


ax[1].hist(df['amount'][df['isfraud']==True].apply(np.log1p), bins=np.arange(0,17.5,0.2), density=True, label='Fraud')
ax[1].hist(df['amount'][df['isfraud']==False].apply(np.log1p), bins=np.arange(0,17.5,0.2), alpha=0.5, density=True, label='Not Fraud')

ax[1].set_title('Distribution of transactions by fraud class (log scale)')
ax[1].legend()
ax[1].set_xlabel("Transaction amount log1P(USD)")


plt.show()<jupyter_output><empty_output><jupyter_text>The team feels lucky! They have found a strong differentiator between fraud and legitimate transactions. it is of course the transaction amount. They plan to use log1p(amount)&nbsp;as a feature for their model.<jupyter_code># It's very observable when looking at this boxplot
sns.boxplot(data=df, y='amount', x='isfraud', showfliers=False)
plt.show()<jupyter_output><empty_output><jupyter_text>Other numeric features<jupyter_code>df['diff_origin_equal_amount'] = (df['oldbalanceorg'] - df['newbalanceorig']).abs() == df['amount']
df['diff_dest_equal_amount'] = (df['oldbalancedest'] - df['newbalancedest']).abs() == df['amount']
df.columns
num_features_to_plot = ['oldbalanceorg', 'newbalanceorig', 'oldbalancedest',
                        'newbalancedest', 'diff_origin_equal_amount', 'diff_dest_equal_amount']

fig, ax = plt.subplots(nrows=2, ncols=3, figsize=(20,9))
plot_i = 0
for feature_to_plot in num_features_to_plot:
    row = int(plot_i/2)
    col = plot_i % 2
    sns.boxplot(data=df, y=feature_to_plot, x='isfraud', showfliers=False, ax=ax[col, row])
    plot_i += 1
plt.show()<jupyter_output><empty_output><jupyter_text>The data clearly shows some interesting points: For all fraud cases the new origin balance is 0! meaning, that if money leaves for fraud they take it all The old balance of destination fraud accounts is significantly lower&nbsp; Fraud cases involve much higher balance origin accounts&nbsp;Only in fraud cases there is a difference in the calculated transaction amount vs the decaled amount  Account ID (Name)  Our team have noticed that some of the features are categorical and cannot be sent to the model as-is. they decided to handle the categorical feature as follows:From the accounts names they extract the initial character indicating whether the account is of type "M" or "C"The transaction type is going to be transformed to 1-hot encoding<jupyter_code># What values does the first char of the account name has?
print(df['nameorig'].apply(lambda x: x[0]).unique())
print(df['namedest'].apply(lambda x: x[0]).unique())
df['nameorig_C'] = (df['nameorig'].apply(lambda x: x[0]) == 'C').astype(int)
df['namedest_C'] = (df['namedest'].apply(lambda x: x[0]) == 'C').astype(int)
# Looks like fraud events only occur in C type destination accounts!
df.groupby('namedest_C').agg({'isfraud':sum}).plot(kind='bar')
plt.show()
payment_types = df['type'].unique()
for payment_type in payment_types:
    df[f'is_{payment_type.lower()}'] = (df['type'] == payment_type).astype(int)<jupyter_output><empty_output><jupyter_text>Feature transformations as a conclusion of the EDA Our data science team decided to implement the following feature transformations:Apply log1p transformation on the following features to bring them closer to gaussian form: amount, oldbalanceorg, newbalanceorig, oldbalancedest, newbalancedest.Calculate two binary features that indicate if the difference between the old and new balances equal the declared amount diff_origin_equal_amount, diff_dest_equal_amountCreate two binary features that indicate if the account (origin or destination) is of type "C": 'nameorig_C', namedest_CGenerate one-hot encoding from the transaction type: is_payment, is_transfer, is_debit, is_cash_out, is_cash_inUse isfraud as the target variable for binary classificationRemove features that were either represented before or can cause overfitting: namedest, nameorig, type, step  Generating the dataset  Now, after performing a process of EDA the team wishes to start training the model! But before they can do that, they would like to write the feature transformation code in SQL. They do it assuming that it will allow then to leverage AWS Redshift to perform the transformations on massive datasets in the future and also use the same code for online inference with&nbsp;Amazon Kinesis Data Analytics SQL (will not be implemented as part of the capstone)<jupyter_code>query_feature_extraction = '''
select
--target variable
  isfraud
-- log transformed features
, (LOG(amount+1)) as amount
, (LOG(oldbalanceorg+1)) as oldbalanceorg
, (LOG(newbalanceorig+1)) as newbalanceorig
, (LOG(oldbalancedest+1)) as oldbalancedest
, (LOG(newbalancedest+1)) as newbalancedest

-- binary boolean features
, CAST (nameorig like 'C%' as int) nameorig_C
, CAST (namedest like 'C%' as int) namedest_C
, CASE ABS(oldbalanceorg - newbalanceorig) WHEN amount THEN 1 ELSE 0 END diff_origin_equal_amount
, CASE ABS(oldbalancedest - newbalancedest) WHEN amount THEN 1 ELSE 0 END diff_dest_equal_amount

-- payment type 1-hot encoding
 , CASE type WHEN 'PAYMENT' THEN 1 ELSE 0 END is_payment
, CASE type WHEN 'TRANSFER' THEN 1 ELSE 0 END is_transfer
, CASE type WHEN 'DEBIT' THEN 1 ELSE 0 END is_debit
, CASE type WHEN 'CASH_OUT' THEN 1 ELSE 0 END is_cash_out
, CASE type WHEN 'CASH_IN' THEN 1 ELSE 0 END is_cash_in

-- time variable
, step
FROM raw_data

'''
query_create_table = f'''
create table dataset as {query_feature_extraction};
'''
connection = engine.connect()
results = connection.execute(text(query_create_table))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10828911890058276, 'fraction_numerical': 0.011481255979820823, 'mean_word_length': 5.767510300176575, 'pattern_counts': {'":': 1, '<': 49, '<?xml version=': 0, '>': 49, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '200052', 'license': 'Apache-2.0', 'n_tokens_mistral': 3598, 'n_tokens_neox': 3389, 'n_words': 1209, 'path': '02_preparation.ipynb', 'repo_name': 'gidutz/aws-capstone-gad', 'size': '11497'}