Analyzing public data on road accidents in Russia

This scenario analyzes public data on road accidents in Russia. In addition to identifying the facts of accident statistics in Russia, during the analysis you will learn how to:

• Work with the DataLens key entities: Connections, Datasets, Charts, and Dashboards.
• Combine multiple sources at the level of a single dataset.
• Use the Date and time data type and edit chart-level groupings.
• Work with geodata: geopoints and geopolygons.
• Create public dashboards that will be available to everyone from any device without authentication.

To visualize and explore data, set up DataLens and follow the steps below:

1. Create a connection to a CSV file.
2. Create a dataset and configure its fields.
3. Create your first chart: a heat map.
4. Create your second chart: a bar chart.
5. Create line charts.
6. Create a dashboard.
7. Add charts to the dashboard.
8. Add selectors and create a dashboard.
9. Connect an additional source with region geolayers.
10. Add new fields to the dataset.
11. Create a chart using the new fields.
12. Add a new chart to the dashboard.
13. Publish the dashboard.

Getting startedGetting started

To get started with DataLens:

If you have any technical questions about the service, contact Yandex Cloud support. To ask for advice, discuss the solution to your problem or best practices of the service, write to the DataLens chat in Telegram.

Step 1. Create a connection to a CSV fileStep 1. Create a connection to a CSV file

1. Download the file with traffic accident statistics for April-December 2018 in CSV format.

2. Open the DataLens home page.

3. In the left-hand panel, select Connections and click Create connection.

4. Select the Files connection type.

   

5. Click Upload files and select the downloaded file. It may take up to several minutes to load it, depending on the speed of your internet connection.

   

   Once loading is compete, you will see a preview of the file data. To view it, use the scroll bars on the right and at the bottom.

   

6. In the top-right corner, click Create connection.

7. Enter a name for the dtp_data connection and click Create.

This will create the file connection. The data will be shown in the same form as in the file. To use it, you need to create a dataset.

Step 2. Create a dataset and configure its fieldsStep 2. Create a dataset and configure its fields

The dataset will consist of a single source: the CSV file.

1. Click Create dataset.

   

2. Go to the Fields tab.

   

   You can add data fields and calculated fields, change the field data type and aggregation rules, and rename fields.

   

3. Change the field names to Russian ones in the following order:

   • Region code
   • Region name
   • Road code
   • Road name
   • Type of road
   • OKTMO code
   • Address
   • Accident type

   

4. The accident date and time are shown in the following fields:

   • crash_date: Numeric date in 20190218 format.
   • crash_time: Time in 19:34 format.

   They will not be used in the dataset in this format. Click the visibility icon to hide them.

   

5. To display information about the accident date and time, add a new field.

   1. Click Add field.

      

   2. Specify the following data for it:

      • Field name: Datetime
      • Formula: DATETIME_PARSE(str([crash_date])+' '+str([crash_time]))

      Tip

      To avoid errors, select fields for the formula from the list on the left instead of entering them manually.

      

      Tip

      To review the documentation for these functions yourself, click Reference at the top right.

   3. Click Create.

   The preview section now correctly displays the accident date and time as a value of the Date and time type with the T separator. The new Date and time field is at the top of the table. Click to edit the field formula.

   

6. Continue renaming the fields in the following order:

   • Cause of accident
   • Death toll
   • Number of victims
   • Number of vehicles
   • Number of participants

   Set the Sum aggregation type for the following fields:

   • Death toll
   • Number of victims
   • Number of vehicles
   • Number of participants

   

7. Add a field to display a geopoint.

   1. Click Add field at the top right.

      

   2. Enter data for the new field:

      • Field name: Geopoint
      • Formula: GEOPOINT([latitude],[longitude])

      Where [latitude] and [longitude] are dataset fields.

      

   3. Click Create.

      Note

      The example is based on ready-to-use data in coordinate format.

   4. Make sure that the table and preview contain the new Geopoint type field.

      

8. Add a field to display the number of accidents.

   1. Click Add field.

      

   2. Enter data for the new field:

      • Field name: Number of accidents
      • Formula: SUM(1)

      

   3. Click Create.

   You do not need to set the aggregation type for this field: when you add it to the chart, DataLens applies the aggregation function automatically.

9. Add a field for data on deaths in road accidents.

   1. Click Add field.

      

   2. Enter data for the new field:

      • Field name: Accident death rate
      • Formula: [Death toll]/[Number of accidents]

      

   3. Click Create

   Note

   You can create calculated fields directly in charts. However, it is more convenient to do this at the dataset level to make them available in all charts.

10. Save the dataset by clicking Save at the top right.

11. In the window that opens, specify the dataset name: Accidents. Click Create.

The dataset is created.

Step 3. Create your first chart: a heat mapStep 3. Create your first chart: a heat map

Proceed to creating the first chart.

1. Click Create chart.

   

2. In the wizard window that opens, click the field that defaults to Bar chart and select the Map chart type.

   

3. Under Points, select the Points (heat map) layer type.

   

4. Drag the Geopoint field to the Geopoints section.

   

   That's your first chart on the map.

5. To save it, click the arrow icon next to the Save button and select Save as.

   

6. In the dialog box, specify the chart name, Heat map, and click Save.

Step 4. Create your second chart: a bar chartStep 4. Create your second chart: a bar chart

1. Analyze the number of accidents by region.

   1. In the same window, select Bar chart as the chart type.

      

   2. Drag:

      • The Region name field to the Y section.
      • The Number of accidents field to the X section.
      • The Number of accidents field to the Sorting section.

      

      The chart shows the number of accidents by region. Most accidents occurred in the Moscow region.

      Note

      The legend may be missing for some of the chart lines. If this is the case, hover over the line and you will see a tooltip with the region name.

   3. Click Save as to save the chart.

   4. In the window that opens, specify the chart name, Number of accidents by region, and save it.

2. Analyze the death rate in accidents by region.

   1. Replace the Number of accidents measure with Accident death rate by dragging and dropping.

      

      This time, Ingushetia, Kalmykia, and the Volgograd region are far ahead in the number of deaths.

   2. Save the chart by clicking Save as and name it Death rate by region.

Step 5. Create more charts: line chartsStep 5. Create more charts: line charts

Let's see how the number of accidents and related deaths are distributed by week, day of the week, and time of day.

1. Analyze the number of accidents and death rate by week.

   1. In the same window, select Line chart as the chart type.

      

   2. The chart management sections contain the previous values. Delete them by clicking .

   3. Drag the Date and time field to the X section and click the green calendar icon.

      

   4. In the Grouping field, choose Date part ⟶ Week and click Apply.

      

   5. Drag:

      • The Number of accidents field to the Y section.
      • The Accident death rate field to the Y2 section.

      

      Now you can see a chart with two graphs: the number of accidents and death rate. If you hover over a point on the chart, a tooltip appears with specific values.

   6. Save the chart by clicking Save as and name it Number of accidents and death rate by week.

2. Now analyze the number of accidents and death rate by day of the week.

   1. Change grouping by date and time to Date part ⟶ Day of week.

      

      This chart shows more obvious trends.

      

   2. Save the chart by clicking Save as and name it Number of accidents and death rate by day of week.

3. Analyze the number of accidents and death rate by hour of day.

   1. Change grouping by date and time by specifying: Date part ⟶ Hour.

      

      The chart shows that less accidents occur at night, but they result in more casualties.

      

   2. Save the chart by clicking Save as and name it Number of accidents and death rate by hour of day.

Step 6. Create a dashboardStep 6. Create a dashboard

1. Open the menu by clicking the icon to the left of the logo and select Dashboards.

   

2. Click Create dashboard.

   

Step 7. Add charts to the dashboardStep 7. Add charts to the dashboard

1. Add the first chart.

   1. Click Add and select Chart from the drop-down list.

      

   2. In the Chart field, click Select and select the previously created Heat Map chart from the chart list.

      

   3. Click Add.

      The chart is displayed on the dashboard.

2. Repeat the previous three steps for the Number of accidents by region, Number of accidents and death rate by week, Number of accidents and death rate by day of week, and Number of accidents and death rate by hour of day charts by adding them to the dashboard.

3. Create a switch for the Number of accidents by region and Death rate by region charts on the dashboard.

   1. Click the Number of accidents by region chart settings icon to open them.

      

   2. Click Add to the left of the screen.

      

   3. In the Chart field, click Select and choose the Death rate by region chart.

      

   4. Click Save.

Step 8. Add selectors and create a dashboardStep 8. Add selectors and create a dashboard

Selectors allow you to filter data by value.

1. Add a selector to show statistics by region name.

   1. Click Add and choose Selector.

      

   2. Select Based on dataset, click Select, and choose the previously created Accidents dataset.

      

   3. In the Field property, choose Region name and select Show next to the selector name. Click Add.

      

   The selector will be displayed on the dashboard as a rectangle.

2. Repeat the previous three steps for the fields:

   • Cause of accident
   • Accident type
   • Type of road
   • Road name

3. You can drag and resize dashboard elements. Place the elements according to the screenshot or however is convenient for you and save the dashboard:

   1. In the top-right corner of the dashboard, click Save.
   2. Name the dashboard Accidents in Russia and click Create.

   

If you choose any values in the selectors, the charts will display data for these values.

You can switch the Number of accidents by region chart to Death rate by region.

Step 9. Add a table with region geolayersStep 9. Add a table with region geolayers

Sometimes, heat maps are not very informative. Upload the polygon reference and add a chart with a color fill for Russian regions.

1. Download the Regions.csv file.

2. Open the dtp_data connection.

3. Click Upload files and select the downloaded file.

4. Take a look at a preview of the file data. For the Column header parameter, set the Yes value.

   

5. At the top right, click Save changes.

Step 10. Add new fields to the datasetStep 10. Add new fields to the dataset

You need to add new fields to the previously created Accidents dataset.

1. In the left menu, select Datasets.

   

2. Choose the Accidents dataset.

   

3. Go to the Sources tab.

4. Drag the new table Regions.csv to the editing area.

   

   Since the charts data has not yet been merged, an error message may appear. Follow these steps:

5. Click the merge data icon.

   

6. Click Add link.

   

7. Select the fields to link: reg_name and Accident region. Then click Apply.

   

   Make sure the data preview is displayed correctly.

   

8. Go to the Fields tab.

9. New fields are displayed at the bottom of the list. For the Polygon field, specify the Geopolygon data type.

   

10. In the top-right corner, click Save.

Step 11. Create a chart using the new fieldsStep 11. Create a chart using the new fields

1. Click Create chart.

   

2. Select the Map chart type.

   

3. Add polygons to the map. Drag the Polygon field from Dimensions to the Geopolygons section.

4. Change colors of the polygons based on the number of accidents. Drag the Number of accidents field from Measures to the Colors section.

5. Drag the following fields to the Tooltips section:

   • Region name
   • Number of accidents
   • Death toll
   • Number of victims
   • Number of vehicles
   • Number of participants
   • Accident death rate

   The chart shows a map with a color fill for regions. If you hover over a region, information for that region appears.

   

6. In the top-right corner, click Save.

7. Enter a chart name, Map of regions, and click Save once again.

Step 12. Add a new chart to the dashboardStep 12. Add a new chart to the dashboard

1. Select Dashboards in the top-left menu.

   

2. Select the previously created dashboard with accident data.

   

3. Click Edit at the top right.

   

4. Click the Heat map settings icon to open them.

   

5. Click Add to the left of the screen.

   

6. In the Chart field, click Select and choose the last chart named Map of regions.

   

7. Click Save.

You can now switch the type of map data visualization: heat map or region fill.

Step 13. Publish the dashboardStep 13. Publish the dashboard

To configure public access to the dashboard:

1. In the top-left corner, click .

   

2. Select Public access in the drop-down menu.

   

3. Enable access via link. You can copy and share the link that appears. Everyone can use the link to access the dashboard from any device and without authentication. Click Apply.

   

   Note

   You can enable or disable public access separately for each chart and dataset associated with the dashboard.

   The dashboard is published and available to users.