{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# License Information"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "This sample IPython Notebook is shared by Microsoft under the MIT license. Please check the LICENSE.txt file in the directory where this  IPython Notebook is stored for license information and additional details."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# NYC Data wrangling using IPython Notebook and SQL Data Warehouse"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook demonstrates data exploration and feature generation using Python and SQL queries for data stored in Azure SQL Data Warehouse. We start with reading a sample of the data into a Pandas data frame and visualizing and exploring the data. We show how to use Python to execute SQL queries against the data and manipulate data directly within the Azure SQL Data Warehouse.\n",
    "\n",
    "This IPNB is accompanying material to the data Azure Data Science in Action walkthrough document (https://azure.microsoft.com/en-us/documentation/articles/machine-learning-data-science-process-sql-walkthrough/) and uses the New York City Taxi dataset (http://www.andresmh.com/nyctaxitrips/)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read data in Pandas frame for visualizations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We start with loading a sample of the data in a Pandas data frame and performing some explorations on the sample. \n",
    "\n",
    "We join the Trip and Fare data and sub-sample the data to load a 0.1% sample of the dataset in a Pandas dataframe. We assume that the Trip and Fare tables have been created and loaded from the taxi dataset mentioned earlier. If you haven't done this already please refer to the 'Move Data to SQL Server on Azure' article linked from the Cloud Data Science process map."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Import required packages in this experiment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pandas import Series, DataFrame\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from time import time\n",
    "import pyodbc\n",
    "import os\n",
    "import tables\n",
    "import time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Set plot inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Initialize Database Credentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SERVER_NAME = '<server name>'\n",
    "DATABASE_NAME = '<database name>'\n",
    "USERID = '<user name>'\n",
    "PASSWORD = '<password>'\n",
    "DB_DRIVER = '<database driver>'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Create Database Connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "driver = 'DRIVER={' + DB_DRIVER + '}'\n",
    "server = 'SERVER=' + SERVER_NAME \n",
    "database = 'DATABASE=' + DATABASE_NAME\n",
    "uid = 'UID=' + USERID \n",
    "pwd = 'PWD=' + PASSWORD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "CONNECTION_STRING = ';'.join([driver,server,database,uid,pwd, ';TDS_VERSION=7.3;Port=1433'])\n",
    "print CONNECTION_STRING\n",
    "conn = pyodbc.connect(CONNECTION_STRING)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Report number of rows and columns in table <nyctaxi_trip>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "nrows = pd.read_sql('''SELECT SUM(rows) FROM sys.partitions WHERE object_id = OBJECT_ID('<schemaname>.<nyctaxi_trip>')''', conn)\n",
    "print 'Total number of rows = %d' % nrows.iloc[0,0]\n",
    "\n",
    "ncols = pd.read_sql('''SELECT count(*) FROM information_schema.columns WHERE table_name = ('<nyctaxi_trip>') AND table_schema = ('<schemaname>')''', conn)\n",
    "print 'Total number of columns = %d' % ncols.iloc[0,0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Report number of rows and columns in table <nyctaxi_fare>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "nrows = pd.read_sql('''SELECT SUM(rows) FROM sys.partitions WHERE object_id = OBJECT_ID('<schemaname>.<nyctaxi_fare>')''', conn)\n",
    "print 'Total number of rows = %d' % nrows.iloc[0,0]\n",
    "\n",
    "ncols = pd.read_sql('''SELECT count(*) FROM information_schema.columns WHERE table_name = ('<nyctaxi_fare>') AND table_schema = ('<schemaname>')''', conn)\n",
    "print 'Total number of columns = %d' % ncols.iloc[0,0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Read-in data from SQL Data Warehouse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "t0 = time.time()\n",
    "\n",
    "#load only a small percentage of the joined data for some quick visuals\n",
    "df1 = pd.read_sql('''select top 10000 t.*, f.payment_type, f.fare_amount, f.surcharge, f.mta_tax, \n",
    "      f.tolls_amount, f.total_amount, f.tip_amount \n",
    "      from <schemaname>.<nyctaxi_trip> t, <schemaname>.<nyctaxi_fare> f where datepart(\"mi\",t.pickup_datetime)=0 and t.medallion = f.medallion \n",
    "      and t.hack_license = f.hack_license and t.pickup_datetime = f.pickup_datetime''', conn)\n",
    "\n",
    "t1 = time.time()\n",
    "print 'Time to read the sample table is %f seconds' % (t1-t0)\n",
    "\n",
    "print 'Number of rows and columns retrieved = (%d, %d)' % (df1.shape[0], df1.shape[1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Descriptive Statistics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can explore the sample data. We start with looking at descriptive statistics for trip distance:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1['trip_distance'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Box Plot"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next we look at the box plot for trip distance to visualize quantiles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.boxplot(column='trip_distance',return_type='dict')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Distribution Plot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "fig = plt.figure()\n",
    "ax1 = fig.add_subplot(1,2,1)\n",
    "ax2 = fig.add_subplot(1,2,2)\n",
    "df1['trip_distance'].plot(ax=ax1,kind='kde', style='b-')\n",
    "df1['trip_distance'].hist(ax=ax2, bins=100, color='k')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Binning trip_distance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "trip_dist_bins = [0, 1, 2, 4, 10, 1000]\n",
    "df1['trip_distance']\n",
    "trip_dist_bin_id = pd.cut(df1['trip_distance'], trip_dist_bins)\n",
    "trip_dist_bin_id"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Bar and Line Plots"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The distribution of the trip distance values after binning looks like the following:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pd.Series(trip_dist_bin_id).value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can plot the above bin distribution in a bar or line plot as below"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pd.Series(trip_dist_bin_id).value_counts().plot(kind='bar')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pd.Series(trip_dist_bin_id).value_counts().plot(kind='line')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also use bar plots for visualizing the sum of passengers for each vendor as follows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "vendor_passenger_sum = df1.groupby('vendor_id').passenger_count.sum()\n",
    "print vendor_passenger_sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "vendor_passenger_sum.plot(kind='bar')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Scatterplot "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We plot a scatter plot between trip_time_in_secs and trip_distance to see if there is any correlation between them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "plt.scatter(df1['trip_time_in_secs'], df1['trip_distance'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To further drill down on the relationship we can plot distribution side by side with the scatter plot (while flipping independentand dependent variables) as follows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1_2col = df1[['trip_time_in_secs','trip_distance']]\n",
    "pd.scatter_matrix(df1_2col, diagonal='hist', color='b', alpha=0.7, hist_kwds={'bins':100})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similarly we can check the relationship between rate_code and trip_distance using a scatter plot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "plt.scatter(df1['passenger_count'], df1['trip_distance'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Correlation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas 'corr' function can be used to compute the correlation between trip_time_in_secs and trip_distance as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1[['trip_time_in_secs', 'trip_distance']].corr()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sub-Sampling the Data in SQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this section we used a sampled table we pregenerated by joining Trip and Fare data and taking a sub-sample of the full dataset. \n",
    "\n",
    "The sample data table named '<nyctaxi_sample>' has been created and the data is loaded when you run the PowerShell script. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Report number of rows and columns in the sampled table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "nrows = pd.read_sql('''SELECT SUM(rows) FROM sys.partitions WHERE object_id = OBJECT_ID('<schemaname>.<nyctaxi_sample>')''', conn)\n",
    "print 'Number of rows in sample = %d' % nrows.iloc[0,0]\n",
    "\n",
    "ncols = pd.read_sql('''SELECT count(*) FROM information_schema.columns WHERE table_name = ('<nyctaxi_sample>') AND table_schema = ('<schemaname>')''', conn)\n",
    "print 'Number of columns in sample = %d' % ncols.iloc[0,0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We show some examples of exploring data using SQL in the sections below. We also show some useful visualizatios that you can use below. Note that you can read the sub-sample data in the table above in Azure Machine Learning directly using the SQL code in the reader module. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploration in SQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this section, we would be doing some explorations using SQL on the 1% sample data (that we created above)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Tipped/Not Tipped Distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "query = '''\n",
    "        SELECT tipped, count(*) AS tip_freq\n",
    "        FROM <schemaname>.<nyctaxi_sample>\n",
    "        GROUP BY tipped\n",
    "        '''\n",
    "\n",
    "pd.read_sql(query, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Tip Class Distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "query = '''\n",
    "        SELECT tip_class, count(*) AS tip_freq\n",
    "        FROM <schemaname>.<nyctaxi_sample>\n",
    "        GROUP BY tip_class\n",
    "'''\n",
    "\n",
    "tip_class_dist = pd.read_sql(query, conn)\n",
    "tip_class_dist"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Plot the tip distribution by class"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tip_class_dist['tip_freq'].plot(kind='bar')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Daily distribution of trips"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "query = '''\n",
    "        SELECT CONVERT(date, dropoff_datetime) as date, count(*) as c \n",
    "        from <schemaname>.<nyctaxi_sample> \n",
    "        group by CONVERT(date, dropoff_datetime)\n",
    "        '''\n",
    "pd.read_sql(query,conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Trip distribution per medallion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "query = '''select medallion,count(*) as c from <schemaname>.<nyctaxi_sample> group by medallion'''\n",
    "pd.read_sql(query,conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Trip distribution by medallion and hack license"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "query = '''select medallion, hack_license,count(*) from <schemaname>.<nyctaxi_sample> group by medallion, hack_license'''\n",
    "pd.read_sql(query,conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Trip time distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "query = '''select trip_time_in_secs, count(*) from <schemaname>.<nyctaxi_sample> group by trip_time_in_secs order by count(*) desc'''\n",
    "pd.read_sql(query,conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Trip distance distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "query = '''select floor(trip_distance/5)*5 as tripbin, count(*) from <schemaname>.<nyctaxi_sample> group by floor(trip_distance/5)*5 order by count(*) desc'''\n",
    "pd.read_sql(query,conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Payment type distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "query = '''select payment_type,count(*) from <schemaname>.<nyctaxi_sample> group by payment_type'''\n",
    "pd.read_sql(query,conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "query = '''select TOP 10 * from <schemaname>.<nyctaxi_sample>'''\n",
    "pd.read_sql(query,conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have now explored the data and can import the sampled data in Azure Machine Learning, add some features there and  predict things like whether a tip will be given (binary class), the tip amount (regression) or the tip amount range (multi-class)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}