Expand and collapse an integer JSON column — expand_integer

This function accepts a data.table along with a set of grouping variables and a character-format integer-style JSON column (i.e. starts with square brackets, not curly).

expand_integer_json(
  dt,
  expand,
  index = "index",
  by = NULL,
  fast = TRUE,
  fun = sum,
  na.rm = TRUE,
  set_key = TRUE
)

Arguments

dt: data.table object (or something that can be coerced to data.table)
expand: String indicating the JSON column to be expanded.
index: String indicating the name of the new index column
by: Character vector indicating the variables to group by after expanding. Set to NULL to aggregate across all initial rows, or set to FALSE to not aggregate at all (this will also add an initial_rowno column showing the original row number).
fast: Assumes that all the JSON vectors are of equal length, and adds the values together rather than using whatever is in fun.
fun: Function that takes a vector and returns a single value to use when collapsing to the by level. Requires fast = FALSE.
na.rm: Ignore missing values of expand
set_key: Set the key of dt to by. Set to FALSE if you have already set the key or want it returned without key.

Details

It expands that JSON column into long format, with one row per observation per value of the JSON column, and then collapses everything according to the set of grouping variables.

Examples


# Example data
patterns <- data.table::data.table(state_fips = c(1,1,2,2),
                                     int_origin = c('[2,3]',
                                                    '[3,4]',
                                                    '[4,5]',
                                                    '[5,6]'))

expand_integer_json(patterns, 'int_origin', by = 'state_fips')[]
#>    state_fips int_origin index
#> 1:          1          5     1
#> 2:          1          7     2
#> 3:          2          9     1
#> 4:          2         11     2