Add values to multiple rows of id up to specified amount

user_16448520 0
Viewed 34

I have a data frame with multiple rows per id, similar to this:

id amount
1 21
1 2
1 3
2 66
2 22
3 10
3 11
3 12
3 13
3 14
3 15
3 16
3 17

I would now like to add more rows to each id, until every id has 8 rows in total. The amount in the added rows should be zero. So, f.e., for 1 it would look like this:

id amount
1 21
1 2
1 3
1 0
1 0
1 0
1 0
1 0

How can I achieve this? Thanks in advance!

rows add r
1 Answers

We could use complete after creating a sequence column grouped by 'id' (rowid - from data.table). Within in complete, change the rn to range from 1 to 8, and fill the 'amount' to 0 (by default it is NA)

library(dplyr)
library(tidyr)
library(data.table)
df1 %>%
     mutate(rn = rowid(id)) %>% 
     complete(id, rn = 1:8, fill = list(amount = 0)) %>% 
     select(-rn)

-output

# A tibble: 24 x 2
      id amount
   <int>  <dbl>
 1     1     21
 2     1      2
 3     1      3
 4     1      0
 5     1      0
 6     1      0
 7     1      0
 8     1      0
 9     2     66
10     2     22
# … with 14 more rows

data

df1 <- structure(list(id = c(1L, 1L, 1L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 
3L, 3L, 3L), amount = c(21L, 2L, 3L, 66L, 22L, 10L, 11L, 12L, 
13L, 14L, 15L, 16L, 17L)), class = "data.frame", row.names = c(NA, 
-13L))
user_37322710
Related