{"cells":[
 {
  "cell_type": "code",
  "execution_count": null,
  "metadata": {
   "collapsed": false
  },
  "outputs": [],
  "source": "# Kaggle-competititon \"Avito Context Ad Clicks\"\n# See https://www.kaggle.com/c/avito-context-ad-clicks\n\n# In order to run this script on Kaggle-scripts I had to limit the number of entries to read\n# from the database as well as to decrease the sample-size. With the full dataset from the database as well\n# as a sample of 10 millions entries I got a 0.05104 on the public leaderboard\n\nlibrary(\"data.table\")\nlibrary(\"RSQLite\")\nlibrary(\"caret\")\n\n# ----- Prepare database -------------------------------------------------------\n\ndb <- dbConnect(SQLite(), dbname=\"../input/database.sqlite\")\ndbListTables(db)\n\n# ----- Utitlies ---------------------------------------------------------------\n\n# Define constants to improve readability of large number\nthousand <- 1000\nmillion  <- thousand * thousand \nbillion  <- thousand * million\n\n# Runs the query, fetches the given number of entries and returns a\n# data.table\nfetch  <- function(db, query, n = -1) {\n  result <- dbSendQuery(db, query)\n  data <- dbFetch(result, n)\n  dbClearResult(result)\n  return(as.data.table(data))\n}\n\n# Loss-function to evaluate result\n# See https://www.kaggle.com/c/avito-context-ad-clicks/details/evaluation\nlogloss <- function(y, yHat){\n  \n  threshold <- 10^(-15)\n  yHat <- pmax(pmin(yHat, 1-threshold), threshold)\n  \n  loss <- -mean(y*log(yHat) + (1-y)*log(1-yHat))\n  \n  return(loss)\n}\n\n# ----- Simple Machine Learning ------------------------------------------------\n\n# Select contextual Ads (OnjectType=3), results in 190.157.735 entries\n# Warning: Takes a few minutes\ntrainSearchStreamContextual <- fetch(db, \"select HistCTR, IsClick from trainSearchStream where ObjectType=3\", 10 * million)\nm <- nrow(trainSearchStreamContextual)\n\n# Create stratified sample \nsampleSize <- 1 * million #100 * million\nsampleRatio <- sampleSize / m\nsampleIndex <- createDataPartition(trainSearchStreamContextual$IsClick, p = sampleRatio, list=FALSE)\ntrainSearchStreamContextualSample <- trainSearchStreamContextual[as.vector(sampleIndex), ]\n\n# Compare click-ratio in full set and sample to verify stratification\nprint(paste(\"Clickratio full dataset:\", sum(trainSearchStreamContextual$IsClick)/m))\nprint(paste(\"Clickratio sample:\", sum(trainSearchStreamContextualSample$IsClick)/sampleSize))\n\n# Create stratified random split ...\ntrainSampleIndex <- createDataPartition(y = trainSearchStreamContextualSample$IsClick, p = .80, list = FALSE)\n\n# ... and partition data-set into train- and validation-set\ntrainSearchStreamContextualTrainSample <- trainSearchStreamContextualSample[as.vector(trainSampleIndex),]\ntrainSearchStreamContextualValidationSample <- trainSearchStreamContextualSample[-as.vector(trainSampleIndex),]\n\n# Build a logistic regression ...\nmodel <- glm(IsClick ~ HistCTR, data = trainSearchStreamContextualTrainSample, family=\"binomial\")\n\n# Check that regression-coefficients have significant impact\nsummary(model)\n\n# ... and predict data on validation data-set\nprediction <- predict(model, trainSearchStreamContextualValidationSample, type=\"response\")\nprint(logloss(trainSearchStreamContextualValidationSample$IsClick, prediction))\n\n# ----- Predict submission dataset ---------------------------------------------\n\ntestSearchStreamContextual <- fetch(db, \"select TestId, HistCTR from testSearchStream where ObjectType=3\")\nprediction <- predict(model, testSearchStreamContextual, type=\"response\")\n\nsubmissionData <- data.frame(ID=testSearchStreamContextual$TestId, IsClick=prediction)\nsubmissionFile <- paste0(\"glm\", format(Sys.time(), \"%Y-%m-%d-%H:%M:%S\"), \".csv\")\n#write.csv(submissionData, submissionFile, sep=\",\", dec=\".\", col.names=TRUE, row.names=FALSE)\n\n# ----- Clean up ---------------------------------------------------------------\n\ndbDisconnect(db)\n\n"
 }
],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"}}, "nbformat": 4, "nbformat_minor": 0}