{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "### Input Redshift Cluster Endpoint and User\n", "Please input your redshift cluster endpoint and existing user on that cluster." ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "REDSHIFT_ENDPOINT = 'redshift-cluster.xxxxxxxxxx.us-east-1.redshift.amazonaws.com:5439/dev'\n", "REDSHIFT_USER=\"awsuser\"" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Setup Run SQL function using Redshift Data API to get SQL query output directly into pandas dataframe\n", "In this step, we are creating function run_sql, which we will use to get SQL query output directly into pandas dataframe. We will also use this function to run DDL statements" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "\n", "import boto3\n", "import time\n", "import pandas as pd\n", "import numpy as np\n", "import matplotlib.pyplot as plt\n", "\n", "session = boto3.session.Session()\n", "region = session.region_name\n", "\n", "\n", "def run_sql(sql_text):\n", " client = boto3.client(\"redshift-data\")\n", " res = client.execute_statement(Database=REDSHIFT_ENDPOINT.split('/')[1], DbUser=REDSHIFT_USER, Sql=sql_text,\n", " ClusterIdentifier=REDSHIFT_ENDPOINT.split('.')[0])\n", " query_id = res[\"Id\"]\n", " done = False\n", " while not done:\n", " time.sleep(1)\n", " status_description = client.describe_statement(Id=query_id)\n", " status = status_description[\"Status\"]\n", " if status == \"FAILED\":\n", " raise Exception('SQL query failed:' + query_id + \": \" + status_description[\"Error\"])\n", " elif status == \"FINISHED\":\n", " if status_description['ResultRows']>0:\n", " results = client.get_statement_result(Id=query_id)\n", " column_labels = []\n", " for i in range(len(results[\"ColumnMetadata\"])): column_labels.append(results[\"ColumnMetadata\"][i]['label'])\n", " records = []\n", " for record in results.get('Records'):\n", " records.append([list(rec.values())[0] for rec in record])\n", " df = pd.DataFrame(np.array(records), columns=column_labels)\n", " return df\n", " else:\n", " return query_id\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Data Preparation Script\n", "Data preparation script to be run on Redshift" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "setup_script = \"\"\"\n", "DROP TABLE IF EXISTS ridership CASCADE;\n", "DROP TABLE IF EXISTS weather CASCADE;\n", "DROP TABLE IF EXISTS holiday CASCADE;\n", "DROP TABLE IF EXISTS trip_data CASCADE;\n", "\n", "CREATE TABLE IF NOT EXISTS ridership\n", "( trip_id INT\n", ", trip_duration_seconds INT\n", ", trip_start_time timestamp\n", ", trip_stop_time timestamp\n", ", from_station_name VARCHAR(50)\n", ", to_station_name VARCHAR(50)\n", ", from_station_id SMALLINT\n", ", to_station_id SMALLINT\n", ", user_type VARCHAR(20));\n", "\n", "CREATE TABLE IF NOT EXISTS weather\n", "( longitude_x DECIMAL(5,2)\n", ", latitude_y DECIMAL(5,2)\n", ", station_name VARCHAR(20)\n", ", climate_id BIGINT\n", ", datetime_utc TIMESTAMP\n", ", weather_year SMALLINT\n", ", weather_month SMALLINT\n", ", weather_day SMALLINT\n", ", time_utc VARCHAR(5)\n", ", temp_c DECIMAL(5,2)\n", ", temp_flag VARCHAR(1)\n", ", dew_point_temp_c DECIMAL(5,2)\n", ", dew_point_temp_flag VARCHAR(1)\n", ", rel_hum SMALLINT\n", ", rel_hum_flag VARCHAR(1)\n", ", precip_amount_mm DECIMAL(5,2)\n", ", precip_amount_flag VARCHAR(1)\n", ", wind_dir_10s_deg VARCHAR(10)\n", ", wind_dir_flag VARCHAR(1)\n", ", wind_spd_kmh VARCHAR(10)\n", ", wind_spd_flag VARCHAR(1)\n", ", visibility_km VARCHAR(10)\n", ", visibility_flag VARCHAR(1)\n", ", stn_press_kpa DECIMAL(5,2)\n", ", stn_press_flag VARCHAR(1)\n", ", hmdx SMALLINT\n", ", hmdx_flag VARCHAR(1)\n", ", wind_chill VARCHAR(10)\n", ", wind_chill_flag VARCHAR(1)\n", ", weather VARCHAR(10));\n", "\n", "CREATE TABLE IF NOT EXISTS holiday\n", "( holiday_date DATE\n", ", description VARCHAR(100));\n", "\n", "\n", "COPY ridership FROM \n", "'s3://redshift-ml-bikesharing-data/bike-sharing-data/ridership/'\n", "IAM_ROLE 'arn:aws:iam:::role/RedshiftML'\n", "FORMAT csv IGNOREHEADER 1 DATEFORMAT 'auto' TIMEFORMAT 'auto' REGION 'us-west-2' gzip;\n", "\n", "COPY weather FROM\n", "'s3://redshift-ml-bikesharing-data/bike-sharing-data/weather/'\n", "IAM_ROLE 'arn:aws:iam:::role/RedshiftML'\n", "FORMAT csv IGNOREHEADER 1 DATEFORMAT 'auto' TIMEFORMAT 'auto' REGION 'us-west-2' gzip;\n", "\n", "COPY holiday FROM\n", "'s3://redshift-ml-bikesharing-data/bike-sharing-data/holiday/'\n", "IAM_ROLE 'arn:aws:iam:::role/RedshiftML'\n", "FORMAT csv IGNOREHEADER 1 DATEFORMAT 'auto' TIMEFORMAT 'auto' REGION 'us-west-2' gzip;\n", "\n", "CREATE OR REPLACE VIEW ridership_view AS\n", "SELECT\n", " trip_time\n", " , trip_count\n", " , TO_CHAR(trip_time,'hh24') ::INT trip_hour\n", " , TO_CHAR(trip_time, 'dd') :: INT trip_day\n", " , TO_CHAR(trip_time, 'mm') :: INT trip_month\n", " , TO_CHAR(trip_time, 'yy') :: INT trip_year\n", " , TO_CHAR(trip_time, 'q') :: INT trip_quarter\n", " , TO_CHAR(trip_time, 'w') :: INT trip_month_week\n", " , TO_CHAR(trip_time, 'd') :: INT trip_week_day\n", "FROM \n", " (SELECT \n", " CASE\n", " WHEN TRUNC(r.trip_start_time) < '2017-07-01'::DATE\n", " THEN CONVERT_TIMEZONE('US/Eastern', DATE_TRUNC('hour',r.trip_start_time))\n", " ELSE DATE_TRUNC('hour',r.trip_start_time)\n", " END trip_time\n", " , COUNT(1) trip_count\n", " FROM \n", " ridership r\n", " WHERE r.trip_duration_seconds BETWEEN 60 AND 60 * 60 * 24\n", " GROUP BY\n", " 1);\n", "\n", "CREATE OR REPLACE VIEW weather_view AS\n", "SELECT \n", " CONVERT_TIMEZONE('US/Eastern', \n", " DATE_TRUNC('hour',datetime_utc)) daytime\n", " , ROUND(AVG(temp_c)) temp_c\n", " , ROUND(AVG(precip_amount_mm)) precip_amount_mm\n", "FROM weather\n", "GROUP BY 1;\n", "\n", "DROP TABLE IF EXISTS trip_data;\n", "CREATE TABLE trip_data AS \n", "SELECT \n", " r.trip_time\n", " ,r.trip_count\n", " ,r.trip_hour\n", " ,r.trip_day\n", " ,r.trip_month\n", " ,r.trip_year\n", " ,r.trip_quarter\n", " ,r.trip_month_week\n", " ,r.trip_week_day\n", " ,w.temp_c\n", " ,w.precip_amount_mm\n", " ,CASE\n", " WHEN h.holiday_date IS NOT NULL\n", " THEN 1\n", " WHEN TO_CHAR(r.trip_time,'D')::INT IN (1,7)\n", " THEN 1\n", " ELSE 0\n", " END is_holiday\n", " , ROW_NUMBER() OVER (ORDER BY RANDOM()) serial_number\n", "FROM \n", " ridership_view r\n", "JOIN weather_view w\n", " ON ( r.trip_time = w.daytime )\n", "LEFT OUTER JOIN holiday h\n", " ON ( TRUNC(r.trip_time) = h.holiday_date );\n", "\n", "\"\"\"" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Run data preparation script in Redshift" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "for sql_text in setup_script.split(\";\"):\n", " run_sql(sql_text);" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Read SQL output with Pandas Dataframe" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
trip_counttrip_time
02322017-11-24 07:00:00.0
1172017-07-12 04:00:00.0
2742017-02-16 13:00:00.0
32292017-04-27 18:00:00.0
4612018-01-15 12:00:00.0
51402017-12-05 11:00:00.0
62252018-04-02 09:00:00.0
7202017-12-29 10:00:00.0
82872018-05-10 14:00:00.0
93342017-08-31 12:00:00.0
\n", "
" ], "text/plain": [ " trip_count trip_time\n", "0 232 2017-11-24 07:00:00.0\n", "1 17 2017-07-12 04:00:00.0\n", "2 74 2017-02-16 13:00:00.0\n", "3 229 2017-04-27 18:00:00.0\n", "4 61 2018-01-15 12:00:00.0\n", "5 140 2017-12-05 11:00:00.0\n", "6 225 2018-04-02 09:00:00.0\n", "7 20 2017-12-29 10:00:00.0\n", "8 287 2018-05-10 14:00:00.0\n", "9 334 2017-08-31 12:00:00.0" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df = run_sql(\"select trip_count, trip_time from trip_data\");\n", "df.head(10)" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
trip_counttrip_time
count1730617306
unique106617306
top22017-11-28 20:00:00.0
freq2391
\n", "
" ], "text/plain": [ " trip_count trip_time\n", "count 17306 17306\n", "unique 1066 17306\n", "top 2 2017-11-28 20:00:00.0\n", "freq 239 1" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.describe()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Visualize data using matplotlib" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Text(0.5, 1.0, 'trip count histogram')" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" }, { "data": { "image/png": "iVBORw0KGgoAAAANSUhEUgAAAX0AAAEWCAYAAACKSkfIAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuNCwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8QVMy6AAAACXBIWXMAAAsTAAALEwEAmpwYAAAWvklEQVR4nO3df7RdZX3n8feHoEBVFIZAIYkNtWkVaMUSU1qdKQ5WMtrVMDOLNo4KddGmUsaxa43jhPnR+itLWmd1HNpCh+UPAv7AjNUSdXBkpaKtoiEoGn4uIiDEIEmlKlAFE7/zx3lSD5f749zk5t4kz/u11ll7n+9+nr2fc8TP3Xn2OfukqpAk9eGQuR6AJGn2GPqS1BFDX5I6YuhLUkcMfUnqiKEvSR0x9DVrkjw7ySNJ5s31WGZKkiuSvH2S7Y8k+enZHJM0GUNfMyLJvUleOlmbqrqvqp5eVbtma1x7I8kZSbbuzT7a6717Xx9HGpWhr1mR5NC5HkOvDqZ/WWnvGfraa0muAp4NfLxNZ7wpyeIkleT8JPcBfzNUO7T1uz7JO5JsTPLdJNckOXqS46xIcnOS7yX5epLlrX5CkvVJHkqyJcnvDvV5wvTL2LPq9i+UNyb5WhvDh5McnuRpwLXACe01PZLkhAmGdlSSTyZ5OMmXkjxnaP+V5Gfa+suT3NbafbMdd9zjJDksybuSbGuPdyU5bGi/b0ryQNv2O2OOc0WSy5L83ySPAi9J8ookX2nv3f1J3jy0r93/u7y2bfuHJK9L8sL2vnwnyZ9P/V+CDghV5cPHXj+Ae4GXDj1fDBRwJfA04Iih2qGtzfXAN4FTWpu/At4/wf6XAd8Ffo3BycoC4Llt22eBS4HDgVOBHcCZbdsVwNuH9nMGsHXMuDcCJwBHA7cDrxuv7QTjugJ4qI3vUOADwNVD2wv4mbb+APDP2/pRwC9OdBzgrcAXgWOB+cAXgLe1bcuBbwEnAz8BXDXmOFe09+pF7b06vB3j59vzXwAeBM4e87/VX7a2LwN+APx1O/4CYDvwq3P935mPvX94pq997c1V9WhVfX+C7VdV1S1V9Sjw34HfnGA64nzgvVV1XVX9qKq+WVV3JFkEvBj4z1X1g6q6GXg38JppjPGSqtpWVQ8BH2fwh2M6PlpVG6tqJ4PQn6j/D4GTkhxZVf9QVV+eZJ+vAt5aVduragfwFn78mn4TeF9V3VpV/9i2jXVNVX2+vVc/qKrrq2pze/414EPAr47p87bW9tPAo8CH2vG/Cfwt8IKp3wrt7wx97Wv3T2P7N4CnAMeM024R8PVx6icAD1XVw2P2s2AaY/zW0Po/Ak+fRt/p9P+3wMuBbyT5bJJfnmSfJzB4Hbt9o9V2bxt+38Z7j59QS/JLST6TZEeS7wKv48nv84ND698f5/l03xfthwx9zZSJbtc61W1cFw2tP5vB2fDfj9PufuA549S3AUcnecaY/XyzrT/KYApkt5+cYjzDZvQWtFV1Y1WtYDBl8tfAukmOsw34qaHnz241GEwTLRzaNvwe/tPhxjz/ILAeWFRVz2QwlZPpjF8HB0NfM+VBYE8+j/7qJCcl+QkG89gfqfE/0vke4LVJzkxySJIFSZ5bVfczmO9+R7sA+wsMpoI+0PrdDLw8ydFJfhL4g2m+pn+W5Jl78LqeIMlTk7wqyTOr6ofA94Ddr3O843wI+G9J5ic5BvhD4P1t2zoG78Xz2vv2hyMM4RkM/kX0gyTLgH+3t69JByZDXzPlHQxC6jtJ3jiNflcxuPD4LQYXEf/DeI2qaiPwWuB/MrhI+Vl+fCb8SgYXI7cBHwP+qKquG9r/VxlcsP008OFRB1ZVdzAI37vb65ro0zujeg1wb5LvMZheefUkx3k7sAn4GrAZ+HKrUVXXApcAnwG2ADe0/T82ybF/H3hrkocZ/JFYN0lbHcRS5Y+oaG4kuZ7Bp3XePddjOZAleR5wC3BYu5gsTcgzfekAlORftymjo4A/Bj5u4GsUhr50YPo9Bt9H+DqDawMXzO1wdKBwekeSOuKZviR1ZL+/CdYxxxxTixcvnuthSNIB5aabbvr7qpo/tr7fh/7ixYvZtGnTXA9Dkg4oSb4xXt3pHUnqiKEvSR0ZKfSTPCvJR5LckeT2JL/cvtZ+XZK72vKoofYXtfua35nkrKH6aUk2t22XJPHeH5I0i0Y90/9fwKeq6rnA8xncc3w1sKGqlgAb2nOSnASsZHCv7+XApUO3yr0MWAUsaY/lM/Q6JEkjmDL0kxwJ/AsGN7yiqh6vqu8AK4C1rdla4Oy2voLBj0g8VlX3MLg3yLIkxwNHVtUNNfhywJVDfSRJs2CUM/2fZvDNv/e1n1t7d/uJt+Oq6gGAtjy2tV/AE+/lvbXVFrT1sfUnSbIqyaYkm3bs2DGtFyRJmtgooX8o8IvAZVX1Agb3J189Sfvx5ulrkvqTi1WXV9XSqlo6f/6TPmYqSdpDo4T+Vga/3/ml9vwjDP4IPNimbGjL7UPth3/UYSGDW95u5Yk//LC7LkmaJVOGflV9C7g/yc+10pnAbQx+hee8VjsPuKatrwdWJjksyYkMLthubFNADyc5vX1q59yhPpKkWTDqN3JfD3wgyVOBuxn8mMUhwLok5wP3AecAVNWtSdYx+MOwE7hw6JeQLmDwgxlHANe2xz6zePUn9+XuJ3Tvxa+Yk+NK0lRGCv2quhlYOs6mMydovwZYM059E3DKNMYnSZpBfiNXkjpi6EtSRwx9SeqIoS9JHTH0Jakjhr4kdcTQl6SOGPqS1BFDX5I6YuhLUkcMfUnqiKEvSR0x9CWpI4a+JHXE0Jekjhj6ktQRQ1+SOmLoS1JHDH1J6oihL0kdMfQlqSOGviR1xNCXpI4Y+pLUEUNfkjpi6EtSR0YK/ST3Jtmc5OYkm1rt6CTXJbmrLY8aan9Rki1J7kxy1lD9tLafLUkuSZKZf0mSpIlM50z/JVV1alUtbc9XAxuqagmwoT0nyUnASuBkYDlwaZJ5rc9lwCpgSXss3/uXIEka1d5M76wA1rb1tcDZQ/Wrq+qxqroH2AIsS3I8cGRV3VBVBVw51EeSNAtGDf0CPp3kpiSrWu24qnoAoC2PbfUFwP1Dfbe22oK2Prb+JElWJdmUZNOOHTtGHKIkaSqHjtjuRVW1LcmxwHVJ7pik7Xjz9DVJ/cnFqsuBywGWLl06bhtJ0vSNdKZfVdvacjvwMWAZ8GCbsqEtt7fmW4FFQ90XAttafeE4dUnSLJky9JM8Lckzdq8DLwNuAdYD57Vm5wHXtPX1wMokhyU5kcEF241tCujhJKe3T+2cO9RHkjQLRpneOQ74WPt05aHAB6vqU0luBNYlOR+4DzgHoKpuTbIOuA3YCVxYVbvavi4ArgCOAK5tD0nSLJky9KvqbuD549S/DZw5QZ81wJpx6puAU6Y/TEnSTPAbuZLUEUNfkjpi6EtSRwx9SeqIoS9JHTH0Jakjhr4kdcTQl6SOGPqS1BFDX5I6YuhLUkcMfUnqiKEvSR0x9CWpI4a+JHXE0Jekjhj6ktQRQ1+SOmLoS1JHDH1J6oihL0kdMfQlqSOGviR1xNCXpI4Y+pLUEUNfkjoycugnmZfkK0k+0Z4fneS6JHe15VFDbS9KsiXJnUnOGqqflmRz23ZJkszsy5EkTWY6Z/pvAG4fer4a2FBVS4AN7TlJTgJWAicDy4FLk8xrfS4DVgFL2mP5Xo1ekjQtI4V+koXAK4B3D5VXAGvb+lrg7KH61VX1WFXdA2wBliU5Hjiyqm6oqgKuHOojSZoFo57pvwt4E/CjodpxVfUAQFse2+oLgPuH2m1ttQVtfWz9SZKsSrIpyaYdO3aMOERJ0lSmDP0kvw5sr6qbRtznePP0NUn9ycWqy6tqaVUtnT9//oiHlSRN5dAR2rwI+I0kLwcOB45M8n7gwSTHV9UDbepme2u/FVg01H8hsK3VF45TlyTNkinP9KvqoqpaWFWLGVyg/ZuqejWwHjivNTsPuKatrwdWJjksyYkMLthubFNADyc5vX1q59yhPpKkWTDKmf5ELgbWJTkfuA84B6Cqbk2yDrgN2AlcWFW7Wp8LgCuAI4Br20OSNEumFfpVdT1wfVv/NnDmBO3WAGvGqW8CTpnuICVJM8Nv5EpSRwx9SeqIoS9JHTH0Jakjhr4kdcTQl6SOGPqS1BFDX5I6YuhLUkf25jYMmsDi1Z+cs2Pfe/Er5uzYkvZ/nulLUkcMfUnqiKEvSR0x9CWpI4a+JHXE0Jekjhj6ktQRQ1+SOmLoS1JHDH1J6oihL0kdMfQlqSOGviR1xNCXpI4Y+pLUEUNfkjoyZegnOTzJxiRfTXJrkre0+tFJrktyV1seNdTnoiRbktyZ5Kyh+mlJNrdtlyTJvnlZkqTxjHKm/xjwL6vq+cCpwPIkpwOrgQ1VtQTY0J6T5CRgJXAysBy4NMm8tq/LgFXAkvZYPnMvRZI0lSlDvwYeaU+f0h4FrADWtvpa4Oy2vgK4uqoeq6p7gC3AsiTHA0dW1Q1VVcCVQ30kSbNgpDn9JPOS3AxsB66rqi8Bx1XVAwBteWxrvgC4f6j71lZb0NbH1iVJs2Sk0K+qXVV1KrCQwVn7KZM0H2+eviapP3kHyaokm5Js2rFjxyhDlCSNYFqf3qmq7wDXM5iLf7BN2dCW21uzrcCioW4LgW2tvnCc+njHubyqllbV0vnz509niJKkSYzy6Z35SZ7V1o8AXgrcAawHzmvNzgOuaevrgZVJDktyIoMLthvbFNDDSU5vn9o5d6iPJGkWHDpCm+OBte0TOIcA66rqE0luANYlOR+4DzgHoKpuTbIOuA3YCVxYVbvavi4ArgCOAK5tD0nSLJky9Kvqa8ALxql/Gzhzgj5rgDXj1DcBk10PkCTtQ34jV5I6YuhLUkcMfUnqiKEvSR0x9CWpI4a+JHXE0Jekjhj6ktQRQ1+SOmLoS1JHDH1J6oihL0kdMfQlqSOGviR1xNCXpI4Y+pLUEUNfkjpi6EtSRwx9SeqIoS9JHZnyh9F1YFm8+pNzctx7L37FnBxX0vR4pi9JHTH0Jakjhr4kdcTQl6SOGPqS1JEpQz/JoiSfSXJ7kluTvKHVj05yXZK72vKooT4XJdmS5M4kZw3VT0uyuW27JEn2zcuSJI1nlDP9ncB/rKrnAacDFyY5CVgNbKiqJcCG9py2bSVwMrAcuDTJvLavy4BVwJL2WD6Dr0WSNIUpQ7+qHqiqL7f1h4HbgQXACmBta7YWOLutrwCurqrHquoeYAuwLMnxwJFVdUNVFXDlUB9J0iyY1px+ksXAC4AvAcdV1QMw+MMAHNuaLQDuH+q2tdUWtPWxdUnSLBk59JM8Hfgr4A+q6nuTNR2nVpPUxzvWqiSbkmzasWPHqEOUJE1hpNBP8hQGgf+BqvpoKz/Ypmxoy+2tvhVYNNR9IbCt1ReOU3+Sqrq8qpZW1dL58+eP+lokSVMY5dM7Ad4D3F5Vfzq0aT1wXls/D7hmqL4yyWFJTmRwwXZjmwJ6OMnpbZ/nDvWRJM2CUW649iLgNcDmJDe32n8BLgbWJTkfuA84B6Cqbk2yDriNwSd/LqyqXa3fBcAVwBHAte0hSZolU4Z+Vf0d48/HA5w5QZ81wJpx6puAU6YzQEnSzPEbuZLUEe+nrxkxV/fxB+/lL02HZ/qS1BFDX5I6YuhLUkcMfUnqiKEvSR0x9CWpI4a+JHXE0Jekjhj6ktQRQ1+SOmLoS1JHDH1J6oihL0kdMfQlqSPeWlkHvLm6rbO3dNaByDN9SeqIoS9JHTH0Jakjhr4kdcTQl6SOGPqS1BFDX5I6YuhLUkcMfUnqiKEvSR2ZMvSTvDfJ9iS3DNWOTnJdkrva8qihbRcl2ZLkziRnDdVPS7K5bbskSWb+5UiSJjPKmf4VwPIxtdXAhqpaAmxoz0lyErASOLn1uTTJvNbnMmAVsKQ9xu5TkrSPTRn6VfU54KEx5RXA2ra+Fjh7qH51VT1WVfcAW4BlSY4HjqyqG6qqgCuH+kiSZsmezukfV1UPALTlsa2+ALh/qN3WVlvQ1sfWx5VkVZJNSTbt2LFjD4coSRprpi/kjjdPX5PUx1VVl1fV0qpaOn/+/BkbnCT1bk9D/8E2ZUNbbm/1rcCioXYLgW2tvnCcuiRpFu3pj6isB84DLm7La4bqH0zyp8AJDC7YbqyqXUkeTnI68CXgXODP9mrk0hybqx9vAX/ARXtuytBP8iHgDOCYJFuBP2IQ9uuSnA/cB5wDUFW3JlkH3AbsBC6sql1tVxcw+CTQEcC17SFJmkVThn5VvXKCTWdO0H4NsGac+ibglGmNTpI0o/xGriR1xNCXpI4Y+pLUEUNfkjpi6EtSRwx9SerInn45S9IcmqsvhvmlsAOfZ/qS1BFDX5I6YuhLUkcMfUnqiKEvSR0x9CWpI4a+JHXE0JekjvjlLEkj80thBz7P9CWpI4a+JHXE0JekjjinL2m/N1fXEuDgu57gmb4kdcTQl6SOGPqS1BFDX5I64oVcSZrEwfaFNM/0Jakjsx76SZYnuTPJliSrZ/v4ktSzWQ39JPOAvwD+FXAS8MokJ83mGCSpZ7N9pr8M2FJVd1fV48DVwIpZHoMkdWu2L+QuAO4fer4V+KWxjZKsAla1p48kuXMPj/fctnwK8MP9cLk/j82lS5dzuMwf80PgDvbcT41XTFXtxT6nJ8k5wFlV9Tvt+WuAZVX1+n10vF1t9RDgR/vhcn8em0uXLud4WVXzmGGHzPQOp7AVWDT0fCGwbZbHIEndmu3QvxFYkuTEJE8FVgLrZ3kMktStWZ3Tr6qdSf498P+AecB7q+rWfXjIG9vyWGD7frjcn8fm0qXLuV/OuFmd05ckza3Znt6RJM0hQ1+SOnLA3nAtySLgw8ALOYBfhyTtoe8AVwFvqGnM0x/IZ/o7gTcDbwfeAuwCHm/bdk3QR5IOFp8ETgeWT6fTQXEhN8liYDPw1PbYxeDTQZJ0sHobg9C/p6p+b9ROB8u0yELgCH78LxcDX9LBbCfwMgYnuAum0/FAnt4BIMnTgf8NFJA5Ho4k7Uu7p64PZZB5u9pyZAd06Cd5CvBR4HAGZ/ePT95Dkg5ojw2tnwx8l2neyuaADf0kAd7DYGrnWAZvxtfndFCStG99eWj9IeBI4Jrp7OCAvZCb5MXA3871OCRpjmwH/g/w+ul8ZPOADX1J0vQdsNM7kqTpM/QlqSOGviR1xNCXpI4Y+pLUEUNfB60kz0ry+1O0+cJsjWcUSc5OctJcj0MHL0NfB7NnAeOGfpJ5AFX1K7M5oBGcDRj62mcMfR3MLgaek+TmJO9MckaSzyT5IIO7spLkkbY8I8nnknwsyW1J/jLJk/7/keSFSb6Q5KtJNiZ5RpLDk7wvyeYkX0nyktb2t5P8+VDfTyQ5Y/dxk6xp+/likuOS/ArwG8A725ifs6/fIPXnYLnLpjSe1cApVXUqDIIdWNZq94zTfhmDs+xvAJ8C/g3wkd0bkzyVwQ/3/FZV3ZjkSOD7wBsAqurnkzwX+HSSn51ibE8DvlhV/zXJnwC/W1VvT7Ie+ERVfWSK/tIe8Uxfvdk4QeDv3nZ3Ve0CPgS8eMz2nwMeqKobAarqe1W1s7W7qtXuYPBHY6rQfxz4RFu/CVg83Rci7QlDX715dJJtY+9JMvZ5xqntro9nJ0/8/9jhQ+s/HLpfyi78V7dmiaGvg9nDwDOm0X5ZkhPbXP5vAX83ZvsdwAlJXgjQ5vMPBT4HvKrVfhZ4NnAncC9wapJD2m86L9sHY5amxdDXQauqvg18PsktSd45QpcbGFz8vQW4B/jYmP09zuCPwZ8l+SpwHYOz90uBeUk2M5jz/+2qegz4fNvPZuB/8MTb4k7kauA/tQvCXsjVjPMumxL/dJH3jVX163M8FGmf8kxfkjrimb4kdcQzfUnqiKEvSR0x9CWpI4a+JHXE0Jekjvx/Irp0LiFFNNIAAAAASUVORK5CYII=\n", "text/plain": [ "
" ] }, "metadata": { "needs_background": "light" }, "output_type": "display_data" } ], "source": [ "plt.hist(df.trip_count)\n", "plt.xlabel('trip count')\n", "plt.title('trip count histogram')" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
trip_counttrip_hour
01.011.0
110.017.0
20.03.0
30.05.0
41.014.0
52.019.0
62.015.0
71.09.0
81.023.0
90.04.0
\n", "
" ], "text/plain": [ " trip_count trip_hour\n", "0 1.0 11.0\n", "1 10.0 17.0\n", "2 0.0 3.0\n", "3 0.0 5.0\n", "4 1.0 14.0\n", "5 2.0 19.0\n", "6 2.0 15.0\n", "7 1.0 9.0\n", "8 1.0 23.0\n", "9 0.0 4.0" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df = run_sql(\"select round(trip_count/100) trip_count, trip_hour from trip_data\");\n", "df.head(10)\n" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "image/png": "iVBORw0KGgoAAAANSUhEUgAAAagAAAEYCAYAAAAJeGK1AAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuNCwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8QVMy6AAAACXBIWXMAAAsTAAALEwEAmpwYAAAo1UlEQVR4nO3de5hcVZnv8e/PEKAlgYhIa5pgEDWohMMlcplwtFvFIKLGzKgwQdFR4m0Uz0CUGB3AkUlm8qAyF50B5QEE016IAZUxcuQElOEiEKTRGBW5pQNBjIE0EyAJ7/lj704qlbrs6uqq2tX1+zxPP+lau9beb63e6bf3qlXvVkRgZmaWN89rdQBmZmalOEGZmVkuOUGZmVkuOUGZmVkuOUGZmVkuOUGZmVkuOUF1MEkHShqSNK7VseSJpF5Ja1sdRymSuiT9QNITkr6b4fm5fS15JOkySV9sdRyWcIIaoyQ9IOlNlZ4TEQ9FxISI2NasuOrhX7YA/BXQDbwwIt7V6mBGk6SQ9PKCx7n+eRfHa6PPCapDSdqt1TF0OiVq/T/4UuC3EbG1ETGNJT7H258T1Bgk6ZvAgcAP0im8T0uamv7F90FJDwE3FLTtlvZbKWmRpNvTKaRrJO1b4TjvkHS3pCcl3SfpxLR9sqRrJW2Q9HtJZxT02WkKpfiv5PTK72xJ96QxfFvSnpL2Av4LmJy+piFJk4viOVbSo4VTlpLeKeme9PujJd2Rxrte0peqjONZkh6T9IikDxS07yPpCkl/lPSgpM8NJxpJ50m6suC5pcb4Akk3A/8DvKzEcV+VPm+jpF9Jenvafj7w98B70tf/wRJ9u9Ix/rOkXwOvLdp+Tvqz2iTp15Lembbvkf68phc8d39JmyW9qMRxXi7pxvRn9Likbxdse42k69P9rZf02YLxvyV9XY9I+jdJu6fbbkq7/zJ9badT4uct6XkFr+FPkr4zfI6WOsdLxN0raa2kz6ZxPyBpbvHzCp5/RnoOb0jP6cll4n1PuX1YHSLCX2PwC3gAeFPB46lAAFcAewFdBW27pc9ZCQwCh6bPuRq4ssz+jwaeAE4g+UOnBzgk3XYj8FVgT+Bw4I/AG9NtlwFfLNhPL7C2KO7bgcnAvsBq4COlnlsmrvuAEwoefxc4J/3+FuC96fcTgGPL7KMX2Ap8ARgPnESSTF6Qbr8CuAaYmI7hb4EPptvOKxyzMmP8EPAaYDdgfNGxxwO/Bz4L7A68AdgETCu1/xKxLwZ+lo7dFODeovF9Vzq2zwPeAzwFvCTd9lXgnwqeeybwgzLHWQosTPezJ3B82j4ReAQ4K22fCByTbjsKODZ93VPTn+2nCvYZwMvLnRtp26eAW4EDgD2A/wSWljvHK/xsv5T2f306BsPjexnp+ZmO/ePAkelz/xW4qVy8/hr9L19BdZ7zIuKpiNhcZvs3I+LeiHgK+DzwbpVeRPFB4NKIuD4inouIwYj4jaQpwPHAZyLi6Yi4G/g68N4aYvyXiFgXERuAH5AkuayWAqcCSJpIklyWptu2AC+XtF9EDEXErRX2swX4QkRsiYjrgCFgWjoW7wEWRMSmiHgAuJDaXt9lEfGriNgaEVuKth1LkjwXR8SzEXED8MPh15TBu4ELImJDRDwM/Evhxoj4bjq2z0XEt4HfkfyxAXA58NfaMe34XuCbZY6zhWS6cXL6c/552n4y8GhEXJi2b4qI29Jj3xkRt6av+wGS5PL6jK9r2IeBhRGxNiKeIUnYf6Wdp/OqneMAn4+IZyLiRuBHJONWbC7JOX5XeqwFwHGSptYYs42QE1TnebiG7Q+S/EW/X4nnTSG5Wik2GdgQEZuK9tNTQ4yPFnz/PyS/sLP6FjBH0h7AHOCuiHgw3fZB4JXAbyT9QtLJFfbzp9j5fZ7hOPYjubJ5sGBbra+v0s9gMvBwRDw3wv1PZtef4XaS3qdkWnajpI0kV8v7AaSJ5Cng9ZIOAV4OXFvmOJ8GBNyeTkP+Tdpe7rxA0isl/TCdhn0S+EdKn1uVvBT4fkH8q4FtJAtHhlU7x/+c/gE27EGScSs2mYLxi4gh4E/U9rO2OjhBjV3lytRXK18/peD7A0n+Un68xPMeBg4u0b4O2De9eincz2D6/VPA8wu2vbhKPIWqlt6PiF+T/FJ5C/DXJAlreNvvIuJUYH/gn4DvKXlvqxaPs+PqYVitr6/S61gHTNHOiycK91/NI+z6MwRA0kuBS4C/JVkFOIlkClAFz78cOI3k6ul7EfF0qYNExKMRcUZETCa5qvmqkhVt5c4LgK8BvwFeERF7k0xjqsxzofQ4PQy8JSImFXztGRGDVfoVekHRz/1AknEvto6Cn3Pa54Vk/1lYnZygxq71lHgDPoPTJL1a0vNJ3oP5XpRehv4N4AOS3pi+cd0j6ZB0Wum/gUVKFjccRnLlclXa727gJEn7SnoxyXsKtbymF0rap8rzvgV8EngdyXtQAEg6TdKL0quTjWlzTUvs07H4DnCBpInpL/2/A4YXRtwNvE7JZ8z2IZkWqsXwVcynJY2X1Au8DejP2P87wAJJL5B0APCJgm17kfzy/iOAkoUfhxb1/ybwTpIkdUW5g0h6V7p/gD+n+91GMh35YkmfShdeTJR0TPq8icCTwFB6hfbRot0Wn7Olft7/QTL2L03jeJGkd5SLs4LzJe0u6X+TTEuW+kzZt0jO8cPTK/J/BG5LpydLxWujzAlq7FoEfC6dCjm7hn7fJHmj+FGSN7k/WepJEXE78AHgyySLJW5kx1+bp5K8Yb0O+D5wbkRcX7D/X5IshvgJsH31VzUR8RuS95P+kL6uUtMypM/pBW6IiMKrvxOBX0kaAi4CTil3hVDFJ0iSyB+An5P8Irs0jfF6ktd0D3AnyS/szCLiWeDtJFeAj5MsXHhf+tqzOJ/kCvJ+kvHd/h5SenV5IclikfXAdODmouOvBe4iSTg/q3Cc1wK3pWN5LXBmRNyfTu2eQJJUHyV5j6sv7XM2yVXtJpIrueKf/XnA5enP9t1lft4Xpcf7iaRNJAsmjqE2j5Ik1XUkfzh9pNT4RsRPSd6HvZrkyvRg4JRy8dYYg2WgCN+w0BKSVpKsEPt6q2Ox1pF0KbAuIj7X6lhGW3pFemVEHFDlqZYD/iCbmW2XrlCbAxzR4lDMPMVnZglJ/0CyaGJJRNzf6njMPMVnZma55CsoMzPLpbZ4D2q//faLqVOn1rWPp556ir32qvUjL53JY5WNxykbj1N2nTpWd9555+MRsUvNx7ZIUFOnTuWOO+6oax8rV66kt7d3dAIa4zxW2XicsvE4ZdepYyXpwVLtnuIzM7NccoIyM7NccoIyM7NccoIyM7NccoIyM7NcaliCknSpkttl31vU/glJa9J7yPxzo45vZp1p+apBZi6+gYPO+REzF9/A8lW+O0a7auQy88uAf6OgZL+kPuAdwGER8Yyk/Rt4fDPrMMtXDbJg2QCbtyR3URncuJkFywYAmH2E7zPYbhp2BRURNwEbipo/SnIr62fS5zzWqOObWedZsmLN9uQ0bPOWbSxZsaZFEVk9GlqLL62M/MOIODR9fDdwDcl9eZ4Gzo6IX5TpOw+YB9Dd3X1Uf3/W+7WVNjQ0xIQJtdw5vHN5rLLxOGXTzHEaGHyi7LbpPdXuc9l6nXpO9fX13RkRM4rbm11JYjfgBcCxJDc8+46kl0WJLBkRFwMXA8yYMSPq/XR1p35CeyQ8Vtl4nLJp5jgtXHwDgxs379LeM6mLT8xtTgz18Dm1s2av4lsLLIvE7cBzwH5NjsHMxqj5s6bRNX7cTm1d48cxf9a0FkVk9Wh2gloOvAFA0iuB3Ulua21mVrfZR/SwaM50eiZ1IZIrp0VzpnuBRJtq2BSfpKVAL7CfpLXAucClwKXp0vNngdNLTe+ZmY3U7CN6nJDGiIYlqIg4tcym0xp1TDMzGztcScLMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHKpkXfUvRQ4GXgsIg4t2nY2sAR4UUT4lu9mNmrmXnILN9+3YfvjmQfvy1VnHNfCiGykGnkFdRlwYnGjpCnACcBDDTy2mXWg4uQEcPN9G5h7yS0tisjq0bAEFRE3ARtKbPoy8GkgGnVsM+tMxcmpWrvlmyIalyckTQV+ODzFJ+ntwBsj4kxJDwAzyk3xSZoHzAPo7u4+qr+/v65YhoaGmDBhQl376BQeq2w8Ttk0c5wGBp8ou216zz5NiaEenXpO9fX13RkRM4rbG/YeVDFJzwcWAm/O8vyIuBi4GGDGjBnR29tb1/FXrlxJvfvoFB6rbDxO2TRznN5/zo/KbntgbnNiqIfPqZ01cxXfwcBBwC/Tq6cDgLskvbiJMZjZGDbz4H1rard8a1qCioiBiNg/IqZGxFRgLXBkRDzarBjMbGy76ozjdklGXsXXvhq5zHwp0AvsJ2ktcG5EfKNRxzMzA5yMxpCGJaiIOLXK9qmNOraZmbU/V5IwM7NccoIyM7NccoIyM7NccoIyM7NcatoHdc3MmmFqiQ/rPrD4rZn6Ll81yJIVa1i3cTOTJ3Uxf9Y0Zh/RM9ohWka+gjKzMaNUcqrUXmj5qkEWLBtgcONmAhjcuJkFywZYvmpwlKO0rJygzMyAJSvWsHnLtp3aNm/ZxpIVa1oUkTlBmZkB6zZurqndGs8JyswMmDypq6Z2azwnKDMzYP6saXSNH7dTW9f4ccyfNa1FEZkTlJmNGeVW62VZxTf7iB4WzZlOz6QuBPRM6mLRnOlexddCXmZuZmNK1iXlpcw+oscJKUd8BWVmZrnkBGVmZrnkBGVmZrnkBGVmZrnUyDvqXgqcDDwWEYembUuAtwHPAvcBH4iIjY2Kwca2E760kt899tT2x6/Yfy+u/7ve1gVkuVBPLb56uI7f6GvkFdRlwIlFbdcDh0bEYcBvgQUNPL6NYcXJCeB3jz3FCV9a2ZqALBfqqcVXD9fxa4yGJaiIuAnYUNT2k4jYmj68FTigUce3sa04OVVrN2sk1/FrDEVE43YuTQV+ODzFV7TtB8C3I+LKMn3nAfMAuru7j+rv768rlqGhISZMmFDXPjpFO4zVwOATZbdN79mnKTG0wzjlQTPHqVXnxWgdt1PPqb6+vjsjYkZxe0sSlKSFwAxgTmQIYMaMGXHHHXfUFcvKlSvp7e2tax+doh3GqtKUTTPeb4D2GKc8aOY4teq8mLn4BgZLFJXtmdTFzee8IfN+OvWcklQyQTV9FZ+k00kWT8zNkpzMSnnF/nvV1G7WSK7j1xhNTVCSTgQ+A7w9Iv6nmce2seX6v+vdJRl5FZ/VU4uvHq7j1xiNXGa+FOgF9pO0FjiXZNXeHsD1kgBujYiPNCoGG9ucjKyUZk3xFnMdv9HXsAQVEaeWaP5Go45nZmZjiytJmJlZLjlBmZlZLjlBmZlZLjlBmZlZLlVdJCFpf2AmMBnYDNwL3BERzzU4NrOKWlUU1PKtnvPCBV/zpewVlKQ+SSuAHwFvAV4CvBr4HDAg6XxJezcnTLOdtaooqOVbPeeFC77mT6UrqJOAMyLioeINknYjqQZxAnB1g2IzM2uaSgVffRXVGmUTVETMr7BtK7C8EQGZmbXCuhK19Cq1W+NVXSQh6ZuS9il4PFXSTxsblplZc02e1FVTuzVellV8Pwduk3SSpDOAnwBfaWhUZmZN5oKv+VM1QUXEfwIfAq4BvgC8LiJ+0OjAzCppVVFQy7d6zgsXfM2fLMvM3wt8HngfcBhwnaQPRMQvGx2cWSVORlZKPeeFC77mS5ZisX8JHB8RjwFLJX0fuAw4opGBmZlZZ6uaoCJidtHj2yUd07CIzMzMqJKgJM0CZgM9QADrgGsi4seND83MzDpZ2QQl6SvAK4ErgLVp8wHAJyW9JSLObHx4ZmbWqSpWkoiIVxY3Svo28FugYoKSdClJtYnHIuLQtG1f4NvAVOAB4N0R8ecRRW4dz7X4rJR6zgufU/lSaZn505KOLtH+WuDpDPu+DDixqO0c4KcR8Qrgp+ljs5q5Fp+VUs954XMqfypdQb0f+JqkieyY4psCPJluqygibpI0taj5HUBv+v3lwErgM1mDNTOzzqGIqPwE6cUkiyQErI2IRzPvPElQPyyY4tsYEZMKtv85Il5Qpu88YB5Ad3f3Uf39/VkPW9LQ0BATJkyoax+doh3GamDwibLbpvfsU3bbaGqHccqDZo5TPeeFz6nW6evruzMiZhS3V1vFJ+Cl7FjFN07S+qiW1UZBRFwMXAwwY8aM6O3trWt/K1eupN59dIp2GKv3V5h2eWBub1NiaIdxyoNmjlM954XPqfyptIrvzcBXgd8BwzdEOQB4uaSPRcRPRnC89ZJeEhGPSHoJ8NgI9mFmZh2g0iKJi4A3RcRbIuJD6deJJPeAumiEx7sWOD39/nSS+n5mNXMtPiulnvPC51T+VJri240diyMKDQLjq+1Y0lKSBRH7SVoLnAssBr4j6YPAQ8C7ag3YbJh/cVgp9ZwXPqfypVKCuhT4haR+4OG0bQpwCvCNajuOiFPLbHpjTRGamVlHqnRH3UWSlpMsDT+OdBUfMDcift2c8MzMrFNVXMUXEauB1U2KxczMbLssd9TdhaT/Gu1AzMzMClVaZn5kuU3A4Q2JxszMLFVpiu8XwI0kCanYpIZEY1YDF/bMt+WrBlmyYg2nTNnEwsU3MH/WtMx3qz3mgutZv+nZ7Y+7J+7ObQtPyNS3HYvF1jNWY1mlKb7VwIcjoq/4C3i8SfGZleTCnvm2fNUgC5YNMLhxMwCDGzezYNkAy1cNVum5a3ICWL/pWY654PqqfduxWGw9YzXWVUpQ51XY/onRD8XMxoolK9awecu2ndo2b9nGkhVrqvYtTk7V2ttdPWM11lVaZv69CtuWNyQaMxsT1qVXA1nbO5nHqryyV1CSTpNUafvBko5vTFhm1s4mT+qqqb2TeazKqzTF90JglaRLJX1c0rslvU/SFyTdCPwzsL45YZpZO5k/axpd48ft1NY1fhzzZ02r2rd74u41tbe7esZqrCuboCLiIuBIYCnwIpISRUeS1OJ7b0T8ZUT8rilRmhVxYc98m31ED4vmTKcnvQromdTFojnTM61Mu23hCbsko6yr+NqxWGw9YzXWVb1hYR7MmDEj7rjjjrr24fusZOexysbjlI3HKbtOHStJJW9YOKJKEmZmZo3mBGVmZrlUNUFJOihLm5mZ2WjKcgV1dYm2sp+RMjMzGw2VisUeArwG2EfSnIJNewN71nNQSf8H+BAQwADwgYh4up59WufptFp8w/Xa1m3czORJXTXVazvs3B/z5DM7qhXsvcc47jn/xKr9DjrnRxQuoxJwf4117c6avpX3p983oyZeq/rOveQWbr5vw/bHMw/el6vOOC5TXyut0hXUNOBkksKwbyv4OhI4Y6QHlNQDfBKYERGHAuNI7tJrllmn1eIrrNcW1FavrTg5ATz5zDYOO/fHFfsVJydI/qI8qMF17dqxb3FyArj5vg3MveSWqn2tvEqljq4BrpF0XESM9ijvBnRJ2gI8H1g3yvs3G1Mq1WurdhVVnJyqtQ8r9wGU/H8wpfmKk1O1dsum6uegJL2I5IppKgUJLSL+ZsQHlc4ELgA2Az+JiLklnjMPmAfQ3d19VH9//0gPB8DQ0BATJkyoax+doh3GamDwibLbpvfs05QYmjlO9bzekfYdrWN2d8H6grJyjYq3XfsWaof/e43Q19dX8nNQWRLUfwM/A+4Etv/JFRGlFk9UJekFJAsv3gNsBL4LfC8irizXxx/Uba52GKtK0y7Neh+qmeM0c/EN22/HUKhnUhc3n/OGin1HOlb1jHFh37Omb+XCgR2TNbX0ree47dK3UDv832uEej6o+/yI+ExEfCcirh7+qiOWNwH3R8QfI2ILsAz4izr2Zzbm1VOvbe89xtXUPqzUnUortXeymQfvW1O7ZZMlQf1Q0kmjeMyHgGMlPV+SSGr8rR7F/VsH6LRafIX12kRt9druOf/EXZJRllV89y9+6y7JKOsqvlbVxGtV36vOOG6XZORVfPXLMsW3CdgLeAbYQnKORkTsPeKDSueTTPFtBVYBH4qIZ8o931N8zeWxysbjlI3HKbtOHatyU3xlV/ENi4iJox1MRJwLnDva+zUzs7GjaoKS9LpS7RFx0+iHY2ZmlqiaoID5Bd/vCRxNsqKv8tIhMzOzOmSZ4ntb4WNJU0jupmtmZtYwI7ndxlrg0NEOxMzMrFCW96D+lR3VTZ4HHA78soExmWXSqqKgrTLSgq8w8tc7GmPcKcVi6+k7XAj4lCmbWLj4hpoKAddTRDjvslxB3UHyntOdwC3AZyLitIZGZVZFq4qCtspIC77CyF9vOxZtbce+hYWAobZCwPUUEW4HVRNURFwOLCVJUL8Ebm90UGa2s5EWfLX8q1QIuJF920GWKb5e4HLgAZIP6U6RdLqXmZuZ1W9diRqLldpHq287yDLFdyHw5oh4fUS8DpgFfLmxYZmZdYbJk7pqah+tvu0gS4IaHxHbrxcj4rfA+MaFZGbFRlrw1fKvnkLA9fRtB5kWSUj6hqTe9OsSkvejzFqmVUVBW2WkBV9h5K+3HYu2tmPfwkLAUFsh4HqKCLeDLMVi9wA+DhxP8h7UTcBXKxV3HW0uFttcHqtsPE7ZeJyy69SxGnGx2PQ5F0XEl9IdjQP2GOX4zMzMdpJliu+nQOE7bl3A/21MOGZmZoksCWrPiBgafpB+//zGhWRmZpYtQT0l6cjhB5KOAsbGInszM8utLO9BfQr4rqR16eOXkNwNd8QkTQK+TlJ0NoC/iYhb6tmndZ52rMV3yMLreHrbjoVJe44Tv7ngpEx9W/F6XYuvOX1fvuBHbI0dY7Wb4PeL8ruqtFmylDr6BXAI8FHgY8CrIqLeZeYXAT+OiEOA/wWsrnN/1mHasRZfcXICeHpbcMjC66r2bcXrbce6du3Ydzg5FdoaSXuny3IFRURsAe4djQNK2ht4HfD+dN/PAs+Oxr7N8qw4OVVrt85QnJyqtXeSqp+DGvUDSocDFwO/Jrl6uhM4MyKeKnrePGAeQHd391H9/f11HXdoaIgJEybUtY9O0Q5jNTD4RNlt03v2aVjfQrWOU6tiHmnf0TpmdxesL3jXOo+vNS99ax2rsaKvr6/k56BakaBmALcCMyPiNkkXAU9GxOfL9fEHdZurHcaq0tRJtXn/evoWqnWcWhXzSPuO1jHPmr6VCwd2TNbk8bXmpW+tYzVW1PNBXSTNIakkEcDPI+L7dcSyFlgbEbelj78HnFPH/szawp7jVHI6b89xakE0lhe7qfR03m4+LaovkpD0VeAjwADJ+1AflvTvIz1gRDwKPCxpuJrhG0mm+8wya8dafL+54KRdklHWVXyteL3tWNeuHfv+ftFbd0lGXsWXyFKL71fAoZE+UdLzgIGIeM2ID5q8D/V1YHfgD8AHIuLP5Z7vKb7m8lhl43HKxuOUXaeOVT1TfGuAA4EH08dTgHvqCSYi7gZ2CcbMzGxYlgT1QmC1pOFbvb8WuEXStQAR8fZGBWdmZp0rS4L6+4ZHYWZmVqRqgoqIG5sRiJmZWaGyCUrSzyPieEmbSJaXb98ERETs3fDozCpoVd205asGWbJiDadM2cTCxTcwf9a0zHcwbbc6ca7F19y+IxmruZfcws33bdj+eObB+3LVGcdl6pt3ZZeZR8Tx6b8TI2Lvgq+JTk7Waq2qm7Z81SALlg0wuDH5uP/gxs0sWDbA8lWDDT2ua/G5bynFyQng5vs2MPeSsVF7u+LnoCQ9T9Ko1OAzGwuWrFjD5i3bdmrbvGUbS1asaVFE1smKk1O19nZTMUFFxHPALyUd2KR4zHJt3cbSt0Ir125mI5dlFd9LgF+ly8y3F3T18nLrRJMndW2f3ituN7PRlSVBnd/wKMzaxPxZ01iwbGCnab6u8eOYP2tahV5mjTHz4H1LTufNPHjfFkQz+rLc8v2kiLix8AvIdgtQswZpVd202Uf0sGjOdHrSK6aeSV0smjM90yq+dqsT127xdmLfq844bpdkNJZW8WWpxXdXRBxZ1HZPRBzW0MgKuBZfc3mssvE4ZeNxyq5Tx6rmWnyShm/x/jJJhbX3JgI3j36IZmZmO1R6D+pbwH8Bi9j5fk2bImJsrGE0M7PcKpugIuIJ4Ang1OaFY2ZmlsiySMLMzKzpnKDMzCyXsnwOqiEkjQPuAAYj4uRWxWGt1erinPX07YQiqB6nsX1O5b3QbCuvoM4EVrfw+NZi7Vics5P6tlu87ltb33YoNNuSBCXpAOCtwNdbcXwzs07XDoVmq35QtyEHlb5Hsnx9InB2qSk+SfOAeQDd3d1H9ff313XMoaEhJkyYUNc+OkWzxmpg8Imy26b37JP7vt1dsL6gLF87xFxLX49T8/u2y1iNtr6+vpIf1G16gpJ0Mkn5pI9J6qVMgirkShLN1ayxqjQNUW0OPQ99z5q+lQsHdryN2w4x19LX49T8vu0yVqOtXCWJVkzxzQTeLukBoB94g6QrWxCHmVnHKldQNk+FZpueoCJiQUQcEBFTgVOAGyLitGbHYa3XjsU5O6lvu8XrvrX1bYdCsy15D2r7wT3Fl0seq2w8Ttl4nLLr1LGquVhsM0TESmBlK2MwM7N8ciUJMzPLJScoMzPLJScoMzPLpZa+B2XW6tpn9fTthBpzHiefU6Ucc8H1rN/07PbH3RN357aFJ2Q6Zi18BWUt0471yzqpb7vF677N6VucnADWb3qWYy64vuoxa+UEZWZmmRUnp2rt9XCCMjOzXHKCMjOzXHKCMjOzzLon7l5Tez2coKxl2rF+WSf1bbd43bc5fW9beMIuyahRq/haWosvK9fiay6PVTYep2w8Ttl16ljl6XYbZmZmVTlBmZlZLjlBmZlZLjlBmZlZLjlBmZlZLjW9WKykKcAVwIuB54CLI+KiRh3vc8sHWHrbw3zq0C18cMF1nHrMFL44e3qmvstXDbJkxRrWbdzM5EldzJ81jdlH9DQq1I7U6uKc9fRtl8Ke9fT1OPmcGu1j1qIVV1BbgbMi4lXAscDHJb26EQf63PIBrrz1IbalS+m3RXDlrQ/xueUDVfsuXzXIgmUDDG7cTACDGzezYNkAy1cNNiLUjtRuRTI7rW+7xeu+zelbzzFr1fQEFRGPRMRd6febgNVAQy5Llt72cE3thZasWMPmLdt2atu8ZRtLVqwZldjMzKyyln5QV9JU4Cbg0Ih4smjbPGAeQHd391H9/f01739g8Int33d3wfrNO7ZN79knc99i1fq2u6GhISZMmNDw49Qzxnno28xzqhV9PU7N79sOY9WI3419fX0lP6jbsgQlaQJwI3BBRCyr9NyRVpI4eMF126f3zpq+lQsHkrfcxknct+ikin1nLr6BwY2bd2nvmdTFzee8oeZY2kmzPs1eaUqg2nx2HvoWnlPNPG6z+nqcmt+3HcaqnmOWk6tKEpLGA1cDV1VLTvU49ZgpNbUXmj9rGl3jx+3U1jV+HPNnTRuV2MzMrLKmJyhJAr4BrI6ILzXyWF+cPZ3Tjj2QcRKQXDmdduyBmVbxzT6ih0VzptMzqQuRXDktmjPdq/hGUbsVyey0vu0Wr/s2p289x6xV06f4JB0P/AwYIFlmDvDZiLiuXB8Xi20uj1U2HqdsPE7ZdepYlZvia/rnoCLi54CafVwzM2svriRhZma55ARlZma55ARlZma51PT3oJqt3WpcQfvVAGx1/bJW9W2nc8q1+Nqjb7uM1Viuxdc07VbjCtqvBmA7jrH7ZuvbbvG6b3P6julafFaZawCamSWcoHJmXYnySpXazczGKieonJk8qaumdjOzscoJKmdcA9DMLDGmE1S71biC9qsB2I5j7L7Z+rZbvO7bnL5juhbfSLgWX3N5rLLxOGXjccquU8cqV7fbMDMzq8YJyszMcskJyszMcskJyszMcskJyszMcqklq/gknQhcBIwDvh4Riys9v55VfIVFGC8cSGrj5rkIYx761jpWrY63VX074ZzyOPmcGu1jlpKbVXySxgH/DrwFeDVwqqRXN+JY7VaEsR37tlu87pu9b7vF677N6TvWi8UeDfw+Iv4QEc8C/cA7WhCHmZnlWNOn+CT9FXBiRHwoffxe4JiI+Nui580D5gF0d3cf1d/fX/OxBgaf2P59dxesL6i3Or1nn8x9i431vrWMVR7ibVXfsX5OeZya37cdxqqeY5bT19dXcoqvFQnqXcCsogR1dER8olyfkb4HVXjJWTi3C9XnSytdro71vrWMVR7ibVXfsX5OeZya37cdxqqeY5aTm/eggLXAlILHBwDrWhCHmZnlWCsS1C+AV0g6SNLuwCnAtY04ULsVYWzHvu0Wr/tm79tu8bpvc/qO+WKxkk4CvkKyzPzSiLig0vNdLLa5PFbZeJyy8Thl16ljVW6Kb7dST260iLgOuK4VxzYzs/bgShJmZpZLTlBmZpZLTlBmZpZLTlBmZpZLbXHLd0l/BB6sczf7AY+PQjidwGOVjccpG49Tdp06Vi+NiBcVN7ZFghoNku4otYzRduWxysbjlI3HKTuP1c48xWdmZrnkBGVmZrnUSQnq4lYH0EY8Vtl4nLLxOGXnsSrQMe9BmZlZe+mkKygzM2sjTlBmZpZLHZGgJJ0oaY2k30s6p9Xx5JWkByQNSLpbUn3l48cYSZdKekzSvQVt+0q6XtLv0n9f0MoY86DMOJ0naTA9r+5O72bQ0SRNkfT/JK2W9CtJZ6btPqcKjPkEJWkc8O/AW4BXA6dKenVro8q1vog43J/F2MVlwIlFbecAP42IVwA/TR93usvYdZwAvpyeV4endzPodFuBsyLiVcCxwMfT30s+pwqM+QQFHA38PiL+EBHPAv3AO1ock7WZiLgJ2FDU/A7g8vT7y4HZzYwpj8qMkxWJiEci4q70+03AaqAHn1M76YQE1QM8XPB4bdpmuwrgJ5LulDSv1cG0ge6IeASSXzjA/i2OJ8/+VtI96RRgR09bFZM0FTgCuA2fUzvphASlEm1eW1/azIg4kmQ69OOSXtfqgGxM+BpwMHA48AhwYUujyRFJE4CrgU9FxJOtjidvOiFBrQWmFDw+AFjXolhyLSLWpf8+BnyfZHrUylsv6SUA6b+PtTieXIqI9RGxLSKeAy7B5xUAksaTJKerImJZ2uxzqkAnJKhfAK+QdJCk3YFTgGtbHFPuSNpL0sTh74E3A/dW7tXxrgVOT78/HbimhbHk1vAv3NQ78XmFJAHfAFZHxJcKNvmcKtARlSTSZa1fAcYBl0bEBa2NKH8kvYzkqglgN+BbHqcdJC0Fekluh7AeOBdYDnwHOBB4CHhXRHT0AoEy49RLMr0XwAPAh4ffZ+lUko4HfgYMAM+lzZ8leR/K51SqIxKUmZm1n06Y4jMzszbkBGVmZrnkBGVmZrnkBGVmZrnkBGVmZrnkBGVWB0mTJH2synP+u8Z9PiBpv/oiM2t/TlBm9ZkElExQaSV9IuIvmhlQmVgkyf/fra34hDWrz2Lg4PQ+R0sk9ab3+fkWyYcwkTSU/tsr6SZJ35f0a0n/USFpfELSXen9uQ5J++8raXladPVWSYel7edJOnu4o6R7JU1Nv1ZL+ipwFzuX/DLLPScos/qcA9yX3udoftp2NLAwIkrdd+xo4CxgOkkB1Tll9vt4Wrj3a8Bw8jkfWBURh5FUHbgiQ3zTgCsi4oiIeDDTKzLLCScos9F3e0TcX2HbHyJiG7AUOL7M84aLh94JTE2/Px74JkBE3AC8UNI+VWJ5MCJuzRy5WY44QZmNvqcqbCuuLVau1tgz6b/bSGojQvlbx2xl5//Le2aMxSzXnKDM6rMJmFjD849OK+s/D3gP8PMa+t4EzIXk/SySacAnSQqwHpm2HwkcVMM+zXLLCcqsDhHxJ+DmdGHCkgxdbiFZWHEvcD87KshncR4wQ9I96T6Gb8twNbCvpLuBjwK/rWGfZrnlauZmTZJe9ZwdESe3OBSztuArKDMzyyVfQZmZWS75CsrMzHLJCcrMzHLJCcrMzHLJCcrMzHLJCcrMzHLp/wPJnyFxHYZe3AAAAABJRU5ErkJggg==\n", "text/plain": [ "
" ] }, "metadata": { "needs_background": "light" }, "output_type": "display_data" } ], "source": [ "plt.scatter(df.trip_hour,df.trip_count)\n", "plt.ylabel('trip count (100x)')\n", "plt.xlabel('trip hour')\n", "plt.title('trip count vs hour of day scatter plot ')\n", "plt.grid(True)\n", "plt.tight_layout()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Run Create Model statement to create a new ML model with REdshift ML" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "run_sql(\"\"\"\n", "CREATE MODEL predict_rental_count \n", "FROM (SELECT\n", " trip_count,trip_hour,trip_day,trip_month,trip_year,trip_quarter,\n", " trip_month_week, trip_week_day, temp_c, precip_amount_mm, is_holiday\n", "FROM trip_data)\n", "TARGET trip_count\n", "FUNCTION predict_rental_count\n", "IAM_ROLE 'arn:aws:iam:::role/RedshiftML'\n", "PROBLEM_TYPE regression\n", "OBJECTIVE 'mse'\n", "SETTINGS (s3_bucket 'redshiftml-')\n", "\"\"\");" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### View Model Progress " ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
KeyValue
0Model Namepredict_rental_count
1Schema Namepublic
2Ownerawsuser
3Creation TimeTue, 06.07.2021 14:56:58
4Model StateREADY
5Training Job StatusMaxAutoMLJobRuntimeReached
6validation:mse1504.939453
7Estimated Cost5.096579
8
9TRAINING DATA:
10QuerySELECT TRIP_COUNT,TRIP_HOUR,TRIP_DAY,TRIP_MONT...
11FROM TRIP_DATA
12Target ColumnTRIP_COUNT
13
14PARAMETERS:
15Model Typeauto
16Problem TypeRegression
17ObjectiveMSE
18AutoML Job Name2021-07-06-14-56-58-198333-auto
19Function Namepredict_rental_count
20Function Parameterstrip_hour trip_day trip_month trip_year trip_q...
21Function Parameter Typesint4 int4 int4 int4 int4 int4 int4 numeric num...
22IAM Rolearn:aws:iam::009858891282:role/redshift_s3_ful...
23S3 Bucketanalyticsaws
24Max Runtime5400
\n", "
" ], "text/plain": [ " Key \\\n", "0 Model Name \n", "1 Schema Name \n", "2 Owner \n", "3 Creation Time \n", "4 Model State \n", "5 Training Job Status \n", "6 validation:mse \n", "7 Estimated Cost \n", "8 \n", "9 TRAINING DATA: \n", "10 Query \n", "11 \n", "12 Target Column \n", "13 \n", "14 PARAMETERS: \n", "15 Model Type \n", "16 Problem Type \n", "17 Objective \n", "18 AutoML Job Name \n", "19 Function Name \n", "20 Function Parameters \n", "21 Function Parameter Types \n", "22 IAM Role \n", "23 S3 Bucket \n", "24 Max Runtime \n", "\n", " Value \n", "0 predict_rental_count \n", "1 public \n", "2 awsuser \n", "3 Tue, 06.07.2021 14:56:58 \n", "4 READY \n", "5 MaxAutoMLJobRuntimeReached \n", "6 1504.939453 \n", "7 5.096579 \n", "8 \n", "9 \n", "10 SELECT TRIP_COUNT,TRIP_HOUR,TRIP_DAY,TRIP_MONT... \n", "11 FROM TRIP_DATA \n", "12 TRIP_COUNT \n", "13 \n", "14 \n", "15 auto \n", "16 Regression \n", "17 MSE \n", "18 2021-07-06-14-56-58-198333-auto \n", "19 predict_rental_count \n", "20 trip_hour trip_day trip_month trip_year trip_q... \n", "21 int4 int4 int4 int4 int4 int4 int4 numeric num... \n", "22 arn:aws:iam::009858891282:role/redshift_s3_ful... \n", "23 analyticsaws \n", "24 5400 " ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df = run_sql(\"SHOW MODEL predict_rental_count\")\n", "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Run Prediction and compare actual vs predicted\n" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
trip_timeactual_countpredicted_countdifference
02018-01-01 08:00:00.0413.333012580871582-9.333012580871582
12018-04-27 10:00:00.0200205.99017333984375-5.99017333984375
22018-04-19 22:00:00.08476.413619995117197.5863800048828125
32018-08-04 17:00:00.0830826.2636718753.736328125
42018-04-04 23:00:00.03330.6710529327392582.328947067260742
\n", "
" ], "text/plain": [ " trip_time actual_count predicted_count difference\n", "0 2018-01-01 08:00:00.0 4 13.333012580871582 -9.333012580871582\n", "1 2018-04-27 10:00:00.0 200 205.99017333984375 -5.99017333984375\n", "2 2018-04-19 22:00:00.0 84 76.41361999511719 7.5863800048828125\n", "3 2018-08-04 17:00:00.0 830 826.263671875 3.736328125\n", "4 2018-04-04 23:00:00.0 33 30.671052932739258 2.328947067260742" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df = run_sql(\"\"\"\n", "SELECT trip_time, actual_count, predicted_count, ( actual_count - predicted_count ) difference\n", "FROM \n", "(SELECT\n", " trip_time\n", ",trip_count AS actual_count\n", ",PREDICT_RENTAL_COUNT (trip_hour, trip_day, trip_month, trip_year, trip_quarter, trip_month_week, trip_week_day, temp_c, precip_amount_mm, is_holiday) predicted_count\n", "FROM trip_data\n", ") LIMIT 5;\n", "\"\"\");\n", "df\n" ] } ], "metadata": { "kernelspec": { "display_name": "conda_python3", "language": "python", "name": "conda_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.6.13" } }, "nbformat": 4, "nbformat_minor": 4 }