For each row in a data frame create multiple rows based on numbers from-to in R

Question

In tabel df1 I have housenumbers from-to and the required fill up sequence:

ID      HsnrFrom    HsnrTo  sequence
180700241   64  68  2
180901612   179 183 2
180900571   8   11  1
180900680   9   13  2

I want to multiply the rows, tot get a dataframe with all housenumbers in the range from-to in df2. Because of the even and odd numbering the filling has to be based ond the displayed sequence:

ID      HsnrFrom    HsnrTo  sequence    Hsnr
180700241   64  68  2   64
180700241   64  68  2   66
180700241   64  68  2   68
180901612   179 183 2   179
180901612   179 183 2   181
180901612   179 183 2   183
180900571   8   11  1   8
180900571   8   11  1   9
180900571   8   11  1   10
180900571   8   11  1   11
180900680   9   13  2   9
180900680   9   13  2   11
180900680   9   13  2   13

I have tried to translate this script to my challenge. For each row in a data frame, create multiple rows based on date ranges

library(data.table)
setDT(df)[, c('StartDate', 'EndDate') := lapply(.SD, as.Date, format = '%m/%d/%Y'), .SDcols = 2:3
          ][, .(date = seq(StartDate, EndDate, 'day')), by = .(Name, StartDate, EndDate)]

Unfortunately, I can't figure it out. That is why I hope for good advice.

Ronak Shah · Accepted Answer · 2020-08-10T08:09:42.733

You don't want to convert the values to date here. Also, the creation of sequence is not fixed so use it as an argument.

library(data.table)
setDT(df)[, .(Hsnr = seq(HsnrFrom, HsnrTo, sequence)), 
            .(ID, HsnrFrom, HsnrTo, sequence)]

#           ID HsnrFrom HsnrTo sequence Hsnr
# 1: 180700241       64     68        2   64
# 2: 180700241       64     68        2   66
# 3: 180700241       64     68        2   68
# 4: 180901612      179    183        2  179
# 5: 180901612      179    183        2  181
# 6: 180901612      179    183        2  183
# 7: 180900571        8     11        1    8
# 8: 180900571        8     11        1    9
# 9: 180900571        8     11        1   10
#10: 180900571        8     11        1   11
#11: 180900680        9     13        2    9
#12: 180900680        9     13        2   11
#13: 180900680        9     13        2   13

With dplyr 1.0.0 you can do :

library(dplyr)
df %>%
  group_by(ID, HsnrFrom, HsnrTo, sequence) %>%
  summarise(Hsnr = seq(HsnrFrom, HsnrTo, sequence))

score 0 · Answer 2 · answered Aug 10 '20 at 08:23

If you want to do it manually without a library, you can use a for-loop to create a new dataset, based on the informations in df1.

df1 = data.frame("ID"=c(180700241,180901612,180900571,180900680),
                "HsnrFrom"=c(64,179,8,9),
                "HsnrTo"=c(68,183,11,13),
                "sequence"=c(2,2,1,2))

df2 = list("ID"=c(),"HsnrFrom"=c(),"HsnrTo"=c(), "sequence"=c(), "Hsnr"=c())
for(i in 1:dim(df1)[1]){
  numbers = seq(df1$HsnrFrom[i],df1$HsnrTo[i],df1$sequence[i])
  df2[["ID"]]=c(df2[["ID"]],rep(df1$ID[i],length(numbers)))
  df2[["HsnrFrom"]]=c(df2[["HsnrFrom"]],rep(df1$HsnrFrom[i],length(numbers)))
  df2[["HsnrTo"]]=c(df2[["HsnrTo"]],rep(df1$HsnrTo[i],length(numbers)))
  df2[["sequence"]]=c(df2[["sequence"]],rep(df1$sequence[i],length(numbers)))
  df2[["Hsnr"]]=c(df2[["Hsnr"]],numbers)
}

df2 = data.frame(df2)

The output of this program would be:

> df2
          ID HsnrFrom HsnrTo sequence Hsnr
1  180700241       64     68        2   64
2  180700241       64     68        2   66
3  180700241       64     68        2   68
4  180901612      179    183        2  179
5  180901612      179    183        2  181
6  180901612      179    183        2  183
7  180900571        8     11        1    8
8  180900571        8     11        1    9
9  180900571        8     11        1   10
10 180900571        8     11        1   11
11 180900680        9     13        2    9
12 180900680        9     13        2   11
13 180900680        9     13        2   13

score 0 · Answer 3 · answered Aug 10 '20 at 12:25

Here is a base R option

do.call(
  rbind,
  c(Map(
    function(v) cbind(v, Hsnr = with(v, seq(HsnrFrom, HsnrTo, by = sequence)), row.names = NULL),
    split(df, 1:nrow(df))
  ),
  make.row.names = FALSE
  )
)

which gives

          ID HsnrFrom HsnrTo sequence Hsnr
1  180700241       64     68        2   64
2  180700241       64     68        2   66
3  180700241       64     68        2   68
4  180901612      179    183        2  179
5  180901612      179    183        2  181
6  180901612      179    183        2  183
7  180900571        8     11        1    8
8  180900571        8     11        1    9
9  180900571        8     11        1   10
10 180900571        8     11        1   11
11 180900680        9     13        2    9
12 180900680        9     13        2   11
13 180900680        9     13        2   13

Data

df <- structure(list(ID = c(180700241L, 180901612L, 180900571L, 180900680L
), HsnrFrom = c(64L, 179L, 8L, 9L), HsnrTo = c(68L, 183L, 11L,
13L), sequence = c(2L, 2L, 1L, 2L)), class = "data.frame", row.names = c(NA, 
-4L))

For each row in a data frame create multiple rows based on numbers from-to in R

3 Answers3