32:T564,## 3. Data and Data Quality In general we can say that our entire data is based on the year 2017. The first dataset we used was the "accidents_2017.csv" file. It was published directly by the city administration and therefore has a very good shape and detailed information. For each of the over 10000 accidents we have an ID, some location variables such as District/Neighborhood/Street, some time variables such as Day/Hour, some variables which describe the severity of the accident such as Number Victims/Injuries and finally the exact geographic coordinates of the accidents. The full data set can be seen below (loaded and stored in the variable "data_accidents"). However, the initial data was not in English, but in Spain, so we had to translate everything first. Next to this we checked our dataset thoroughly for the data quality and found some mistakes. We found out that the unique ID of an accident was used 4 times twice. Next to this we realized that there were unknown district names and neighborhood names which need to be filtered out. We also checked for the time variables such as Day, Month, Hour, Part of the Day that at least one of each possible value was present, so that the dataset gives a realistic and non biased picture. We also checked the distributions of the severity (Number Victims/Injuries) of the accidents, so that they are normal distributed.33:T5b2,#### Preprocessing and Setup The first chart shows the aggregated number of mild injuries for each district. The data of this bar chart is provided by the array "filtered_data_accidents_by_District" which utilises the Wrangler RollUp function for the aggregation on district-level. The second chart is a grouped bubble/circle chart which shows the number of mild injuries on the neighborhood level. The color of each circle is determined by the corresponding district (each district of Barcelona has about 5 neighborhoods). The data for this chart is stored in the array "filtered_data_accidents_by_District" which also utilizes the Wrangler RollUp function for aggregation on neighborhood-level. Hereby we have to mention that due to the use of the RollUp function the column with the district names for each neighborhood is deleted. Therefore we had to create the function "add_column_District_Name()" which adds this column back. Otherwise the coloring of the bubble chart wouldn't work. In order to make our chart interactive we applied tooltips and also created 3 filters which are between the two charts (so that you can see the impact of the filter directly on the two charts). We have a filter by month, a injuries filter which determines the severity (so only accidents with a minimum number of injuries were included) and finally a filter by the part of the day. For each of the filters we created a function which applies the filter on the arrays.34:T6c4,#### Preprocessing and Setup We first take a look at the population in each district and the corresponding accident rate. In the chart below you can see that we have a geographic map with the borders of each district. We were not able to replicate this map-view in Observable because it was already quite complicated to create this map in Tableau. What do we mean by this? Well, for the creation in Tableau, based an the dataset "accidents_2017.csv", we calculated the aggregated numbers for each district first. Then we downloaded and inserted the data of the unemployment rates from [Barcelona's website](https://opendata-ajuntament.barcelona.cat/data/dataset/est-atur-pes). Hence we had a dataset containing the number of accidents, the population and the unemployment rate for each district. After that we downloaded the border coordinates of each district from this [geographic data provider](https://vangdata.carto.com/tables/shapefiles_barcelona_distrito) in order to be able to create the geographic map view. The problem is that both the dataset containing the figures per distirct (in .csv format) and the dataset containing the borders per district (in .geojson format) need to merged in order to plot figures and coordinates on one map in Tableau. We achieved that by utilising this [Online GeoJson Converter](https://geoman.io/geojson-editor). The final merged file can be plotted in Tableau and we were finally able to create the map-view below. The left view is colored with a green tone according to the population per district and the right side is colored with a green according to the number of accidents per district. The file containing the merged data is imported below and stored in the object "data_geojson".35:T5d4,## 6. Discussion & Conclusion To sum up the relationships of our five key variables and the accidents we can say that at the weekend accidents are less likely, however the proportion of accidents at night is increased. Next to this we showed that there is a strong positive trend between the population and the number of accidents as well as the accident rate and the employment rate. Furthermore, we explored a positive relation between the immigration rate and the accident rate. Finally we found out that there is a slightly positive link between the accident rate and the life expectancy, which is quite surprising because normally you would expect the opposite. Our conclusion is that we explored and presented many interesting relationships and correlations between the variables and the traffic accidents in Barcelona. But hereby we have to keep in mind that we can always only talk about correlations and relationships, but never about direkt causations. We can say that on average if there is a high employment rate that there is also a high accident rate (correlation). But we can not say that if we increase the employment rate we would also increase the accident rate (causation). However, we hope that we have contributed our part to Barcelonas Digitalization Project and that we presented some helpful insights. And maybe, if somebody from the city administration reads this report he/she can include our findings in the next adjustments of the city safety/improvement plan. 36:[{"id":"1886d46009571c6241ed17362a20c3095b3eee33dbeeb517aa637a1d0043efafae789ae3666862ec054453ba41031c9d08636d585b9ced13d3e388544361f198","href":"https://static.observableusercontent.com/files/1886d46009571c6241ed17362a20c3095b3eee33dbeeb517aa637a1d0043efafae789ae3666862ec054453ba41031c9d08636d585b9ced13d3e388544361f198","name":"Comparison.png","type":"image/png","lastModified":1656609505692.642,"size":1082818},{"id":"5f3c3c69032533da9a40bcf64b66d3971a9c524b47d03f9096e9dbae050a74ab38d4dff38a2d8e8e8a150a60176480676e2f0b2e165279147e4bf1488b07201f","href":"https://static.observableusercontent.com/files/5f3c3c69032533da9a40bcf64b66d3971a9c524b47d03f9096e9dbae050a74ab38d4dff38a2d8e8e8a150a60176480676e2f0b2e165279147e4bf1488b07201f","name":"Screenshot 2022-06-23 180742.png","type":"image/png","lastModified":1656609505692.642,"size":35248},{"id":"607633b4812a447c42f7ce0d482e2f670429b8dfc93f9804ce13e996b1ae00a49a045d5881abeeed0c8e6836f09733b09222f884a19ea8e43571067961376518","href":"https://static.observableusercontent.com/files/607633b4812a447c42f7ce0d482e2f670429b8dfc93f9804ce13e996b1ae00a49a045d5881abeeed0c8e6836f09733b09222f884a19ea8e43571067961376518","name":"Screenshot 2022-06-23 180710@1.png","type":"image/png","lastModified":1656609505692.642,"size":27295},{"id":"a69d137456e85a4d39feb5cecf0e2fa2617efbe5bb48e7b886f752b6a61fe496782723b0a2633f06d2a4fbce01e7e57ded97d6b375778849649ac84a7a052429","href":"https://static.observableusercontent.com/files/a69d137456e85a4d39feb5cecf0e2fa2617efbe5bb48e7b886f752b6a61fe496782723b0a2633f06d2a4fbce01e7e57ded97d6b375778849649ac84a7a052429","name":"Screenshot 2022-06-25 185328.png","type":"image/png","lastModified":1656609505692.642,"size":57071},{"id":"0a0ebac1c319dbfd335b5a27d93c754df8267a92cce432af0c7c663cfba5eb31669fca7cb54f172b064896e1b4679effe9a32c8e85b644c4cc515ded0cfba1ef","href":"https://static.observableusercontent.com/files/0a0ebac1c319dbfd335b5a27d93c754df8267a92cce432af0c7c663cfba5eb31669fca7cb54f172b064896e1b4679effe9a32c8e85b644c4cc515ded0cfba1ef","name":"population.csv","type":"text/csv","lastModified":1656609505692.642,"size":4559248},{"id":"cc78292ae8c2484856f72597fabdc1bce738fa8174d52b05e819cfd65013e2a87ab99bc5df98548088f4a7933290b743f407d2bff86a9e49840583bb9b9ac8b6","href":"https://static.observableusercontent.com/files/cc78292ae8c2484856f72597fabdc1bce738fa8174d52b05e819cfd65013e2a87ab99bc5df98548088f4a7933290b743f407d2bff86a9e49840583bb9b9ac8b6","name":"immigrants_emigrants_by_age.csv","type":"text/csv","lastModified":1656609505692.642,"size":275607},{"id":"1f0f178dfd36f223709fafded19b58290398964927873aa747f085bbe583e6994d0d649a85e63cca9cace418ca37d07c8346a6ff6884ab54da9a8d4857ccc60e","href":"https://static.observableusercontent.com/files/1f0f178dfd36f223709fafded19b58290398964927873aa747f085bbe583e6994d0d649a85e63cca9cace418ca37d07c8346a6ff6884ab54da9a8d4857ccc60e","name":"life_expectancy.csv","type":"text/csv","lastModified":1656609505692.642,"size":7141},{"id":"b32fc3665e8adbc7cbbe24ab3af0d3e51a530d6e1fd35b1f4a2327bbe17416f820e06de356934c017b0aa5275469f510a2e44966f781d25a05a1d5fea5143b8a","href":"https://static.observableusercontent.com/files/b32fc3665e8adbc7cbbe24ab3af0d3e51a530d6e1fd35b1f4a2327bbe17416f820e06de356934c017b0aa5275469f510a2e44966f781d25a05a1d5fea5143b8a","name":"accidents_2017.csv","type":"text/csv","lastModified":1656609505692.642,"size":1770231},{"id":"27088bec86cb3eb79c4c37607b2148e9cd1e2381302c5707c1be0e11096a888e709ecdb88947821f48dcb20610bf2f21726e067fc69b9d9f7e2de9e5b91b8ea0","href":"https://static.observableusercontent.com/files/27088bec86cb3eb79c4c37607b2148e9cd1e2381302c5707c1be0e11096a888e709ecdb88947821f48dcb20610bf2f21726e067fc69b9d9f7e2de9e5b91b8ea0","name":"Map with Scatter.png","type":"image/png","lastModified":1656609505692.642,"size":737366},{"id":"cdc86b38f70882c8558586d183240df46fdcf27cabda5cdb290bba95db0d2cfd53550bc8b20ca68ee567e5d35bb1e6aceaf795feb2772cd2bee864d6e1853150","href":"https://static.observableusercontent.com/files/cdc86b38f70882c8558586d183240df46fdcf27cabda5cdb290bba95db0d2cfd53550bc8b20ca68ee567e5d35bb1e6aceaf795feb2772cd2bee864d6e1853150","name":"Map and Data per District.geojson","type":"application/geo+json","lastModified":1656609505692.642,"size":93736},{"id":"471556a6222e84bc8ff397ffabbbcc6bb0ba7aab130aa8fe882f85fb15698add642d155eab034f89c35dd13544643e74a0912509a52a48535803bffef9391eb1","href":"https://static.observableusercontent.com/files/471556a6222e84bc8ff397ffabbbcc6bb0ba7aab130aa8fe882f85fb15698add642d155eab034f89c35dd13544643e74a0912509a52a48535803bffef9391eb1","name":"D Unemploymentrate.png","type":"image/png","lastModified":1656609505692.642,"size":486805},{"id":"ded7093de1b3ecc4f2bcc6ca5fe8631119c6c14c961965d710c58cea9567c1a8d922e097d8007500e81f15b0330f6117a24439aca48db8bc997336ae7049e2a5","href":"https://static.observableusercontent.com/files/ded7093de1b3ecc4f2bcc6ca5fe8631119c6c14c961965d710c58cea9567c1a8d922e097d8007500e81f15b0330f6117a24439aca48db8bc997336ae7049e2a5","name":"D Population.png","type":"image/png","lastModified":1656609505692.642,"size":521492},{"id":"d997f6d05fb4fe8d2d806eddef1b9b40cddd9b336a35f0267ef7a8efa3047ccc076fcfb530ba3d6adbbfb9265b402cc200957c8e82aacd6208fb93f903a49fdb","href":"https://static.observableusercontent.com/files/d997f6d05fb4fe8d2d806eddef1b9b40cddd9b336a35f0267ef7a8efa3047ccc076fcfb530ba3d6adbbfb9265b402cc200957c8e82aacd6208fb93f903a49fdb","name":"Part 1-Excel@1.csv","type":"text/csv","lastModified":1656609505692.642,"size":2358852}] 30:{"title":"Final Report - Analysing the Traffic Accidents in Barcelona.","cells":[{"id":0,"value":"# Final Report - Analysing the Traffic Accidents in Barcelona. ","pinned":false,"hidden":false,"mode":"md"},{"id":1159,"value":"toc({skip: 'Final Report Template'})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1079,"value":"## 1. Summary\nAs part of Barcelona's Digital Strategy in 2011 the city administration published a huge dataset containing public data related to the city. The aim of this project was to push modernisation, increase existing forecasting models and get a better understanding of the data. We also want to contribute in this process and support the government with their ambitious targets. In our final project we analysed the relationship between the traffic accidents in Barcelona and five key variables containing the Time, Population, Unemployment rate, Immigration rate and Life Expectancy. By presenting exciting and illustrative charts we showed that some variables have a positive relation, some a negative relation and some variables have nearly no correlation overall. Sounds interesting? -> Than keep reading...","pinned":false,"hidden":false,"mode":"md"},{"id":1086,"value":"## 2. Introduction\nWe all know the situation when we are sitting in the car and hear on the radio that there was a severe traffic accident, maybe even close to our current location. A very emotional moment, it scares us and we hope that there were no causalities and that there was nobody involved who we know. Maybe, if we think further we ask ourselves why traffic accidents happen and if there are even ways to limit them. Have you ever wondered what influences or at least correlates with traffic accidents? That's at least the question we dealt with as part of our final project. In the following we are trying to bring light in the darkness.","pinned":false,"hidden":false,"mode":"md"},{"id":1089,"value":"$32","pinned":false,"hidden":false,"mode":"md"},{"id":6,"value":"data_accidents_original = FileAttachment(\"accidents_2017.csv\").csv({ typed: true })","pinned":false,"hidden":false,"mode":"ojs"},{"id":1762,"value":"immigrants_emigrants_by_age = FileAttachment(\"immigrants_emigrants_by_age.csv\").csv()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1763,"value":"life_expectancy = FileAttachment(\"life_expectancy.csv\").csv()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1764,"value":"population = FileAttachment(\"population.csv\").csv()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1098,"value":"## 4. General Preprocessing\nAs mentioned in the Data and Data Quality part our dataset contains some general mistakes such as double existing IDs and unknown District Names. In the following Wrangler step and by use of the self-implemented function \"double_existing_id_fil-ter()\" we are going to filter this mistakes out...","pinned":false,"hidden":false,"mode":"md"},{"id":1108,"value":"double_existing_id_filter = function (array) {\n var index_IDs = new Object();\n var arr_filtered = []\n for (var i = 0; i <= array.length-1; i++) {\n if (array[i][\"Id\"] in index_IDs == false ) {\n index_IDs[array[i][\"Id\"]] = array[i][\"Id\"];\n arr_filtered.push(array[i])\n }\n }\n return arr_filtered\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1103,"value":"data_accidents = aq\n .from(double_existing_id_filter(data_accidents_original))\n .filter(d => d[\"District Name\"] !== \"Unknown\")\n .objects()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1753,"value":"For the district and neighborhood analysis, we created a different dataset by aggerating the original \"accidents_2017.csv\" file combined with the data from the \"population.csv\" file, the \"immigrants_emigrants_by_age.csv\" file and the \"life_expectancy.csv\" file. The resulting dataset is stored in the file \"Part 1-Excel.csv\". First, we cleaned the population data set by only keeping the population data by district and neighborhood for 2017, then we aggerated it into our \"accident_2017.csv\" file. After that, we applied the same technique for the \"immigrants_emigrants_by_age.csv\" and \"life_expectancy.csv\" files in order to extract the neighborhood and district level data. Finally, we created some new variables by dividing the immigration variables by the population variables at the neighborhood and district level. For more detailed information take a look into out tableau preparation file. Here we explained in depth and step by step how we preprocessed our data.","pinned":false,"hidden":false,"mode":"md"},{"id":1748,"value":"screenshot20220625185328 = FileAttachment(\"Screenshot 2022-06-25 185328.png\").image()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1116,"value":"## 5. Exploration, Analysis and Results\nWe divided our \"Exploration, Analysis and Results\" part in six sections according to our exploratory dashboard and the five key variables. In each section we are going to explore what's the relationship between the traffic accidents and the underlying key variable. With that said lets start with our first section and the exploratory dashboard.","pinned":false,"hidden":false,"mode":"md"},{"id":614,"value":"### 5.1 Exploration / Get a feeling for the data:\nIn the following we are going to create two of the three charts from our first dashboard from the submitted Tableau.twbx file. The first Tableau dashboard and the three charts from this section are kind of exploratory charts. The aim of this section is to get a feeling and a better understanding of the data.","pinned":false,"hidden":false,"mode":"md"},{"id":1204,"value":"$33","pinned":false,"hidden":false,"mode":"md"},{"id":850,"value":"monthly_filter_2 = function (array) {\n var arr_months = [\"January\", \"February\", \"March\", \"April\", \"May\", \"June\", \"July\", \"August\", \"September\", \"October\", \"November\", \"December\"];\n if (value_month_2===0) {\n return array;\n \n } else {\n var new_array = [];\n for (var i = 0; i <= array.length-1; i++) {\n if (array[i][\"Month\"] === arr_months[value_month_2-1]) {\n new_array.push(array[i]); \n }\n }\n return new_array;\n }\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":899,"value":"severety_filter = function (array) {\n var new_array = [];\n for (var i = 0; i <= array.length-1; i++) {\n if (array[i][\"Mild injuries\"] >= value_injuries) {\n new_array.push(array[i]); \n }\n }\n return new_array;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":920,"value":"partOfDay_filter = function (array) {\n if (value_partOfDay===\"All\" || value_partOfDay==null) {\n return array;\n \n } else {\n var new_array = [];\n for (var i = 0; i <= array.length-1; i++) {\n if (array[i][\"Part of the day\"] === value_partOfDay) {\n new_array.push(array[i]); \n }\n }\n return new_array;\n }\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":769,"value":"add_column_District_Name = function (array) {\n \n var dictionary = new Object();\n for (var i = 0; i <= data_accidents.length-1; i++) {\n if (data_accidents[i][\"Neighborhood Name\"] in dictionary == false && data_accidents[i][\"District Name\"]!=\"Unknown\" && data_accidents[i] \n [\"Neighborhood Name\"]!=\"Unknown\") {\n dictionary[data_accidents[i][\"Neighborhood Name\"]] = data_accidents[i][\"District Name\"];\n }\n }\n \n for (var x = 0; x <= array.length-1; x++) {\n array[x][\"District Name\"] = dictionary[array[x][\"Neighborhood Name\"]];\n }\n return array;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":618,"value":"filtered_data_accidents_by_District = \n aq.from(partOfDay_filter( severety_filter( monthly_filter_2( data_accidents ) ) ) )\n\t.select('District Name','Mild injuries')\n\t.groupby('District Name')\n\t.rollup({sum:d => op.sum(d[\"Mild injuries\"])})\n\t.filter(d => d[\"District Name\"] !== \"Unknown\")\n\t.orderby(aq.desc(\"sum\"))\n\t.rename({sum: \"Mild Injuries\"})\n\t.objects()","pinned":false,"hidden":false,"mode":"ojs"},{"id":752,"value":"filtered_data_accidents_by_Neighborhood = add_column_District_Name(\n aq.from(partOfDay_filter( severety_filter( monthly_filter_2( data_accidents ) ) ) )\n\t.select(\"District Name\", 'Neighborhood Name','Mild injuries')\n\t.groupby('Neighborhood Name')\n\t.rollup({sum:d => op.sum(d[\"Mild injuries\"])})\n\t.filter(d => d[\"Neighborhood Name\"] !== \"Unknown\")\n\t.orderby(aq.desc(\"sum\"))\n\t.rename({sum: \"Mild Injuries\"})\n\t.objects()\n)","pinned":false,"hidden":false,"mode":"ojs"},{"id":636,"value":"Plot.plot({\n width: 900,\n marks: [Plot.barY(filtered_data_accidents_by_District, { x: \"District Name\", y: \"Mild Injuries\", fill: \"District Name\", \n sort: {x: \"y\", reverse: true}, title: (d) => \"Mild Injuries: \"+d[\"Mild Injuries\"]})]\n \n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":859,"value":"viewof value_month_2 = slider({\n min: 0,\n max: 12,\n step: 1,\n value: 0,\n title: \"Filter by Month (For Location)\",\n description: \"Month 1-12, whereby 0 represents all months\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":867,"value":"viewof value_injuries = slider({\n min: 0,\n max: 10,\n step: 1,\n value: 0,\n title: \"Filter by Severety: Mild Injuries per Accident (For Location)\",\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":872,"value":"viewof value_partOfDay = Inputs.radio([\"All\", \"Morning\", \"Afternoon\", \"Night\"], {label: \"Filter by Part of Day: \", value:\"All\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":750,"value":"chart = BubbleChart(filtered_data_accidents_by_Neighborhood, {\n label: d => d[\"Neighborhood Name\"]+\": \"+d[\"Mild Injuries\"],\n value: d => d[\"Mild Injuries\"],\n group: d => d[\"District Name\"],\n width: 900,\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1216,"value":"Next to the two charts above we also created an interactive geographic map which depicts all the accidents from our dataset on the map. In Tableau this map-view can also be filter by the three filters. Each accident is presented by a dot, whereby the color is determined by the district and the radius by the number of mild injuries. This chart guarantees a great overview of the dataset, however it is only available in our Tableau Dashboard. We tried to replicate it in Observable by utilizing the GL-JS-MapBoxes library but we had difficulties with the API key which costs money.","pinned":false,"hidden":false,"mode":"md"},{"id":950,"value":"mapWithScatter1 = FileAttachment(\"Map with Scatter.png\").image()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1218,"value":"#### Results\nThe charts above clearly present that there is a huge variance between the number of accidents and the corresponding districts. The district Eixample has the highest number of mild injuries and within Eixample the neighborhood \"la Dreta de l'Eixample\" has the most injuries.","pinned":false,"hidden":false,"mode":"md"},{"id":609,"value":"### 5.2 Influence of Time:\nIn this section we focus on the relationship between the traffic accidents and the key variable time.","pinned":false,"hidden":false,"mode":"md"},{"id":1120,"value":"#### Preprocessing and Setup\nIn the following we are going to create the two charts from our second dashboard. In order to do so we have to plot a stacked area chart and a stacked bar chart. The two charts could be filtered by month, so we create a monthly slider and also the function \"monthly filter()\" which applies this filter on our dataset. For the first chart we have to create an array which stores the number of Mild Injuries for each Part of the Day and each Weekday. This array is called \"filtered_data_accidents_by-_partOfTheDay_weekday\" and utilizes the Wrangler Rollup function. The template and setup for this chart is copied from the user @zachl (see link below). The second chart shows the victims for each weekday per district. The created array for this chart is called \"filtered_data_accidents-_by_District_weekday\". Here the rollup/aggregation is done automatically by the imported D3-Plotting library from @mkfreeman. Both charts support Tooltips and are interactive due to the monthly filter.","pinned":false,"hidden":false,"mode":"md"},{"id":356,"value":"monthly_filter = function (array) {\n var arr_months = [\"January\", \"February\", \"March\", \"April\", \"May\", \"June\", \"July\", \"August\", \"September\", \"October\", \"November\", \"December\"];\n if (value_month===0) {\n return array;\n \n } else {\n var new_array = [];\n for (var i = 0; i <= array.length-1; i++) {\n if (array[i][\"Month\"] === arr_months[value_month-1]) {\n new_array.push(array[i]); \n }\n }\n return new_array;\n }\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":244,"value":"convert_data = function (array) {\n var arr_days = [\n \"Monday\",\n \"Tuesday\",\n \"Wednesday\",\n \"Thursday\",\n \"Friday\",\n \"Saturday\",\n \"Sunday\"\n ];\n var arr_nums = [\n \"1 Monday\",\n \"2 Tuesday\",\n \"3 Wednesday\",\n \"4 Thursday\",\n \"5 Friday\",\n \"6 Saturday\",\n \"7 Sunday\"\n ];\n for (var i = 0; i <= array.length-1; i++) {\n for (var x = 0; x <= arr_days.length-1; x++) {\n if (array[i][\"Weekday\"] === arr_days[x]) {\n array[i][\"Weekday\"] = arr_nums[x];\n }\n }\n }\n return array;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":26,"value":"filtered_data_accidents_by_partOfTheDay_weekday = convert_data(\n aq\n .from(monthly_filter(data_accidents))\n .select(\"Weekday\", \"Part of the day\", \"Mild injuries\")\n .groupby(\"Weekday\", \"Part of the day\")\n .rollup({ sum: (d) => op.sum(d[\"Mild injuries\"]) })\n .rename({ sum: \"Aggregated_Mild_Injuries\" })\n .orderby(\"Weekday\")\n .objects()\n)","pinned":false,"hidden":false,"mode":"ojs"},{"id":439,"value":"filtered_data_accidents_by_District_weekday = convert_data(\n aq\n .from(monthly_filter(data_accidents))\n .select(\"Weekday\", \"District Name\", \"Victims\")\n .objects()\n)","pinned":false,"hidden":false,"mode":"ojs"},{"id":329,"value":"viewof value_month = slider({\n min: 0,\n max: 12,\n step: 1,\n value: 0,\n title: \"Filter by Months (For Time)\",\n description: \"Month 1-12, whereby 0 represents all months\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":112,"value":"{\n let copy = Object.assign({}, template, {});\n copy.width = 600;\n copy.height = 300;\n copy.selection.sel_boro.on = \"mouseover\";\n return embed(copy);\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":491,"value":"Plot.plot({\n y: {\n grid: true\n },\n marks: [\n Plot.barY(filtered_data_accidents_by_District_weekday, Plot.groupX({y: \"count\"}, {x: \"Weekday\", fill: \"District Name\", \n title: (d) => `District: ${d[\"District Name\"]} \\n Aggregated Victims: `})),\n Plot.ruleY([0])\n ],\n color: {\n legend: true,\n columns: \"110px\",\n width: 640\n }\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":107,"value":"// See Source: https://observablehq.com/@zachl/a4-2-interactive-stacked-area-chart\n\ntemplate = ({\n $schema: \"https://vega.github.io/schema/vega-lite/v3.json\",\n data: { values: filtered_data_accidents_by_partOfTheDay_weekday },\n /*\"transform\" : [\n { \"filter\": { \"field\": \"date\", \"range\": [2006, 2018] } }\n ],*/\n description: \"1\",\n width: 800,\n height: 300,\n mark: { type: \"area\", line: true },\n\n // REPARE AQUI\n selection: {\n sel_boro: { type: \"single\" }\n },\n\n encoding: {\n x: {\n field: \"Weekday\",\n type: \"nominal\",\n axis: { title: \"Weekday\" }\n },\n y: {\n field: \"Aggregated_Mild_Injuries\",\n type: \"quantitative\",\n stack: \"zero\",\n axis: { title: \"Aggregated Mild Injuries\" }\n },\n\n // REPARE AQUI\n color: {\n condition: {\n selection: \"sel_boro\",\n field: \"Part of the day\",\n type: \"nominal\"\n },\n value: \"#EFEFEF\"\n }\n }\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":114,"value":"// See Source: https://observablehq.com/@zachl/a4-2-interactive-stacked-area-chart\n\nembed = require(\"vega-embed@3\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":1144,"value":"#### Results\nAs presented in the two charts above we can see that at the weekend the overall number of accidents diminishes strongly. The majority of people don't go to work, therefore the number of cars on the streets is reduced and hence the number of accidents as well. However, the left chart also presents that the number of accidents which happened at night increase at the weekend (probably because many people make party and drive more risky at night at weekend).","pinned":false,"hidden":false,"mode":"md"},{"id":937,"value":"### 5.3 Influence of Population:\nIn this section we focus on the relationship between the traffic accidents and the population first. Based on this we will derive a accident ratio which allows us to explore the link between the unemployment rate and the accident rate.","pinned":false,"hidden":false,"mode":"md"},{"id":1232,"value":"$34","pinned":false,"hidden":false,"mode":"md"},{"id":1062,"value":"dPopulation = FileAttachment(\"D Population.png\").image()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1236,"value":"The scatter plot below was created by deriving the figures for each district from the \"data_geojson\" array. For this purpose we implemented the function \"get_and_calc_data_geojson()\". Then we defined a linear regression object and plotted the trendline. The result is a plot presenting the population in each district and the corresponding number of accidents.","pinned":false,"hidden":false,"mode":"md"},{"id":957,"value":"data_geojson = FileAttachment(\"Map and Data per District.geojson\").json()","pinned":false,"hidden":false,"mode":"ojs"},{"id":968,"value":"get_and_calc_data_geojson = function (array) {\n var array_final = [{}, {}, {}, {}, {}, {}, {}, {}, {}, {}];\n for (var i = 0; i <= array.length-1; i++) {\n array_final[i][\"District Name\"] = array[i].properties[\"n_distri\"]\n array_final[i][\"Number Accidents\"] = array[i].properties[\"Number of Accidents\"]\n array_final[i][\"Population\"] = array[i].properties[\"Population\"]\n array_final[i][\"Employmentrate\"] = 100 - array[i].properties[\"Unemploymentrate\"]\n array_final[i][\"Accident Rate\"] = Math.round (array[i].properties[\"Number of Accidents\"] / array[i].properties[\"Population\"] * 100) / 100\n }\n return array_final\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":973,"value":"data_by_district = get_and_calc_data_geojson(data_geojson[\"features\"])","pinned":false,"hidden":false,"mode":"ojs"},{"id":987,"value":"regression = require('d3-regression')","pinned":false,"hidden":false,"mode":"ojs"},{"id":996,"value":"linearRegression1 = regression.regressionLinear()\n .x(d => d[\"Population\"])\n .y(d => d[\"Number Accidents\"])","pinned":false,"hidden":false,"mode":"ojs"},{"id":993,"value":"trendLine1 = linearRegression1(data_by_district)","pinned":false,"hidden":false,"mode":"ojs"},{"id":1002,"value":"Plot.plot({\n marks: [\n Plot.dot(data_by_district, {\n x: \"Population\",\n y: \"Number Accidents\",\n r: 6,\n opacity: 0.9,\n fill: \"green\",\n title: \"District Name\"\n }),\n Plot.link([0], {\n x1: trendLine1[0][0],\n y1: trendLine1[0][1],\n x2: trendLine1[1][0],\n y2: trendLine1[1][1],\n stroke: \"grey\",\n })\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1243,"value":"#### Result\nAs presented in the map-view, dark green districts on the left view are also dark green on the right view (color tone is proportional). This visual demonstration is also supported by the scatter plot containing a clear trendline. We have a R² (Coefficient of determination) of 0.598. If we square this number we get a strong positive correlation of 0.77 which is quite impressive. The bigger the population within a district, the more cars are in the districts and hence the number of accidents is increased. We have to keep this relationship in mind if we want to compare the accidents with the unemployment rate. Therefore, in the next section we will make use of the calculated accident rate, which is defined by the number of accidents divided by the population for each district. The unit hereby is: number of accidents per one million inhabitants.","pinned":false,"hidden":false,"mode":"md"},{"id":1251,"value":"### 5.3 Influence of Unemployment rate","pinned":false,"hidden":false,"mode":"md"},{"id":1256,"value":"#### Preprocessing and Setup\nAs already mentioned above, if we want to compare the accidents with the unemployment rate we can't use the number of accidents, but we have to use the accidents rate. This is done in two map-views below. The left side contains a red tone according to the employment rate per district and the right side contains a red tone according to the accident rate per district. The map-view below was generated in the same way as described in section [5.2]. ","pinned":false,"hidden":false,"mode":"md"},{"id":1031,"value":"linearRegression2 = regression.regressionLinear()\n .x(d => d[\"Employmentrate\"])\n .y(d => d[\"Accident Rate\"])","pinned":false,"hidden":false,"mode":"ojs"},{"id":1042,"value":"trendLine2 = linearRegression2(data_by_district)","pinned":false,"hidden":false,"mode":"ojs"},{"id":1065,"value":"dUnemploymentrate = FileAttachment(\"D Unemploymentrate.png\").image()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1046,"value":"Plot.plot({\n marks: [\n Plot.dot(data_by_district, {\n x: \"Employmentrate\",\n y: \"Accident Rate\",\n r: 6,\n opacity: 0.9,\n fill: \"#FA8322\",\n title: \"District Name\"\n }),\n Plot.link([0], {\n x1: trendLine2[0][0],\n y1: trendLine2[0][1],\n x2: trendLine2[1][0],\n y2: trendLine2[1][1],\n stroke: \"grey\"\n })\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1262,"value":"#### Result\nWe still have a trend, brighter red colors on the left map view are also brighter on the right map view. However, the trend is not as clear as at the green views above. This is also presented by the scatter plot below. The slope of the trendline is steeper, but the variance is also bigger which decreases the correlation. After applying linear regression again we can see that our R² is only 0.439 which result in a positive correlation of 0.66. The correlation is still there, but it is smaller than the correlation of the relationship with the population variable. If we think about it this connection also makes sense. If the employment rate in a district is higher there is probably more wealth. Wealthier people are able to buy more cars, so the car density within the district is increased which ultimately increases the accident rate.","pinned":false,"hidden":false,"mode":"md"},{"id":1182,"value":"### 5.4 Influence of Life expectancy\n","pinned":false,"hidden":false,"mode":"md"},{"id":1277,"value":"#### Preprocessing and Setup\nIn the following we are going to explore the link between the accident rate and the life expectancy. Hereby we loaded our data from the \"Part 1-Excel.csv\" file and created two scatter plots including a trendline.","pinned":false,"hidden":false,"mode":"md"},{"id":1475,"value":"data_prep = FileAttachment(\"Part 1-Excel@1.csv\").csv({ typed: true })","pinned":false,"hidden":false,"mode":"ojs"},{"id":1435,"value":"data_prep_district = aq.from(data_prep)\n .select(\n \"Life expectancy by district\",\n \"Accident Rate by District\",\n \"District Name\"\n )\n .dedupe(\"District Name\")\n .objects()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1506,"value":"linearRegression_district = regression.regressionLinear()\n .x((d) => d[\"Accident Rate by District\"])\n .y((d) => d[\"Life expectancy by district\"])","pinned":false,"hidden":false,"mode":"ojs"},{"id":1511,"value":"trendLine_district = linearRegression_district(data_prep_district)","pinned":false,"hidden":false,"mode":"ojs"},{"id":1562,"value":"data_prep_neighborhood = aq.from(data_prep)\n\t.select('Average','Accident Rate by Neighborhood ','Neighborhood Name')\n\t.rename({Average: \"Life Expectancy\"})\n\t.dedupe('Neighborhood Name')\n .objects()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1772,"value":"linearRegression_neighborhood = regression.regressionLinear()\n .x((d) => d[\"Life Expectancy\"])\n .y((d) => d[\"Accident Rate by Neighborhood \"])","pinned":false,"hidden":false,"mode":"ojs"},{"id":1776,"value":"trendLine_neighborhood = linearRegression_neighborhood(data_prep_neighborhood)","pinned":false,"hidden":false,"mode":"ojs"},{"id":1440,"value":"Plot.plot({\n marks: [\n Plot.dot(data_prep_district, {\n x: \"Accident Rate by District\",\n grid: true,\n y: \"Life expectancy by district\",\n\n r: 10,\n opacity: 0.9,\n fill: \"#FA8322\",\n title: \"District Name\"\n }),\n Plot.link([0], {\n x1: trendLine_district[0][0],\n y1: trendLine_district[0][1],\n x2: trendLine_district[1][0],\n y2: trendLine_district[1][1],\n stroke: \"grey\"\n })\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1565,"value":"Plot.plot({\n x: { domain: [78, 85] },\n y: { domain: [0, 0.04] },\n marks: [\n Plot.dot(data_prep_neighborhood, {\n x: \"Life Expectancy\",\n grid: true,\n y: \"Accident Rate by Neighborhood \",\n r: 4,\n opacity: 0.5,\n fill: \"#FA8322\",\n title: \"Neighborhood Name\"\n }),\n Plot.link([0], {\n x1: trendLine_neighborhood[0][0],\n y1: trendLine_neighborhood[0][1],\n x2: trendLine_neighborhood[1][0],\n y2: trendLine_neighborhood[1][1],\n stroke: \"grey\"\n })\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1281,"value":"#### Result\nBy utilizing our created data set \"Part 1-Excel.csv\" we plotted the correlation between the life expectancy and the accident rate by district and neighborhood. In general, we found little evidence between the life expectancy and the accident rate at district or neighborhood level. We see no clear pattern or any distinct trend in this life expectancy data . There are certainly more stronger factors which kill people and influence the life expectancy than the accident rate. For comparison, each year in Germany there are 240000 people dying due to cancer, but only 3000 deaths due to traffic accidents. ","pinned":false,"hidden":false,"mode":"md"},{"id":1668,"value":"### 5.5 Influence of Immigration","pinned":false,"hidden":false,"mode":"md"},{"id":1933,"value":"#### Preprocessing and Setup\nIn this section we want to examine the relationship between the migration rates and the accident rates. We again use the created dataset \"Part 1-Excel.csv\" in order to plot a scatterplot with a trendline. For the other combined bar- and line chart we were not able to replicate them due to dimensionality issues with the dual axis.","pinned":false,"hidden":false,"mode":"md"},{"id":1586,"value":"data_migration = aq\n .from(data_prep)\n .select(\n \"Immigrant/Population by Neighborhood\",\n \"Accident Rate by Neighborhood \",\n \"Neighborhood Name\"\n )\n .filter((d) => d[\"Accident Rate by Neighborhood \"] < 0.25) // Remover outlier\n .dedupe(\"Neighborhood Name\")\n .objects()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1616,"value":"linearRegression_migration = regression.regressionLinear()\n .x((d) => d[\"Immigrant/Population by Neighborhood\"])\n .y((d) => d[\"Accident Rate by Neighborhood \"])","pinned":false,"hidden":false,"mode":"ojs"},{"id":1622,"value":"trendLine_migration = linearRegression_migration(data_migration)","pinned":false,"hidden":false,"mode":"ojs"},{"id":1829,"value":"screenshot20220623180742 = FileAttachment(\"Screenshot 2022-06-23 180742.png\").image()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1589,"value":"Plot.plot({\n marks: [\n Plot.dot(data_migration, {\n x: \"Immigrant/Population by Neighborhood\",\n grid: true,\n y: \"Accident Rate by Neighborhood \",\n\n r: 6,\n opacity: 0.5,\n fill: \"#FA8322\",\n title: \"Neighborhood Name\"\n }),\n Plot.link([0], {\n x1: trendLine_migration[0][0],\n y1: trendLine_migration[0][1],\n x2: trendLine_migration[1][0],\n y2: trendLine_migration[1][1],\n stroke: \"grey\"\n })\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1285,"value":"#### Result\nIn this section we used two different graphs, one of them is to compare the total number of victims with the emigrant/immigrant rates to discover any interesting information. The other plot compared the migration rate with the number of victims per district. We can only see same slight trends. In the first chart there is a slightly positive connection between the number of victims and the migration rates. In the second chart, we see a soft positive correlation by comparing the migration rate with the accident rate. However, those trends can't be taken serious, there are to many strong outliers which break the statistic.","pinned":false,"hidden":false,"mode":"md"},{"id":1833,"value":"### 5.6 Accident rate comparison between district and neighborhood levels.","pinned":false,"hidden":false,"mode":"md"},{"id":1849,"value":"When we compare the accident rate between the district and neighborhood levels, we can see some interesting information revealed. Compared with district levels, neighborhood levels show us a more detailed overview of where the accident rates are created. We can see that at the district level the accident rate of Sants-Montjuic is relatively low compared to other districts. But when we zoom in on the neighborhood levels we can clearly see that one of the neighborhoods has the highest accident rate of all of Barcelona. That is one of our main drivers to distinguish between the district and neighborhood levle in order to get a more in depth picture. Below you can see a screenshot from our Tableau dashboard which allows us by clicking on the colored circles to filter the data by district and by neighborhood. The chosen filters are then applied on the scatterplots below the two maps. Take a look in our Tableau dashboard, those two filters reveal some quite interesting insights.","pinned":false,"hidden":false,"mode":"md"},{"id":1837,"value":"comparison = FileAttachment(\"Comparison.png\").image()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1828,"value":"screenshot202206231807101 = FileAttachment(\"Screenshot 2022-06-23 180710@1.png\").image()","pinned":false,"hidden":false,"mode":"ojs"},{"id":1844,"value":"#### Result\nIn the last chart from our report we compare the injury rate with the victims in order to see the real underlying danger between the neighborhoods. We can see some neighborhoods, such as Les Corts, which have a very high number of victims in total but a comparably small injury rate. These results could be really valuable for example for an insurance company when they charging the premium for each neighborhood and customer.","pinned":false,"hidden":false,"mode":"md"},{"id":1180,"value":"$35","pinned":false,"hidden":false,"mode":"md"},{"id":1175,"value":"## 7. Appendix","pinned":false,"hidden":false,"mode":"md"},{"id":551,"value":"import {Plot} from \"@mkfreeman/plot-tooltip\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":15,"value":"import { Wrangler, op } from \"@observablehq/data-wrangler\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":336,"value":"import { slider } from \"@jashkenas/inputs\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":744,"value":"// See: https://observablehq.com/@d3/bubble-chart\nimport {BubbleChart} from \"@d3/bubble-chart\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":1161,"value":"import {toc} from \"@nebrius/indented-toc\"","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W36","stdlib":"1","titling":"auto"}