<jupyter_start><jupyter_text>Technical Test Case DataSprintsThis notebook has the objective to complete the tasks given for the techincal data science case for recruiting, using Python and SQL skills to complete the tasks.For this I will be using SQLite3 library in python so we can mix SQL queries and Python code with ease, without needing to execute queries outside our code.  Index-> [Environment Preparation](Installs)-> [Loading and Exploring Data](Loading-and-Exploring-Data)* [Trips Data](Trips-Data)* [Payment Data](Payment-Lookup-Data)* [Vendors Data](Vendors-Data)-> [Analysis and Tasks](Analysis-and-Tasks)* [Question 1](Question-1)* [Question 2](Question-2)* [Question 3](Question-3)* [Question 4](Question-4)-> [Bonus Tasks](Bonus-Tasks)* [Bonus 1](Bonus-1)* [Bonus 2](Bonus-2)* [Bonus 3](Bonus-3)  Libraries<jupyter_code>!pip install pandas==1.3.4
!pip install numpy==1.20
!pip install plotly==5.3.1
!pip install datashader==0.13.0
!pip install dash==2.0.0<jupyter_output><empty_output><jupyter_text>Python version<jupyter_code>!python --version<jupyter_output><empty_output><jupyter_text>Imports<jupyter_code>import pandas as pd
import sqlite3
import plotly.express as px
import plotly.graph_objects as go
import dateutil.parser
from datetime import datetime
from colorcet import fire, kbc
import datashader as ds
import datashader.transfer_functions as tf
import json
import dash
from dash.dependencies import Output, Input
import dash_core_components as dcc
from dash import html
from collections import deque<jupyter_output><empty_output><jupyter_text>Loading and Exploring DataWe will create a local database with the data we have been given from the **NYC Taxi Trips** database. First loading the data from JSON and CSV files, transforming and appending where it's necesssary, then passing it all into tables in the local database. We will also be checking for data inconsistincy and missing data to prevent running into trouble. Checking the data files in our directory<jupyter_code>!dir<jupyter_output><empty_output><jupyter_text>Trips DataHere the data is stored in seperate JSON files, apparently by year. To ease our analysis we will read all the data files and make it into a single table.  Loading data<jupyter_code>#Creating empty dataframe to populate with the Trips data
trips_df = pd.DataFrame()

#Iterating through each year of the data files
for year in range(2009,2013):
    
    #Reading json file from the iterating year into a temporary dataframe
    df = pd.read_json(f'data-sample_data-nyctaxi-trips-{year}-json_corrigido.json', lines=True)
    
    #If trips_df is still empty, passes the temporary dataframe to the trips_df
    if trips_df.empty:
        trips_df = df
    
    #Else, appends the temporary dataframe to the end of the trips_df
    else:
        trips_df = trips_df.append(df)
    

trips_df.head()<jupyter_output><empty_output><jupyter_text>Checking data Checking for NAN values in the columns<jupyter_code>print('__NAN Values__')

#Iterating through each column
for column in trips_df.columns:
    
    #Sums all rows that are True for NAN value
    nans = trips_df[f'{column}'].isna().sum()
    
    print(f'{column}: {nans}')<jupyter_output><empty_output><jupyter_text>Checking column data types<jupyter_code>df.dtypes<jupyter_output><empty_output><jupyter_text>Dataframe size<jupyter_code>trips_df.shape<jupyter_output><empty_output><jupyter_text>Dataframe memory usage in megabytes<jupyter_code>memory_mb = trips_df.memory_usage(index=True).sum() * 0.000001
memory_mb<jupyter_output><empty_output><jupyter_text>General view of the dataset<jupyter_code>trips_df.describe()<jupyter_output><empty_output><jupyter_text>Improving Data formatDatetime in this dataframe is in ISO format, which causes compatibility problems with SQLite, and is also not very good for visualising and understanding a dataframe, so we will change the datetime into a better format.<jupyter_code>#Parsing the ISO datetime format and getting the datetime object
datetimes = [dateutil.parser.isoparse(c) for c in trips_df['pickup_datetime']]

#Changing datetime format to a more conventional one that SQLite can read
datetimes = [c.strftime('%Y-%m-%d %H:%M:%S') for c in datetimes]
trips_df['pickup_datetime'] = datetimes

#Parsing the ISO datetime format and getting the datetime object
datetimes = [dateutil.parser.isoparse(c) for c in trips_df['dropoff_datetime']]

#Changing datetime format to a more conventional one that SQLite can read
datetimes = [c.strftime('%Y-%m-%d %H:%M:%S') for c in datetimes]
trips_df['dropoff_datetime'] = datetimes


trips_df.head()<jupyter_output><empty_output><jupyter_text>Removing columnsThe _rate_code_ column is just a waste of space since its entirely populated by NANs, so we will drop it.<jupyter_code>trips_df = trips_df.drop('rate_code',axis=1)<jupyter_output><empty_output><jupyter_text>Payment Lookup DataNow to look in the Payment Lookup dataHere the data is stored in a single CSV file which helps loading in data.  Loading data<jupyter_code>payment_df = pd.read_csv('data-payment_lookup-csv.csv', header=1)
payment_df<jupyter_output><empty_output><jupyter_text>Checking dataThis data looks odd, let's check the size of the dataset, then how many lines have these Foo values Looking at the size of the dataset<jupyter_code>payment_df.shape<jupyter_output><empty_output><jupyter_text>Looking at how many rows have the value _Foo_ in the _payment_lookup_ column<jupyter_code>payment_df[payment_df['payment_lookup']=='Foo'].shape<jupyter_output><empty_output><jupyter_text>Checking the different values we have in each column<jupyter_code>payment_df['payment_lookup'].unique()
payment_df['payment_type'].unique()<jupyter_output><empty_output><jupyter_text>The _payment_type_ column has strange values considering it's relation with the _payment_lookup_ column.Let's check the cardinality of the columns.<jupyter_code>len(payment_df['payment_type'].unique())
len(payment_df['payment_lookup'].unique())<jupyter_output><empty_output><jupyter_text>There are very few lines with usefull information in the _payment_type_ column, all the rest have these Foo values. The _payment_type_ column also has strange values and has a very **high cardinality** .Looks like this dataset has the wrong values in it. We won't need this dataset anyways, since payment information is already included in the Trips dataset, so we will move to the next data file.  Vendors DataHere the data is also stored in a single CSV file.  Loading Data<jupyter_code>vendor_df = pd.read_csv('data-vendor_lookup-csv.csv')
vendor_df<jupyter_output><empty_output><jupyter_text>Checking dataThis dataframe luckly has only 5 rows so we can check it just by looking at it.<jupyter_code>vendor_df.shape<jupyter_output><empty_output><jupyter_text>Looks ok, not much to see here, we will move on to creating the local database just with the Trips dataset.  Creating Local DatabaseNow we will gather the datasets we need into a database with 1 table, the **Trips table**, with only the Trips dataset since it's the only one we will be needing. With this local database we can use SQL queries in the analysis.<jupyter_code>#Creates new local database if one doesn't already exist
conn = sqlite3.connect("Trips_and_vendors.db")


#Storing both dataframes in the database as tables, removing tables if they already exist, for pushing changes.

try: trips_df.to_sql('Trips', conn)
except Exception as e:
    
    if "Table 'Trips' already exists." in str(e):
        conn.cursor().execute('DROP TABLE Trips')
        trips_df.to_sql('Trips', conn)
        
    else: raise e



#Query to test if we can perform queries on the database
query = 'SELECT * FROM Trips'

#Reading the query result into a dataframe
temp_df = pd.read_sql(query, conn)
temp_df.head()<jupyter_output><empty_output><jupyter_text>Analysis and TasksNow that we have our local database, we will start analysing the data and answering the questions given for the technical test case.  Question 1_**What is the average distance traveled by trips with a maximum of 2 passengers?**_So we need to get the average distance traveled on trips that have **not more than 2 passengers**, very important detail on maximum of 2 passengers, since the _passenger_count_ column has 0 values. So we need to include these data entries too. Although it's weird to have trips with no passengers, we will just follow the question literally.<jupyter_code>#Query to get trip distance from trips with a maximum of 2 passengers
query = '''SELECT trip_distance 
FROM Trips
WHERE passenger_count <= 2
'''

#Reading the query result into a dataframe
temp_df = pd.read_sql(query, conn)

#Query to get the average of the trip distance, just to show knowledge of aggregating functions
query = '''SELECT AVG(trip_distance ) as avg_distance
FROM Trips
WHERE passenger_count <= 2
'''

#Reading the query result
avg = pd.read_sql(query, conn)['avg_distance'].values[0]

fig = px.histogram(temp_df, x='trip_distance', y='trip_distance', title='Max 2 Passengers Trip Distance Distribution', nbins=70)
fig.add_vline(x=avg, line_dash = 'dash', line_color = 'firebrick',annotation_text=f"Mean: {avg}")
fig.show()<jupyter_output><empty_output><jupyter_text>Question 2_**Which are the 3 biggest vendors based on the total amount of money raised?**_So we need to sum the total ammount of money raised by each vendor and compare them. Total amount of money collected per trip can be found in the _total_amount_ column. This could be a trick question, since tip money probably doesn't go to the vendors, but for the purpose of getting straight to the point, and since this wasn't specified, we won't dive into the matter.<jupyter_code>#Query to get the total amount of money raised per vendor, then limiting it to the top 3 vendors
query = '''SELECT vendor_id, SUM(total_amount) as total_money_raised
FROM Trips
GROUP BY vendor_id
ORDER BY total_money_raised desc
LIMIT 3
'''

#Reading the query result into a dataframe
temp_df = pd.read_sql(query, conn)

fig = px.bar(temp_df, x='vendor_id', y='total_money_raised', title='3 Biggest Money Raising Vendors ')
fig.show()<jupyter_output><empty_output><jupyter_text>Here we can see that **VTS** and **CMT** are by far the strongest vendors in NYC, towering over the third biggest vendor  Question 3_**Make a histogram of the monthly distribution over 4 years of rides paid with cash**_First we will check if the data really has 4 years of data in it, then we do the histogram<jupyter_code>query='''SELECT pickup_datetime
FROM Trips
'''

#Reading the query result into a dataframe
temp_df = pd.read_sql(query, conn)

#Creating a list of years in the dataset, parsing the datetime format and getting the year of the datetime object
datetimes = [datetime.strptime(c,'%Y-%m-%d %H:%M:%S').year for c in temp_df['pickup_datetime']]

#Removing duplicates from list
datetimes = list(set(datetimes))
datetimes<jupyter_output><empty_output><jupyter_text>So we do really have 4 years of dataNow we need to check the unique values in the payment type column to see how we will get payments made in cash, since it could be written in different ways, like cash and money.<jupyter_code>query='''SELECT DISTINCT(payment_type)
FROM Trips
'''

#Reading the query result into a dataframe
temp_df = pd.read_sql(query, conn)
temp_df<jupyter_output><empty_output><jupyter_text>So _cash_ is written in full uppercase also, we need to handle that as we query the data we need.Now to make the histogram plot, for each month, of the total amount of rides paid with cash. We transform the _payment_type_ column into a boolean column 1 values for cash, this way the histogram will sum the column values and give us the count of cash payments made, as we want.<jupyter_code>#Query to get the rows where payment was made with cash, and get the month that payment was made
query='''SELECT payment_type, strftime('%m', pickup_datetime) as month
FROM Trips
WHERE lower(payment_type) like "%cash%"
ORDER BY month asc
'''

#Reading the query result into a dataframe
temp_df = pd.read_sql(query, conn)

#Making the column into 1 values to be able to make the histogram work, can be interpreted as boolean for Cash 
temp_df['payment_type'] = 1

fig = px.histogram(temp_df, x='month', y='payment_type', title='Trips paid with Cash Distribution by Month ')
fig.show()<jupyter_output><empty_output><jupyter_text>We can see that cash payment drops **drastically** near the holidays, interesting. Probably because of all the spending in the holidays.  Question 4_**Make a time series chart computing the number of tips each day for the last 3 months of2012**_Here we will need to get data ranging between the last three months of 2012, count the rows where tips were given (tip>0) and aggregate the counting by date. Then plot it as a time series.But it looks like the data only goes to the near end of october, so we won't get the three last months of the year exactly. Instead we will get the last 3 months of the dataset.<jupyter_code>query='''
SELECT SUM(case when tip_amount>0 then 1 else 0 end) as n_tips, strftime('%Y-%m-%d', pickup_datetime) as date
FROM Trips
GROUP BY date
ORDER BY date desc


'''

#Reading the query result into a dataframe
temp_df = pd.read_sql(query, conn)

temp_df<jupyter_output><empty_output><jupyter_text>Here we will use a subquery to get the last date entry in our table, then use it to subtract 3 months to get the rows for the last 3 months of the year. We also need to sum the _tip_amount_ as 0 and 1 so we can count even the days where nobody gives tips, if there is such a day. If a tip is given in a day (tip > 0), it is summed in the total, if no tip is given in a day, then we count 0 tips on that day. If we were to use _WHERE_ for this, we could end up excluding days with no tips from our query.Then we group by each day and plot the data.<jupyter_code>query='''
SELECT SUM(case when tip_amount>0 then 1 else 0 end) as n_tips, strftime('%Y-%m-%d', pickup_datetime) as date
FROM Trips
WHERE date BETWEEN DATE(   (SELECT strftime('%Y-%m-%d', pickup_datetime) as date
                           FROM Trips
                           ORDER BY date desc
                           LIMIT 1),  "-3 months") 
                           
                        and
                        
                        (SELECT strftime('%Y-%m-%d', pickup_datetime) as date
                        FROM Trips
                        ORDER BY date desc
                        LIMIT 1) 
GROUP BY date
ORDER BY date


'''

#Reading the query result into a dataframe
temp_df = pd.read_sql(query, conn)

temp_df
x=temp_df['date']
y=temp_df['n_tips']


fig = go.Figure(data=go.Scatter(x=x, y=y, name='Number of tips p/day'))
y=temp_df['n_tips'].rolling(5).mean()

fig.add_trace(go.Scatter(x=x, y=y, name='M.A. 5 days'))
fig.show()<jupyter_output><empty_output><jupyter_text>Since data seems very bouncy and volatile I took the freedom to plot a 5 day moving average to better see the trend of the data.  Bonus TasksHere are the solutions to the bonus tasks of the test case  Bonus 1_**What is the average trip time on Saturdays and Sundays**_This is a query that needs alot of datetime manipulation. We need to get the difference between _pickup_datetime_ and _dropoff_datetime_. We also need to get the day of the week from this datetime. We will be showing the trip time in minutes<jupyter_code>query='''SELECT AVG((julianday(dropoff_datetime) - julianday(pickup_datetime))* 24 * 60) as avg_trip_time,
            cast (strftime('%w', pickup_datetime) as integer) as week_day
FROM Trips
WHERE week_day == 0 or week_day==6
GROUP BY week_day
'''

#Reading the query result into a dataframe
temp_df = pd.read_sql(query, conn)

#Labeling the days of the week, that are returned as integers by the query
temp_df.loc[temp_df['week_day']==0,'week_day'] = 'Sunday'
temp_df.loc[temp_df['week_day']==6,'week_day'] = 'Saturday'

#Getting the highest average trip time between both days
avg = temp_df['avg_trip_time'].max()

fig = px.bar(temp_df, x='week_day', y='avg_trip_time', title='AVG trip time in minutes by day')
fig.add_hline(y=avg, line_dash = 'dash', line_color = 'firebrick',annotation_text=f"Highest avg: {avg}")
fig.show()<jupyter_output><empty_output><jupyter_text>As we can see there is a minuscule difference between the average trip time in both days   Bonus 2  _**Make a latitude and longitude map view of pickups and dropoffs in the year 2010**_  This is a complicated task, since there are millions of data points and this can cause problems rendering our map if we don't use some nice hardware. Luckly we have the _datashader_ module that can add datapoints to images as pixels, creating a fast and light plot. Perfect for big data.Query to get all the coordinates we need for the year 2010<jupyter_code>query='''SELECT pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude, strftime("%Y",pickup_datetime) as year
FROM Trips
WHERE year = "2010"
'''


#Reading the query result into a dataframe
temp_df = pd.read_sql(query, conn)<jupyter_output><empty_output><jupyter_text>Now we will modify our dataframe so that we have all latitude and longitude coordinates, from pickups and dropoffs, in the same pair of columns. Then we will create a column which tells us if the coordinates are for a dropoff or a pickup.<jupyter_code>from datashader.utils import lnglat_to_meters as webm
from functools import partial
from datashader.utils import export_image
from datashader.colors import colormap_select, Greys9
from IPython.core.display import HTML, display




#Creating a new dataframe where we will store all coordinates
newdf = pd.DataFrame()
newdf['latitude'] = temp_df['pickup_latitude']
newdf['longitude'] = temp_df['pickup_longitude']

#Categorizing coordinates
newdf['type'] = 'pickup'


#Dataframe for all dropoffs
drop_df=pd.DataFrame()
drop_df['latitude'] = temp_df['dropoff_latitude']
drop_df['longitude'] = temp_df['dropoff_longitude']
drop_df['type'] = 'dropoff'

#Changing the index, since we cant append Series objects with equal index
drop_df.index = [c for c in range(newdf.shape[0], newdf.shape[0]+drop_df.shape[0])]

#Appending the dropoff dataframe to the pickup dataframe
newdf = newdf.append(drop_df)

del drop_df

#Setting a categorical column for our categories
newdf['categorical'] = pd.Categorical(newdf['type'])

#Filtering just in case
types=['pickup','dropoff']
newdf = newdf[newdf['type'].isin(types)]

#Changing the type of coordinates to suit the needs of Datashader
lon = newdf['longitude']
lat = newdf['latitude']
newdf.loc[:, 'easting'], newdf.loc[:, 'northing'] = webm(lon,lat)
newdf.head()<jupyter_output><empty_output><jupyter_text>Here we will make the map view for the dropoffs and pickups. In **fuchsia** color pixels we have the dropoffs and in **aqua** pixels we have the pickups. We can see how dropoffs are very scattered around and pickups are very focused in the main parts of the city, like avenues and streets.<jupyter_code>#image resolution
plot_width = int(1000)
plot_height = int(1000)

#Background color
background = "black"


export = partial(export_image, background = background, export_path="export")
cm = partial(colormap_select, reverse=(background!="black"))

display(HTML("<style>.container { width:100% !important; }</style>"))

#Setting the borders of the map view. We have some outliers in our dataset that go far from NYC, which we don't want to affect the map view, so we use quantiles.
y_range_min = lat.quantile(0.02)-0.10
y_range_max = lat.quantile(0.98)+0.10
x_range_min = lon.quantile(0.02)-0.10
x_range_max = lon.quantile(0.98)+0.10

#Passing the borders to the mapview
sw = webm(x_range_min,y_range_min)
ne = webm(x_range_max,y_range_max)
SF = zip(sw, ne)

#Color mapping for our categories, very important
color_key = {'dropoff':'fuchsia' , 'pickup': 'aqua'}


cvs = ds.Canvas(plot_width, plot_height, *SF)
agg = cvs.points(newdf, 'easting', 'northing',agg=ds.count_cat('categorical'))

img = export(tf.shade(agg, color_key =color_key, how='eq_hist'),"sf_biz_grey")
img<jupyter_output><empty_output><jupyter_text>Now we would just need to overlay this on a real map from NYC and it would be perfect, but we still have some things to cover and this took some time to pull off. Maybe later.  Bonus 3_**Simulate JSON data streaming and view using a real-time metric with the Trips Data**_So we will need a class to simulate a streaming of data, where we can pull a continuous stream of JSON data. For this I will create the Transaction class with the Connect method to pull data to start the streaming, and the Serialize class that returns a row of data in JSON string everytime it's called. Connect method can be seen as a method to restart the fake data stream.<jupyter_code>class Transaction:
    
    def __init__(self):
        pass
    
    def connect(self):
        """Function to connect to local database and pull the data necessary for the data streaming task
        """
        
        query = '''SELECT vendor_id, total_amount as total_money_raised, dropoff_datetime
                    FROM Trips
                    ORDER BY dropoff_datetime
                    
                '''
        
        try: conn.cursor()
        except: conn = sqlite3.connect("Trips_and_vendors.db")
        
        #Reading the query result into a dataframe
        self.df = pd.read_sql(query, conn)
        self.row = 0
        
        
        

    def serialize(self):
        """Function to mock retrieval of a row from a stream of data

        :raises AttributeError: Error for when the Transaction class doesn't have attributes, meaning the connect function 
        was not used.
        :return: A row of data from the class dataframe in JSON format, row by row as a stream.
        :rtype: json dictionary
        """        
        try:
            #restarts from row 0 if we reach end of dataframe
            if self.row == self.df.shape[0]: self.row = 0 

            #Gets a row slice of dataframe and transforms into json format
            data = self.df.iloc[self.row].to_json()
            self.row = self.row+1

            return data
        except AttributeError: raise AttributeError('No data to retrieve, please run the connect function first')
        
       
    
stream = Transaction()
stream.connect()

for _ in range(0,10):
    print(stream.serialize())<jupyter_output><empty_output><jupyter_text>And we will have to create a live plot of some metric to watch at real time as the serialize() function is called. We will be watching the total amount of money collected by the taxi trips for each timestamp along the data stream.For this we will be using Dash, unfortunately this requires running on a local machine, since Dash uses a local IP to host the live graph. Or at least I don't know a way to display the app in the jupyter notebook<jupyter_code>#Deques are lists that have a maximum length, and as soon as they reach that length, they start popping out the first entries to it.
#Perfect for our purposes
X = deque(maxlen=20)
Y = deque(maxlen=20)

#Total money that will be summed up over time as the data stream comes
total_money = 0

#starting stream
stream = Transaction()
stream.connect()



#Creating the Dash app for our live data plot
app = dash.Dash(__name__)

app.layout= html.Div([
    
    dcc.Graph(id='live-graph', animate=True),
    
    #interval of 1 second -> 1000 miliseconds
    dcc.Interval(id='graph-update', interval=1000, n_intervals=0)
    
    
])


#Callback function that updates our graph in the given interval
@app.callback(Output('live-graph','figure'),
              [Input('graph-update', 'n_intervals')])
def update_graph(*args):
    
    #Now, there probably is a better way to reference these variables, but just to make the plot work, let's 
    #leave it like this
    global X
    global Y
    global total_money 
    
    #Reading data from the stream
    data = stream.serialize()
    a_json = json.loads(data)
    
    #Summing up the money collected by taxi trips
    total_money = total_money + a_json['total_money_raised']
    
    #Appending our data to the deques
    Y.append(total_money)
    X.append(a_json['dropoff_datetime'])
    
    #Line plot for our data
    data=go.Scatter(x = list(X),
                y=list(Y),
                name='scatter',
                mode='lines+markers'
               )
    
    #Returns updated attributes for our figure/plot
    return {'data':[data],
            'layout': go.Layout(yaxis = dict(range=[min(Y), max(Y)]),
                               xaxis = dict(range=[X[0], X[-1]]))}
#Running Dash app
if __name__ == '__main__':
    app.run_server(debug=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08145840077695046, 'fraction_numerical': 0.007850437034639042, 'mean_word_length': 4.472320637732507, 'pattern_counts': {'":': 0, '<': 137, '<?xml version=': 0, '>': 144, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '33', 'dir': 'jupyter-structured-clean-dedup', 'id': '545995', 'license': 'MIT', 'n_tokens_mistral': 7602, 'n_tokens_neox': 7236, 'n_words': 2955, 'path': 'DataSprints_Test_Case.ipynb', 'repo_name': 'Iuryck/DataSprintsTestCase', 'size': '24712'}