Data Checks
Match of code-description for accsmaps
The tables ‘allaccs’, ‘casualties’ and ‘vehicles’ can be exported from accmaps as csv files and then imported. A number of the fields are exported as codes so the codes have to replaced by the matching description for this analysis. Some of the codes can be linked to the fields in the open dataset guide linked to the DfT annual report. Others, such as contributory factors, are found only in the stats20 guide. T
Summary of other cleaning etc of imported data
- Convert all date character strings to date format.
- Convert all police_ref fields to upper case then trim, otherwise they won’t join properly.
- Check data type matches for all rows.
- Convert all vehicle and casualty references to three digits.
- Rename some columns to add clarity.
- In the contributory factor fields, replace NA with blank.
- Create longitude and latitude columns using easting and northing.
- Add longitude, latitude, easting and northing to casualties table.
- Merge the four partial accident description fields.
- Rename all motorcycle types into the single description “Motorcycle”.
- Add datetime, location, accident description, district into casualties table.
- Add vehicle type of each casualty into casualty table.
- Create cyclist casualties dataframe.
- Join each row of cyclist casualties dataframe to corresponding accident row (same accident can apply to more than one casualty).
- Create column showing where contributory factor is assigned to cyclist casualty.
- Map contributory factors to road safety factors.
Check for errors in the csv imports
DfT and Surrey Police data are validated by the respective organisations. The additional check here is to look for duplicates or missing rows in the imported data.
Check GB data - csv files imported from DfT road safety data site
NB all years before 2008 in imported data filtered out.
Accidents table
| Number of columns | 44 |
| Number of rows | 2,220,891 |
Vehicles table
| Number of columns | 32 |
| Number of rows | 4,067,917 |
Casualties table
| Number of columns | 23 |
| Number of rows | 2,932,362 |
Results of duplicate row check and any duplicate deletion required
Accidents
| collision_ref_no | collision_year |
|---|
## [1] "No duplicate rows"
Number of rows in table after removing any duplicates: 2,220,891.
Vehicles
| collision_ref_no | collision_year | vehicle_reference |
|---|
## [1] "No duplicate rows"
Number of rows in table after removing any duplicates: 4,067,917.
Casualties
| collision_ref_no | collision_year | casualty_reference |
|---|---|---|
| 04CJ13162 | 2013 | 3 |
| 04CJ14198 | 2014 | 3 |
| 04CK11089 | 2011 | 3 |
| 04WA14102 | 2014 | 3 |
| 04TA08267 | 2008 | 2 |
| 04CL12218 | 2012 | 1 |
| 04WA14102 | 2014 | 3 |
| 04FJ11013 | 2011 | 4 |
| 04CC08101 | 2008 | 6 |
| 04FJ11013 | 2011 | 4 |
| 04EA09322 | 2009 | 2 |
| 471305010 | 2013 | 1 |
| 04CJ14198 | 2014 | 3 |
| 04ED16270 | 2016 | 2 |
| 04CJ13162 | 2013 | 3 |
| 04TA08267 | 2008 | 2 |
| 04CK11089 | 2011 | 3 |
| 04ED16270 | 2016 | 2 |
| 04CC08101 | 2008 | 6 |
| 04EA13001 | 2013 | 3 |
| 04EA13001 | 2013 | 3 |
| 04CL12218 | 2012 | 1 |
| 04EA09322 | 2009 | 2 |
| 471305010 | 2013 | 1 |
## Number of duplicate rows = 12
Number of rows in table after removing any duplicates: 2,932,350.
Check Surrey data - csv files imported from Accsmap
Number of unique police references in each export
Should be the same for all three.
| Table export | Number of unique police references |
|---|---|
| casualties | 60227 |
| vehicles | 60226 |
| accidents | 60227 |
Does the most recent casualty match?
| Table export | Most recent record |
|---|---|
| casualties | 2025-12-31 |
| collisions | 2025-12-31 18:50:00 |
| accidents | 2025-12-31 |
Number of police references in one table but not another
Totals should all be 0. The listings should all be <0 rows>.
Total number
| Comparison | Number |
|---|---|
| Refs in casualties but not in vehicles | 1 |
| Refs in casualties but not in accidents | 35 |
| Refs in vehicles but not in casualties | 0 |
| Refs in vehicles but not in accidents | 57 |
| Refs in accidents but not in casualties | 25 |
| Refs in accidents but not in vehicles | 26 |
Listing of anomalies
Refs in casualties but not in vehicles
## date police_ref
## 1 2011-04-16 SP59738/11
Refs in casualties but not in accidentss
## date police_ref
## 1 2018-10-10 0980591
## 2 2018-12-27 0805157
## 3 2019-01-13 0807401
## 4 2019-01-13 0807401
## 5 2019-01-16 0813040
## 6 2019-01-17 0808717
## 7 2019-03-15 0823770
## 8 2019-03-15 0823770
## 9 2019-04-14 0831508
## 10 2019-04-25 0834269
## 11 2019-04-25 0834269
## 12 2019-05-07 0837640
## 13 2019-05-31 0844013
## 14 2019-05-31 0844083
## 15 2019-05-31 0844083
## 16 2019-06-13 0847997
## 17 2019-07-08 0856939
## 18 2019-07-08 0856939
## 19 2019-09-05 0875112
## 20 2019-09-16 0878949
## 21 2019-09-16 0878949
## 22 2019-09-16 0878949
## 23 2019-09-25 0881729
## 24 2019-09-25 0881729
## 25 2019-10-11 0886953
## 26 2019-10-28 0892375
## 27 2019-10-28 0892375
## 28 2019-10-29 0892856
## 29 2019-11-01 0894373
## 30 2019-11-07 0896153
## 31 2019-11-19 0901375
## 32 2019-11-25 0905805
## 33 2019-11-27 0903169
## 34 2019-11-27 0903169
## 35 2019-12-21 0921804
Refs in vehicles but not in casualties
## [1] date police_ref
## <0 rows> (or 0-length row.names)
Refs in vehicles but not in accidents
## date police_ref
## 1 2018-10-10 0980591
## 2 2018-12-27 0805157
## 3 2018-12-27 0805157
## 4 2018-12-27 0805157
## 5 2019-01-13 0807401
## 6 2019-01-13 0807401
## 7 2019-01-13 0807401
## 8 2019-01-16 0813040
## 9 2019-01-16 0813040
## 10 2019-01-17 0808717
## 11 2019-01-17 0808717
## 12 2019-01-17 0808717
## 13 2019-03-15 0823770
## 14 2019-04-14 0831508
## 15 2019-04-25 0834269
## 16 2019-04-25 0834269
## 17 2019-05-07 0837640
## 18 2019-05-07 0837640
## 19 2019-05-31 0844013
## 20 2019-05-31 0844083
## 21 2019-05-31 0844083
## 22 2019-06-13 0847997
## 23 2019-06-13 0847997
## 24 2019-06-13 0847997
## 25 2019-06-13 0847997
## 26 2019-07-08 0856939
## 27 2019-07-08 0856939
## 28 2019-09-05 0875112
## 29 2019-09-05 0875112
## 30 2019-09-16 0878949
## 31 2019-09-16 0878949
## 32 2019-09-16 0878949
## 33 2019-09-25 0881729
## 34 2019-09-25 0881729
## 35 2019-10-11 0886953
## 36 2019-10-28 0892375
## 37 2019-10-28 0892375
## 38 2019-10-29 0892856
## 39 2019-10-29 0892856
## 40 2019-10-29 0892856
## 41 2019-10-29 0892856
## 42 2019-10-29 0892856
## 43 2019-10-29 0892856
## 44 2019-10-29 0892856
## 45 2019-10-29 0892856
## 46 2019-11-01 0894373
## 47 2019-11-01 0894373
## 48 2019-11-07 0896153
## 49 2019-11-07 0896153
## 50 2019-11-19 0901375
## 51 2019-11-19 0901375
## 52 2019-11-25 0905805
## 53 2019-11-25 0905805
## 54 2019-11-27 0903169
## 55 2019-11-27 0903169
## 56 2019-12-21 0921804
## 57 2019-12-21 0921804
Refs in accidents but not in casualties
## datetime police_ref
## 1 2018-10-10 12:55:00 980591
## 2 2018-12-27 17:02:00 805157
## 3 2019-01-13 19:00:00 807401
## 4 2019-01-16 12:51:00 813040
## 5 2019-01-17 15:10:00 808717
## 6 2019-03-15 21:38:00 823770
## 7 2019-04-14 16:57:00 831508
## 8 2019-04-25 09:20:00 834269
## 9 2019-05-07 15:40:00 837640
## 10 2019-05-31 08:10:00 844013
## 11 2019-05-31 17:54:00 844083
## 12 2019-06-13 20:36:00 847997
## 13 2019-07-08 15:03:00 856939
## 14 2019-09-05 13:25:00 875112
## 15 2019-09-16 17:00:00 878949
## 16 2019-09-25 22:25:00 881729
## 17 2019-10-11 16:55:00 886953
## 18 2019-10-28 06:24:00 892375
## 19 2019-10-29 15:07:00 892856
## 20 2019-11-01 21:23:00 894373
## 21 2019-11-07 07:20:00 896153
## 22 2019-11-19 17:10:00 901375
## 23 2019-11-25 14:22:00 905805
## 24 2019-11-27 17:28:00 903169
## 25 2019-12-21 22:00:00 921804
Refs in accidents but not in vehicles
## datetime police_ref
## 1 2011-04-16 16:55:00 SP59738/11
## 2 2018-10-10 12:55:00 980591
## 3 2018-12-27 17:02:00 805157
## 4 2019-01-13 19:00:00 807401
## 5 2019-01-16 12:51:00 813040
## 6 2019-01-17 15:10:00 808717
## 7 2019-03-15 21:38:00 823770
## 8 2019-04-14 16:57:00 831508
## 9 2019-04-25 09:20:00 834269
## 10 2019-05-07 15:40:00 837640
## 11 2019-05-31 08:10:00 844013
## 12 2019-05-31 17:54:00 844083
## 13 2019-06-13 20:36:00 847997
## 14 2019-07-08 15:03:00 856939
## 15 2019-09-05 13:25:00 875112
## 16 2019-09-16 17:00:00 878949
## 17 2019-09-25 22:25:00 881729
## 18 2019-10-11 16:55:00 886953
## 19 2019-10-28 06:24:00 892375
## 20 2019-10-29 15:07:00 892856
## 21 2019-11-01 21:23:00 894373
## 22 2019-11-07 07:20:00 896153
## 23 2019-11-19 17:10:00 901375
## 24 2019-11-25 14:22:00 905805
## 25 2019-11-27 17:28:00 903169
## 26 2019-12-21 22:00:00 921804
Check imported data tables against published DfT data
This checks that the total numbers used on this site are the same as DfT published data.
Data import from Accsmaps compared to DfT data
This is covered in the data sources page.
Data import from DfT compared to published DfT data
DfT publish the annual total of cyclist casualties in GB through table RAS0201, so this should be the same as in the csv files imported from their site:
| ras40006.ras0201_year | total.from.ras40006.ras0201 | csv import total | difference |
|---|---|---|---|
| 2010 | 17185 | 17185 | 0 |
| 2011 | 19215 | 19215 | 0 |
| 2012 | 19091 | 19091 | 0 |
| 2013 | 19438 | 19438 | 0 |
| 2014 | 21287 | 21287 | 0 |
| 2015 | 18844 | 18844 | 0 |
| 2016 | 18477 | 18477 | 0 |
| 2017 | 18321 | 18321 | 0 |
| 2018 | 17550 | 17550 | 0 |
| 2019 | 16884 | 16883 | -1 |
| 2020 | 16294 | 16294 | 0 |
| 2021 | 16458 | 16458 | 0 |
| 2022 | 15693 | 15693 | 0 |
Miscellaneous subsetting checks
This is a place for recording of the reason for an unexpected number of rows has been found in a sub-set. The total number of cyclist casualties in the GB data is 297,645 and in the Surrey data 8,478.
GB - DfT data
Mapping number of GB cyclist casualties
Some of the GB data are missing longitude and latitude, so cannot be presented on the GB map. Of the total of 297,645 cyclist casualties recorded in GB, 32 have had to be excluded from the map.
Crash types - avoiding ‘many to many’ relationship between casualty and accident tables
To create the crash types, the table of GB cyclist casualties is joined to the accident table. The latter has the number of participants in the accident by mode type. However, as in some accidents there were two or more cyclist casualties, only distinct rows the from table of GB cyclist casualties have been used. This reduces the number of rows in the table of GB cyclist casualties from the correct number of 297,645 rows down to 294,524, so a slightly less than full picture is presented.
Crash types ‘other’
‘Other’ crash types are excluded from the totals on the Comparisons: other places page. This is because the number of ‘other’ = 221 - so is relatively small.
Surrey - Accsmap data
Crash types ‘other’
‘Other’ crash types are excluded from the totals on the how page. This is because the number of ‘other’ = 7 - so is relatively small; each of these are 2+ vehicles with ped and cyclist casualties.