# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 2
#     language: python
#     name: python2
# ---

# # License Information

# + active=""
# 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.
# -

# # NYC Data wrangling using IPython Notebook and SQL Data Warehouse

# 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.
#
# 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/).

# ## Read data in Pandas frame for visualizations

# We start with loading a sample of the data in a Pandas data frame and performing some explorations on the sample. 
#
# 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.

# #### Import required packages in this experiment

import pandas as pd
from pandas import Series, DataFrame
import numpy as np
import matplotlib.pyplot as plt
from time import time
import pyodbc
import os
import tables
import time

# #### Set plot inline

# %matplotlib inline

# #### Initialize Database Credentials

SERVER_NAME = '<server name>'
DATABASE_NAME = '<database name>'
USERID = '<user name>'
PASSWORD = '<password>'
DB_DRIVER = '<database driver>'

# #### Create Database Connection

driver = 'DRIVER={' + DB_DRIVER + '}'
server = 'SERVER=' + SERVER_NAME 
database = 'DATABASE=' + DATABASE_NAME
uid = 'UID=' + USERID 
pwd = '<PASSWORD>=' + PASSWORD

CONNECTION_STRING = ';'.join([driver,server,database,uid,pwd, ';TDS_VERSION=7.3;Port=1433'])
print CONNECTION_STRING
conn = pyodbc.connect(CONNECTION_STRING)

# #### Report number of rows and columns in table <nyctaxi_trip>

# +
nrows = pd.read_sql('''SELECT SUM(rows) FROM sys.partitions WHERE object_id = OBJECT_ID('<schemaname>.<nyctaxi_trip>')''', conn)
print 'Total number of rows = %d' % nrows.iloc[0,0]

ncols = pd.read_sql('''SELECT count(*) FROM information_schema.columns WHERE table_name = ('<nyctaxi_trip>') AND table_schema = ('<schemaname>')''', conn)
print 'Total number of columns = %d' % ncols.iloc[0,0]
# -

# #### Report number of rows and columns in table <nyctaxi_fare>

# +
nrows = pd.read_sql('''SELECT SUM(rows) FROM sys.partitions WHERE object_id = OBJECT_ID('<schemaname>.<nyctaxi_fare>')''', conn)
print 'Total number of rows = %d' % nrows.iloc[0,0]

ncols = pd.read_sql('''SELECT count(*) FROM information_schema.columns WHERE table_name = ('<nyctaxi_fare>') AND table_schema = ('<schemaname>')''', conn)
print 'Total number of columns = %d' % ncols.iloc[0,0]
# -

# #### Read-in data from SQL Data Warehouse

# +
t0 = time.time()

#load only a small percentage of the joined data for some quick visuals
df1 = pd.read_sql('''select top 10000 t.*, f.payment_type, f.fare_amount, f.surcharge, f.mta_tax, 
      f.tolls_amount, f.total_amount, f.tip_amount 
      from <schemaname>.<nyctaxi_trip> t, <schemaname>.<nyctaxi_fare> f where datepart("mi",t.pickup_datetime)=0 and t.medallion = f.medallion 
      and t.hack_license = f.hack_license and t.pickup_datetime = f.pickup_datetime''', conn)

t1 = time.time()
print 'Time to read the sample table is %f seconds' % (t1-t0)

print 'Number of rows and columns retrieved = (%d, %d)' % (df1.shape[0], df1.shape[1])
# -

# #### Descriptive Statistics

# Now we can explore the sample data. We start with looking at descriptive statistics for trip distance:

df1['trip_distance'].describe()

# #### Box Plot

# Next we look at the box plot for trip distance to visualize quantiles

df1.boxplot(column='trip_distance',return_type='dict')

# #### Distribution Plot

fig = plt.figure()
ax1 = fig.add_subplot(1,2,1)
ax2 = fig.add_subplot(1,2,2)
df1['trip_distance'].plot(ax=ax1,kind='kde', style='b-')
df1['trip_distance'].hist(ax=ax2, bins=100, color='k')

# #### Binning trip_distance

trip_dist_bins = [0, 1, 2, 4, 10, 1000]
df1['trip_distance']
trip_dist_bin_id = pd.cut(df1['trip_distance'], trip_dist_bins)
trip_dist_bin_id

# #### Bar and Line Plots

# The distribution of the trip distance values after binning looks like the following:

pd.Series(trip_dist_bin_id).value_counts()

# We can plot the above bin distribution in a bar or line plot as below

pd.Series(trip_dist_bin_id).value_counts().plot(kind='bar')

pd.Series(trip_dist_bin_id).value_counts().plot(kind='line')

# We can also use bar plots for visualizing the sum of passengers for each vendor as follows

vendor_passenger_sum = df1.groupby('vendor_id').passenger_count.sum()
print vendor_passenger_sum

vendor_passenger_sum.plot(kind='bar')

# #### Scatterplot 

# We plot a scatter plot between trip_time_in_secs and trip_distance to see if there is any correlation between them.

plt.scatter(df1['trip_time_in_secs'], df1['trip_distance'])

# 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

df1_2col = df1[['trip_time_in_secs','trip_distance']]
pd.scatter_matrix(df1_2col, diagonal='hist', color='b', alpha=0.7, hist_kwds={'bins':100})

# Similarly we can check the relationship between rate_code and trip_distance using a scatter plot

plt.scatter(df1['passenger_count'], df1['trip_distance'])

# #### Correlation

# Pandas 'corr' function can be used to compute the correlation between trip_time_in_secs and trip_distance as follows:

df1[['trip_time_in_secs', 'trip_distance']].corr()

# ## Sub-Sampling the Data in SQL

# 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. 
#
# The sample data table named '<nyctaxi_sample>' has been created and the data is loaded when you run the PowerShell script. 

# #### Report number of rows and columns in the sampled table

# +
nrows = pd.read_sql('''SELECT SUM(rows) FROM sys.partitions WHERE object_id = OBJECT_ID('<schemaname>.<nyctaxi_sample>')''', conn)
print 'Number of rows in sample = %d' % nrows.iloc[0,0]

ncols = pd.read_sql('''SELECT count(*) FROM information_schema.columns WHERE table_name = ('<nyctaxi_sample>') AND table_schema = ('<schemaname>')''', conn)
print 'Number of columns in sample = %d' % ncols.iloc[0,0]
# -

# 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. 

# ## Exploration in SQL

# In this section, we would be doing some explorations using SQL on the 1% sample data (that we created above).

# #### Tipped/Not Tipped Distribution

# +
query = '''
        SELECT tipped, count(*) AS tip_freq
        FROM <schemaname>.<nyctaxi_sample>
        GROUP BY tipped
        '''

pd.read_sql(query, conn)
# -

# #### Tip Class Distribution

# +
query = '''
        SELECT tip_class, count(*) AS tip_freq
        FROM <schemaname>.<nyctaxi_sample>
        GROUP BY tip_class
'''

tip_class_dist = pd.read_sql(query, conn)
tip_class_dist
# -

# #### Plot the tip distribution by class

tip_class_dist['tip_freq'].plot(kind='bar')

# #### Daily distribution of trips

query = '''
        SELECT CONVERT(date, dropoff_datetime) as date, count(*) as c 
        from <schemaname>.<nyctaxi_sample> 
        group by CONVERT(date, dropoff_datetime)
        '''
pd.read_sql(query,conn)

# #### Trip distribution per medallion

query = '''select medallion,count(*) as c from <schemaname>.<nyctaxi_sample> group by medallion'''
pd.read_sql(query,conn)

# #### Trip distribution by medallion and hack license

query = '''select medallion, hack_license,count(*) from <schemaname>.<nyctaxi_sample> group by medallion, hack_license'''
pd.read_sql(query,conn)

# #### Trip time distribution

query = '''select trip_time_in_secs, count(*) from <schemaname>.<nyctaxi_sample> group by trip_time_in_secs order by count(*) desc'''
pd.read_sql(query,conn)

# #### Trip distance distribution

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'''
pd.read_sql(query,conn)

# #### Payment type distribution

query = '''select payment_type,count(*) from <schemaname>.<nyctaxi_sample> group by payment_type'''
pd.read_sql(query,conn)

query = '''select TOP 10 * from <schemaname>.<nyctaxi_sample>'''
pd.read_sql(query,conn)

# 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)



StarcoderdataJupyterScriptsDedupFiltered