format_vehicle and format_casualty

[wengraf]

Hi:

If I download and read in the full CSV (e.g., https://data.dft.gov.uk/road-accidents-safety-data/dft-road-casualty-statistics-casualty-1979-latest-published-year.csv) using read.csv, and then I use format_casualty or format_vehicle on that df, it turns age of casualty and age of vehicle to NA, and leaves only descriptions of those results that are actually NA.

Ivo

[Robinlovelace]

Does sound like a bug..

[wengraf]

Either a bug, or the coding makes an assumption of about CSV formatting that doesn't apply in the case of the very biggest three CSVs. I'd look more closely now, but I've got a specific job I need to crack on with...

[layik]

Hi @wengraf. Thanks for raising this and sorry for lack of response. @Robinlovelace I think this might be the cause.

# issue 235 checks
library(stats19)
#> Data provided under OGL v3.0. Cite the source and link to:
#> www.nationalarchives.gov.uk/doc/open-government-licence/version/3/
colnames(stats19_variables)
#> [1] "table"       "variable"    "note"        "column_name" "type"
stats19_variables[grep("age_", stats19_variables$variable), ]
#>       table             variable note          column_name      type
#> 44 Casualty age_band_of_casualty <NA> age_band_of_casualty character
#> 45 Casualty      age_of_casualty <NA>      age_of_casualty character
#> 74  Vehicle   age_band_of_driver <NA>   age_band_of_driver character
#> 75  Vehicle        age_of_driver <NA>        age_of_driver character
#> 76  Vehicle       age_of_vehicle <NA>       age_of_vehicle character

^{Created on 2024-07-18 with reprex v2.1.1}

See if I can fix the issue quickly.

[Robinlovelace]

Should be numeric for sure. Thanks Layik, hopefully a quick fix.

[wengraf]

It isn't just the one's with age...I think engine capacity is another one...

[layik]

It is not a quick one I am afraid and my time is needed elsewhere. This is actually across years.

library(stats19)
#> Data provided under OGL v3.0. Cite the source and link to:
#> www.nationalarchives.gov.uk/doc/open-government-licence/version/3/
cas = get_stats19(year = 2022, type = "cas")
#> Files identified: dft-road-casualty-statistics-casualty-2022.csv
#>    https://data.dft.gov.uk/road-accidents-safety-data/dft-road-casualty-statistics-casualty-2022.csv
#> Attempt downloading from: https://data.dft.gov.uk/road-accidents-safety-data/dft-road-casualty-statistics-casualty-2022.csv
#> Data saved at /tmp/RtmpFcCkYU/dft-road-casualty-statistics-casualty-2022.csv
#> Rows: 135480 Columns: 19
#> ── Column specification ────────────────────────────────────────────────────────
#> Delimiter: ","
#> chr  (3): accident_index, accident_reference, lsoa_of_casualty
#> dbl (16): accident_year, vehicle_reference, casualty_reference, casualty_cla...
#> 
#> ℹ Use `spec()` to retrieve the full column specification for this data.
#> ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
sapply(grep("age_", colnames(cas)), function(x) class(x))
#> [1] "integer" "integer"
###### yet!
table(cas$age_of_casualty)
#> 
#> Data missing or out of range 
#>                         3129
###### fine here
table(cas$age_band_of_casualty)
#> 
#>                        0 - 5                      11 - 15 
#>                         2211                         6209 
#....

^{Created on 2024-07-18 with reprex v2.1.1}

@Robinlovelace and other contributors, I believe stats19::stats19_variables$type is not used to read the data in.

Proof?

library(stats19)
#> Data provided under OGL v3.0. Cite the source and link to:
#> www.nationalarchives.gov.uk/doc/open-government-licence/version/3/

pkg_env = as.environment("package:stats19")
unlockBinding("stats19_variables", pkg_env)
stats19_variables = get("stats19_variables", envir = pkg_env)
# just test age_of_casualty
stats19_variables[grep("age_of_casualty", 
                       stats19_variables$variable), "type"] = "numeric"
assign("stats19_variables", stats19_variables, envir = pkg_env)
lockBinding("stats19_variables", pkg_env)
# read
cas = get_stats19(year = 2022, type = "cas")
#> Files identified: dft-road-casualty-statistics-casualty-2022.csv
#>    https://data.dft.gov.uk/road-accidents-safety-data/dft-road-casualty-statistics-casualty-2022.csv
#> Attempt downloading from: https://data.dft.gov.uk/road-accidents-safety-data/dft-road-casualty-statistics-casualty-2022.csv
#> Data saved at /tmp/Rtmp4E06yZ/dft-road-casualty-statistics-casualty-2022.csv
#> Rows: 135480 Columns: 19
#> ── Column specification ────────────────────────────────────────────────────────
#> Delimiter: ","
#> chr  (3): accident_index, accident_reference, lsoa_of_casualty
#> dbl (16): accident_year, vehicle_reference, casualty_reference, casualty_cla...
#> 
#> ℹ Use `spec()` to retrieve the full column specification for this data.
#> ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
sapply(grep("age_", colnames(cas)), function(x) class(x))
#> [1] "integer" "integer"
table(cas$age_of_casualty)
#> 
#> Data missing or out of range 
#>                         3129
table(cas$age_band_of_casualty)
#> 
#>                        0 - 5                      11 - 15 
#>                         2211                         6209 
#...

^{Created on 2024-07-18 with reprex v2.1.1}

I must leave it there.

[layik]

cc @wengraf

library(stats19)
#> Data provided under OGL v3.0. Cite the source and link to:
#> www.nationalarchives.gov.uk/doc/open-government-licence/version/3/
# Function to check for columns with lots of NAs
find_cols_with_many_NAs = function(df, na_threshold = 0.5) {
  # Calculate the proportion of NAs in each column
  na_proportions = sapply(df, function(col) sum(is.na(col)) / length(col))
  
  # Find columns where the proportion of NAs is greater than the threshold
  cols_with_many_NAs = names(na_proportions[na_proportions > na_threshold])
  
  return(cols_with_many_NAs)
}
col = get_stats19(year = 2022, type = "col")
#> Files identified: dft-road-casualty-statistics-collision-2022.csv
#>    https://data.dft.gov.uk/road-accidents-safety-data/dft-road-casualty-statistics-collision-2022.csv
cas = get_stats19(year = 2022, type = "cas")
#> Files identified: dft-road-casualty-statistics-casualty-2022.csv
#> 
#>    https://data.dft.gov.uk/road-accidents-safety-data/dft-road-casualty-statistics-casualty-2022.csv
veh = get_stats19(year = 2022, type = "veh")
#> Files identified: dft-road-casualty-statistics-vehicle-2022.csv
#> 
#>    https://data.dft.gov.uk/road-accidents-safety-data/dft-road-casualty-statistics-vehicle-2022.csv
find_cols_with_many_NAs(col)
#> [1] "local_authority_district" "first_road_number"
find_cols_with_many_NAs(cas)
#> [1] "age_of_casualty"
find_cols_with_many_NAs(veh)
#> [1] "age_of_driver"      "engine_capacity_cc" "generic_make_model"

^{Created on 2024-07-27 with reprex v2.1.1}

EDIT:
certainly related to #220

What a subtle bug this is. Found it I think:

stats19/R/format.R

Line 84 in 3242e36

x[[i]] = lookup$label[match(x[[i]], lookup$code)]

As it can be seen above, it has nothing to do with setting the class of the columns (the draft PR). Above is an attempt at converting say 1, 2, 3 for sexes male, female and uknown to replace all the values as such using the lookup object which works for say 99.9% of the values. In our integer values case, for instance, it fails on 99% as only the unknown are coded as -1 for instance. Because that is what match does which by the way is as complicated as R gets :)

[layik]

This is the cure and now engine capacity, and for that matter every column, should not have crazy amount of NAs injected in them whilst formatting any of the three tables.
839c656

[layik]

Once #245 is in this will close, we will have much better get started vignette than current which shows almost useless tables.

[Robinlovelace]

Heads-up @layik I'm still hitting the issue with get_stats19() as per reprex below, can you test? It may be using the wrong version but re-opening to double check:

#| eval: false
install.packages("stats19")
packageVersion("stats19")
# [1] ‘3.0.3’
cas = stats19::get_stats19(type = "cas", year = 1979)
names(cas)
table(cas$age_of_casualty)
# Data missing or out of range 
#                       211849 

remotes::install_dev("stats19")
packageVersion("stats19")
# [1] ‘3.1.0’
cas = stats19::get_stats19(type = "cas", year = 1979)
names(cas)
table(cas$age_of_casualty)
# Data missing or out of range 
#                       211849 

[Robinlovelace]

Correction: it IS fixed, it was just using old version of data. Reprex: incoming.