Insert subsequent values (rows) between present values in df

Question

let's create a simple df:

col1 <- runif(n = 10, min = 0.1, max = 1)
col2 <- c(1,3,5,8,19,21,34,41,45,46)
col3 <- c('A','B','C','D','E','F','G','H','I','J')
df <- data.frame(col1, col2, col3)
df
        col1 col2 col3
1  0.4715977    1    A
2  0.8364755    3    B
3  0.2936675    5    C
4  0.5438777    8    D
5  0.3691008   19    E
6  0.6030533   21    F
7  0.5171806   34    G
8  0.4804162   41    H
9  0.3031454   45    I
10 0.8507250   46    J

I'd like to add rows with zeros and NAs between numeric values of col2, in regard col2 noew subsequent values, so between row 1 and 2, I'd like to have one additional row with value 2 in col2 such like this:

        col1 col2 col3
1  0.4715977    1    A
2         0     2    NA
2  0.8364755    3    B

Summarizing whole df should look like:

            col1 col2 col3
    1  0.4715977    1    A
    2          0    2    NA
    3  0.8364755    3    B
    4          0    4    NA
    5  0.2936675    5    C
    6          0    6    NA
    7          0    7    NA
    8  0.5438777    8    D
    9          0    9    NA
    10         0    10   NA
    11         0    11   NA
    12         0    12   NA
    13         0    13   NA
    14         0    14   NA
    15         0    15   NA
    16         0    16   NA
    17         0    17   NA
    18         0    18   NA
    19 0.3691008    19   E
    20         0    20   NA
    21 0.6030533    21   F
    22         0    22   NA
    23         0    23   NA
    24         0    24   NA
    25         0    25   NA
    26         0    26   NA
    27         0    27   NA
    28         0    28   NA
    29         0    29   NA
    30         0    30   NA
    31         0    31   NA
    32         0    32   NA
    33         0    33   NA
    34 0.5171806    34   G
    35         0    35   NA
    36         0    36   NA
    37         0    37   NA
    38         0    38   NA
    39         0    39   NA
    40         0    40   NA
    41 0.4804162    41   H
    42         0    42   NA
    43         0    43   NA
    44         0    44   NA
    45 0.3031454    45   I
    10 0.8507250    46   J

Could you give me a clue how to do this in easy way, I was thinking about dplyr...

Try `merge(data.frame(col2 = seq(max(df$col2))), df, by = 'col2', all = TRUE)` — Sotos, Apr 08 '19 at 08:55
From the link : `tidyr::complete(df,col2 = full_seq(col2, period = 1), fill = list(col1 = 0))` — Ronak Shah, Apr 08 '19 at 09:43

score 2 · Accepted Answer · answered Apr 08 '19 at 08:43

With tidyr, you can do:

complete(df, col2 = seq(min(col2), max(col2), 1), fill = list(col1 = 0))

    col2  col1 col3 
   <dbl> <dbl> <fct>
 1     1 0.191 A    
 2     2 0     <NA> 
 3     3 0.638 B    
 4     4 0     <NA> 
 5     5 0.908 C    
 6     6 0     <NA> 
 7     7 0     <NA> 
 8     8 0.995 D    
 9     9 0     <NA> 
10    10 0     <NA>

Insert subsequent values (rows) between present values in df

1 Answers1