bike-sharing-demand.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "quiet-boutique",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "\n",
    "spark = SparkSession.builder.appName(\"bike-sharing-demand\").getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "premium-eclipse",
   "metadata": {},
   "outputs": [],
   "source": [
    "trainDF = spark.read.csv(\"./data/train.csv\", header=True, inferSchema=True)\n",
    "testDF = spark.read.csv(\"./data/test.csv\", header=True, inferSchema=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "hundred-football",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- datetime: string (nullable = true)\n",
      " |-- season: integer (nullable = true)\n",
      " |-- holiday: integer (nullable = true)\n",
      " |-- workingday: integer (nullable = true)\n",
      " |-- weather: integer (nullable = true)\n",
      " |-- temp: double (nullable = true)\n",
      " |-- atemp: double (nullable = true)\n",
      " |-- humidity: integer (nullable = true)\n",
      " |-- windspeed: double (nullable = true)\n",
      " |-- casual: integer (nullable = true)\n",
      " |-- registered: integer (nullable = true)\n",
      " |-- count: integer (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "trainDF.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "complete-spelling",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10886"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trainDF.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "according-converter",
   "metadata": {},
   "outputs": [],
   "source": [
    "trainDF = trainDF.na.drop()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "adjustable-carrier",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10886"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trainDF.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "metric-marijuana",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+------+-------+----------+-------+-----+------+--------+---------+------+----------+-----+\n",
      "|           datetime|season|holiday|workingday|weather| temp| atemp|humidity|windspeed|casual|registered|count|\n",
      "+-------------------+------+-------+----------+-------+-----+------+--------+---------+------+----------+-----+\n",
      "|2011-01-01 00:00:00|     1|      0|         0|      1| 9.84|14.395|      81|      0.0|     3|        13|   16|\n",
      "|2011-01-01 01:00:00|     1|      0|         0|      1| 9.02|13.635|      80|      0.0|     8|        32|   40|\n",
      "|2011-01-01 02:00:00|     1|      0|         0|      1| 9.02|13.635|      80|      0.0|     5|        27|   32|\n",
      "|2011-01-01 03:00:00|     1|      0|         0|      1| 9.84|14.395|      75|      0.0|     3|        10|   13|\n",
      "|2011-01-01 04:00:00|     1|      0|         0|      1| 9.84|14.395|      75|      0.0|     0|         1|    1|\n",
      "|2011-01-01 05:00:00|     1|      0|         0|      2| 9.84| 12.88|      75|   6.0032|     0|         1|    1|\n",
      "|2011-01-01 06:00:00|     1|      0|         0|      1| 9.02|13.635|      80|      0.0|     2|         0|    2|\n",
      "|2011-01-01 07:00:00|     1|      0|         0|      1|  8.2| 12.88|      86|      0.0|     1|         2|    3|\n",
      "|2011-01-01 08:00:00|     1|      0|         0|      1| 9.84|14.395|      75|      0.0|     1|         7|    8|\n",
      "|2011-01-01 09:00:00|     1|      0|         0|      1|13.12|17.425|      76|      0.0|     8|         6|   14|\n",
      "|2011-01-01 10:00:00|     1|      0|         0|      1|15.58|19.695|      76|  16.9979|    12|        24|   36|\n",
      "|2011-01-01 11:00:00|     1|      0|         0|      1|14.76|16.665|      81|  19.0012|    26|        30|   56|\n",
      "|2011-01-01 12:00:00|     1|      0|         0|      1|17.22| 21.21|      77|  19.0012|    29|        55|   84|\n",
      "|2011-01-01 13:00:00|     1|      0|         0|      2|18.86|22.725|      72|  19.9995|    47|        47|   94|\n",
      "|2011-01-01 14:00:00|     1|      0|         0|      2|18.86|22.725|      72|  19.0012|    35|        71|  106|\n",
      "|2011-01-01 15:00:00|     1|      0|         0|      2|18.04| 21.97|      77|  19.9995|    40|        70|  110|\n",
      "|2011-01-01 16:00:00|     1|      0|         0|      2|17.22| 21.21|      82|  19.9995|    41|        52|   93|\n",
      "|2011-01-01 17:00:00|     1|      0|         0|      2|18.04| 21.97|      82|  19.0012|    15|        52|   67|\n",
      "|2011-01-01 18:00:00|     1|      0|         0|      3|17.22| 21.21|      88|  16.9979|     9|        26|   35|\n",
      "|2011-01-01 19:00:00|     1|      0|         0|      3|17.22| 21.21|      88|  16.9979|     6|        31|   37|\n",
      "+-------------------+------+-------+----------+-------+-----+------+--------+---------+------+----------+-----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "trainDF.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "arctic-campbell",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import when, lit\n",
    "trainDF = trainDF.withColumn('season_1', when(trainDF['season']==1, lit(1)).otherwise(lit(0))) \\\n",
    "                .withColumn('season_2', when(trainDF['season']==2, lit(1)).otherwise(lit(0))) \\\n",
    "                .withColumn('season_3', when(trainDF['season']==3, lit(1)).otherwise(lit(0))) \\\n",
    "                .withColumn('season_4', when(trainDF['season']==4, lit(1)).otherwise(lit(0))).drop(trainDF['season'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "exempt-slide",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['datetime',\n",
       " 'holiday',\n",
       " 'workingday',\n",
       " 'weather',\n",
       " 'temp',\n",
       " 'atemp',\n",
       " 'humidity',\n",
       " 'windspeed',\n",
       " 'casual',\n",
       " 'registered',\n",
       " 'count',\n",
       " 'season_1',\n",
       " 'season_2',\n",
       " 'season_3',\n",
       " 'season_4']"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trainDF.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "immediate-strategy",
   "metadata": {},
   "outputs": [],
   "source": [
    "trainDF = trainDF.withColumn('weather_1', when(trainDF['weather']==1, lit(1)).otherwise(lit(0))) \\\n",
    "                .withColumn('weather_2', when(trainDF['weather']==2, lit(1)).otherwise(lit(0))) \\\n",
    "                .withColumn('weather_3', when(trainDF['weather']==3, lit(1)).otherwise(lit(0))) \\\n",
    "                .withColumn('weather_4', when(trainDF['weather']==4, lit(1)).otherwise(lit(0))).drop(trainDF['weather'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "abandoned-pierce",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['datetime',\n",
       " 'holiday',\n",
       " 'workingday',\n",
       " 'temp',\n",
       " 'atemp',\n",
       " 'humidity',\n",
       " 'windspeed',\n",
       " 'casual',\n",
       " 'registered',\n",
       " 'count',\n",
       " 'season_1',\n",
       " 'season_2',\n",
       " 'season_3',\n",
       " 'season_4',\n",
       " 'weather_1',\n",
       " 'weather_2',\n",
       " 'weather_3',\n",
       " 'weather_4']"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trainDF.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "automatic-percentage",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import split\n",
    "\n",
    "trainDF = trainDF.withColumn('year', split(split(trainDF['datetime'], ' ')[0], '-')[0].cast('int'))\n",
    "trainDF = trainDF.withColumn('month', split(split(trainDF['datetime'], ' ')[0], '-')[1].cast('int'))\n",
    "trainDF = trainDF.withColumn('day', split(split(trainDF['datetime'], ' ')[0], '-')[2].cast('int'))\n",
    "trainDF = trainDF.withColumn('hour', split(split(trainDF['datetime'], ' ')[1], ':')[0].cast('int'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "acquired-journalism",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-----+---+----+\n",
      "|year|month|day|hour|\n",
      "+----+-----+---+----+\n",
      "|2011|    1|  1|   0|\n",
      "|2011|    1|  1|   1|\n",
      "|2011|    1|  1|   2|\n",
      "|2011|    1|  1|   3|\n",
      "|2011|    1|  1|   4|\n",
      "|2011|    1|  1|   5|\n",
      "|2011|    1|  1|   6|\n",
      "|2011|    1|  1|   7|\n",
      "|2011|    1|  1|   8|\n",
      "|2011|    1|  1|   9|\n",
      "|2011|    1|  1|  10|\n",
      "|2011|    1|  1|  11|\n",
      "|2011|    1|  1|  12|\n",
      "|2011|    1|  1|  13|\n",
      "|2011|    1|  1|  14|\n",
      "|2011|    1|  1|  15|\n",
      "|2011|    1|  1|  16|\n",
      "|2011|    1|  1|  17|\n",
      "|2011|    1|  1|  18|\n",
      "|2011|    1|  1|  19|\n",
      "+----+-----+---+----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "trainDF.select('year', 'month', 'day', 'hour').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "liked-wages",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+----------+\n",
      "|month|sum(count)|\n",
      "+-----+----------+\n",
      "|   12|    160160|\n",
      "|    1|     79884|\n",
      "|    6|    220733|\n",
      "|    3|    133501|\n",
      "|    5|    200147|\n",
      "|    9|    212529|\n",
      "|    4|    167402|\n",
      "|    8|    213516|\n",
      "|    7|    214617|\n",
      "|   10|    207434|\n",
      "|   11|    176440|\n",
      "|    2|     99113|\n",
      "+-----+----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "trainDF.groupBy('month').sum('count').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "accomplished-louisville",
   "metadata": {},
   "outputs": [],
   "source": [
    "## gathering all the features into one array using VectorAssembler\n",
    "from pyspark.ml.feature import VectorAssembler\n",
    "\n",
    "assembler = VectorAssembler(inputCols=['holiday', \\\n",
    "                                         'workingday', \\\n",
    "                                         'temp', \\\n",
    "                                         'atemp', \\\n",
    "                                         'humidity', \\\n",
    "                                         'windspeed', \\\n",
    "                                         'casual', \\\n",
    "                                         'registered', \\\n",
    "                                         'season_1', \\\n",
    "                                         'season_2', \\\n",
    "                                         'season_3', \\\n",
    "                                         'season_4', \\\n",
    "                                         'weather_1', \\\n",
    "                                         'weather_2', \\\n",
    "                                         'weather_3', \\\n",
    "                                         'weather_4', \\\n",
    "                                         'year', \\\n",
    "                                         'month', \\\n",
    "                                         'day', \\\n",
    "                                         'hour'], outputCol=\"features\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "annoying-correspondence",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-------+----------+-----+------+--------+---------+------+----------+-----+--------+--------+--------+--------+---------+---------+---------+---------+----+-----+---+----+--------------------+\n",
      "|           datetime|holiday|workingday| temp| atemp|humidity|windspeed|casual|registered|count|season_1|season_2|season_3|season_4|weather_1|weather_2|weather_3|weather_4|year|month|day|hour|            features|\n",
      "+-------------------+-------+----------+-----+------+--------+---------+------+----------+-----+--------+--------+--------+--------+---------+---------+---------+---------+----+-----+---+----+--------------------+\n",
      "|2011-01-01 00:00:00|      0|         0| 9.84|14.395|      81|      0.0|     3|        13|   16|       1|       0|       0|       0|        1|        0|        0|        0|2011|    1|  1|   0|(20,[2,3,4,6,7,8,...|\n",
      "|2011-01-01 01:00:00|      0|         0| 9.02|13.635|      80|      0.0|     8|        32|   40|       1|       0|       0|       0|        1|        0|        0|        0|2011|    1|  1|   1|(20,[2,3,4,6,7,8,...|\n",
      "|2011-01-01 02:00:00|      0|         0| 9.02|13.635|      80|      0.0|     5|        27|   32|       1|       0|       0|       0|        1|        0|        0|        0|2011|    1|  1|   2|(20,[2,3,4,6,7,8,...|\n",
      "|2011-01-01 03:00:00|      0|         0| 9.84|14.395|      75|      0.0|     3|        10|   13|       1|       0|       0|       0|        1|        0|        0|        0|2011|    1|  1|   3|(20,[2,3,4,6,7,8,...|\n",
      "|2011-01-01 04:00:00|      0|         0| 9.84|14.395|      75|      0.0|     0|         1|    1|       1|       0|       0|       0|        1|        0|        0|        0|2011|    1|  1|   4|(20,[2,3,4,7,8,12...|\n",
      "|2011-01-01 05:00:00|      0|         0| 9.84| 12.88|      75|   6.0032|     0|         1|    1|       1|       0|       0|       0|        0|        1|        0|        0|2011|    1|  1|   5|(20,[2,3,4,5,7,8,...|\n",
      "|2011-01-01 06:00:00|      0|         0| 9.02|13.635|      80|      0.0|     2|         0|    2|       1|       0|       0|       0|        1|        0|        0|        0|2011|    1|  1|   6|(20,[2,3,4,6,8,12...|\n",
      "|2011-01-01 07:00:00|      0|         0|  8.2| 12.88|      86|      0.0|     1|         2|    3|       1|       0|       0|       0|        1|        0|        0|        0|2011|    1|  1|   7|(20,[2,3,4,6,7,8,...|\n",
      "|2011-01-01 08:00:00|      0|         0| 9.84|14.395|      75|      0.0|     1|         7|    8|       1|       0|       0|       0|        1|        0|        0|        0|2011|    1|  1|   8|(20,[2,3,4,6,7,8,...|\n",
      "|2011-01-01 09:00:00|      0|         0|13.12|17.425|      76|      0.0|     8|         6|   14|       1|       0|       0|       0|        1|        0|        0|        0|2011|    1|  1|   9|(20,[2,3,4,6,7,8,...|\n",
      "|2011-01-01 10:00:00|      0|         0|15.58|19.695|      76|  16.9979|    12|        24|   36|       1|       0|       0|       0|        1|        0|        0|        0|2011|    1|  1|  10|(20,[2,3,4,5,6,7,...|\n",
      "|2011-01-01 11:00:00|      0|         0|14.76|16.665|      81|  19.0012|    26|        30|   56|       1|       0|       0|       0|        1|        0|        0|        0|2011|    1|  1|  11|(20,[2,3,4,5,6,7,...|\n",
      "|2011-01-01 12:00:00|      0|         0|17.22| 21.21|      77|  19.0012|    29|        55|   84|       1|       0|       0|       0|        1|        0|        0|        0|2011|    1|  1|  12|(20,[2,3,4,5,6,7,...|\n",
      "|2011-01-01 13:00:00|      0|         0|18.86|22.725|      72|  19.9995|    47|        47|   94|       1|       0|       0|       0|        0|        1|        0|        0|2011|    1|  1|  13|(20,[2,3,4,5,6,7,...|\n",
      "|2011-01-01 14:00:00|      0|         0|18.86|22.725|      72|  19.0012|    35|        71|  106|       1|       0|       0|       0|        0|        1|        0|        0|2011|    1|  1|  14|(20,[2,3,4,5,6,7,...|\n",
      "|2011-01-01 15:00:00|      0|         0|18.04| 21.97|      77|  19.9995|    40|        70|  110|       1|       0|       0|       0|        0|        1|        0|        0|2011|    1|  1|  15|(20,[2,3,4,5,6,7,...|\n",
      "|2011-01-01 16:00:00|      0|         0|17.22| 21.21|      82|  19.9995|    41|        52|   93|       1|       0|       0|       0|        0|        1|        0|        0|2011|    1|  1|  16|(20,[2,3,4,5,6,7,...|\n",
      "|2011-01-01 17:00:00|      0|         0|18.04| 21.97|      82|  19.0012|    15|        52|   67|       1|       0|       0|       0|        0|        1|        0|        0|2011|    1|  1|  17|(20,[2,3,4,5,6,7,...|\n",
      "|2011-01-01 18:00:00|      0|         0|17.22| 21.21|      88|  16.9979|     9|        26|   35|       1|       0|       0|       0|        0|        0|        1|        0|2011|    1|  1|  18|(20,[2,3,4,5,6,7,...|\n",
      "|2011-01-01 19:00:00|      0|         0|17.22| 21.21|      88|  16.9979|     6|        31|   37|       1|       0|       0|       0|        0|        0|        1|        0|2011|    1|  1|  19|(20,[2,3,4,5,6,7,...|\n",
      "+-------------------+-------+----------+-----+------+--------+---------+------+----------+-----+--------+--------+--------+--------+---------+---------+---------+---------+----+-----+---+----+--------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "output = assembler.transform(trainDF)\n",
    "output.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "centered-diesel",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df = output.select('datetime', 'features', 'count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "subject-fishing",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+--------------------+-----+\n",
      "|           datetime|            features|count|\n",
      "+-------------------+--------------------+-----+\n",
      "|2011-01-01 00:00:00|(20,[2,3,4,6,7,8,...|   16|\n",
      "|2011-01-01 01:00:00|(20,[2,3,4,6,7,8,...|   40|\n",
      "|2011-01-01 02:00:00|(20,[2,3,4,6,7,8,...|   32|\n",
      "|2011-01-01 03:00:00|(20,[2,3,4,6,7,8,...|   13|\n",
      "|2011-01-01 04:00:00|(20,[2,3,4,7,8,12...|    1|\n",
      "|2011-01-01 05:00:00|(20,[2,3,4,5,7,8,...|    1|\n",
      "|2011-01-01 06:00:00|(20,[2,3,4,6,8,12...|    2|\n",
      "|2011-01-01 07:00:00|(20,[2,3,4,6,7,8,...|    3|\n",
      "|2011-01-01 08:00:00|(20,[2,3,4,6,7,8,...|    8|\n",
      "|2011-01-01 09:00:00|(20,[2,3,4,6,7,8,...|   14|\n",
      "|2011-01-01 10:00:00|(20,[2,3,4,5,6,7,...|   36|\n",
      "|2011-01-01 11:00:00|(20,[2,3,4,5,6,7,...|   56|\n",
      "|2011-01-01 12:00:00|(20,[2,3,4,5,6,7,...|   84|\n",
      "|2011-01-01 13:00:00|(20,[2,3,4,5,6,7,...|   94|\n",
      "|2011-01-01 14:00:00|(20,[2,3,4,5,6,7,...|  106|\n",
      "|2011-01-01 15:00:00|(20,[2,3,4,5,6,7,...|  110|\n",
      "|2011-01-01 16:00:00|(20,[2,3,4,5,6,7,...|   93|\n",
      "|2011-01-01 17:00:00|(20,[2,3,4,5,6,7,...|   67|\n",
      "|2011-01-01 18:00:00|(20,[2,3,4,5,6,7,...|   35|\n",
      "|2011-01-01 19:00:00|(20,[2,3,4,5,6,7,...|   37|\n",
      "+-------------------+--------------------+-----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "final_df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "lovely-purpose",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.ml.regression import LinearRegression\n",
    "train_data, test_data = final_df.randomSplit([0.80, 0.20])\n",
    "regressor = LinearRegression(featuresCol='features', labelCol='count')\n",
    "regressor = regressor.fit(train_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "figured-arizona",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DenseVector([0.0, -0.0, -0.0, 0.0, -0.0, -0.0, 1.0, 1.0, 0.0002, 0.0001, -0.0001, -0.0002, 0.0, -0.0, 0.0, 0.0, 0.0, 0.0, -0.0, 0.0])"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "regressor.coefficients"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "lined-provincial",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-0.04313314507693853"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "regressor.intercept"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "prescription-suicide",
   "metadata": {},
   "outputs": [],
   "source": [
    "pred_results = regressor.evaluate(test_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "subjective-closing",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+--------------------+-----+------------------+\n",
      "|           datetime|            features|count|        prediction|\n",
      "+-------------------+--------------------+-----+------------------+\n",
      "|2011-01-01 09:00:00|(20,[2,3,4,6,7,8,...|   14|13.999978514932085|\n",
      "|2011-01-01 10:00:00|(20,[2,3,4,5,6,7,...|   36| 35.99997175991032|\n",
      "|2011-01-01 15:00:00|(20,[2,3,4,5,6,7,...|  110|109.99995345296183|\n",
      "|2011-01-01 17:00:00|(20,[2,3,4,5,6,7,...|   67| 66.99994766371339|\n",
      "|2011-01-01 22:00:00|(20,[2,3,4,5,6,7,...|   28|27.999941070989152|\n",
      "|2011-01-02 11:00:00|(20,[2,3,4,5,6,7,...|   70| 69.99995487572122|\n",
      "|2011-01-02 17:00:00|(20,[2,3,4,5,6,7,...|   65|  64.9999914338179|\n",
      "|2011-01-02 21:00:00|(20,[2,3,4,5,6,7,...|   31| 30.99999966114603|\n",
      "|2011-01-02 23:00:00|(20,[2,3,4,5,7,8,...|    8| 8.000004244636637|\n",
      "|2011-01-03 06:00:00|(20,[1,2,3,4,5,7,...|   30|29.999978281770453|\n",
      "|2011-01-03 08:00:00|[0.0,1.0,5.74,6.0...|  154|153.99998685119584|\n",
      "|2011-01-03 09:00:00|[0.0,1.0,6.56,6.8...|   88| 87.99998795697006|\n",
      "|2011-01-03 10:00:00|[0.0,1.0,7.38,8.3...|   44|  43.9999903454323|\n",
      "|2011-01-03 22:00:00|(20,[1,2,3,4,5,7,...|   20|19.999977487268207|\n",
      "|2011-01-04 01:00:00|(20,[1,2,3,4,5,7,...|    2|1.9999778223908962|\n",
      "|2011-01-04 05:00:00|(20,[1,2,3,4,5,7,...|    4|3.9999736287562837|\n",
      "|2011-01-04 07:00:00|[0.0,1.0,4.92,7.5...|   94| 93.99997195450267|\n",
      "|2011-01-04 08:00:00|[0.0,1.0,5.74,7.5...|  179|178.99997674959792|\n",
      "|2011-01-04 09:00:00|[0.0,1.0,6.56,7.5...|  100| 99.99997524996259|\n",
      "|2011-01-04 17:00:00|[0.0,1.0,11.48,13...|  212|211.99999326740388|\n",
      "+-------------------+--------------------+-----+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "pred_results.predictions.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "treated-newcastle",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4.0823111889379854e-05"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Root Mean Square Error and Mean Absolute Error\n",
    "pred_results.meanAbsoluteError"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "pretty-yugoslavia",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2.551507048289733e-09"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pred_results.meanSquaredError"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "muslim-spank",
   "metadata": {},
   "outputs": [],
   "source": [
    "modelPath = \"./model/bike-sharing-demand.model\"\n",
    "regressor.write().overwrite().save(modelPath)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "western-purchase",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+--------------------+-----+------------------+\n",
      "|           datetime|            features|count|        prediction|\n",
      "+-------------------+--------------------+-----+------------------+\n",
      "|2011-01-01 09:00:00|(20,[2,3,4,6,7,8,...|   14|13.999978514932085|\n",
      "|2011-01-01 10:00:00|(20,[2,3,4,5,6,7,...|   36| 35.99997175991032|\n",
      "|2011-01-01 15:00:00|(20,[2,3,4,5,6,7,...|  110|109.99995345296183|\n",
      "|2011-01-01 17:00:00|(20,[2,3,4,5,6,7,...|   67| 66.99994766371339|\n",
      "|2011-01-01 22:00:00|(20,[2,3,4,5,6,7,...|   28|27.999941070989152|\n",
      "|2011-01-02 11:00:00|(20,[2,3,4,5,6,7,...|   70| 69.99995487572122|\n",
      "|2011-01-02 17:00:00|(20,[2,3,4,5,6,7,...|   65|  64.9999914338179|\n",
      "|2011-01-02 21:00:00|(20,[2,3,4,5,6,7,...|   31| 30.99999966114603|\n",
      "|2011-01-02 23:00:00|(20,[2,3,4,5,7,8,...|    8| 8.000004244636637|\n",
      "|2011-01-03 06:00:00|(20,[1,2,3,4,5,7,...|   30|29.999978281770453|\n",
      "|2011-01-03 08:00:00|[0.0,1.0,5.74,6.0...|  154|153.99998685119584|\n",
      "|2011-01-03 09:00:00|[0.0,1.0,6.56,6.8...|   88| 87.99998795697006|\n",
      "|2011-01-03 10:00:00|[0.0,1.0,7.38,8.3...|   44|  43.9999903454323|\n",
      "|2011-01-03 22:00:00|(20,[1,2,3,4,5,7,...|   20|19.999977487268207|\n",
      "|2011-01-04 01:00:00|(20,[1,2,3,4,5,7,...|    2|1.9999778223908962|\n",
      "|2011-01-04 05:00:00|(20,[1,2,3,4,5,7,...|    4|3.9999736287562837|\n",
      "|2011-01-04 07:00:00|[0.0,1.0,4.92,7.5...|   94| 93.99997195450267|\n",
      "|2011-01-04 08:00:00|[0.0,1.0,5.74,7.5...|  179|178.99997674959792|\n",
      "|2011-01-04 09:00:00|[0.0,1.0,6.56,7.5...|  100| 99.99997524996259|\n",
      "|2011-01-04 17:00:00|[0.0,1.0,11.48,13...|  212|211.99999326740388|\n",
      "+-------------------+--------------------+-----+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "pred_results.predictions.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "conditional-secretariat",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_to_db = pred_results.predictions.select('datetime', 'prediction')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "great-smith",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+------------------+\n",
      "|           datetime|        prediction|\n",
      "+-------------------+------------------+\n",
      "|2011-01-01 09:00:00|13.999978514932085|\n",
      "|2011-01-01 10:00:00| 35.99997175991032|\n",
      "|2011-01-01 15:00:00|109.99995345296183|\n",
      "|2011-01-01 17:00:00| 66.99994766371339|\n",
      "|2011-01-01 22:00:00|27.999941070989152|\n",
      "|2011-01-02 11:00:00| 69.99995487572122|\n",
      "|2011-01-02 17:00:00|  64.9999914338179|\n",
      "|2011-01-02 21:00:00| 30.99999966114603|\n",
      "|2011-01-02 23:00:00| 8.000004244636637|\n",
      "|2011-01-03 06:00:00|29.999978281770453|\n",
      "|2011-01-03 08:00:00|153.99998685119584|\n",
      "|2011-01-03 09:00:00| 87.99998795697006|\n",
      "|2011-01-03 10:00:00|  43.9999903454323|\n",
      "|2011-01-03 22:00:00|19.999977487268207|\n",
      "|2011-01-04 01:00:00|1.9999778223908962|\n",
      "|2011-01-04 05:00:00|3.9999736287562837|\n",
      "|2011-01-04 07:00:00| 93.99997195450267|\n",
      "|2011-01-04 08:00:00|178.99997674959792|\n",
      "|2011-01-04 09:00:00| 99.99997524996259|\n",
      "|2011-01-04 17:00:00|211.99999326740388|\n",
      "+-------------------+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_to_db.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "religious-obligation",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- datetime: string (nullable = true)\n",
      " |-- prediction: double (nullable = false)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_to_db.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "breathing-aruba",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import to_timestamp\n",
    "# df.select(to_timestamp(df.t, 'yyyy-MM-dd HH:mm:ss').alias('dt'))\n",
    "df_to_db = df_to_db.withColumn('datetime', to_timestamp(df_to_db['datetime'], 'yyyy-MM-dd HH:mm:ss'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "overhead-senior",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- datetime: timestamp (nullable = true)\n",
      " |-- prediction: double (nullable = false)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_to_db.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "chubby-appreciation",
   "metadata": {},
   "outputs": [],
   "source": [
    "mode = \"overwrite\"\n",
    "url = \"jdbc:oracle:thin:@localhost:1521:orcl\"\n",
    "properties = {\n",
    "    \"user\": \"hr\",\n",
    "    \"password\": \"hr\"\n",
    " }\n",
    "#CREATE TABLE bike_sharing_preditions( prediction VARCHAR(50), datetime DOUBLE);\n",
    "df_to_db.write.jdbc(url=url, table=\"bike_sharing_predictions\", mode=mode, properties=properties)\n",
    "# Select (prediction, true label) and compute test error\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "experimental-sherman",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}