<jupyter_start><jupyter_text>Assignment 1 - Exercise 3 (30%)  RiderlogIn this exercise we will use the RiderLog dataset, which contains all bicycle trips collected by RiderLog between 2010 and 2013 in the Greater Sydney region. The data is described [here on CityData](https://citydata.be.unsw.edu.au/layers/geonode%3AGreaterSydney_NSW_RiderLog_2010to2013).The original data is in a point format, where bicycle trips are divided into points which are centroids of cells in a 500m * 500m grid. In order to better explore the relationship between attributes of routes, we kept only one records for each RouteId. While sacrificing some of the spatial information, the dataset is now more easy for you to play with!Let's get started!**First, lets import pandas library and the RiderLog data. ****point_data** contains the original dataset, and the duplicates have been droped for you and stored in the variable **route_data**.<jupyter_code>import ???? as pd

point_data = pd.read_csv("https://citydata.be.unsw.edu.au/geoserver/wfs?typename=geonode%3AGreaterSydney_NSW_RiderLog_2010to2013&outputFormat=csv&version=1.0.0&request=GetFeature&service=WFS")

route_data = point_data.drop_duplicates(subset='RouteId') 
# Try printing out both point and route data to find out what does this line does.<jupyter_output><empty_output><jupyter_text>Let's explore the dataset first! Question 1: How many routes are there in the dataset?<jupyter_code>route_data.????<jupyter_output><empty_output><jupyter_text>Question 2: What is the mean, median and max value for distance?  Question 3: Have you observed anything abnormal in the dataset?(*Hint: check the minimum values*)<jupyter_code>route_data.????<jupyter_output><empty_output><jupyter_text>Question 4: How many numerical attributes are there in the dataset? *Hint:* Try to find the answer by getting the shape of the dataframe generated above by the *describe* method.<jupyter_code>route_data.????.????<jupyter_output><empty_output><jupyter_text>Question 5: How many of them are integer attributes?*Hint*: Apart from describing the dataset, Python also knows how to count unique values! See [value_counts()](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.value_counts.html) method.<jupyter_code>route_data.dtypes.????<jupyter_output><empty_output><jupyter_text>Now let's see if we can find out relationships between attributes! Question 6: How many records have a Duration larger than 60 mins?Remember to try out both the **shape** method and the new **value_counts** method!<jupyter_code>route_data[route_data['????'] > ??].shape
(route_data['Duration'] ?? 60).?????<jupyter_output><empty_output><jupyter_text>Question 7: For all records matching the descrition above, what is the minimum distance?**Hint:** To get information on a single column, you can **index that specific column** and perform something you have used in class. Yes! I'm talking about the *max* function. If you get stuck, try this [link](https://docs.python.org/3/library/functions.html)!<jupyter_code>min_distance_long_duration = ???(route_data[route_data['????'] > ??]['????'])
print('Minimum distance for records which have a duration larger than 60 mins is', min_distance_long_duration)<jupyter_output><empty_output><jupyter_text>You can also get this information by calling the *describe* method on a subset of the route_data.<jupyter_code>route_data[route_data['Duration'] > 60].????<jupyter_output><empty_output><jupyter_text>Have you noticed something abnormal? It seems that the rider didn't move a lot in that trip which lasted at least one hour!To exclude these outliers, we need to clean the data before doing further analysis. Question 8: Generate a dataset containing only routes which have:1. an average travel speed between 5 km/h and 50 km/h; and2. a duration longer than 60 minutes. To achieve this, let's try *loop* and *if* statements. (__This is not the best way to do it!__ But the purpose of this exercise is to get you familiar with loop and conditional statements.)The _drop_ method used in the code is explained [here](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.drop.html).<jupyter_code>long_duration_routes = ???? # Create a subset of route_data which meets condition 2.

print('The shape of the dataset before cleanning is: ', long_duration_routes.shape)

routes_to_be_dropped = ?? # Create a empty list to keep records of rows to be dropped
?? index, row ?? long_duration_routes.iterrows():
    if ???? or row.AvSpeed >= 50: 
        routes_to_be_dropped.append(index) # Add the indexes of rows to be dropped into the list
        
cleaned_dataset = long_duration_routes.????(labels=routes_to_be_dropped) # Drop the records by index

print('The shape of the dataset after cleanning is: ', cleaned_dataset.????)<jupyter_output><empty_output><jupyter_text>Question 9: How many recreational trips have a duration longer than 60 minutes?*Hint*: Use the dataset you cleaned above! Attribute *RidePurpos* denotes the purpose of the trip, and the value for recreational trips is *Recreation*.<jupyter_code>cleaned_dataset[cleaned_dataset['????'] ?? 'Recreation'].????<jupyter_output><empty_output><jupyter_text>Now it's time for you to try coding on your own! Please use the _'cleaned dataset'_ instead of _'route data'_ from now on. Question 10: What is the top speed for recreational trips with a female rider in the cleaned dataset? The cell below shows a one-line yet illegible solution.<jupyter_code>max(cleaned_dataset[cleaned_dataset['RidePurpos'] == 'Recreation'][cleaned_dataset[cleaned_dataset['RidePurpos'] == 'Recreation']['Gender'] == 'F']['TopSpeed'])<jupyter_output><empty_output><jupyter_text>Can you translate the code above into two or three lines of code so that people can better understanding your thinking?Don't worry if you need to use more lines! Our goal is to make the code legible, which will make people's lives easier!  Question 11: What are the top 10 most popular destinations for recreational trips in the cleaned dataset?*Hint:* You can try to let the notebook print them all (and count manually), or you can make use of this *[nlargest](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.nlargest.html)* method  Question 12: Make a scatter plot to see the distribution of the distance*Hint:* You can always go check [bar chart](https://plot.ly/python/bar-charts/) on plotly if you get stuck.<jupyter_code>import ????.graph_objs as go
from plotly.offline import init_notebook_mode, iplot

init_notebook_mode() # Plot data in the notebook not to a file

bined_distance = pd.cut(cleaned_dataset['Distance'], bins=20) # Distance are binned into 20 groups

# Use plotly to build a bar chart
???? = go.????(
    x=bined_distance.????().index,  # The pandas series method value_counts() returns counts of unique values
    y=bined_distance.????(),
    name='Distance Distribution'
)

# Define the layout
layout = go.Layout(title='Distance Distribution of cleaned dataset',
                   plot_bgcolor='rgb(230,230,230)', # Background colour in RGB (Red, Green, Blue) format
                   xaxis = dict(title='????'),  # Give axes proper titles
                   yaxis = dict(title='????')
                  )

# Combine the trace and the layout into a figure
fig = go.Figure(????=[trace], ????=layout)

# Try figure out what function needs to be called to plot the figure!
????(fig)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09946308724832215, 'fraction_numerical': 0.012080536912751677, 'mean_word_length': 5.623111111111111, 'pattern_counts': {'":': 0, '<': 49, '<?xml version=': 0, '>': 53, 'https://': 7, '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': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '657597', 'license': 'MIT', 'n_tokens_mistral': 2254, 'n_tokens_neox': 2104, 'n_words': 825, 'path': 'Assignment 1/Assignment 1 - Exercise 3.ipynb', 'repo_name': 'SimoneZLeao/Programmable-Cities-Block-1-Jupyter-notebooks', 'size': '7450'}