Feedback

×

• Feedback
• Was this page helpful?*
  • Yes
  • No
• Do you have any additional feedback?
  • Yes
  • No
• Additional Feedback

Credit Card Fraud

In this example, we use VerticaPy to detect fraudulent credit card transactions. You can download the Jupyter notebook here and the dataset here.

The Credit Card Fraud Detection dataset contains credit card transactions from September 2013 by European cardholders. It contains numerical input variables from a principal component analysis (PCA) transformation.

To preserve the cardholders’ confidentiality, we cannot access the original features and background information about the data.

Time and Amount are the only features that have not been transformed with PCA.

• V1, V2,…, V28: principal components from PCA.

• Time: Number of seconds elapsed between this transaction and the first transaction in the dataset.

• Amount: Transaction amount.

• Class: Response variable, where a value of 1 indicates fraudulent activity.

Amount will be useful for example-dependent cost-sensitive learning.

We will follow the entire Data Science cycle (Data Exploration, Data Preparation, Data Modeling, Model Evaluation, Model Deployment) to solve this problem.

Initialization

This example uses the following version of VerticaPy:

import verticapy as vp

vp.__version__
Out[2]: '1.1.0'

Connect to Vertica. This example uses an existing connection called VerticaDSN. For details on how to create a connection, see the Connection tutorial. You can skip the below cell if you already have an established connection.

vp.connect("VerticaDSN")

Let’s create a Virtual DataFrame of the dataset.

creditcard = vp.read_csv(
    "creditcard.csv",
    parse_nrows = 1000,
)
creditcard.head(5)

	123 Time Int 100%	...	123 Amount Numeric(11,4) 100%	123 Class Int 100%
1	12	...	58.8	0
2	16	...	231.71	0
3	26	...	6.14	0
4	41	...	34.13	0
5	44	...	21.55	0

Warning

This example uses a sample dataset. For the full analysis, you should consider using the complete dataset.

Data Exploration and Preparation

Let’s explore the data by displaying descriptive statistics of all the columns.

creditcard.describe()

	...	approx_75%	max
"Time"	...	140284.333333333	172788.0
"V1"	...	1.30899729344704	2.42250773211137
"V2"	...	0.840343912731398	22.0577289904909
"V3"	...	1.00894865876028	4.06986478102804
"V4"	...	0.808516745593699	16.7155373723131
"V5"	...	0.627332920856438	23.5898035308963
"V6"	...	0.384693919094963	16.4932270978583
"V7"	...	0.576372584125521	28.0698221389603
"V8"	...	0.334974080039734	20.0072083651213
"V9"	...	0.611828466897571	10.3139737345822
"V10"	...	0.422402729472809	15.2360282040071
"V11"	...	0.771781952733642	12.0189131816199
"V12"	...	0.610380428750866	4.14780434342835
"V13"	...	0.670068784279188	3.77962042833723
"V14"	...	0.482384207265436	6.94048384657416
"V15"	...	0.659638697804663	4.44117661446309
"V16"	...	0.517776347462204	5.02188051133651
"V17"	...	0.397316266610719	7.61186152343735
"V18"	...	0.503602894030831	5.04106918541184
"V19"	...	0.461956926728285	5.2283417900513
"V20"	...	0.140493870809816	39.4209042482199
"V21"	...	0.192142493336697	27.2028391573154
"V22"	...	0.530388134172724	8.36198519168435
"V23"	...	0.154671294203237	17.7684617982855
"V24"	...	0.442422063002013	3.52024099866823
"V25"	...	0.354253853343844	7.51958867870916
"V26"	...	0.243296742427893	2.95209267905604
"V27"	...	0.0957161965392776	6.26770908866261
"V28"	...	0.0797957218024922	22.6200722185803
"Amount"	...	75.9978571428571	19656.53
"Class"	...	0.0	1.0

It’ll be difficult to work on the principal components (V1 through V28) without knowing what they mean. The only features we can work on are Time and Amount.

Let’s convert the number of seconds elapsed to the correct date and time. We know that the records were ingested in September 2013, so we’ll use that to create the new feature.

creditcard["Time"].apply("TIMESTAMPADD(second, {}::int, '2013-09-01 00:00:00'::timestamp)")

	📅 Time Timestamp(29) 100%	...	123 Amount Numeric(11,4) 100%	123 Class Int 100%
1	2013-09-01 00:00:12	...	58.8	0
2	2013-09-01 00:00:16	...	231.71	0
3	2013-09-01 00:00:26	...	6.14	0
4	2013-09-01 00:00:41	...	34.13	0
5	2013-09-01 00:00:44	...	21.55	0
6	2013-09-01 00:01:53	...	31.17	0
7	2013-09-01 00:02:03	...	40.0	0
8	2013-09-01 00:02:03	...	160.86	0
9	2013-09-01 00:02:36	...	500.0	0
10	2013-09-01 00:02:36	...	184.31	0
11	2013-09-01 00:03:16	...	63.9	0
12	2013-09-01 00:04:58	...	13.99	0
13	2013-09-01 00:05:10	...	22.09	0
14	2013-09-01 00:06:16	...	12.89	0
15	2013-09-01 00:08:58	...	158.0	0
16	2013-09-01 00:09:08	...	41.96	0
17	2013-09-01 00:09:32	...	8.98	0
18	2013-09-01 00:09:49	...	1.0	0
19	2013-09-01 00:10:00	...	2.72	0
20	2013-09-01 00:10:51	...	1.0	0

When performing machine learning, we’ll take the data from two days and split it into a training set (first day) and a test set (second day).

creditcard["Time"].describe()

	value
name	"Time"
dtype	timestamp(29)
count	29014
min	2013-09-01 00:00:00
max	2013-09-02 23:59:48

Fraudulent activity probably isn’t uniform across all hours of the day, so we’ll extract the hour from the time and see how that influences the prediction.

import verticapy.sql.functions as fun

creditcard["hour"] = fun.hour(creditcard["Time"])
creditcard[["Time", "hour"]]

	📅 Time Timestamp(29) 100%	123 hour Integer 100%
1	2013-09-01 00:00:00	0
2	2013-09-01 00:00:02	0
3	2013-09-01 00:00:17	0
4	2013-09-01 00:00:39	0
5	2013-09-01 00:00:42	0
6	2013-09-01 00:00:48	0
7	2013-09-01 00:01:17	0
8	2013-09-01 00:01:35	0
9	2013-09-01 00:01:40	0
10	2013-09-01 00:01:43	0
11	2013-09-01 00:01:52	0
12	2013-09-01 00:02:44	0
13	2013-09-01 00:02:52	0
14	2013-09-01 00:03:14	0
15	2013-09-01 00:04:01	0
16	2013-09-01 00:04:31	0
17	2013-09-01 00:04:42	0
18	2013-09-01 00:04:57	0
19	2013-09-01 00:04:58	0
20	2013-09-01 00:05:54	0

We can visualize the frequency of fraudulent transactions throughout the day with a histogram.

creditcard["hour"].hist(method = "avg", of = "Class")

It seems like most fraudulent activity happens at night.

The transaction amount also likely differs between fraudulent and genuine transactions, so we’ll look at that relationship with a bar chart. Notice that fraudulent transactions tend to be larger purchases.

creditcard["Class"].bar(
    method = "avg",
    of = "Amount",
)

Let’s create some new features and move forward from there.

Features Engineering

Since all data (besides Time and Amount) are encoded, we’re somewhat limited in creating features. One way to work with this limitation for time series is with moving windows.

In lieu of customer IDs, we’ll aggregate on the transaction amount over some partitions. Let’s compute some features to analyze the transaction amount and frequencies across different windows: 5 hours preceding, 5 minutes preceding, and 5 seconds preceding. Choosing these windows is pretty subjective, but we can close in on the most relevant windows after some more extensive testing.

creditcard.rolling(
    name = "nb_same_transactions_mn_5h",
    func = "COUNT",
    columns = "Amount",
    window = ("- 5 hours", "0 hour"),
    by = ["Amount"],
    order_by = ["Time"],
)
creditcard.rolling(
    name = "nb_same_transactions_mn_5m",
    func = "COUNT",
    columns = "Amount",
    window = ("- 5 minutes", "0 minute"),
    by = ["Amount"],
    order_by = ["Time"],
)
creditcard.rolling(
    name = "nb_same_transactions_mn_5s",
    func = "COUNT",
    columns = "Amount",
    window = ("- 5 seconds", "0 second"),
    by = ["Amount"],
    order_by = ["Time"],
)

	📅 Time Timestamp(29) 100%	...	123 nb_same_transactions_mn_5m Integer 100%	123 nb_same_transactions_mn_5s Integer 100%
1	2013-09-01 14:31:02	...	1	1
2	2013-09-01 14:33:35	...	2	1
3	2013-09-01 14:36:27	...	2	1
4	2013-09-01 17:18:43	...	1	1
5	2013-09-01 23:24:39	...	1	1
6	2013-09-02 08:31:54	...	1	1
7	2013-09-02 18:43:12	...	1	1
8	2013-09-01 17:09:10	...	1	1
9	2013-09-02 11:49:54	...	1	1
10	2013-09-02 21:08:43	...	1	1
11	2013-09-01 10:26:34	...	1	1
12	2013-09-01 11:46:44	...	1	1
13	2013-09-01 12:18:24	...	1	1
14	2013-09-01 13:25:54	...	1	1
15	2013-09-01 13:32:55	...	1	1
16	2013-09-01 14:00:13	...	1	1
17	2013-09-01 14:15:17	...	1	1
18	2013-09-01 14:25:57	...	1	1
19	2013-09-01 14:44:36	...	1	1
20	2013-09-01 14:44:45	...	2	1

As an aside, we could also create some features that represent different parts of the day, but won’t be useful for our use case since we’re only working with data for two days’ worth of data.

Let’s look at the correlation matrix and see which features influence our prediction.

creditcard.corr()

Our new features aren’t linearly correlated with our response, but some of the components seem to have a large influence on our prediction. We’ll use these when we create our model.

To simplify things, let’s save the dataset into a new table.

vp.drop(
    "creditcard_clean",
    method = "table",
)
creditcard.to_db(
    "creditcard_clean",
    relation_type = "table",
    inplace = True,
)

	📅 Time Timestamp 100%	...	123 nb_same_transactions_mn_5m Int 100%	123 nb_same_transactions_mn_5s Int 100%
1	2013-09-01 00:06:46	...	1	1
2	2013-09-01 00:55:30	...	1	1
3	2013-09-01 01:05:42	...	1	1
4	2013-09-01 01:15:37	...	1	1
5	2013-09-01 01:24:03	...	1	1
6	2013-09-01 02:25:39	...	1	1
7	2013-09-01 02:46:59	...	1	1
8	2013-09-01 03:21:33	...	1	1
9	2013-09-01 03:23:02	...	2	1
10	2013-09-01 03:28:42	...	1	1
11	2013-09-01 03:45:05	...	1	1
12	2013-09-01 04:00:46	...	1	1
13	2013-09-01 04:31:21	...	1	1
14	2013-09-01 06:27:47	...	1	1
15	2013-09-01 07:34:44	...	1	1
16	2013-09-01 07:37:18	...	2	1
17	2013-09-01 07:43:10	...	1	1
18	2013-09-01 07:55:33	...	1	1
19	2013-09-01 09:04:46	...	1	1
20	2013-09-01 09:25:08	...	1	1

Data Modeling

Train/Test sets

Since we’re dealing with time series data, we have to maintain time linearity. Our goal is to use the past to predict the future, so a k-fold cross-validation, for example, wouldn’t make much sense here.

We will split the dataset into a train (day 1) and a test (day 2).

train = creditcard.search("Time  < '2013-09-02 00:00:00'")

test  = creditcard.search("Time >= '2013-09-02 00:00:00'")

Supervision

Supervising would make this pretty easy since it would just be a binary classification problem. We can use different algorithms to optimize the prediction. Our dataset is unbalanced, so the AUC might be a good metric to evaluate the model. The PRC AUC would also be a relevant metric.

LogisticRegression works well with monotonic relationships. Since we have a lot of independent features that correlate with the response, it should be a good first model to use.

from verticapy.machine_learning.vertica import LogisticRegression

predictors = creditcard.get_columns(exclude_columns = ["Class", "Time"])
response = "Class"
model = LogisticRegression(
    penalty = 'L2',
    tol = 1e-6,
    max_iter = 1000,
    solver = "BFGS",
)
model.fit(train, predictors, response, test)
model.classification_report()

	value
auc	0.9696939596548432
prc_auc	0.8487787039660222
accuracy	0.9955791945845134
log_loss	0.00881807221936879
precision	0.9016393442622951
recall	0.7819905213270142
f1_score	0.8375634517766497
mcc	0.8375101766126264
informedness	0.7807287801241543
markedness	0.8984212107797149
csi	0.7205240174672489

Based on the report, our model is very good at detecting non-fraudulent events; the AUC is high and the PRC AUC is very good. We can use this model to filter obvious events and to get some insight on the importance of each feature.

model.features_importance()

Some PCA components seem to be very relevant and will be essential for finding anomalies.

Unsupervised Learning

There are many unsupervised learning techniques, but not all of them will be useful for detecting anomalies. Since there’s no rigid mathematical definition for what an outlier is, finding anomalies becomes somewhat subjective. To solve this problem, we have to evaluate our constraints and needs. Do we need to find anomalies in real-time? Do we have a time constraint?

• Real-time: We don’t have access to historical data, so we need an easy way to preprocess the data that is wholly independent from historical data, and the model must be simple to deploy at the source of the data stream. For example, we might use simple preprocessing techniques like normalization, standardization or One-Hot Encoding instead of more complex ones like windows, interpolation, or intersection. Isolation forests, KMeans, robust PCA, or global outlier detection using z-score would be ideal, whereas local outlier factor, DBSCAN, or other hard-to-deploy methods cannot be used.

• Near Real-time: We have access to historical data and our preprocessing method must be fast. The model has to be simple to score with. We can use any preprocessing technique as long as it is fast enough, which of course varies. Since this is still a real-time use case, we should still avoid any hard-to-deploy algorithms like DBSCAN or local outlier factor.

• No time constraint: We can use any techniques we want.

Due to the complexity of the computations, anomalies are difficult to detect in the context of “Big Data”. We have three efficient methods for that case:

• Machine Learning: We need to use easily-deployable algorithms to perform real-time fraud detection. Isolation forests and KMeans can be easily deployed and they work well for detecting anomalies.

• Rules & Thresholds: The z-score can be an efficient solution for detecting global outliers.

• Decomposition: Robust PCA is another technique for detecting outliers.

Before using these techniques, let’s draw some scatter plots to get a better idea of what kind of anomalies we can expect.

creditcard.scatter(
    ["V12", "V17"],
    by = "Class",
    max_nb_points = 5000000,
)

creditcard.scatter(
    ["V12", "V17", "V10"],
    by = "Class",
)

In this case, the anomalies seem pretty clear global outliers of the distributions. When doing unsupervised learning, we don’t have this information in advance.

For the rest of this example, we’ll investigate labels and how they can help us understand the efficacy of each technique.

k-means Clustering

We begin by examining KMeans clustering, which partitions the data into k clusters.

We can use an elbow curve to find a suitable number of clusters. We can then add more clusters then the amount suggested by the elbow() curve to create clusters mainly composed of anomalies. Clusters with relatively fewer elements can then be investigated by an expert to label the anomalies.

From there, we perform the following procedure:

• Label historical data by looking at unsupervised learning results.

• Use supervised learning models to learn on the labeled anomalies. This model will be brought to the source of the data stream.

Once we deploy the unsupervised model and can reliably detect suspicious transactions, we could block them and contact the cardholder about potential fraudulent activity on their card.

from verticapy.machine_learning.model_selection import elbow

elbow(
    creditcard,
    ["V12", "V17", "V10", "V14", "V16"],
    n_cluster = [1, 2, 10, 20, 30],
)

10 seems to be a suitable number of clusters, so let’s try out 20 clusters and see if the collective outliers cluster together. We can then then evaluate each cluster independently and see which clusters have the most anomalies.

from verticapy.machine_learning.vertica import KMeans

model = KMeans(n_cluster = 20)

model.fit(creditcard, ["V12", "V17", "V10"])


=======
centers
=======
   v12   |   v17   |   v10   
---------+---------+---------
-0.10078 | 0.72248 | 0.61112 
-15.31183|-14.13473|-13.39178
-8.29833 |-12.06506|-7.51092 
-15.78237|-20.76398|-12.30598
-0.00650 |-0.42282 | 3.81104 
-3.39662 |-4.05109 |-2.96764 
-0.98183 | 0.18416 | 1.16959 
 0.88533 | 0.48376 |-0.36416 
 0.12367 |-0.45914 | 1.03600 
-6.00912 |-7.00773 |-4.81290 
-0.15270 |-0.43247 |-0.17058 
-0.03970 |-0.69950 | 8.75200 
-0.09229 | 2.33768 |-1.27621 
 0.70740 |-0.22501 |-1.28569 
-10.71347|-13.64468|-22.06666
-0.21550 | 0.52570 |-0.70378 
-6.47719 |-1.43305 |-4.95597 
-12.36671|-19.66206|-14.29831
-2.25885 | 0.55997 |-0.43850 
 0.77979 |-0.52648 |-0.02806 


=======
metrics
=======
Evaluation metrics:
     Total Sum of Squares: 177690.24
     Within-Cluster Sum of Squares: 
         Cluster 0: 1379.9516
         Cluster 1: 214.41237
         Cluster 2: 465.44353
         Cluster 3: 201.60469
         Cluster 4: 890.35549
         Cluster 5: 379.99089
         Cluster 6: 1541.2117
         Cluster 7: 1480.0222
         Cluster 8: 1495.7035
         Cluster 9: 613.96449
         Cluster 10: 1819.2186
         Cluster 11: 660.12728
         Cluster 12: 1637.6328
         Cluster 13: 1671.6019
         Cluster 14: 70.362277
         Cluster 15: 1321.7936
         Cluster 16: 421.91474
         Cluster 17: 180.72459
         Cluster 18: 2449.9003
         Cluster 19: 1551.7494
     Total Within-Cluster Sum of Squares: 20447.686
     Between-Cluster Sum of Squares: 157242.55
     Between-Cluster SS / Total SS: 88.49%
 Number of iterations performed: 120
 Converged: True
 Call:
kmeans('"public"."_verticapy_tmp_kmeans_v_mldb_706954b697b011efa8720242ac120002_"', '"public"."_verticapy_tmp_view_v_mldb_70cb8fdc97b011efa8720242ac120002_"', '"V12", "V17", "V10"', 20
USING PARAMETERS max_iterations=300, epsilon=0.0001, init_method='kmeanspp', distance_method='euclidean')

Let’s direct our attention to the smallest clusters.

model.predict(creditcard, name = "cluster")
creditcard.groupby(
    ["cluster"],
    [
        "COUNT(*) AS total",
        "100 * AVG(Class) AS percent_fraud",
        "SUM(Class) / 492 AS total_fraud",
    ],
).sort("total")

	123 cluster Integer 100%	...	123 total Integer 100%	123 total_fraud Numeric(38) 100%
1	14	...	10	0.02032520325203252
2	17	...	25	0.0508130081300813
3	3	...	26	0.052845528455284556
4	1	...	28	0.0508130081300813
5	16	...	53	0.10365853658536585
6	2	...	71	0.1402439024390244
7	5	...	91	0.17886178861788618
8	9	...	97	0.1951219512195122
9	11	...	103	0.0
10	4	...	330	0.006097560975609756
11	12	...	725	0.06707317073170732
12	18	...	1946	0.022357723577235773
13	0	...	1973	0.026422764227642274
14	13	...	2157	0.008130081300813007
15	6	...	2233	0.012195121951219513
16	8	...	2794	0.012195121951219513
17	15	...	2877	0.026422764227642274
18	7	...	3055	0.01016260162601626
19	19	...	5195	0.006097560975609756
20	10	...	5225	0.01016260162601626

Notice that clusters with fewer elemenets tend to contain much more fraudulent events than the others. This methodology makes KMeans a good algorithm for catching collective outliers. Combining KMeans with other techniques like Z-score, we can find most of the outliers of the distribution.

Outliers of the distribution

Let’s use the Z-score to detect global outliers of the distribution.

creditcard.outliers(
    ["V12", "V17", "V10"],
    name = "global_outliers",
    threshold = 5.0,
)
creditcard.groupby(
    ["global_outliers"],
    [
        "COUNT(*) AS total",
        "100 * AVG(Class) AS percent_fraud",
        "SUM(Class) / 492 AS total_fraud",
    ],
).sort("total")

	123 global_outliers Integer 100%	...	123 total Integer 100%	123 total_fraud Numeric(38) 100%
1	1	...	315	0.45934959349593496
2	0	...	28699	0.540650406504065

creditcard.outliers_plot(
    ["V12", "V17",],
    threshold = 5.0,
)

We can see that we can caught more than 71% of the fraudulent activity in less than 1% of the dataset.

Neighbors

Other algorithms could be used to solve the problem with more precision if we could use a more powerful clustering method and didn’t have a time constraint. Based on neighbors, these algorithms are very computationally expensive. An example of this kind of algorithm is the local outlier factor.

from verticapy.machine_learning.vertica import LocalOutlierFactor

model = LocalOutlierFactor()
model.fit(creditcard.sample(x = 0.01), ["V12", "V17", "V10"])
lof_creditcard = model.predict()
lof_creditcard["outliers"] = "(CASE WHEN lof_score > 2 THEN 1 ELSE 0 END)"
lof_creditcard.scatter(["V12", "V17", "V10"], by = "outliers")

We can catch outliers with a neighbors score. Again, the main problem with these sorts of algorithms is that what they have in precision, they lack in speed, which makes them unsuitable for scoring new data. This is why it’s important to focus on scalable techniques like KMeans.

Other Techniques

Other scalable techniques that can solve this problem are robust PCA and isolation forest.

Conclusion

We’ve solved our problem in a Pandas-like way, all without ever loading data into memory!