CODEWITHSUNDEEP
rdplyr
EML
EML

Reputation: 671

Insert NA row after new value in column

How can I insert a row of NA values after each new character string in a column (here, the "key" column)?

Data:

df <- data.frame(var1=c("M", "F", "M", "F"),
                 var2=c(1,2,2,2),
                 var3=c("A", "B", "C", "D"))
# make data long
df %>% gather(key, value) %>%
    dplyr::group_by(key, value) %>%
  summarise(n=n())

`summarise()` has grouped output by 'key'. You can override using the `.groups` argument.
# A tibble: 8 x 3
# Groups:   key [3]
  key   value     n
  <chr> <chr> <int>
1 var1  F         2
2 var1  M         2
3 var2  1         1
4 var2  2         3
5 var3  A         1
6 var3  B         1
7 var3  C         1
8 var3  D         1

Looking for this output:

    key value  n
1  var1     F  2
2  var1     M  2
3  <NA>  <NA> NA
4  var2     1  1
5  var2     2  3
6  <NA>  <NA> NA
7  var3     A  1
8  var3     B  1
9  var3     C  1
10 var3     D  1

The goal is to fill the NA values with blanks in order to separate the variables.

Upvotes: 0

Views: 45

Answers (2)

AndrewGB
AndrewGB

Reputation: 16856

Another tidyverse option:

library(tidyverse)

df %>% 
  gather(key, value) %>%
  group_by(key, value) %>%
  summarise(n=n()) %>% 
  group_by(key) %>% 
  group_modify(~ add_row(.x)) %>% 
  mutate(key = ifelse(row_number() == n(), NA, key)) %>% 
  ungroup %>% 
  slice(1:(n()-1))

Output

   key   value     n
   <chr> <chr> <int>
 1 var1  F         2
 2 var1  M         2
 3 NA    NA       NA
 4 var2  1         1
 5 var2  2         3
 6 NA    NA       NA
 7 var3  A         1
 8 var3  B         1
 9 var3  C         1
10 var3  D         1

Upvotes: 2

Allan Cameron
Allan Cameron

Reputation: 173803

You could do:

df %>% gather(key, value) %>%
    dplyr::group_by(key, value) %>%
  summarise(n=n()) %>%
  summarise(across(everything(), ~c(.x, NA))) %>%
  mutate(key = ifelse(is.na(value), NA, key))
#> # A tibble: 11 x 3
#> # Groups:   key [4]
#>    key   value     n
#>    <chr> <chr> <int>
#>  1 var1  F         2
#>  2 var1  M         2
#>  3 <NA>  <NA>     NA
#>  4 var2  1         1
#>  5 var2  2         3
#>  6 <NA>  <NA>     NA
#>  7 var3  A         1
#>  8 var3  B         1
#>  9 var3  C         1
#> 10 var3  D         1
#> 11 <NA>  <NA>     NA

Upvotes: 2

Related Questions