Add data row(s) to a tibble depending on the content of a single column

user_11597488 0
Viewed 128

I want to automatically add one or more rows to an existing tibble depending on the values present in one of the tibble columns.

Data

    A   B   C   D     E
1   1   1   1   5   7.81
2   1   1   1   4   13.12
3   1   1   1   5   3.39
4   1   1   1   4   3.28
5   1   1   1   5   2.69
6   1   1   1   2   5.70
7   1   1   1   1   8.22

Expected Output if '3' is missing from 'D'

    A   B   C   D     E
1   1   1   1   5   7.81
2   1   1   1   4   13.12
3   1   1   1   5   3.39
4   1   1   1   4   3.28
5   1   1   1   5   2.69
6   1   1   1   2   5.70
7   1   1   1   1   8.22
8   1   1   1   3   0.00

In the tibble column D the values should range from 1:5 depending on the data set.

I want to be able to identify whether one or more numbers from this range are missing from column D, which if one value (e.g. 3) is missing I want to add a new row that copies the data in columns A : C and enters 3 in column D and 0 in column E. If two or more values are missing (e.g. 3 and 4) I want to add two rows, etc.

tidyr dplyr tidyverse r
1 Answers

You can use tidyr::complete:

library(tidyverse)

Data %>% 
     complete(nesting(A,B,C), D = seq(min(D), max(D), 1L))

#> # A tibble: 8 x 5
#>       A     B     C     D     E
#>   <int> <int> <int> <int> <dbl>
#> 1     1     1     1     1  8.22
#> 2     1     1     1     2  5.7 
#> 3     1     1     1     3 NA   
#> 4     1     1     1     4 13.1 
#> 5     1     1     1     4  3.28
#> 6     1     1     1     5  7.81
#> 7     1     1     1     5  3.39
#> 8     1     1     1     5  2.69

I'd recommend not using 0 instead of NA but if you want to add them at the bottom and have them as 0 then this works:

Data %>% 
     complete(nesting(A,B,C), D = seq(min(D), max(D), 1L)) %>% 
     arrange(is.na(E)) %>% 
     mutate(E = replace_na(E, 0))

#> # A tibble: 8 x 5
#>       A     B     C     D     E
#>   <int> <int> <int> <int> <dbl>
#> 1     1     1     1     1  8.22
#> 2     1     1     1     2  5.7 
#> 3     1     1     1     4 13.1 
#> 4     1     1     1     4  3.28
#> 5     1     1     1     5  7.81
#> 6     1     1     1     5  3.39
#> 7     1     1     1     5  2.69
#> 8     1     1     1     3  0

Created on 2019-06-20 by the reprex package (v0.3.0)

user_64614620
Related