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

# # Before starting
#
# For the first point, We started downloading the yellow cab data for January 2018(yellow_tripdata_2018-01.csv). Our plan was to make our studies and analysis firstly for one month and then extend our results considering also other years.
# Our Data-set contains a really large amount of data so we opted to choose the simple pd.read_csv() module from pandas.
# After considering many options such as Dask DataFrames, we decided to use the pandas importing method together with the chucksize option. The solution to working with a massive file with eight milions of lines is to load the file in smaller chunks and analyze with the smaller chunks. Here is our code: 

import pandas as pd
def data_aggregator(path,columnnumber,chunksize):
    df_list = []
    for chunk in pd.read_csv(path,usecols=columnnumber, chunksize=chunksize):
        df_list.append(pd.DataFrame(chunk).dropna())
    result = pd.concat(df_list)
    del df_list
    return result
JanData="yellow_tripdata_2018-01.csv" #We define the path of our data(of January)
JanDF=data_aggregator(JanData,[1,2,4,7,16],10000)
print(JanDF.head())
#With this function we can easily import data we need. We select the columns and the dataset we desire and, as we said, 
#we use chunksize the make the function run faster(and to avoid memory problems). We have printed the head of the dataframe to
#show that everything was correct and the df contained columns corresponding to [1,2,4,7,16] indexs.

# After defining the function to import the data correctly, We were asked to merge our data with a .json file in the homework repo  ([.json](https://github.com/CriMenghini/ADM-2018/blob/master/Homework_2/taxi_zone_lookup.csv)). The data was embedd in the page source-code so we had to apply our knowledge in web-scraping ang get the lines of sourcecode we needed. We used the **Beautiful** package. We firstly collected all the location ids, then the boroughs, then the zones and finally the serving zones. We have done that noticing that in the sourcecode all the above information required where all starting with a <td> and we simply went trough all the values starting with it. After that we merged all together in a Pandas DataFrame called boroghFrame.

# +
import requests
from bs4 import BeautifulSoup
page = requests.get("https://github.com/CriMenghini/ADM-2018/blob/master/Homework_2/taxi_zone_lookup.csv")
soup = BeautifulSoup(page.content, 'html.parser')
ids=[]
bor=[]
zon=[]
srv_zon=[]
cell=0
for i in range(2,1327,5):    #Firstly We get all the location ids
    a=soup.find_all('td')[i].get_text()  
    ids.append(a)
    cell=cell+1
    
cell=0
for i in range(3,1328,5):    #Then all the boroughs
    a=soup.find_all('td')[i].get_text()
    bor.append(a)
    cell=cell+1
    
cell=0
for i in range(4,1329,5):   #After that we get al zones
    a=soup.find_all('td')[i].get_text()
    zon.append(a)
    cell=cell+1
cell=0
for i in range(5,1330,5):    #Finally we get all the seving zones
    a=soup.find_all('td')[i].get_text()
    srv_zon.append(a)
    cell=cell+1
# after getting all the informations needed we merged all the lists together
data_tuples = list(zip(ids,bor,zon,srv_zon))
boroughFrame=pd.DataFrame(data_tuples,columns = ["PULocationID", "Borough", "Zone", "srv_zon"]) #name columns assigned
boroughFrame['PULocationID']=boroughFrame['PULocationID'].apply(int) #We wanted to be sure that PuLocationId were all ints.



# -

# Then, we finally merged our Dataframes defining this function. We merged using the PULocation id column. **So we assumed that the LocationID in the .json file was equal to PULocationId in taxidataset.**

def data_aggregator2(df1,df2,oncolumns,jointype):
    result = pd.merge(df1, boroughFrame, on=oncolumns,how=jointype)
    return result
JanData="yellow_tripdata_2018-01.csv" #We define the path of our data(of January)
JanBoroughData=data_aggregator2(JanDF,boroughFrame,['PULocationID'],"inner")  
print(len(JanBoroughData))
del JanDF,boroughFrame,page,soup,ids,bor,zon,srv_zon 
#we also clean our memory deleting all the valuables we do not need. 

# We also noticed, exploring our dataset, that we had some values simply not reasonable and we decided to get rid of them. For example, for some rows the trip distance was equal to 0 or negative and that values would have impacted our analysis. We removed the Nan values from the datasets because we tought that those would have affected our results. We lost approximately 55000 rows

JanBoroughData = JanBoroughData[JanBoroughData.trip_distance >0]
print(len(JanBoroughData))

# Let's describe a litte bit our datasets. Our original dataset had 16 columns and over 8 millions(yes, millions) rows. It has all the information related to taxi in 7 so called 'Boroughs'. We decided to exclude from our analysis the 'EWR' and 'Unknown' boroughs because they had what appeared to be fictional values. For example, in EWR you can find a total price for the trip of 600$ for a 200 meters path. We have extended these hypothesis also to other monthsd
# Anyway,  We decided to import just the columns corresping to those indexes:**[1,2,4,7,16]**
#
# As we said we have only 5 Boroughs: Manhattan, Bronx, Brooklyn, Staten Island and Queens. The Borough with the maximum numbers of observation is undoubtly Manhattan, center of the economical life of New York. Bronx and Staten Island both have a really low number of observations.
#
# We have two columns, which will be used to calculate the time of the trip,  in a datetime format. They show date and time of pickup and dropoff. 
#
# The maximum amount money spent for a single taxi trip has been spent in Queens and corresponds to 3006 dollars fro a trip distance of 189483.84 miles. On average, Queens is the most expensive borough as well with a mean of 11.6 miles per trip. We will see that these result won't be confirmed in we consider 
# $$ \frac{totalamount}{tripdistance}$$
#

JanBoroughData.groupby(['Borogh']).max()

JanBoroughData.groupby(['Borogh']).mean()

StarcoderdataJupyterScriptsDedupFiltered