user_logs_processing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "\n",
    "spark = SparkSession.builder.appName(\"WSDM User Logs Processing\").getOrCreate() \n",
    "df_validation = spark.read.csv('./data/sample_submission_v2.csv', inferSchema=True, header=True)\n",
    "df_userlogs = spark.read.csv('./data/user_logs_v2.csv', inferSchema=True, header=True)\n",
    "# Limit userlog 'msno' column to only include those 'msno' values present in 'df_validation', so we only have the logs \n",
    "# of relevant users (in this case, the users in the validation set). This will take a while, as will all Spark SQL calls.\n",
    "df_validation_userlogs = df_userlogs.join(df_validation, 'msno', how='inner')\n",
    "# Clear up some memory\n",
    "del df_userlogs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Now, to aggregate over the relevant user log data\n",
    "AGG_COLS = ['num_100', 'num_25', 'num_50', 'num_75', 'num_985', 'num_unq', 'total_secs']\n",
    "AGG_FNS = ['avg', 'max', 'min', 'sum', 'stddev']\n",
    "build_agg_dict = lambda agg_name: dict((cname, agg_name) for cname in AGG_COLS)\n",
    "dfs_agg = []\n",
    "\n",
    "for agg_name in AGG_FNS:\n",
    "    agg_dict = build_agg_dict(agg_name)\n",
    "    df_agg = df_validation_userlogs.groupby('msno').agg(agg_dict)\n",
    "    dfs_agg.append(df_agg)\n",
    "    \n",
    "# Clear up some memory\n",
    "del df_validation_userlogs\n",
    "\n",
    "for df_agg in dfs_agg:\n",
    "    df_validation = df_validation.join(df_agg, 'msno', how='inner')\n",
    "    del df_agg\n",
    "\n",
    "assert len(df_validation.columns) >= 30"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Now processing file name ./data/validation_ulog_agg/part-00000-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00001-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00002-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00003-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00004-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00005-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00006-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00007-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00008-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00009-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00010-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00011-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00012-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00013-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00014-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00015-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00016-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00017-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00018-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00019-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00020-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00021-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00022-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00023-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00024-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00025-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00026-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00027-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00028-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00029-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00030-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00031-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00032-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00033-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00034-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00035-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00036-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00037-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00038-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00039-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00040-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00041-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00042-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00043-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00044-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00045-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00046-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00047-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00048-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00049-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00050-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00051-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00052-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00053-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00054-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00055-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00056-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00057-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00058-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00059-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00060-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00061-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00062-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00063-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00064-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00065-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00066-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00067-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00068-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00069-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00070-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00071-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00072-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00073-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Now processing file name ./data/validation_ulog_agg/part-00074-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00075-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00076-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00077-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00078-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00079-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00080-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00081-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00082-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00083-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00084-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00085-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00086-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00087-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00088-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00089-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00090-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00091-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00092-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00093-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00094-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00095-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00096-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00097-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00098-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00099-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00100-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00101-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00102-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00103-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00104-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00105-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00106-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00107-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00108-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00109-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00110-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00111-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00112-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00113-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00114-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00115-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00116-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00117-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00118-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00119-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00120-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00121-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00122-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00123-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00124-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00125-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00126-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00127-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00128-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00129-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00130-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00131-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00132-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00133-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00134-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00135-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00136-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00137-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00138-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00139-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00140-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00141-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00142-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00143-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00144-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00145-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00146-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00147-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00148-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Now processing file name ./data/validation_ulog_agg/part-00149-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00150-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00151-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00152-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00153-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00154-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00155-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00156-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00157-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00158-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00159-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00160-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00161-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00162-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00163-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00164-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00165-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00166-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00167-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00168-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00169-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00170-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00171-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00172-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00173-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00174-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00175-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00176-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00177-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00178-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00179-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00180-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00181-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00182-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00183-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00184-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00185-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00186-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00187-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00188-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00189-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00190-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00191-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00192-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00193-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00194-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00195-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00196-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00197-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00198-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Now processing file name ./data/validation_ulog_agg/part-00199-d90d4868-b198-41e0-aabf-5267b96e3627-c000.csv...\n",
      "Finished writing to ./data/validation_ulog_agg_v2.csv!\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>avg(num_100)</th>\n",
       "      <th>sum(total_secs)</th>\n",
       "      <th>sum(num_50)</th>\n",
       "      <th>min(num_75)</th>\n",
       "      <th>avg(num_50)</th>\n",
       "      <th>avg(num_985)</th>\n",
       "      <th>min(total_secs)</th>\n",
       "      <th>min(num_985)</th>\n",
       "      <th>sum(num_25)</th>\n",
       "      <th>min(num_unq)</th>\n",
       "      <th>...</th>\n",
       "      <th>sum(num_unq)</th>\n",
       "      <th>max(num_unq)</th>\n",
       "      <th>max(num_50)</th>\n",
       "      <th>stddev(num_25)</th>\n",
       "      <th>stddev(num_unq)</th>\n",
       "      <th>stddev(num_100)</th>\n",
       "      <th>stddev(num_50)</th>\n",
       "      <th>stddev(total_secs)</th>\n",
       "      <th>stddev(num_75)</th>\n",
       "      <th>stddev(num_985)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9.000000</td>\n",
       "      <td>2362.332</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2362.332</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>24</td>\n",
       "      <td>...</td>\n",
       "      <td>24</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12.000000</td>\n",
       "      <td>59013.159</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>1.058824</td>\n",
       "      <td>0.470588</td>\n",
       "      <td>119.440</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>149</td>\n",
       "      <td>28</td>\n",
       "      <td>12</td>\n",
       "      <td>1.007326</td>\n",
       "      <td>7.964997</td>\n",
       "      <td>13.057565</td>\n",
       "      <td>2.882503</td>\n",
       "      <td>3924.399063</td>\n",
       "      <td>0.528594</td>\n",
       "      <td>1.007326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>30.217391</td>\n",
       "      <td>196442.810</td>\n",
       "      <td>58</td>\n",
       "      <td>0</td>\n",
       "      <td>2.521739</td>\n",
       "      <td>0.869565</td>\n",
       "      <td>864.783</td>\n",
       "      <td>0</td>\n",
       "      <td>970</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>1663</td>\n",
       "      <td>161</td>\n",
       "      <td>9</td>\n",
       "      <td>36.833350</td>\n",
       "      <td>41.307533</td>\n",
       "      <td>14.478688</td>\n",
       "      <td>2.212969</td>\n",
       "      <td>4108.320638</td>\n",
       "      <td>1.202764</td>\n",
       "      <td>0.868873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>34.966667</td>\n",
       "      <td>264692.811</td>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>0.833333</td>\n",
       "      <td>1.033333</td>\n",
       "      <td>1111.770</td>\n",
       "      <td>0</td>\n",
       "      <td>345</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>737</td>\n",
       "      <td>63</td>\n",
       "      <td>8</td>\n",
       "      <td>12.808268</td>\n",
       "      <td>16.564781</td>\n",
       "      <td>21.350092</td>\n",
       "      <td>1.683251</td>\n",
       "      <td>5464.339845</td>\n",
       "      <td>0.808717</td>\n",
       "      <td>1.299425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>48.458333</td>\n",
       "      <td>296370.987</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "      <td>1.125000</td>\n",
       "      <td>0.583333</td>\n",
       "      <td>646.093</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>1021</td>\n",
       "      <td>168</td>\n",
       "      <td>14</td>\n",
       "      <td>14.330468</td>\n",
       "      <td>45.053571</td>\n",
       "      <td>51.425741</td>\n",
       "      <td>2.908944</td>\n",
       "      <td>13264.404016</td>\n",
       "      <td>0.884652</td>\n",
       "      <td>0.974308</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 37 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   avg(num_100)  sum(total_secs)  sum(num_50)  min(num_75)  avg(num_50)  \\\n",
       "0      9.000000         2362.332            1            0     1.000000   \n",
       "1     12.000000        59013.159           18            0     1.058824   \n",
       "2     30.217391       196442.810           58            0     2.521739   \n",
       "3     34.966667       264692.811           25            0     0.833333   \n",
       "4     48.458333       296370.987           27            0     1.125000   \n",
       "\n",
       "   avg(num_985)  min(total_secs)  min(num_985)  sum(num_25)  min(num_unq)  \\\n",
       "0      0.000000         2362.332             0           14            24   \n",
       "1      0.470588          119.440             0            8             1   \n",
       "2      0.869565          864.783             0          970             4   \n",
       "3      1.033333         1111.770             0          345             1   \n",
       "4      0.583333          646.093             0          100             3   \n",
       "\n",
       "        ...         sum(num_unq)  max(num_unq)  max(num_50)  stddev(num_25)  \\\n",
       "0       ...                   24            24            1             NaN   \n",
       "1       ...                  149            28           12        1.007326   \n",
       "2       ...                 1663           161            9       36.833350   \n",
       "3       ...                  737            63            8       12.808268   \n",
       "4       ...                 1021           168           14       14.330468   \n",
       "\n",
       "   stddev(num_unq)  stddev(num_100)  stddev(num_50)  stddev(total_secs)  \\\n",
       "0              NaN              NaN             NaN                 NaN   \n",
       "1         7.964997        13.057565        2.882503         3924.399063   \n",
       "2        41.307533        14.478688        2.212969         4108.320638   \n",
       "3        16.564781        21.350092        1.683251         5464.339845   \n",
       "4        45.053571        51.425741        2.908944        13264.404016   \n",
       "\n",
       "   stddev(num_75)  stddev(num_985)  \n",
       "0             NaN              NaN  \n",
       "1        0.528594         1.007326  \n",
       "2        1.202764         0.868873  \n",
       "3        0.808717         1.299425  \n",
       "4        0.884652         0.974308  \n",
       "\n",
       "[5 rows x 37 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now we write the validation dataframe to a directory called 'validation_data'. Since the Spark execution\n",
    "# model performs everything in parts, I've written a method in utils.py to compile all the parts' individual\n",
    "# csv files into one larger csv file. This file will only be ~300MB and will fit easily into memory so we\n",
    "# can do all our regular tricks with pandas.\n",
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "from utils import compile_csv_parts_to_larger_csv\n",
    "\n",
    "VALIDATION_DATA_PATH = './data/validation_ulog_agg'\n",
    "VALIDATION_CSV_PATH = './data/validation_ulog_agg_v2.csv'\n",
    "\n",
    "df_validation.write.csv(VALIDATION_DATA_PATH, header=True)\n",
    "assert os.path.isdir(VALIDATION_DATA_PATH)\n",
    "del df_validation\n",
    "compile_csv_parts_to_larger_csv(csv_parts_path=VALIDATION_DATA_PATH, to_write_path=VALIDATION_CSV_PATH)\n",
    "assert os.path.isfile(VALIDATION_CSV_PATH)\n",
    "\n",
    "# Sanity check\n",
    "df_validation = pd.read_csv(VALIDATION_CSV_PATH)\n",
    "df_validation.head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}