Credit Card Fraud

In this example, we use vastorbit to detect fraudulent credit card transactions. You can download 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 vastorbit:

import vastorbit as vo

vo.__version__

Connect to VAST. This example uses an existing connection called VASTDSN. 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.

vo.connect("VASTDSN")

Let’s create a VastFrame of the dataset.

creditcard = vo.read_csv("creditcard.csv")
creditcard.head(5)

	123 time Integer 100%	123 v1 Double 100%	123 v2 Double 100%	123 v3 Double 100%	123 v4 Double 100%	123 v5 Double 100%	123 v6 Double 100%	123 v7 Double 100%	123 v8 Double 100%	123 v9 Double 100%	123 v10 Double 100%	123 v11 Double 100%	123 v12 Double 100%	123 v13 Double 100%	123 v14 Double 100%	123 v15 Double 100%	123 v16 Double 100%	123 v17 Double 100%	123 v18 Double 100%	123 v19 Double 100%	123 v20 Double 100%	123 v21 Double 100%	123 v22 Double 100%	123 v23 Double 100%	123 v24 Double 100%	123 v25 Double 100%	123 v26 Double 100%	123 v27 Double 100%	123 v28 Double 100%	123 amount Double 100%	123 class Integer 100%
1	7526	0.0084303648955825	4.13783683497998	-6.24069657194744	6.6757321631344	0.768307024571449	-3.35305954788994	-1.63173467271809	0.15461244822474	-2.79589246446281	-6.18789062970647	5.66439470857116	-9.85448482287037	-0.306166658250084	-10.6911962118171	-0.638498192673322	-2.04197379107768	-1.12905587703585	0.116452521226364	-1.93466573889727	0.488378221134715	0.36451420978479	-0.608057133838703	-0.539527941820093	0.128939982991813	1.48848121006868	0.50796267782385	0.735821636119662	0.513573740679437	1.0	1
2	7672	0.702709900098753	2.42643280600508	-5.23451329584052	4.41666124290876	-2.17080621591773	-2.66755356121463	-3.87808845483572	0.911337122229195	-0.166199039175942	-5.00924850212751	4.67572941865677	-8.16718805173089	0.638559282180499	-6.76333439062322	1.29686025605627	-3.81175840977789	-3.75412806618729	-1.04917740227906	1.55419726345897	0.422743129198702	0.551179689117248	-0.0098023573132531	0.721698230069415	0.473245751402033	-1.9593037711687	0.31947554010746	0.600484916486359	0.129305225096566	1.0	1
3	8415	-0.25147096006823	4.31352332575143	-6.89143771650147	6.79679668055103	0.616296505250374	-2.96632654187403	-2.43665252687714	0.489327898838014	-3.37163857159343	-6.81081309938215	7.62008905836908	-10.2852825163315	-0.342444334069655	-11.5434979157865	-1.3349879032835	-2.68928420445067	-3.20438340295762	0.0865218956911394	-1.31449509665307	0.632709963789826	0.536892074447128	-0.546126023729244	-0.605239547613772	-0.263743459593593	1.53991583077868	0.523573696680871	0.891024688365269	0.572740666549584	1.0	1
4	8614	-2.16992897559082	3.6396539992044	-4.50849778617728	2.73066814884895	-2.12269287053048	-2.34101682766366	-4.23525308290613	1.70353765763508	-1.30527913344307	-6.7167200227127	6.35361231915591	-8.60164826276464	0.449930038237225	-7.50616937408145	-0.438081781812467	-3.69451599830956	-6.30475338603049	-1.26758712788115	0.357987030484253	0.5007790539507	0.645103275621378	-0.503529448911197	-0.0005228218182992	0.0716957787676742	0.0920074334247208	0.308497931577036	0.552590914653841	0.298954478709399	1.0	1
5	8757	-1.86375555343732	3.44264397684973	-4.46825973145511	2.80533625940251	-2.1184124774924	-2.33228488731467	-4.2612371974404	1.70168184397858	-1.43939587542941	-6.99990663386522	6.31620967716699	-8.67081800476226	0.316023991908777	-7.41771206475053	-0.436537471366146	-3.65280196004741	-6.29314531907376	-1.24324829133852	0.364810482193887	0.36092400372093	0.66792657242141	-0.516242362128804	-0.012217811746839	0.070613703103445	0.0585044669560628	0.304882839588584	0.418012467185258	0.208858278145839	1.0	1

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()

	count	mean	std	min	approx_25%	approx_50%	approx_75%	max
"time"	29014.0	95426.72506376232	47789.878667619545	0.0	54819.0	89833.0	140026.0	172788.0
"v1"	29014.0	-0.08281214427187178	2.2301169191918975	-36.8023199088745	-0.954825583101593	0.02004347671813056	1.3284722001565745	2.42250773211137
"v2"	29014.0	0.073960343342847	1.7867361964240793	-63.3446983175027	-0.5660487750755839	0.09004521079797827	0.835724376195209	22.0577289904909
"v3"	29014.0	-0.12579197022091135	1.9668616216741108	-31.8135859546007	-0.9576853540143736	0.15951846187506147	1.0005671891286816	4.06986478102804
"v4"	29014.0	0.07474084568711281	1.562890013936606	-5.68317119816995	-0.8360372740932998	0.019739120693301845	0.8236369756469273	16.7155373723131
"v5"	29014.0	-0.03793360463451251	1.577142811267928	-25.7911323347667	-0.6897514588154391	-0.04656642785405922	0.6317256745106888	23.5898035308963
"v6"	29014.0	-0.0197374607283369	1.3557673956514278	-17.1185917920642	-0.7866909465522046	-0.28713661475859886	0.37376713992164634	16.4932270978583
"v7"	29014.0	-0.0741278935496594	1.654085625219281	-43.5572415712451	-0.5669961865522023	0.036412061474485835	0.5721069301643316	28.0698221389603
"v8"	29014.0	0.015268732040292759	1.4120563863339206	-41.0442609210741	-0.20977334940499515	0.022207494656147506	0.33464145706866705	20.0072083651213
"v9"	29014.0	-0.01874448203152595	1.1879693490174459	-13.4340663182301	-0.6532385452310063	-0.049271810926605306	0.5971095664567827	10.3139737345822
"v10"	29014.0	-0.09171225138488077	1.4401948070195507	-24.5882624372475	-0.57282736983338	-0.11574952342832148	0.4173794794270037	15.2360282040071
"v11"	29014.0	0.05429883541147886	1.1644026569523815	-4.04989491435061	-0.7518711485072506	-0.016697442625060482	0.7605068567645482	12.0189131816199
"v12"	29014.0	-0.0941164791546817	1.3802096346921473	-18.6837146333443	-0.43256052335178397	0.1245977721884724	0.6093502551059887	4.14780434342835
"v13"	29014.0	0.005884533677600248	1.003366904063213	-3.81644762329776	-0.6332647398342058	-0.019848393998511404	0.6737240690365754	3.77962042833723
"v14"	29014.0	-0.11812155347540615	1.390132701835722	-19.2143254902614	-0.46775225495852274	0.031083903897998925	0.48225985337987654	6.94048384657416
"v15"	29014.0	0.0069147085578067135	0.9226169812462099	-4.49894467676621	-0.5863296107571331	0.051174450222162444	0.6610596080728545	4.44117661446309
"v16"	29014.0	-0.06322167664418249	1.1115654930397227	-14.1298545174931	-0.4999821410491239	0.058270732235456944	0.5086643948353567	5.02188051133651
"v17"	29014.0	-0.10021182987055195	1.4647062170670861	-25.1627993693248	-0.504238107705212	-0.07424385538199947	0.41600231037589397	7.61186152343735
"v18"	29014.0	-0.02867468278923642	0.9435930891860623	-9.49874592104677	-0.5092200688977337	-0.006215069805713819	0.5008156727527929	5.04106918541184
"v19"	29014.0	0.004245238947237778	0.8397527209853632	-7.21352743017759	-0.44865923804137453	-0.001284867371400239	0.4619647341617663	5.2283417900513
"v20"	29014.0	0.003923588074643721	0.8172200939582945	-22.8385483047643	-0.21147876012991243	-0.05904493050811134	0.14106742050732252	39.4209042482199
"v21"	29014.0	0.010554726477826456	0.8505444530861069	-22.7976039055519	-0.22515757011088944	-0.02279969982253361	0.19296344749411062	27.2028391573154
"v22"	29014.0	-0.0030835577424138264	0.7408052031898827	-10.933143697655	-0.5454642457444665	0.008619814063075599	0.5260575697272204	8.36198519168435
"v23"	29014.0	0.001298547053080301	0.6780581861688931	-44.8077352037913	-0.1643539120577957	-0.012695641195444196	0.15650417812834258	17.7684617982855
"v24"	29014.0	0.0012163164294626677	0.6025546718887111	-2.81489763570598	-0.34665907630873133	0.04638605494149965	0.4387927037488323	3.52024099866823
"v25"	29014.0	0.0010656315957580488	0.5325915965279894	-10.2953970749851	-0.3179377609923194	0.020023997325628536	0.3551751726223694	7.51958867870916
"v26"	29014.0	-0.0009550688095246865	0.4852314090776167	-2.53432972105675	-0.3300670688248193	-0.048421491739607345	0.2484252101722104	2.95209267905604
"v27"	29014.0	0.003653753331796843	0.43729853331388463	-8.87866514836831	-0.07152374827948176	0.004230447224860616	0.0972307960896199	6.26770908866261
"v28"	29014.0	0.00029342160028642493	0.34715171383772586	-8.2779240852007	-0.05309605350072877	0.010823949318420166	0.07902667289966811	22.6200722185803
"amount"	29014.0	88.50709312745573	267.15913986589453	0.0	5.787906883609107	21.4217166813017	75.90580403942816	19656.53
"class"	29014.0	0.01695733094368236	0.12911372674267388	0.0	0.0	0.0	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("DATE_ADD('second', CAST({} AS bigint), TIMESTAMP '2013-09-01 00:00:00')")

	📅 time Timestamp(0) 100%	123 v1 Double 100%	123 v2 Double 100%	123 v3 Double 100%	123 v4 Double 100%	123 v5 Double 100%	123 v6 Double 100%	123 v7 Double 100%	123 v8 Double 100%	123 v9 Double 100%	123 v10 Double 100%	123 v11 Double 100%	123 v12 Double 100%	123 v13 Double 100%	123 v14 Double 100%	123 v15 Double 100%	123 v16 Double 100%	123 v17 Double 100%	123 v18 Double 100%	123 v19 Double 100%	123 v20 Double 100%	123 v21 Double 100%	123 v22 Double 100%	123 v23 Double 100%	123 v24 Double 100%	123 v25 Double 100%	123 v26 Double 100%	123 v27 Double 100%	123 v28 Double 100%	123 amount Double 100%	123 class Integer 100%
1	2013-09-01 02:05:26	0.0084303648955825	4.13783683497998	-6.24069657194744	6.6757321631344	0.768307024571449	-3.35305954788994	-1.63173467271809	0.15461244822474	-2.79589246446281	-6.18789062970647	5.66439470857116	-9.85448482287037	-0.306166658250084	-10.6911962118171	-0.638498192673322	-2.04197379107768	-1.12905587703585	0.116452521226364	-1.93466573889727	0.488378221134715	0.36451420978479	-0.608057133838703	-0.539527941820093	0.128939982991813	1.48848121006868	0.50796267782385	0.735821636119662	0.513573740679437	1.0	1
2	2013-09-01 02:07:52	0.702709900098753	2.42643280600508	-5.23451329584052	4.41666124290876	-2.17080621591773	-2.66755356121463	-3.87808845483572	0.911337122229195	-0.166199039175942	-5.00924850212751	4.67572941865677	-8.16718805173089	0.638559282180499	-6.76333439062322	1.29686025605627	-3.81175840977789	-3.75412806618729	-1.04917740227906	1.55419726345897	0.422743129198702	0.551179689117248	-0.0098023573132531	0.721698230069415	0.473245751402033	-1.9593037711687	0.31947554010746	0.600484916486359	0.129305225096566	1.0	1
3	2013-09-01 02:20:15	-0.25147096006823	4.31352332575143	-6.89143771650147	6.79679668055103	0.616296505250374	-2.96632654187403	-2.43665252687714	0.489327898838014	-3.37163857159343	-6.81081309938215	7.62008905836908	-10.2852825163315	-0.342444334069655	-11.5434979157865	-1.3349879032835	-2.68928420445067	-3.20438340295762	0.0865218956911394	-1.31449509665307	0.632709963789826	0.536892074447128	-0.546126023729244	-0.605239547613772	-0.263743459593593	1.53991583077868	0.523573696680871	0.891024688365269	0.572740666549584	1.0	1
4	2013-09-01 02:23:34	-2.16992897559082	3.6396539992044	-4.50849778617728	2.73066814884895	-2.12269287053048	-2.34101682766366	-4.23525308290613	1.70353765763508	-1.30527913344307	-6.7167200227127	6.35361231915591	-8.60164826276464	0.449930038237225	-7.50616937408145	-0.438081781812467	-3.69451599830956	-6.30475338603049	-1.26758712788115	0.357987030484253	0.5007790539507	0.645103275621378	-0.503529448911197	-0.0005228218182992	0.0716957787676742	0.0920074334247208	0.308497931577036	0.552590914653841	0.298954478709399	1.0	1
5	2013-09-01 02:25:57	-1.86375555343732	3.44264397684973	-4.46825973145511	2.80533625940251	-2.1184124774924	-2.33228488731467	-4.2612371974404	1.70168184397858	-1.43939587542941	-6.99990663386522	6.31620967716699	-8.67081800476226	0.316023991908777	-7.41771206475053	-0.436537471366146	-3.65280196004741	-6.29314531907376	-1.24324829133852	0.364810482193887	0.36092400372093	0.66792657242141	-0.516242362128804	-0.012217811746839	0.070613703103445	0.0585044669560628	0.304882839588584	0.418012467185258	0.208858278145839	1.0	1
6	2013-09-01 02:26:48	-4.61721720415581	1.69569365346656	-3.11437220079491	4.32819855298178	-1.87325699086527	-0.989908135538263	-4.57726462658038	0.4722161584519	0.472016953377646	-5.57602263642415	4.80232276125089	-10.833164469314	0.104303875698575	-9.40542306160986	-0.80747786866047	-7.55234220350747	-9.8025617903912	-4.12062883466552	1.74050729036196	-0.0390459343384186	0.481829697240067	0.146023056445948	0.117038527549065	-0.21756459883966	-0.138776043706563	-0.424452881068275	-1.00204142597227	0.890780287970655	1.1	1
7	2013-09-01 03:13:49	-3.89119195123086	7.09891625215749	-11.4264670967961	8.60755678650129	-2.06570621026229	-2.98528802203595	-8.13858941693861	2.97392807853452	-6.27279046836547	-13.1934150665232	11.6197234753825	-17.6316063138707	-0.355194389515515	-18.8220867423816	-1.2831290856146	-10.0310966568698	-15.2270075099256	-5.32200898058982	-0.501750653452579	1.38261899967275	1.75708522742867	-0.18970915318715	-0.508629244173448	-1.18930816877394	1.18853649934791	0.605241885913626	1.8815287524148	0.875260407222375	1.0	1
8	2013-09-01 03:42:03	-5.45436177939673	8.28742055534983	-12.7528112729386	8.59434189301081	-3.10600228114338	-3.17994875686414	-9.25279393795831	4.24506220985367	-6.32980084623466	-13.1366983691039	11.228470279576	-17.1313009454468	-0.169401056814124	-18.0499976898594	-1.36623566099065	-9.7235653091894	-14.7449024646768	-5.24730110631125	-0.574675143795817	1.30586191483437	1.84616479291417	-0.267171794223081	-0.310803969751621	-1.20168545799806	1.35217609502433	0.608424596360403	1.57471478384204	0.808725205090233	1.0	1
9	2013-09-01 03:54:33	-4.15301449873357	8.20479650456012	-15.0317142097481	10.330099825448	-3.99442609736294	-3.25001318447365	-10.4156978119079	4.62080391044331	-5.71124797098224	-11.7971810675777	11.2779207278067	-16.7283393320915	0.2413676825577	-17.7216383537133	-0.387299923381284	-10.3220166783853	-13.9590853730538	-5.03071003831832	1.19726633504633	1.41262488019492	1.97698839609887	0.25651048717528	0.485908106513804	-1.19882131436036	-0.526567394687845	0.634873502483478	1.62720907623952	0.723235068518316	1.0	1
10	2013-09-01 04:52:00	-5.26805322089363	9.06761342731767	-15.9607281345144	10.2966027898053	-4.70824109122294	-3.39537485177836	-11.1610570028797	5.49996263444947	-5.66737554592835	-11.6271935556579	11.0270590938161	-16.3880541668327	0.363921486512734	-17.230202160711	-0.437487559248993	-10.1223919213552	-13.6392089915234	-4.98645725844229	1.12678437819631	1.45587819528	2.00410994522741	0.191058399384387	0.622927669875126	-1.20926355739155	-0.374798862642666	0.64879803679546	1.58469734677224	0.720055870245492	1.0	1
11	2013-09-01 04:57:18	-5.18787810836635	6.96770866359565	-13.510931139095	8.61789514115238	-11.2144223939458	0.6722478387604	-9.46253281088355	5.32870420774381	-4.89700552817542	-11.7868116556041	9.36907905765884	-15.0941631493865	1.25637700700759	-11.8521613037824	0.274429814172421	-10.6882417917386	-18.3888105354245	-6.89884013301839	2.38280793552097	-0.623737291574186	2.08608300107406	0.760190240866359	0.716805785858217	-0.646743429628277	-1.61704292571624	0.172346889503238	0.626646780979254	-0.169726031129409	766.36	1
12	2013-09-01 05:11:15	-12.3396031574122	4.48826730168204	-16.5870729373632	10.1072738734101	-10.4201989827431	0.130669811857349	-15.6003233044816	-1.15769598093331	-5.3046309683427	-12.9389293107706	8.80568196718575	-13.5561301301468	1.16546376028499	-9.80988150207055	0.369987279150665	-9.50521044515487	-17.5420303090874	-6.79263778723153	2.06937744931751	-0.0855014634055812	-2.0896096332232	1.74531450014405	1.37681580939778	-0.554271418366818	-1.61074085196174	0.153725444536768	1.2124772080589	-1.86929047595671	188.78	1
13	2013-09-01 05:33:31	-14.7246270119253	7.87515679273047	-21.8723173644566	11.9061699078901	-8.34873369160876	-2.26284641969245	-15.8334427819606	0.0778736741759969	-6.35683349086288	-13.2616517082667	10.0637897462894	-14.3947668016721	0.654888723464231	-14.2483158270781	-0.305360761401071	-8.16163244506225	-12.2809648581754	-4.81858639342974	0.719787682108791	0.996468755724792	-2.36234492751884	1.09955729576975	1.03719942301307	-1.03635934178889	-0.254776514154375	0.642343201018144	2.16112922373151	-1.40128201963858	1.0	1
14	2013-09-01 05:56:59	-17.4677100117887	10.1148157246654	-24.2021422329158	11.8054692105913	-10.1980458075926	-2.5799380080012	-17.6567879964837	2.25690247596699	-6.24214930949065	-12.8306571996417	9.44266526535108	-13.5474858999444	0.960728558325579	-13.0287170264518	-0.426674498701321	-7.65266166506753	-11.4853277896478	-4.72136957553102	0.550519004149565	1.00151850195952	-2.32802441621057	0.940830319350178	1.29681706144136	-1.05510390359967	0.11179202805362	0.679694612203418	2.09354057112569	-1.42549145377361	1.0	1
15	2013-09-01 06:58:15	1.19239598990768	1.33897371069007	-0.678876186687009	3.1236723071222	0.643244863589275	-1.18432299431215	0.397585526006899	-0.253498538261658	0.41113497684127	-0.859862301810301	1.124059382453	-3.76387384832398	0.367975966769142	-0.971757538368114	-0.0138825132357776	1.45757852763076	2.61145033447876	1.2919552295122	-1.56381522671193	-0.185454650142832	-0.377503243008699	-0.889596747960403	-0.0742075893964193	0.0354455164139561	0.550577974200231	-0.0271712789934873	-0.0249206212266212	0.0736053986634045	3.12	1
16	2013-09-01 07:22:03	-18.474867903441	11.5863805198184	-21.4029168131391	6.03851541556808	-14.4511581398683	-4.14652350569915	-14.856123673178	12.4311404723263	-4.05335328722588	-9.04039624894471	5.96620250720685	-8.46396612952571	0.0786921317878341	-9.09253265181295	0.0108220973904713	-7.1863755078292	-13.7974745003699	-4.95849390971089	1.32116655982617	1.5779244011474	1.74113559593371	-1.25113794883836	-0.396219126116475	0.0957055555718895	1.32275094272025	-0.217954564863573	1.62879253147016	0.482248274865402	99.99	1
17	2013-09-01 07:22:36	-19.1798264145873	11.8179219897853	-21.9191735807843	6.0862356342864	-14.7088447898427	-4.30888760741835	-15.3579518065836	12.8571650181242	-3.99986076978115	-8.9286556608751	5.8492930661792	-8.26164983722413	0.153829148314596	-8.82935922766409	0.0088788388257268	-7.0709530089254	-13.6297209427085	-4.9588298158479	1.27209136353211	1.572949614923	1.74680154191934	-1.35314876613352	-0.762965072731218	0.117027851903743	1.29799375563032	-0.224825057048525	1.62105238966989	0.4846144027264	99.99	1
18	2013-09-01 07:23:05	-19.8563223334433	12.0958932259299	-22.4640827464876	6.11554110191771	-15.1480215209615	-4.34672408349599	-15.6485074722661	13.2768048056341	-3.97416161954102	-8.85919405880884	5.73081553744459	-8.08803351213651	0.230824969324902	-8.57897327333454	0.000946636693186	-6.94774606726094	-13.4728970076501	-4.94021082886802	1.23014255193802	1.58292955492187	1.75072984045421	-1.40963570908141	-0.809809278333882	0.121396659905885	1.35030007077468	-0.224291873525585	1.59762136835254	0.476919818025438	99.99	1
19	2013-09-01 07:28:51	-22.5616992591298	13.2089042844176	-24.6438187771226	6.23253182330679	-16.905611362323	-4.49743871970001	-16.8101841919321	14.9551067734604	-3.87129682308601	-8.58126551626339	5.25698760960799	-7.39361499215009	0.538800829120431	-7.57755201800709	-0.030725531904052	-6.45478323655342	-12.8456565242928	-4.86591773720169	1.06244185030067	1.62144422472126	1.76598663639159	-1.6355166467436	-0.998316503452087	0.138972227361725	1.55935014018173	-0.22212451669	1.5044246600224	0.445919826109776	99.99	1
20	2013-09-01 07:57:05	-27.84818067198	15.5981926625554	-28.923755945104	6.41844174657532	-20.346228155413	-4.82820246578157	-19.2108964179086	18.329405525597	-3.66873493861418	-8.00915938639777	4.30309581759563	-6.00866000668561	1.1389688287939	-5.58044712038062	-0.11073711578577	-5.49107326163426	-11.5885435992809	-4.71542017123115	0.734573493016404	1.69785626318366	1.8021491336386	-2.06293427611466	-1.26984343615924	0.165409188492223	1.99949911253199	-0.211058522431997	1.32480949279508	0.388090177732107	99.99	1

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(0)
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 vastorbit.sql.functions as fun

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

	📅 time Timestamp(0) 100%	123 hour Bigint 100%
1	2013-09-01 02:05:26	2
2	2013-09-01 02:07:52	2
3	2013-09-01 02:20:15	2
4	2013-09-01 02:23:34	2
5	2013-09-01 02:25:57	2
6	2013-09-01 02:26:48	2
7	2013-09-01 03:13:49	3
8	2013-09-01 03:42:03	3
9	2013-09-01 03:54:33	3
10	2013-09-01 04:52:00	4
11	2013-09-01 04:57:18	4
12	2013-09-01 05:11:15	5
13	2013-09-01 05:33:31	5
14	2013-09-01 05:56:59	5
15	2013-09-01 06:58:15	6
16	2013-09-01 07:22:03	7
17	2013-09-01 07:22:36	7
18	2013-09-01 07:23:05	7
19	2013-09-01 07:28:51	7
20	2013-09-01 07:57:05	7

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(0) 100%	123 v1 Double 100%	123 v2 Double 100%	123 v3 Double 100%	123 v4 Double 100%	123 v5 Double 100%	123 v6 Double 100%	123 v7 Double 100%	123 v8 Double 100%	123 v9 Double 100%	123 v10 Double 100%	123 v11 Double 100%	123 v12 Double 100%	123 v13 Double 100%	123 v14 Double 100%	123 v15 Double 100%	123 v16 Double 100%	123 v17 Double 100%	123 v18 Double 100%	123 v19 Double 100%	123 v20 Double 100%	123 v21 Double 100%	123 v22 Double 100%	123 v23 Double 100%	123 v24 Double 100%	123 v25 Double 100%	123 v26 Double 100%	123 v27 Double 100%	123 v28 Double 100%	123 amount Double 100%	123 class Integer 100%	123 hour Bigint 100%	123 nb_same_transactions_mn_5h Bigint 100%	123 nb_same_transactions_mn_5m Bigint 100%	123 nb_same_transactions_mn_5s Bigint 100%
1	2013-09-01 02:33:50	1.17437383415169	-0.0727020239414754	1.0157602049113	1.06300518017119	-0.607608171650859	0.175083128023047	-0.562853096245295	0.0659284171226058	2.27218502890066	-0.73140877749881	0.180255199516083	-1.85136563250315	1.4530100276224	0.987058783482282	-1.37440280916387	-0.801574111116216	1.27191389585795	-0.809173737486902	-0.197829290135556	-0.217344575153148	-0.27284106486129	-0.154108514168127	-0.0141457853886628	0.0943217544153027	0.417711206498925	0.519504926440657	-0.0142288188738466	0.0021675899350461	0.01	0	2	1	1	1
2	2013-09-01 02:33:58	-0.969803681569317	0.76995899601462	2.16006987107581	-0.12500381056439	-0.0093167864315131	0.164194781682555	0.323466311285079	-0.0761360306778078	2.36416441509351	-0.629056532935246	-0.145063085520417	-2.83765270682239	0.149548958151064	0.733188078669621	-1.84081026215747	-0.736144839157124	0.966306276262094	-0.627522958541049	-0.731946291750037	-0.140714373673462	-0.233070788483237	-0.0438031212641579	-0.212575850451183	0.0471091776375942	-0.135072661962319	0.299978295927844	-0.272313978545899	-0.0531106167300644	0.01	0	2	2	2	1
3	2013-09-01 02:34:02	-0.595601293451289	0.691893099389705	2.29307258867392	-0.0917000108154069	0.0953741041016459	0.198616699236126	0.447361948542058	-0.0315395395764073	1.99068418359368	-0.995258619742464	0.020224467527339	-2.25209200210792	1.24526520523757	0.618599333584601	-1.9848242355799	-0.748169058202612	0.922381147953346	-0.740087208990059	-0.747780040646149	-0.036726520466188	-0.277827525323364	0.0526942240813936	-0.241402841244909	0.0793516537010455	-0.0616127333835326	0.335778834795432	0.152496334398776	-0.058463898999561	0.01	0	2	3	3	2
4	2013-09-01 02:34:20	-1.27393621946076	1.19795705394833	1.94502633073767	-1.15620558752605	1.64276539568964	1.59826695557583	1.47646090059973	-1.04994476894835	3.78595496690622	2.27795063595056	0.944067345412176	-2.32518342262731	1.85073742570544	-1.06747956228605	-2.35418037690081	-0.703954080022561	-0.659756701242662	-0.0078884324998385	0.0277981850081622	1.29030318956994	-0.81218281364009	-0.0815514112954326	-0.630404446080741	-1.72784225077217	0.307019890939147	0.280207952451237	-0.204370384168685	-1.06693190703212	0.01	0	2	4	4	1
5	2013-09-01 02:34:37	-7.36795663832928	2.83180831916746	1.52366227152524	-1.60516733297102	0.904577659334205	-0.812213557655294	3.66992806153215	-3.65603973305442	10.3139737345822	11.9330171580429	2.57245057627018	-3.28434627836233	0.993339261554329	-5.05630303704097	-1.1056914989802	-2.32772998831129	-1.23002287365592	-1.52023096558822	-1.19811661113889	3.4237101586647	-2.59032641806292	-0.0408320118500587	-0.0685150712657048	0.848047733676284	1.35615846689807	0.0140550697650344	0.529443182282133	-0.40242907971091	0.01	0	2	5	5	1
6	2013-09-01 02:35:04	-5.08476024104213	-0.529374357431005	0.883341895242173	0.244990377065742	0.411750426244966	-0.318722759426867	-0.52822931296115	-0.412829179947942	3.37636840344475	1.38155641807928	1.89623112092331	-1.7537344254932	0.421218017401183	0.591739803262256	-2.22911749610749	-0.286167873065592	0.76913817787665	-0.359209783197381	-0.294389791872219	-1.80553251534569	-0.478423480176882	0.154728097374716	1.78530911294736	0.210417746180514	0.767539263048182	0.469557928058457	1.29777707210128	-0.790598643202932	0.01	0	2	6	6	1
7	2013-09-01 02:35:12	-1.97964976201767	1.18899441453179	1.94176417806816	-0.341000178127457	-0.0619011087652189	-0.425829923143512	0.764880557297243	-0.491526343445088	3.65475161443189	1.38457916283083	-0.0829862446270208	-3.12563752097352	-0.0144146915916897	-0.0227658132110199	-1.91890441062163	-0.661737078548146	0.43842792806484	-0.292756168144771	-0.518619141015177	0.671987309894378	-0.664924923468304	-0.21146394907464	-0.0555033624136429	0.333471001281441	0.29270510601087	0.29338987487545	0.451755573427172	-0.0580141863402325	0.01	0	2	7	7	1
8	2013-09-01 02:35:17	-2.12373651817835	1.06044419630134	1.93684837750265	-0.297686224043688	0.145607805009528	-0.481018336823382	0.758801536789849	-0.552369530815229	3.52747217555507	1.38276610547698	0.0968091640385376	-2.56403381514433	1.03262848066368	-0.232143280295274	-2.01523620796584	-0.702537225093224	0.374519735580486	-0.42312054130842	-0.521324212502557	0.72876348885787	-0.662151752131347	-0.14582653765088	-0.123078975883343	0.359555090340329	0.327992334965141	0.286017445921249	0.374265295796615	0.138083935162753	0.01	0	2	8	8	2
9	2013-09-01 02:35:26	-1.86556582235532	1.06840209135298	1.48629121283453	-0.0544833316771568	-0.32016714880878	-0.0295060597235864	-0.193622639744985	0.606706440648507	2.1713682735924	-0.681658050915958	-0.180041752069633	-2.0501429826395	1.22418770812482	0.980729546534914	-1.97891325647769	-0.576471370936482	1.16640253131928	-0.689931118255974	-0.789061021823943	-0.0257229211969707	-0.251512837671866	-0.0192345250957926	0.12557458132808	0.059990758402977	0.143572557587512	0.370937800373073	0.232454556949806	0.331413755205665	0.01	0	2	9	9	1
10	2013-09-01 02:35:48	1.20053511648626	0.026079110608539	0.899596934073932	0.966336711394623	-0.304354409303688	0.408092054597179	-0.468111894959619	0.0022632412360692	2.07084894469618	-0.774612540227533	0.114506190324796	-1.17460540071456	3.00902645186819	0.668576075577221	-1.51287228830024	-0.767549000527472	1.05662931872346	-0.876076429833917	-0.072956443909761	-0.110596803382562	-0.279141762988326	-0.0967797501068228	-0.077075378737238	-0.237805365764505	0.515764039268044	0.537376238227258	-0.0048424597528101	0.0016072976313775	0.01	0	2	10	10	1
11	2013-09-01 02:36:13	1.15134645917296	-0.0468708222551066	1.16050068695655	1.13957581233298	-0.750849120334911	-0.0141649882830707	-0.538950124146666	0.0299338800518957	2.21463703444538	-0.738612238190786	0.512658196503496	-1.61845319822508	1.62914121538786	0.960414983184585	-1.42476172068234	-0.908331882635259	1.37778025490699	-0.945116639504037	-0.32276596157338	-0.213525210718624	-0.248564735862066	-0.0737408845782814	0.0227695372855089	0.414127885223835	0.381668984340458	0.499096502126401	-0.0121800827193915	0.0058520548052222	0.01	0	2	11	11	1
12	2013-09-01 02:36:21	1.15170011149208	-0.0119320169987585	1.1705281906858	1.13488939474779	-0.70780827769076	-0.0026906721927648	-0.506229978775662	0.0025349821915972	2.14161305183478	-0.752777135123339	0.594903990087636	-1.36195976846769	2.1114181572563	0.864435891249312	-1.47767576927063	-0.930954514204398	1.35022083237725	-1.00428622519052	-0.326022097305229	-0.182625478427028	-0.243019720693059	-0.0342435050130241	0.0169807894753756	0.427332209964337	0.397987085997744	0.497480357883862	-0.0090273207065173	0.0068878305063575	0.01	0	2	12	12	1
13	2013-09-01 02:36:23	1.15170011149208	-0.0119320169987585	1.1705281906858	1.13488939474779	-0.70780827769076	-0.0026906721927648	-0.506229978775662	0.0025349821915972	2.14161305183478	-0.752777135123339	0.594903990087636	-1.36195976846769	2.1114181572563	0.864435891249312	-1.47767576927063	-0.930954514204398	1.35022083237725	-1.00428622519052	-0.326022097305229	-0.182625478427028	-0.243019720693059	-0.0342435050130241	0.0169807894753756	0.427332209964337	0.397987085997744	0.497480357883862	-0.0090273207065173	0.0068878305063575	0.01	0	2	13	13	2
14	2013-09-01 02:36:31	-0.860037512152694	0.469211183724031	2.4890716121899	0.027537366554548	0.282903168351467	-0.660368257675906	0.635615044448659	-0.663886979563852	2.64199153051781	0.0119509994777818	0.285314176226303	-2.3745642542457	1.13238308196624	0.105562216666883	-2.17191432471919	-0.750394016146823	0.535391628381209	-0.481706707907894	-0.505624278241851	0.113049983185148	-0.411859774683602	0.0926967025307528	-0.324108983758853	0.713736944787877	-0.328495788580868	0.185299862144925	-0.111511056341975	-0.441329329833803	0.01	0	2	14	14	1
15	2013-09-01 02:36:32	1.15170011149208	-0.0119320169987585	1.1705281906858	1.13488939474779	-0.70780827769076	-0.0026906721927648	-0.506229978775662	0.0025349821915972	2.14161305183478	-0.752777135123339	0.594903990087636	-1.36195976846769	2.1114181572563	0.864435891249312	-1.47767576927063	-0.930954514204398	1.35022083237725	-1.00428622519052	-0.326022097305229	-0.182625478427028	-0.243019720693059	-0.0342435050130241	0.0169807894753756	0.427332209964337	0.397987085997744	0.497480357883862	-0.0090273207065173	0.0068878305063575	0.01	0	2	15	15	2
16	2013-09-01 09:10:16	1.15157763757596	-0.0167198637735371	1.19134728469967	1.13625633473046	-0.700435811799267	0.323663709756618	-0.64181863716041	0.227597222458138	0.564591020196726	-0.0457139687522945	0.717398418801485	1.38144848149376	0.604884219379128	-0.304351124532671	-0.56515162887159	0.340642663320457	-0.652669207715164	0.464318289054177	0.200694646558147	-0.0928805896873416	-0.0310591499018037	0.13280302562462	-0.0561650298138305	0.0296730570055968	0.452408592815015	-0.407368417798342	0.0739168595353029	0.0244942129981812	0.01	0	9	1	1	1
17	2013-09-01 09:46:03	-1.75461427105338	-0.194704764456529	1.06210306797959	-2.79791776137029	0.660730783006917	-0.898656157367035	0.60467378080792	-1.17561290206091	1.43054565913037	-0.434033211858084	1.68925343303504	0.789060739163743	-0.708893226205562	-0.100789899812248	0.553276667797284	-0.421604034376388	-0.687466909501895	-0.0863204177580716	-0.174231918937302	-0.509056155768691	0.547389091433624	0.299576018766378	0.600068815831329	0.0162644666717384	-0.0356283161207234	-0.295442973837425	0.320610766075769	-0.366274830652761	0.01	0	9	2	1	1
18	2013-09-01 09:54:13	1.54402641302852	-0.761242908980963	-0.17433402858969	-1.39811110656457	-0.898719910889933	-0.884399729263868	-0.51660346246968	-0.301736229758136	-2.25225483023085	1.44899397411146	-0.189409114291241	-0.669419519666465	0.690550280560672	-0.0730806840158219	0.487416782621338	-1.08547118788045	0.923324456707021	-0.745374368074212	-0.480295597518074	-0.38200833283334	-0.149320674156989	0.0567173843627435	-0.0448383149098325	0.0752607543275107	0.625484743368405	-0.0526323081015999	0.0182675866380892	0.0014242006764067	0.01	0	9	3	1	1
19	2013-09-01 13:59:00	1.17162075646506	0.0692654416282711	1.31580270483855	1.32585080522113	-0.885902548423458	-0.180538340229057	-0.507002772248593	0.0386481739358494	0.746356204359898	-0.225801589734339	-0.625043337877428	0.995821487824404	0.997946802776625	-0.554058227351889	0.0711340176714213	0.0312516215329477	-0.303405282321943	-0.183133011290416	-0.215112492896172	-0.0911617468395277	-0.0569532965230452	0.110801755971872	-0.0075054428721612	0.421991677335113	0.45105853572827	-0.41150474485333	0.0805577473023398	0.0387893269276862	0.01	0	13	4	1	1
20	2013-09-01 15:16:28	1.04000379466034	-0.739722914435903	1.64851553383418	0.442334544293136	-1.13488096320945	1.49217849759694	-1.38374724223183	0.68740981343528	1.59372940530984	-0.487181945301979	0.847431237467186	1.67678091043467	-0.203578273713224	-0.926371091028804	-1.70997667391179	-0.858089097319018	0.799146026004985	-0.99974720107852	0.191868713071341	-0.165448040329761	-0.0392888170561737	0.438104252755279	0.0295794174775364	-0.157247017320727	0.0905578599770092	1.1511800598537	0.0357067864014139	-0.0008839908796053	0.01	0	15	2	1	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.

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

	📅 time Timestamp(0) 100%	123 v1 Double 100%	123 v2 Double 100%	123 v3 Double 100%	123 v4 Double 100%	123 v5 Double 100%	123 v6 Double 100%	123 v7 Double 100%	123 v8 Double 100%	123 v9 Double 100%	123 v10 Double 100%	123 v11 Double 100%	123 v12 Double 100%	123 v13 Double 100%	123 v14 Double 100%	123 v15 Double 100%	123 v16 Double 100%	123 v17 Double 100%	123 v18 Double 100%	123 v19 Double 100%	123 v20 Double 100%	123 v21 Double 100%	123 v22 Double 100%	123 v23 Double 100%	123 v24 Double 100%	123 v25 Double 100%	123 v26 Double 100%	123 v27 Double 100%	123 v28 Double 100%	123 amount Double 100%	123 class Integer 100%	123 hour Bigint 100%	123 nb_same_transactions_mn_5h Bigint 100%	123 nb_same_transactions_mn_5m Bigint 100%	123 nb_same_transactions_mn_5s Bigint 100%
1	2013-09-01 00:32:49	-0.693947032539896	1.31802884573234	0.815896546390908	-0.0901880214976684	-0.075838234312895	-0.538354143635321	0.213477573817951	0.536657221951409	-0.764248886636331	-0.555085025709551	1.0076697387014	0.542556099942768	-0.0748596594949037	0.108197931976033	0.202795846273843	0.806172170279131	-0.179718298940093	0.528000924977535	0.31976827799819	0.0034508323493783	-0.196070224687173	-0.666479337681067	-0.0186051553568027	-0.0546830086807125	-0.131943387485714	0.0780914222871872	0.115935063821916	0.0210775772996645	0.02	0	0	1	1	1
2	2013-09-01 06:02:53	-1.14969451220909	-0.149049149392535	3.45172397723128	-2.04391487513985	-0.440209234875553	1.35287450398206	-0.509356598137368	0.491119199459543	3.68890786045674	-2.38228228319725	1.53703949480653	-1.06166117837914	0.704980862009968	0.136610477538032	-2.81544765802067	-1.0700412193499	0.859022493149335	0.018447633923925	-1.164538886764	-0.343143816879078	0.0542088275799692	1.03688448650413	-0.531341726003296	-0.281125565819688	0.576517389145626	-0.620884862256632	-0.159750470309145	-0.183100093535197	0.02	0	6	1	1	1
3	2013-09-01 14:40:10	1.42950150321066	-0.990270946322504	-0.224429354355113	-1.80873967798326	0.770787536686733	3.73098858623111	-1.91421161021549	1.0363846234542	-0.316970571150948	0.646102741712846	-0.100189680769797	-0.751094533911632	0.437455710928105	-0.504131403241282	1.49184366998971	2.02403909933868	-0.55493579880519	-0.204874346580464	0.477147063453988	0.206331981847792	0.376136458015709	0.928722847839222	-0.108174338216785	1.04892161530641	0.481213827731516	-0.0263415992038655	0.0629002811121915	0.0246387967096172	0.02	0	14	1	1	1
4	2013-09-01 16:05:56	1.35095806068418	-0.701791063310273	1.51443870664066	-0.553158513725929	-1.90369380186424	-0.718444445490207	-1.31337777922623	0.0043063520743715	-0.234185642513228	0.580121818921367	-0.0033740409599788	-0.373721089571221	0.604794680217453	-0.660874469287345	1.25993041995031	1.59344521213969	0.0539741871936977	-0.779112004471427	-0.019998043022956	0.0827465203042049	0.453046652871799	1.30932244783782	-0.0346537047432989	0.761873961392848	0.283905112974346	-0.0622453621179471	0.0622168850866756	0.0350639464178992	0.02	0	16	2	1	1
5	2013-09-01 18:14:00	1.27292822314594	-0.892467593971756	1.42682976745185	-0.738418520419938	-1.70208947369532	0.126512133408805	-1.58038288864953	0.368861387221243	-0.305193837054185	0.740620034866771	1.59791341505794	-0.288347323355756	-0.693680003873274	-0.201644436440475	0.954718774149329	1.59112785950827	0.105563476832119	-0.542336080988355	-0.0336919116192109	-0.0112577476035205	0.510130513608356	1.41269834193559	-0.0176406044329373	0.253787369668001	0.156714255069728	-0.0388807733557724	0.0666771234322596	0.0177365794454624	0.02	0	18	3	1	1
6	2013-09-02 10:04:14	1.99985212662926	-1.12866033208038	0.209439762579962	-0.530182942627159	-1.22792882274417	0.694198333719758	-1.50668645161802	0.32426489772477	3.6051418372434	-1.19803850131345	-2.52090604755197	1.04389576149831	0.311990821681622	-1.30525786090393	-0.852452409215034	-0.705334588453214	-0.169726921204767	0.638871587635626	1.20923599481302	-0.224268251599207	0.0660954419623233	0.921168471989934	-0.0535830904956647	-0.94898395529477	0.153627838674645	-0.75229394432399	0.16779489935579	-0.0304662655408447	0.02	0	10	1	1	1
7	2013-09-02 09:38:19	2.05498809848153	0.284267775038234	-1.67852013031524	0.516621392970052	0.273809595913958	-1.41999293757602	0.320023704548826	-0.423396056951374	0.44695275624496	-0.506612788262026	-0.13778035384111	0.585974264142807	0.936550765673285	-0.911641131938056	0.841109314225321	0.099178147414804	0.398916715463901	0.294168340288724	-0.509854590595338	-0.158463834244033	0.210477621890101	0.844066988298611	-0.0223558134928546	-0.0512351772991781	0.271292432932106	-0.101172638611134	0.0075661457832999	-0.0301761912740807	0.41	0	9	1	1	1
8	2013-09-02 20:22:16	-0.338654231410615	0.759709924109977	-0.666459804350598	-0.710857062539831	1.31498031092885	-0.392840546800806	0.726882206670462	0.0145185178387038	-0.0588814821698158	-0.409450826183642	0.574303631877135	0.241003998504569	-0.343195886703044	-0.852304474216442	-0.832522111565248	0.658458781704808	-0.0434429047404996	0.455710176633355	0.255589010073821	-0.0505903209501645	-0.307640838742457	-0.822601648064825	0.209977569354639	0.0774270045224069	-0.381162967340547	0.10444401436013	-0.0539954266376779	0.106934403132201	0.64	0	20	1	1	1
9	2013-09-01 13:18:47	-0.801472278937288	1.13829010960086	2.05218284784692	0.55231062509347	0.483049561804379	0.689347507092742	0.435007557161854	0.38386629406628	-1.00491982324785	-0.159405140949012	1.9210033569248	1.21926072074644	0.756840649787061	0.293919364836174	1.02533148746851	-0.533382590587028	0.0653727460082559	-0.610715560002575	0.13900598834456	0.197265760953382	-0.149414443863121	-0.291131263076908	-0.0934242438948921	-0.307800502261707	0.0271214725620796	-0.50349992254788	0.366040110428397	0.134507952069227	0.95	0	13	1	1	1
10	2013-09-01 16:22:58	-1.07183900741184	1.22756375591285	1.25187824199279	0.135979364714769	-0.551661634978032	-0.391285796158987	0.0713306742877466	0.58450326909491	-0.103666727533886	-0.19085938912499	0.790877132149671	1.11364204391378	-0.160462183618166	0.139034400458047	-1.40275424389142	-0.26817538585695	0.0752867984041939	0.0604459812735791	0.505528982998521	0.085441562727798	0.0552067686703952	0.406216065773802	-0.124657021652183	0.600056077518045	-0.119472473006568	0.339128162048251	0.33841240113005	0.187957647438139	0.95	0	16	2	1	1
11	2013-09-01 20:34:54	1.2516371424818	-0.018728947171153	0.297175336596455	0.575553025217061	-0.139177485554658	0.198031978860895	-0.230655403115478	0.0745889209022131	0.355082947318127	-0.0522662495343469	0.241934510707229	1.10061470511969	0.392397107711587	-0.124618554797991	-1.08548689591413	0.0516121718687638	-0.483909452317509	0.150355162091168	0.787033582802688	-0.0721998766267873	-0.089981422892852	-0.0092372054676046	-0.203238971898686	-0.424939449802951	0.681584641278812	0.471523838145321	-0.0170315318124922	-0.0078670408615726	0.95	0	20	2	1	1
12	2013-09-01 23:26:54	-2.2037857406945	1.55347423535466	0.821292283798549	-1.67359018185129	-1.3051002757865	-0.436330187135068	-1.53593501212746	-1.606352968691	0.345192175733562	-1.72170219254449	-1.46963719325939	0.43811972450053	-0.809936098897979	0.441731531361695	-1.19018926341791	0.996442570608421	-0.135552394645336	-0.282892311475285	-1.53681161851443	0.407615732883977	-1.14538939823377	0.728833520817225	0.13010995922011	0.455773847640982	-0.0902302907917962	1.19711772001676	0.0620867676916535	0.11378030795314	0.95	0	23	2	1	1
13	2013-09-02 13:25:00	-0.440709145495647	0.966060432719464	0.357697650206555	-1.05253381715538	1.35774331316337	-1.06325870958681	1.4000452254861	-0.412791510468331	-0.0223904387955023	-1.65010969745612	-0.319171772189187	0.415851684729527	0.791591932654313	-2.30854391959411	-1.63575770420597	0.304586872954058	0.650731500990471	-0.0636663265485459	-1.52587331515157	-0.0635632580509579	0.0377320799000071	0.455226277370916	-0.339278817139595	1.09885269002105	0.0631553904442133	0.313496707624243	-0.0840773480244641	-0.0366408217222032	0.95	0	13	1	1	1
14	2013-09-02 13:54:54	-1.16157699600457	1.74431333106717	-0.294571431375487	-0.591445721664898	0.579927011648599	-0.892343811751285	0.947421415293079	-0.0023183974562828	0.50000657557905	0.0433707824839605	1.0943630884411	0.501501559402544	-0.287555561584148	-1.94204767823141	-1.5222848342947	0.117308735666259	0.863130749888103	0.722201019995122	-0.0837573466011499	0.455296943087842	-0.0689523410672841	0.551216258543212	-0.292769076102858	-0.0019596752524438	-0.101470852672893	0.514791380501416	0.240695025419321	-0.0728099810383488	0.95	0	13	2	1	1
15	2013-09-01 10:40:13	-1.92611548693568	0.184719734803664	2.19271083160893	1.09457247655904	-0.385502519972489	-0.128402466901579	-0.660055513891711	0.801095418563948	-0.104169722462678	-0.197697442296141	0.66933827986375	0.391366727460004	-0.718491452704078	0.266596712260071	0.270732694873461	0.231781219613771	-0.170865982062675	1.11408846316899	0.801274706541643	-0.108397413309935	0.170543297516317	0.768680147526945	0.454539620840751	0.311749275265596	0.551460759425848	-0.0982559605097935	0.218179905628269	-0.0156099776738692	1.23	0	10	1	1	1
16	2013-09-01 19:57:27	-1.14137238851705	1.05107115322749	1.38771417220089	-1.48866262507052	-0.200034561116065	-0.320415338057058	0.255786017821083	0.471954585320992	-0.0677487886236008	-0.298387712816362	1.34973105968381	0.65634227071921	-0.360289060314384	0.267050469338787	0.0946602159663928	0.534181088345345	-0.574698976694094	-0.272436356876521	-0.783269337070311	0.0758339635418711	-0.0475910337455412	0.0107687019602749	-0.0332416934290145	0.0393006514817041	-0.40786516621452	0.711034861984927	0.0864115922092428	-0.065217834299905	1.23	0	19	1	1	1
17	2013-09-01 20:20:16	-0.65667656699872	1.20694537905164	1.02622534714923	0.812685483666262	-0.0078038744382095	-0.175645576505417	0.227742087974501	0.542883092118704	-1.07972964723133	-0.307029518018478	1.39911642601185	0.790202370183494	-0.216831037388552	0.931808865577283	0.526699436607449	-0.353719868930806	0.0927061025883415	0.146995622852067	0.283569133295125	-0.155029857968302	0.290753830772581	0.7475676697386	-0.0849863638815244	0.237666576601252	-0.372742225190831	-0.332787524578975	0.0637383532578724	0.082275432223769	1.23	0	20	2	1	1
18	2013-09-02 13:58:32	-8.83184545374404	5.07310005520069	-2.72160102537414	-1.49042273888606	-1.71884292762375	1.26802658134452	-2.97188541817738	-2.17048795553174	2.50044834179629	3.01699301220904	0.160196258531392	1.79723548175127	1.65974762313288	-1.6142142448328	0.0464890987962726	1.71721986603144	0.533514285076125	1.28374763958823	-0.0578756099260872	-1.1018684751324	3.67295371673412	-1.5904140205892	0.823570692490352	-0.305392683491928	0.946181383425519	0.280642532555803	-2.64528290044007	0.60123218992936	1.23	0	13	1	1	1
19	2013-09-02 20:22:21	-1.77465544727231	1.93946582485034	-1.45555334601239	-1.75000135030913	0.898426910453413	-0.975464411161841	0.874557016712993	0.671086888829141	-0.584006119888973	-0.336409975516436	0.0872884572155252	0.301549153460162	-0.960658974632349	1.43420273232627	-0.911539883725276	0.0859570321855169	-0.575951943281065	0.325187842019577	-0.170551488512453	-0.0683493517674602	0.210981544832965	0.454554995722769	-0.441561589840362	0.374278740581594	0.798320776312719	0.140698124091242	0.0879735616592954	0.122206390045217	1.23	0	20	1	1	1
20	2013-09-01 20:56:04	-1.0427855254866	0.777697881613338	1.62850198513209	0.251932776145157	1.04135388576057	1.18529518434779	0.431327699097117	0.48552528945836	-0.551620362615748	-0.591129044846883	0.259450280872439	-0.277735660492825	-0.969983810389014	0.512020285255607	2.01055460338215	-1.31227628753616	0.861445541068075	-1.67682780889288	-1.72337218250831	-0.245836259083721	0.316541216787259	0.985919521144664	-0.245939718252857	-0.976402408934601	0.341213287043698	-0.0639083724718469	-0.0132083278389024	0.0590477880245944	1.24	0	20	1	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  < TIMESTAMP '2013-09-02 00:00:00'")
test  = creditcard.search("Time >= TIMESTAMP '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 vastorbit.machine_learning.vast import LogisticRegression

predictors = creditcard.get_columns(exclude_columns = ["Class", "Time"])
response = "Class"
model = LogisticRegression(
    max_iter = 3000,
)
model.fit(train, predictors, response, test)
model.classification_report()

	value
auc	0.9697148890112128
prc_auc	0.15988482837422502
accuracy	0.9956482696691303
log_loss	0.020447207064671364
precision	0.9065934065934066
recall	0.7819905213270142
f1_score	0.8396946564885496
mcc	0.8398539006289721
informedness	0.7807988768576464
markedness	0.9033754982338404
csi	0.7236842105263158

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 vastorbit.machine_learning.model_selection import elbow

elbow(
    creditcard,
    ["V12", "V17", "V10", "V14", "V16"],
    n_clusters = [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 vastorbit.machine_learning.vast import KMeans

model = KMeans(n_clusters = 13)
model.fit(creditcard, ["V12", "V17", "V10"])

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 Bigint 100%	123 percent_fraud Double 100%	123 total_fraud Bigint 100%
1	3	89	96.62921348314607	0
2	1	92	96.73913043478261	0
3	6	105	0.0	0
4	8	200	97.5	0
5	10	358	0.8379888268156425	0
6	12	1271	3.304484657749803	0
7	5	1994	1.4543630892678034	0
8	0	2617	0.22927015666794037	0
9	4	3060	0.29411764705882354	0
10	11	3414	0.2050380785002929	0
11	9	3991	0.2756201453269857	0
12	7	5443	0.22046665441851918	0
13	2	6380	0.047021943573667714	0

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) AS total_fraud",
    ],
).sort("total")

	123 global_outliers Integer 100%	123 total Bigint 100%	123 percent_fraud Double 100%	123 total_fraud Bigint 100%
1	1	315	71.74603174603175	226
2	0	28699	0.9268615631206663	266

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 vastorbit.machine_learning.vast 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!