reduce-dataset.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Outbrain click prediction data reduction"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our goal is to create a smaller dataset that can be easily processed with pandas for instance, for getting quick insights, getting familliar with the data and rapid feature prototyping."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I downloaded the data files from https://www.kaggle.com/c/outbrain-click-prediction/data?select=page_views.csv.zip,\n",
    "\n",
    "and uploaded to our HDFS. using a pySpark kernel."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.types import IntegerType\n",
    "\n",
    "events_df = spark.read.csv(\"/user/ykarni/kaggle/events.csv\", header=True)\n",
    "events_df = events_df.withColumn('timestamp', events_df['timestamp'].cast(IntegerType()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['display_id', 'uuid', 'document_id', 'timestamp', 'platform', 'geo_location']"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "events_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+--------------+-----------+---------+--------+------------+\n",
      "|display_id|          uuid|document_id|timestamp|platform|geo_location|\n",
      "+----------+--------------+-----------+---------+--------+------------+\n",
      "|         1|cb8c55702adb93|     379743|       61|       3|   US>SC>519|\n",
      "|         2|79a85fa78311b9|    1794259|       81|       2|   US>CA>807|\n",
      "|         3|822932ce3d8757|    1179111|      182|       2|   US>MI>505|\n",
      "|         4|85281d0a49f7ac|    1777797|      234|       2|   US>WV>564|\n",
      "|         5|8d0daef4bf5b56|     252458|      338|       2|       SG>00|\n",
      "+----------+--------------+-----------+---------+--------+------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "events_df.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "reduced_events_df = events_df.filter('timestamp <  100000000')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [],
   "source": [
    "reduced_events_df.write.option(\"header\", True).csv('/user/ykarni/kaggle/reduced_events.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1856133"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reduced_events_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "reduced_events_display_ids_df = reduced_events_df.select('display_id').distinct()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1856133"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reduced_events_display_ids_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "reduced_events_doc_ids_df = reduced_events_df.select('document_id').distinct()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "184824"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reduced_events_doc_ids_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "reduced_events_uuids_df = reduced_events_df.select('uuid').distinct()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1749023"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reduced_events_uuids_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "document_categories_df = spark.read.csv(\"/user/ykarni/kaggle/documents_categories.csv\", header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "reduced_document_categories_df = document_categories_df.join(reduced_events_doc_ids_df, ['document_id'], how=\"inner\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "354347"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reduced_document_categories_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "reduced_document_categories_df.write.option(\"header\", True).csv('/user/ykarni/kaggle/reduced_document_categories.csv', header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['document_id', 'category_id', 'confidence_level']"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "document_categories_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "97"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "document_categories_df.select(\"category_id\").distinct().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "promoted_content_df = spark.read.csv(\"/user/ykarni/kaggle/promoted_content.csv\", header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "reduced_promoted_content_df = promoted_content_df.join(reduced_events_doc_ids_df, ['document_id'], how=\"inner\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "37449"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reduced_promoted_content_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [],
   "source": [
    "reduced_promoted_content_df.write.option(\"header\", True).csv(\"/user/ykarni/kaggle/reduced_promoted_content.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['ad_id', 'document_id', 'campaign_id', 'advertiser_id']"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "promoted_content_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "documents_meta_df = spark.read.csv(\"/user/ykarni/kaggle/documents_meta.csv\", header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['document_id', 'source_id', 'publisher_id', 'publish_time']"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "documents_meta_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "reduced_documents_meta_df = documents_meta_df.join(reduced_events_doc_ids_df, ['document_id'], how=\"inner\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2999334"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "documents_meta_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "184824"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reduced_documents_meta_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "reduced_documents_meta_df.write.option(\"header\", True).csv(\"/user/ykarni/kaggle/reduced_documents_meta.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "clicks_train_df = spark.read.csv(\"/user/ykarni/kaggle/clicks_train.csv\", header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['display_id', 'ad_id', 'clicked']"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clicks_train_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "87141731"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clicks_train_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [],
   "source": [
    "reduced_clicks_train_df = clicks_train_df.join(reduced_events_display_ids_df, ['display_id'], how=\"inner\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8159306"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reduced_clicks_train_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "reduced_clicks_train_df.write.option(\"header\", True).csv(\"/user/ykarni/kaggle/reduced_clicks_train.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "page_views_df = spark.read.csv(\"/user/ykarni/kaggle/page_views.csv\", header=True)\n",
    "page_views_df = page_views_df.withColumn('timestamp', page_views_df['timestamp'].cast(IntegerType()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['uuid',\n",
       " 'document_id',\n",
       " 'timestamp',\n",
       " 'platform',\n",
       " 'geo_location',\n",
       " 'traffic_source']"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "page_views_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------+-----------+---------+--------+------------+--------------+\n",
      "|          uuid|document_id|timestamp|platform|geo_location|traffic_source|\n",
      "+--------------+-----------+---------+--------+------------+--------------+\n",
      "|1fd5f051fba643|        120| 31905835|       1|          RS|             2|\n",
      "|8557aa9004be3b|        120| 32053104|       1|       VN>44|             2|\n",
      "|c351b277a358f0|        120| 54013023|       1|       KR>12|             1|\n",
      "|8205775c5387f9|        120| 44196592|       1|       IN>16|             2|\n",
      "|9cb0ccd8458371|        120| 65817371|       1|   US>CA>807|             2|\n",
      "+--------------+-----------+---------+--------+------------+--------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "page_views_df.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "reduced_page_views_df = page_views_df.join(reduced_events_uuids_df, ['uuid'], how=\"inner\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [],
   "source": [
    "reduced_page_views_df.write.option(\"header\", True).csv(\"/user/ykarni/kaggle/reduced_page_views.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21489125"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reduced_page_views_df.count()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "pySpark (Spark 2.3.1)",
   "language": "python",
   "name": "pyspark"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.15+"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}