Analyzing public data on road accidents in Russia

This guide 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.
• Merge multiple sources at single dataset level.
• 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.

The following files will provide the source data:

• Traffic accident statistics for April-December 2018 in CSV format.
• Polygon reference for regions of Russia in CSV format.

Download them before you proceed with this guide.

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

1. Create a workbook.
2. Create a connection to files.
3. Create a dataset.
4. Analyze accident distribution density.
5. Analyze the number of accidents and death rate.
6. Analyze the statistics by week, day of week, and time of day.
7. Create a map with color fill by region.
8. Create a dashboard and add charts to it.
9. Add selectors to the dashboard.
10. Add a map with color fill by region to the dashboard.
11. 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.

Create a workbookCreate a workbook

1. Go to the DataLens home page.
2. In the left-hand panel, select Collections and workbooks.
3. In the top-right corner, click Create → Create workbook.
4. Enter a name for the workbook: Tutorials.
5. Click Create.

Create a connection to filesCreate a connection to files

Create a connection to source data files:

1. In the top-right corner of the workbook, click Create → Connection.

2. Under Files and services, select the Files connection.

3. Click Upload files.

4. Select dtp201804-1.csv. Wait until the table content appears on the screen.

   

5. Connect a chart with region geolayers. Click Upload files.

6. Select Regions.csv. Wait for the data to load.

7. Select the uploaded Regions.csv file from the list on the left.

8. For the Column header parameter, set the Yes value.

9. Click Create connection.

   

10. Enter dtp_data for the connection name and click Create.

Create a datasetCreate a dataset

Create a dataset based on the dtp_data connection:

1. In the top-right corner of the connection page, click Create dataset.

2. Drag the dtp201804-1.csv table to the workspace.

3. Drag the Regions.csv table to the workspace. An error message will appear as the tables' data have not been merged yet.

4. Click the link icon between the tables.

   

5. Click Add link.

6. Select the fields to link: reg_name and Accident region.

7. Click Apply.

   

   Make sure that the data in the preview section is presented correctly.

   

8. Navigate to the Fields tab. On this tab, you can add data fields and calculated fields, change field data types and aggregation rules, as well as rename fields.

9. Rename the fields as follows:

   • reg_code to Region code
   • reg_name to Region name
   • road_code to Road code
   • road_name to Road name
   • road_type to Road type
   • oktmo to OKTMO code
   • address to Address
   • crash_type_name to Accident type

   To rename a field, click its current name, delete it, and enter a new one.

   

10. Accident date and time appear in the following fields:

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

    They will not be used in charts in this format. Click to hide them (the icon appears when you hover over the field).

    

11. Create a calculated field to display information about the accident date and time:

    1. In the top-right corner, click Add field.

    2. At the top-left, specify the field name: Date and time.

    3. In the formula field, enter 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.
       • To display information about functions, click Reference at the top right.

    4. Click Create.

    

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

    

12. Rename the fields as follows:

    • crash_reason to Cause of accident
    • fatalities_amount to Death toll
    • victims_amount to Number of victims
    • vehicles_amount to Number of vehicles
    • participants_amount to Number of participants

13. For the Death toll, Number of victims Number of vehicles and Number of participants fields, specify the Sum aggregation type. The fields with defined aggregation functions become measures and change their color to blue.

    

14. Create a field with accident coordinates:

    1. In the top-right corner, click Add field.

    2. At the top-left, specify the field name: Geopoint.

    3. In the formula field, enter GEOPOINT([latitude],[longitude]).

    4. Click Create.

       Note

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

    A new field of the Geopoint type will appear in the list of fields; the data for this field will appear in the preview section.

    

15. Create the following fields:

    • Number of accidents with the SUM(1) formula. You do not need to set the aggregation type for this field: when you add it to the chart, DataLens will apply the aggregation function automatically.
    • Accident death rate with the [Death toll]/[Number of accidents] formula.

16. For the Polygon field, change the data type to Geopolygon.

    

17. Save the dataset:

    1. In the top-right corner, click Save.
    2. Enter Accident as the dataset name and click Create.

Analyze accident distribution densityAnalyze accident distribution density

To visualize accident distribution density by region, create a heat map:

1. On the dataset page, click Create chart in the top-right corner.

2. Select the Map visualization type.

   

3. Select the Heat map (Geopoints) layer type.

   

4. Add points with coordinates to the map. To do this, drag the Geopoint field from Dimensions to the Heatmap (Geopoints) section.

5. Save the chart:

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

   2. In the window that opens, enter Accident heat map as the chart name and click Save.

      

Analyze the number of accidents and death rateAnalyze the number of accidents and death rate

To analyze the number of accidents by region, create a bar chart.

1. Create a copy of the chart you created in the previous step:

   1. In the top-right corner, click → Save as copy.

      

   2. In the window that opens, enter Number of accidents by region as the name for the new chart and click Save.

2. For the visualization type, select Line chart.

   

3. Add region names to your chart. To do this, drag the Region name field from Dimensions to the Y section.

4. Add the number of accidents to your chart. Drag the Number of accidents field from Measures to the X section.

5. Sort the chart by the number of accidents in descending order. Drag the Number of accidents field from Measures to the Sorting section.

   

   The chart will show 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.

6. Save your chart: click Save in the top-right corner.

To analyze the accident death rate by region, create another bar chart:

1. Create a copy of the Number of accidents by region chart.

   1. In the top-right corner, click → Save as copy.
   2. In the window that opens, enter Death rate by region as the name for the new chart and click Save.

2. Replace the number of accidents measure with the accident death rate measure on axis X. To do this, drag the Accident death rate field from Dimensions to the X section and hold it over the Number of accidents field until the latter turns red.

3. Change from sorting by the number of accidents to sorting by descending accident death rate. To do this, drag the Accident death rate field from Dimensions to the Sorting section and hold it over the Number of accidents field until the latter turns red.

   

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

4. Save your chart: click Save in the top-right corner.

Analyze the statistics by week, day of week, and time of dayAnalyze the statistics by week, day of week, and time of day

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

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

   1. Copy the chart you created in the previous step:

      1. In the top-right corner, click → Save as copy.
      2. In the window that opens, enter the Number of accidents and death rate by week as the name for the new chart and click Save.

   2. For the visualization type, choose Line chart.

      

   3. Delete fields in all sections of the chart. To do this, click (the icon appears when you hover over the field) next to a field in a section.

   4. Add accident date and time to your chart. To do this, drag the Date and time field from Dimensions to the X section.

   5. Group dates by week:

      1. Click the calendar icon next to the Date and time field in the X section.
      2. In the Grouping field, choose Date portion ⟶ Week and click Apply.

      

   6. Drag:

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

      

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

   7. Save your chart: click Save in the top-right corner.

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

   1. Copy the chart you created in the previous step:

      1. In the top-right corner, click → Save as copy.
      2. In the window that opens, enter the Number of accidents and death rate by day of week as the name for the new chart and click Save.

   2. Change grouping by date and time to Date portion ⟶ Day of week.

      

      This chart reveals more obvious trends: both measures increase towards the end of the week.

      

   3. Save your chart: click Save in the top-right corner.

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

   1. Copy the chart you created in the previous step:

      1. In the top-right corner, click → Save as copy.
      2. In the window that opens, enter Number of accidents and death rate by time of day as the name for the new chart and click Save.

   2. Change grouping by date and time by specifying: Date portion ⟶ Hour.

      

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

      

   3. Save your chart: click Save in the top-right corner.

Create a map with color fill by regionCreate a map with color fill by region

1. Copy the chart you created in the previous step:

   1. In the top-right corner, click → Save as copy.
   2. In the window that opens, enter the Regions map as the name for the new chart and click Save.

2. Select the Map visualization type.

3. Select Polygons (Geopolygons) as the layer type.

   

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

5. Change colors of the polygons based on the number of accidents. To do this, drag the Number of accidents field from Measures to the Colors section.

6. 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 will show a map with color fill by region. If you hover over a region, information for that region appears.

   

7. Save your chart: click Save in the top-right corner.

Create a dashboard and add charts to itCreate a dashboard and add charts to it

Create a dashboard to add charts and other widgets to:

1. In the left-hand panel, select Collections and workbooks and go to the Tutorials workbook.

2. In the top-right corner, click Create → Dashboard.

3. In the panel at the bottom of the page, hold down Chart and drag it to the required area.

   

4. In the window that opens, click Select.

5. Select the Accident heat map chart.

6. Click Add.

   

7. Repeat steps 3-6 to add the following charts to the dashboard:

   • Number of accidents by region
   • Number of accidents and death rate by week
   • Number of accidents and death rate by day of week
   • Number of accidents and death rate by hour of day

8. On the dashboard, create a widget with two tabs:

   1. In the top-right corner of the Number of accidents by region chart, click .
   2. In the left part of the window, click Add.
   3. In the new tab, click Select.
   4. Select the Death rate by region chart.
   5. Click Save.

   

9. Adjust the size of the charts with your mouse and place them on the dashboard as you prefer.

   

Add selectors to the dashboardAdd selectors to the dashboard

Selectors allow you to filter data by value.

1. Add a widget for data filtering:

   1. In the panel at the bottom of the page, hold down Selector and drag it to the required area.

      

   2. Choose the Accidents dataset.

   3. Select the Region name field.

      

   4. Add selectors by the following fields to the widget:

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

      To to this, on the left side under Selectors, click Add and select a field name.

      

   5. Click Save.

2. Position the widget on the dashboard where you think fit.

   

3. Save the dashboard:

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

Your dashboard is ready.

On the dashboard, you can:

• Select selector values for data filtering.
• Rescale the map.
• Switch the Number of accidents by region chart to Death rate by region and back. To do this, click to the right of the chart name and select the appropriate tab.

Add a map with color fill by region to the dashboardAdd a map with color fill by region to the dashboard

1. Click Edit at the top right.

2. In the top-right corner of the Accident heat map chart, click .

3. In the left part of the window, click Add.

4. In the new tab, click Select.

5. Select the Regions map chart.

6. Click Save.

   

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

Now you can switch between charts: the heat map with accident density or the color fill map.

Publish the dashboardPublish the dashboard

To configure public access to the dashboard:

1. At the top of the screen, click → Public access.

2. In the window that opens, enable the link sharing option next to the dashboard name.

3. Click Continue to confirm publication of the dashboard and the data connected to it.

4. Click Copy link to copy the dashboard's unique URL.

   

5. Click Apply.

6. Open a new tab in the browser and go to the copied link.

   

   You can share this link with your colleagues, partners, and friends. When opening the dashboard link, no authorization and authentication are required.

   Note

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

   Configuring public access for objects