gorkemguneser / amsterdam-transportation-project Goto Github PK

Amsterdam Project - Transportation - Traffic Accidents
Archana Rao
Gorkem Guneser

Ironhack | Amsterdam | Data Analytics Bootcamp - 22-08-2020

• Project Description
• Questions & Hypotheses
• Dataset
• Database
• Workflow
• Organization
• Links

In this project of ours, we have made an attempt to analyze the data about the traffic accidents reported in 2019 in the city of Amsterdam. Road safety has always been a concern for us . Especially in these days of increasing traffic network , it is important that we must be aware of the nature of the accidents happening around us and make sure we drive responsibly and safely .

1. Circumstances/Conditions of Accidents
   What type of vehicles have met with the most number of accidents ? What was the condition of the road surface ,type of roads? We assumed that in a bike-friendly city like Amsterdam bikes/mopeds might have hit the worst no.of cases. That too , during rainy days in the narrow bended roads or so.

2. What could be the severity of accidents in most cases? Material damage or Injured or Fatal?
   We understand that as there isn't much of rash/rough/illegal driving here, most accidents must end up with material damages only.

3. What is the effect of alchohol consumption on driving and its after-effects?
   We think that most of the accidents caused by "drink and drive" should have happened during night time.

4. Which specific area in Amsterdam is most vulnerable to accidents?
   We don't have any idea, and hope to find it out from the available data.

We have considered the datasets from the website of Gemeente Amsterdam. These are accident reports from the police linked to the digital road network (the National Road Database, NWB). This national file contains information about all accidents on the roads in the Netherlands registered by the police.
https://data.amsterdam.nl/datasets/G6xF0loDvppzog/verkeersongevallen-bron-landelijk/

Our databases created by using 5 different tables. The tables are linked to each other by Foreign keys. Created ERD diagram can be found in the following link: https://drive.google.com/drive/folders/10N60jLtsn9Yz3ffKoXbi9Ehjar2a318s

• Choose a sub-topic on transportation ,search and explore for the datasets on the web.
  
• Organized our work with the help of a Trello board. Identify the To-do's , who will do what.
  
• Came up with questions, structured a database in SQL by preparing ERD diagram and SQL files .
  
• Complete Data Analysis using Python including data cleaning , wrangling , merging datasets and manipulation of data.
  
• Interpret the results /answers for the questions asked and any other meaningful insights.

We used Trello board to list and identify the tasks and divided our work accordingly. A git repository is created with a README and a gitignore file. The Master includes a main file , and two other individual python files to do an independent data analysis. The datasets used for analysis are placed in our local data folder. The SQL files , ERD diagram , presentation document and other docs if any are uploaded in the Google Drive.

Repository: https://github.com/gorkemguneser/Project_2_Amsterdam_dataptams2020
Presentation: https://docs.google.com/presentation/d/1hVciD6UOoo7xjAMPAo42wv0FiapTcR801B8GWmix720/edit?ts=5f3eb840#slide=id.g9128f68aea_1_10
Trello: https://trello.com/b/03sWTRj0/project-amsterdam-transportation
Raw files: https://drive.google.com/drive/folders/10N60jLtsn9Yz3ffKoXbi9Ehjar2a318s