Split a column consisting of number range and use the resulting numbers as range values in R

Question

My sample data frame looks like below:

structure(list(Speed = c("0-20", "21-40", "41-60", "61-80", "81-100"
), SpeedLevel = c(1, 2, 3, 4, 5)), row.names = c(NA, -5L), class = c("tbl_df", 
"tbl", "data.frame"))
>

I need to add a column that'll have all values from the range corresponding to the first column "Speed". i.e., I need to split the string at '-' and give the range of values from min to max.

For example, in the first row of Speed column we have '0-20', therefore after splitting the range will be all numbers from 0 to 20. Once I get that I can then use separate_rows or unnest functions of tidyr and dplyr respectively as shown in the below expected output.

Expected Output:

structure(list(Speed = c("0-20", "0-20", "0-20", "0-20", "0-20", 
"0-20", "0-20", "0-20", "0-20", "0-20", "0-20", "0-20", "0-20", 
"0-20", "0-20", "0-20", "0-20", "0-20", "0-20", "0-20", "0-20", 
"21-40", "21-40", "21-40", "21-40", "21-40", "21-40", "21-40", 
"21-40", "21-40", "21-40", "21-40", "21-40", "21-40", "21-40", 
"21-40", "21-40", "21-40", "21-40", "21-40", "21-40", "41-60", 
"41-60", "41-60", "41-60", "41-60", "41-60", "41-60", "41-60", 
"41-60", "41-60", "41-60", "41-60", "41-60", "41-60", "41-60", 
"41-60", "41-60", "41-60", "41-60", "41-60", "61-80", "61-80", 
"61-80", "61-80", "61-80", "61-80", "61-80", "61-80", "61-80", 
"61-80", "61-80", "61-80", "61-80", "61-80", "61-80", "61-80", 
"61-80", "61-80", "61-80", "61-80", "81-100", "81-100", "81-100", 
"81-100", "81-100", "81-100", "81-100", "81-100", "81-100", "81-100", 
"81-100", "81-100", "81-100", "81-100", "81-100", "81-100", "81-100", 
"81-100", "81-100", "81-100"), SpeedLevel = c(1, 1, 1, 1, 1, 
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 
3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4, 4, 4, 
4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 5, 5, 5, 5, 5, 5, 5, 5, 
5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5), ActualSpeed = c(0, 1, 2, 
3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 
20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 
36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 
52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 
68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 
84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 
100)), row.names = c(NA, -101L), class = c("tbl_df", "tbl", "data.frame"
))
>

For string splitting I use "strsplit" function, but not sure if I can use that here. Could someone let me know how to split the "Speed" column and use the two resulting numbers as range values.

akrun · Accepted Answer · 2020-08-18T19:35:23.167

We can split the 'Speed' into two column with separate, then create a sequence list column based on the values of 'start', 'end' and unnest the list column

library(dplyr)
library(tidyr)
library(purrr)
df1 %>% 
  separate(Speed, into = c('start', 'end'), remove = FALSE, convert = TRUE) %>% 
   mutate(AcutalSpeed  = map2(start, end, `:`), start = NULL, end = NULL) %>% 
   unnest(c(AcutalSpeed))
# A tibble: 101 x 3
#   Speed SpeedLevel AcutalSpeed
#   <chr>      <dbl>       <int>
# 1 0-20           1           0
# 2 0-20           1           1
# 3 0-20           1           2
# 4 0-20           1           3
# 5 0-20           1           4
# 6 0-20           1           5
# 7 0-20           1           6
# 8 0-20           1           7
# 9 0-20           1           8
#10 0-20           1           9
# … with 91 more rows

Split a column consisting of number range and use the resulting numbers as range values in R

1 Answers1