build a character chain with successive years

user_5457869 0
Viewed 42

I have a table with startYear, endYear and I want to create a dimension Year as "startYear,endYear"

dt<- data.table(
startYear=c("1994","1995","2013"),
endYear=c("1995","2000","2021"))

I want the following dt table

dt<- data.table(
startYear=c("1994","1995","2013"),
endYear=c("1995","2000","2021"),
Year = c("1994,1995","1995,1996,1997,1998,1999,2000","2013,2014,2015,2016,2017,2018,2019,2020,2021")

I try

dt$Year<- paste(seq(from = dt$startYear,
                          to = dt$endYear, by = 1))

but I get an error message

Error in seq.default(from = test$startYear, to = test$endYear, by = 1) : 
  'from' must be of length 1

I do not know how to change that to work properly. thanks

seq data.table r
1 Answers

The literal expected output ...

dt[, Year := mapply(function(a, b) paste(seq(a, b), collapse = ","), startYear, endYear) ]
#    startYear endYear                                    Year
#       <char>  <char>                                  <char>
# 1:      1994    1995                               1994,1995
# 2:      1995    2000           1995,1996,1997,1998,1999,2000
# 3:      2013    2021 2013,2014,2015,2016,2017,2018,2019,2...

But if you're planning on doing anything with the numbers internally later (not just a collapsed string), it might be useful to deal with Year as a list-column, usable in data.table and dplyr natively, perhaps less aesthetic in base R but it can still work there, too, depending on your use-case.

dt[, Year := Map(seq, startYear, endYear) ]
#    startYear endYear                              Year
#       <char>  <char>                            <list>
# 1:      1994    1995                         1994,1995
# 2:      1995    2000     1995,1996,1997,1998,1999,2000
# 3:      2013    2021 2013,2014,2015,2016,2017,2018,...
user_33582720
Related