<jupyter_start><jupyter_text>Case Introduction (15 minutes) The city of New York has seen a rise in the amount of accidents on the roads in the city. They would like to know if the number of accidents have increased in the last few weeks. For all the reported accidents, they have collected details for each accident and have been maintaining records for the past year and a half (From Jan 2018 to Aug 2019). They have now approached you to provide a few visualisations that would help them to identify the patterns in accidents if possible which would help them take preventive actions to reduce the number of accidents in the future. They have certain parameters like borough, time of day, reason for accident e.t.c based on which they would like to find whether**Your task is to format the given data and provide visualisations that would point out patterns in the accidents, if any**  Analytical Context You will be provided with a csv file containing details about each accident like date, time, location of the accident, a specific reason if there was one, type of vehicles involved, injury and death count e.t.c. The delimiter in the given csv file would be `;` instead of the default `,`. You will be performing the following tasks on the data:- The client wants you to develop these items in 2 individual modules:    1. Read, transform and get data ready for visualisation        - The client wants this module to be provided in a separate file, as they would like to use the code in multiple places        - This module should include            - Read the csv and calculate the total injured and dead people for each accident            - The updated file would be written as a JSON file            - Fetch data about population and area of each borough from the wikipedia page  - https://en.wikipedia.org/wiki/Boroughs_of_New_York_City    2. Perform analytics and visualisation operations on the data and identify patterns on the dataset.        - This module would read the JSON file you created in the first step and visualise the data        - You will be visualising data based on different parameters and find some pattern on the accidents  Data Manipulation  (25 minutes) The first step would be to write the module that reads the given csv file, calculate total injuries and death for each accident, and write the data as JSON. Since the code should be reusable, you will have to create a new `.py` file in the same folder as this notebook and write the following functions:- read_csv_file - This function will read the csv file, split the row based on given delimiter and return the list of rows as list of dictionaries- format_data - When passed with data from previous file, this function would calculate the total injuries and death for each row. This function should also combine the date and time columns in the dataset into a single column.- write_json_file - This function would be used to write the manipulated data into a `.json` file. Create a separate `data` folder (If it already doesn't exist) and place the json file inside the folder. Once you have written the 3 functions in your `.py` file, they have to called from here in order to perform the operations.<jupyter_code>import data_manipulation
rows = data_manipulation.read_csv_file('accidents.csv', delimiter=';')
rows = data_manipulation.calculate_total_injuries_and_death(rows)
data_manipulation.write_json_file(rows)<jupyter_output>['DATE', 'TIME', 'BOROUGH', 'ZIP CODE', 'LATITUDE', 'LONGITUDE', 'ON STREET NAME', 'NUMBER OF PEDESTRIANS INJURED', 'NUMBER OF PEDESTRIANS KILLED', 'NUMBER OF CYCLIST INJURED', 'NUMBER OF CYCLIST KILLED', 'NUMBER OF MOTORIST INJURED', 'NUMBER OF MOTORIST KILLED', 'CONTRIBUTING FACTOR VEHICLE 1', 'CONTRIBUTING FACTOR VEHICLE 2', 'CONTRIBUTING FACTOR VEHICLE 3', 'CONTRIBUTING FACTOR VEHICLE 4', 'CONTRIBUTING FACTOR VEHICLE 5', 'COLLISION_ID', 'VEHICLE TYPE CODE 1', 'VEHICLE TYPE CODE 2', 'VEHICLE TYPE CODE 3', 'VEHICLE TYPE CODE 4', 'VEHICLE TYPE CODE 5']
['DATE', 'TIME', 'BOROUGH', 'ZIP CODE', 'LATITUDE', 'LONGITUDE', 'ON STREET NAME', 'NUMBER OF PEDESTRIANS INJURED', 'NUMBER OF PEDESTRIANS KILLED', 'NUMBER OF CYCLIST INJURED', 'NUMBER OF CYCLIST KILLED', 'NUMBER OF MOTORIST INJURED', 'NUMBER OF MOTORIST KILLED', 'CONTRIBUTING FACTOR VEHICLE 1', 'CONTRIBUTING FACTOR VEHICLE 2', 'CONTRIBUTING FACTOR VEHICLE 3', 'CONTRIBUTING FACTOR VEHICLE 4', 'CONTRIBUTING FACTOR VEHICLE 5', 'COLLISION_[...]<jupyter_text>Data Extraction (20 minutes) Now that the format changes have been made to the data, next we will fetch the population and area of each borough from the wikipedia page - https://en.wikipedia.org/wiki/Boroughs_of_New_York_City<jupyter_code>response = requests.get('https://en.wikipedia.org/wiki/Boroughs_of_New_York_City')
soup = BeautifulSoup(response.text)

borough_data = {}
table_body = soup.find('table', {'class': ['wikitable', 'sortable', 'jquery-tablesorter']}).find('tbody')

for row in table_body.find_all('tr'):
    cells = row.find_all('td')
    if cells and len(cells) == 9:
        name = cells[0].text.strip().lower()
        population = float(cells[2].text.replace(',', '').strip())
        area = float(cells[5].text.strip())
        borough_data[name] = {
            'name': name,
            'population': population,
            'area': area
        }
        
borough_data<jupyter_output><empty_output><jupyter_text>For later usage, let's store the borough data into a json file, in the already created `data` folder.<jupyter_code>with open('data/borough_data.json', 'w+') as f:
    json.dump(borough_data, f)<jupyter_output><empty_output><jupyter_text>Read data with Pandas (15 minutes) We have successfully extracted the data from the wikipedia page and stored it in the above dictionary. This can be used later when we visualise data based on the boroughs. Next, let's read the JSON file that we wrote in the previous file. Since it was stored as a JSON file, we'll use `read_json` function of pandas to do that<jupyter_code>with open('data.json') as f:
    df = pd.read_json(f, orient='records')<jupyter_output><empty_output><jupyter_text>Let's go through the columns present in the dataframe.<jupyter_code>df.columns<jupyter_output><empty_output><jupyter_text>We have the following columns- **Borough** - The borough in which the accident occured- **COLLISION_ID** - A unique identifier for this collision- **CONTRIBUTING FACTOR VEHICLE (1, 2, 3, 4, 5)** - Reasons for the accident- **CROSS STREET NAME** - Nearest cross street to the place of accidents- **DATE** - Date of the accident- **TIME** - Time of accident- **DATETIME** - The column we previously created with the combination of date and time- **LATITUDE** - Latitude of the accident- **LONGITUDE** - Longitude of the accident- **NUMBER OF (CYCLIST, MOTORIST, PEDESTRIANS) INJURED** - Category wise injury- **NUMBER OF (CYCLIST, MOTORIST, PEDESTRIANS) KILLED** - Category wise death- **ON STREET NAME** - Street where the accident occured- **TOTAL INJURED** - Total injury from the accident- **TOTAL KILLED** - Total casualities in the accident- **VEHICLE TYPE CODE (1, 2, 3, 4, 5)** - Types of vehicles involved in the accident- **ZIP CODE** - zipcode of the accident location  Data Exploration (20 minutes) Now that we are familiar with the data, let's start doing some exploration on the given data and get some insights that can be useful for the customer. In cases like these, it's upto your decision making skill to determine the important columns / data points to look for in the given dataset. The client might give you indicators on some items they expect the results for (E.g. The client here is interested in researching things based on borough of the accident). Keeping that as the baseline, you should be trying to combine other columns present in the dataset to get proper correlations and dependencies which can be useful for the client.In any given dataset, if there is a date / time column present, that is the first item that we need to look for to see how the data is distributed, or how frequent the events are, or how they are spread out across a day. The first step on that would be to check how the data is distributed. Do we have records for all months of the year? Is it continous or discrete?<jupyter_code># Check whether the number of accidents have increased over time

monthly_accidents = df.groupby(df['DATETIME'].dt.to_period('M')).size()
monthly_accidents.plot.line()<jupyter_output><empty_output><jupyter_text>We can see that the data is continous and present for the entire year and a half. The sudden dip at the end of the graph could be attributed to the fact that the data is not present for the entire month of August 2019. When we think of the next step in the analysis, it makes sense to check how the number of accidents vary across the day. Do more accidents occur in the night? Or do they occur more during the peak hours of morning? Or in the evening? Let's find out.Since there is no specific column for the hour of the day of the accident, you will need to create a new column `HOUR` based on the data from the `DATETIME` column.<jupyter_code># Find out how the number of accidents varies across hours. 
# Are there more accidents in the night? Or during peak hours?
# Create a new hour column and group by
df['HOUR'] = df['DATETIME'].dt.hour
hourly_accidents = df.groupby('HOUR').size()
hourly_accidents.plot.bar()<jupyter_output><empty_output><jupyter_text>Seeing the graph gives us a clear idea that more accidents occur in the afternoon (2PM to 6PM) rather than in the night time or in the morning hours. Similar to hour of the day, another pattern we could look for is with the weekdays. Looking for patterns in the day of the week makes more sense in this case rather than something like day of month. Because, people have a different routine of travel on the first five days of the week (Monday through Friday), but not on the weekends (Saturday and Sunday). But the same does not apply for the day of the month. Your daily travel routine is seldom affected based on whether it's the first of the month or the 10th of the month. Let's check if there are more accidents on the weekdays or weekends<jupyter_code>df['WEEKDAY'] = df['DATETIME'].dt.weekday
weekday_accidents = df.groupby('WEEKDAY').size()
weekday_accidents.plot.bar()<jupyter_output><empty_output><jupyter_text>We can see that Weekends have a relatively lesser accident count in New York. And Fridays seem to have the most number of accidents.  Injury vs Death count (10 minutes) After datetime of the accidents, one other thing that catches our eye is the injured and death count in the accidents. How do these counts correlate? Does the number of deaths increase proportionally with the injury count?<jupyter_code>plt.scatter(df['TOTAL INJURED'], df['TOTAL KILLED'], s=10)
plt.title('Injured vs killed count')
plt.xlabel('Injured')
plt.ylabel('Killed')<jupyter_output><empty_output><jupyter_text>We can see from the above that accidents have had very less death count compared to the injured count, even when the injury count has raised to more than 20. The maximum number of deaths in a single accident is 2.  Borough based analysis (15 minutes) Next let's get into the borough based analysis. The basic step would be find out how the number of accidents vary across boroughs<jupyter_code>boroughs = df.groupby('BOROUGH').size()
boroughs.plot.bar()<jupyter_output><empty_output><jupyter_text>We can see that Brooklyn and Queens have a very high number compared to the other 3. Is this only because they are the largest boroughs in the city and hence have more chances of accidents? Or is there any other specific reason for this higher accident count? Let's use the area data we collected before to verify the same. In order to do that, check if the keys / names of the boroughs match between the borough data we collected and the dataframe we have. If there are mismatches in the name, fix it by changing the borough name in the key of the `borough_data` dictionary. We are doing this to easily map the accident count and the area of the borough in the next step.<jupyter_code># Update keys in borough data
print(borough_data.keys())
print(df['BOROUGH'].unique())

# Since there are differences in the text used in the data and Wikipedia data, let's update it
borough_data['bronx'] = borough_data.pop('the bronx')<jupyter_output>dict_keys(['the bronx', 'brooklyn', 'manhattan', 'queens', 'staten island'])
['BRONX' 'BROOKLYN' 'QUEENS' 'MANHATTAN' 'STATEN ISLAND']<jupyter_text>We have now got the keys to match in the dictionary and the dataframe. The difference in case can be handled by making the mapping action case insensitive. This can be done by either converting the dictionary keys to upper, or the dataframe data to lower.Let's do that and plot `accidents_per_sq_km`, which is the number of accidents / area of the borough<jupyter_code>borough_frame = pd.DataFrame(boroughs)
borough_frame.columns = ['count']
borough_frame['borough'] = borough_frame.index

borough_frame['accidents_per_sq_km'] = borough_frame.apply(lambda x: x['count'] / borough_data[x['borough'].lower()]['area'], axis=1)
borough_frame.plot.bar(x='borough', y='accidents_per_sq_km')<jupyter_output><empty_output><jupyter_text>When looking at the `accidents_per_sq_km` parameter, Manhattan tops that list by a very large number. This clearly shows that even though Brooklyn and Queens have more number of accidents, there is a problem with Manhattan where the number of accidents are way higher for it's small area.   Borough and Hour analysis (15 minutes) We have made separate analysis on how the data varied across boroughs, and how it varied across time. Let's combine both of them and see whether there are different patterns based on time for each borough. This should be done by grouping all the records based on the borough and hour of the day, and use seaborn to plot different bar graphs for each of the boroughs<jupyter_code>df1 = pd.DataFrame({'count': df.groupby(['BOROUGH', 'HOUR']).size()})
df1 = df1.reset_index()

chart = sns.FacetGrid(df1, col='BOROUGH', margin_titles=True, col_wrap=3, aspect=2)
chart.map(sns.barplot, 'HOUR', 'count',)<jupyter_output>/Users/aswinmurugesh/codes/venv_python3_iot/lib/python3.7/site-packages/seaborn/axisgrid.py:715: UserWarning: Using the barplot function without specifying `order` is likely to produce an incorrect plot.
  warnings.warn(warning)<jupyter_text>We can see that in all the boroughs the accident count has increased around the afternoon time and then reduced after 6PM. But in Manhattan and Bronx you can see that there is not much of an increase as in Brooklyn or in Queens. And compartively, Staten Island seems to have the least number of accidents.  Cause of accident (15 minutes) The next interesting factor in the dataset could be the cause of the accident. What causes more number of accidents? Overspeeding? Driver carelessness? or is it Vehicle malfunction? Let's check it out.Now that the data is present in multiple columns (`CONTRIBUTING FACTOR VEHICLE 1 - 5`), we need to add up all the values to get the right picture.In order to do that, we need to get the sum of each contributing factor for each column, and then combine them together to get the overall sum of accidents for a contributing factor.<jupyter_code>combined_df = pd.DataFrame(columns=['factor', 'count'])
columns = ['CONTRIBUTING FACTOR VEHICLE 1', 'CONTRIBUTING FACTOR VEHICLE 2', 
           'CONTRIBUTING FACTOR VEHICLE 3', 'CONTRIBUTING FACTOR VEHICLE 4',
          'CONTRIBUTING FACTOR VEHICLE 5']
for column in columns:
    column_df = pd.DataFrame({'count': df.groupby(column).size()})
    column_df = column_df.reset_index()
    column_df.columns = ['factor', 'count']
    combined_df = pd.concat([combined_df, column_df], sort=True)
    
# Now that we have combined the sum of each column into a single dataframe, a groupby on that dataframe
# will give you the overall sum of each contributing factor
combined_df.groupby('factor')['count'].sum().sort_values(ascending=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07254755688176054, 'fraction_numerical': 0.005843590699987567, 'mean_word_length': 4.820188133140376, 'pattern_counts': {'":': 0, '<': 54, '<?xml version=': 0, '>': 54, 'https://': 3, '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': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '267882', 'license': 'MIT', 'n_tokens_mistral': 4609, 'n_tokens_neox': 4336, 'n_words': 2218, 'path': 'extended_cases/week1/week_1_extended_case_student/.ipynb_checkpoints/python_extended-checkpoint.ipynb', 'repo_name': 'japarra27/ds4a_2019', 'size': '16086'}