{"metadata":{"kernelspec":{"name":"ir","display_name":"R","language":"R"},"language_info":{"name":"R","codemirror_mode":"r","pygments_lexer":"r","mimetype":"text/x-r-source","file_extension":".r","version":"4.0.5"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# Library","metadata":{}},{"cell_type":"code","source":"library(tidyverse) # metapackage of all tidyverse packages\nlibrary(ggplot2)","metadata":{"_uuid":"051d70d956493feee0c6d64651c6a088724dca2a","_execution_state":"idle","execution":{"iopub.status.busy":"2021-10-17T09:27:21.572014Z","iopub.execute_input":"2021-10-17T09:27:21.573466Z","iopub.status.idle":"2021-10-17T09:27:21.586676Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Data pre-processing\n\n1. Import data\n2. EDA (on columns, excluding outcome)\n3. Export data (train + test)","metadata":{}},{"cell_type":"markdown","source":"## Import data","metadata":{}},{"cell_type":"code","source":"path <- \"../input/facebook-recruiting-iv-human-or-bot/\"\nlist.files(path = path)\n# unzip the csv files\nunzip(paste0(path, \"train.csv.zip\"))\nunzip(paste0(path, \"test.csv.zip\"))\nunzip(paste0(path, \"bids.csv.zip\"))\nlist.files(\"../working\")","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:27:21.588825Z","iopub.execute_input":"2021-10-17T09:27:21.590098Z","iopub.status.idle":"2021-10-17T09:27:28.451492Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data <- read.csv(\"../working/train.csv\")\ndata$outcome = as.factor(data$outcome)\ndata %>% str()","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:27:28.453802Z","iopub.execute_input":"2021-10-17T09:27:28.455244Z","iopub.status.idle":"2021-10-17T09:27:28.496473Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# test data\ntest_data <- read.csv(\"../working/test.csv\")\ntest_data %>% str()","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:27:28.500282Z","iopub.execute_input":"2021-10-17T09:27:28.502985Z","iopub.status.idle":"2021-10-17T09:27:28.546993Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# note: bidder_id is the key to join with bids info and bidders \nbids_info <- read.csv(\"../working/bids.csv\")\nbids_info %>% str()","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:27:28.549122Z","iopub.execute_input":"2021-10-17T09:27:28.55042Z","iopub.status.idle":"2021-10-17T09:28:16.446117Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# EDA: on X only","metadata":{}},{"cell_type":"markdown","source":"## Training data\n\nWe will explore the following:\n1. Missing row/value\n2. Is there duplicated information","metadata":{}},{"cell_type":"code","source":"data %>% head()","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:28:16.449812Z","iopub.execute_input":"2021-10-17T09:28:16.45138Z","iopub.status.idle":"2021-10-17T09:28:16.477283Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Check if there is any duplication in `bidder_id`, `payment_account`, `address`.\n\nHaving duplication might suggest a foul play (bidder who register multiple accounts for bidding)","metadata":{}},{"cell_type":"code","source":"# missing values\ndata.frame(\n    bidder_id = data$bidder_id %>% is.na() %>% sum(),\n    payment_account = data$payment_account %>% is.na() %>% sum(),\n    address = data$address %>% is.na() %>% sum(),\n    outcome = data$outcome %>% is.na() %>% sum()\n)","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:28:16.481014Z","iopub.execute_input":"2021-10-17T09:28:16.482639Z","iopub.status.idle":"2021-10-17T09:28:16.505951Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# non unique counts\ndata.frame(\n    data$bidder_id %>% table() %>% data.frame() %>% filter(Freq > 1) %>% summarise(bidder_id = sum(Freq)),\n    data$payment_account %>% table() %>% data.frame() %>% filter(Freq > 1) %>% summarise(payment_account = sum(Freq)),\n    data$address %>% table() %>% data.frame() %>% filter(Freq > 1) %>% summarise(address = sum(Freq))\n)","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:28:16.509428Z","iopub.execute_input":"2021-10-17T09:28:16.510995Z","iopub.status.idle":"2021-10-17T09:28:16.572978Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# outcome variable\ndata$outcome %>% summary()","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:28:16.57651Z","iopub.execute_input":"2021-10-17T09:28:16.578073Z","iopub.status.idle":"2021-10-17T09:28:16.597645Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We checked and concluded that:\n1. There is no missing bidder id, payment account, address and outcome\n2. Every bidder has unique ID, payment account and address.\n3. There is imbalance data: only 103 robots (5.39%)","metadata":{}},{"cell_type":"code","source":"data$payment_account[1] %>% nchar()","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:28:16.601151Z","iopub.execute_input":"2021-10-17T09:28:16.602607Z","iopub.status.idle":"2021-10-17T09:28:16.618976Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Notice that there is a lot of common starting payment account\n# perhaps this suggest common accounts, differ by the final digits\n# we notice high sd over mean as well\nstr_extract(data$payment_account, \"\\\\w{32}\") %>% as.factor() %>% table() %>% as.data.frame() %>% filter(Freq > 1) %>% \n    summary()\nstr_extract(data$payment_account, \"\\\\w{32}\") %>% as.factor() %>% table() %>% as.data.frame() %>% filter(Freq > 1) %>% \n    summarise(sd_mean = sd(Freq)/mean(Freq))","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:28:16.621335Z","iopub.execute_input":"2021-10-17T09:28:16.622656Z","iopub.status.idle":"2021-10-17T09:28:16.66359Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# translate payment account into number of similiar account\ndata$payment_account <- str_extract(data$payment_account, \"\\\\w{30}\")\ntest_data$payment_account <- str_extract(test_data$payment_account, \"\\\\w{30}\")\npayment_account_counts <- c(\n    data$payment_account,\n    test_data$payment_account\n) %>% table() %>% as.data.frame()\nnames(payment_account_counts) = c(\"payment_account\", \"n_similiar_payment_account\")\npayment_account_counts$n_similiar_payment_account <- payment_account_counts$n_similiar_payment_account - 1\n\ndata <- data %>% left_join(payment_account_counts, by=\"payment_account\")\ntest_data <- test_data %>% left_join(payment_account_counts, by=\"payment_account\")\ndata <- data %>% select(-payment_account)\ntest_data <- test_data %>% select(-payment_account)","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:28:16.665966Z","iopub.execute_input":"2021-10-17T09:28:16.66726Z","iopub.status.idle":"2021-10-17T09:28:16.721203Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# drop address\ndata <- data %>% select(-address)\ntest_data <- test_data %>% select(-address)","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:28:16.723449Z","iopub.execute_input":"2021-10-17T09:28:16.724788Z","iopub.status.idle":"2021-10-17T09:28:16.74357Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Bids info\n\nNow, we notice that bids_info is a many to one matching to bidder infomation.\\\nThis means that simply joining the two tables does not help us in understanding the characteristics between human and robot bidders.\\\nInstead, we will perform data preocessing to extract information from the raw data.\n\nGenerally, our extraction methods involves extracting summary statistics (e.g. count) to gather the sufficient statistics.","metadata":{}},{"cell_type":"code","source":"bids_info %>% head()","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:28:16.745779Z","iopub.execute_input":"2021-10-17T09:28:16.747125Z","iopub.status.idle":"2021-10-17T09:28:16.771913Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# bids_info groupped by bidder id\nbidder_id_info <- bids_info %>% group_by(bidder_id) %>% summarise(\n    n_auction = length(unique(auction)),\n    n_merchandise = length(unique(merchandise)),\n    n_device = length(unique(device)),\n    n_country = length(unique(country)),\n    n_ip = length(unique(ip)),\n    n_url = length(unique(url)),\n    time_mean = mean(time),\n    time_sd = ifelse(is.na(sd(time)), 0, sd(time))\n)\nbidder_id_info %>% head()\nbidder_id_info %>% summary()","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:28:16.774178Z","iopub.execute_input":"2021-10-17T09:28:16.775471Z","iopub.status.idle":"2021-10-17T09:28:22.32594Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# checking the variation of data, normalised by the mean\n# keep: auction, device, country, ip, url, time_sd\nbidder_id_info %>% summarise(\n    sd_auction = sd(n_auction) / mean(n_auction),\n    sd_merchandise = sd(n_merchandise) / mean(n_merchandise),\n    sd_device = sd(n_device) / mean(n_device),\n    sd_country = sd(n_country) / mean(n_country),\n    sd_ip = sd(n_ip) / mean(n_ip),\n    sd_url = sd(n_url) / mean(n_url),\n    sd_time_mean = sd(time_mean) / mean(time_mean),\n    sd_time_sd = sd(time_sd) / mean(time_sd)\n)","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:28:22.328205Z","iopub.execute_input":"2021-10-17T09:28:22.329513Z","iopub.status.idle":"2021-10-17T09:28:22.355706Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# final merge\ndata <- data %>% left_join(\n    bidder_id_info %>% select(\n        bidder_id, n_auction, n_device, n_country, n_ip, n_url, time_sd\n    ), \n    by=\"bidder_id\"\n)","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:28:22.358141Z","iopub.execute_input":"2021-10-17T09:28:22.359556Z","iopub.status.idle":"2021-10-17T09:28:22.382035Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"row.names(data) <- data$bidder_id\ndata <- data %>% select(-bidder_id)\ndata %>% head()","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:28:22.384377Z","iopub.execute_input":"2021-10-17T09:28:22.38574Z","iopub.status.idle":"2021-10-17T09:28:22.416487Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# check to make sure no empty rows\ndata$time_sd %>% is.na() %>% sum()\ndata[data$time_sd %>% is.na(), ] %>% head()\ndata[data$time_sd %>% is.na(), ] %>% select(outcome) %>% summary()","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:28:22.419288Z","iopub.execute_input":"2021-10-17T09:28:22.420655Z","iopub.status.idle":"2021-10-17T09:28:22.458781Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# not found in bidder id\nbids_info %>% filter(bidder_id == \"5f50c6187a179e2ee7ba2fbcfc845c7a1smgr\")","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:28:22.461541Z","iopub.execute_input":"2021-10-17T09:28:22.463002Z","iopub.status.idle":"2021-10-17T09:28:22.97276Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# remove NA rows\n# those are bidders with no activity -> assume to be human since the purpose of a robot is to bid\n# also, since majority class is human, to minimise misclassification we should consider them as human\ndata <- data[!(is.na(data$time_sd)), ]","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:28:22.974977Z","iopub.execute_input":"2021-10-17T09:28:22.976253Z","iopub.status.idle":"2021-10-17T09:28:22.989168Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_data <- test_data %>% left_join(\n    bidder_id_info %>% select(\n        bidder_id, n_auction, n_device, n_country, n_ip, n_url, time_sd\n    ), \n    by=\"bidder_id\"\n)","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:28:22.991408Z","iopub.execute_input":"2021-10-17T09:28:22.992642Z","iopub.status.idle":"2021-10-17T09:28:23.014162Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# we have some non-bidders in test data too\ntest_data %>% filter(is.na(n_auction)) %>% head()","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:28:23.016385Z","iopub.execute_input":"2021-10-17T09:28:23.017662Z","iopub.status.idle":"2021-10-17T09:28:23.043082Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"bids_info %>% filter(bidder_id==\"a48f8c34dd8acc8f0150ee729f500df6luw4q\")","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:28:23.045276Z","iopub.execute_input":"2021-10-17T09:28:23.046529Z","iopub.status.idle":"2021-10-17T09:28:23.14963Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Export data","metadata":{}},{"cell_type":"code","source":"list.files(\"../working\")","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:28:23.151908Z","iopub.execute_input":"2021-10-17T09:28:23.15321Z","iopub.status.idle":"2021-10-17T09:28:23.168338Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data %>% str()\ndata %>% head()\ndata %>% write.csv(\"../working/train_processed.csv\")","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:28:23.170547Z","iopub.execute_input":"2021-10-17T09:28:23.171783Z","iopub.status.idle":"2021-10-17T09:28:23.218853Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_data %>% str()\ntest_data %>% head()\ntest_data %>% write.csv(\"../working/test_processed.csv\")","metadata":{"execution":{"iopub.status.busy":"2021-10-17T09:28:23.221173Z","iopub.execute_input":"2021-10-17T09:28:23.222453Z","iopub.status.idle":"2021-10-17T09:28:23.29022Z"},"trusted":true},"execution_count":null,"outputs":[]}]}