Including missing values in summarise output

user_823859 0
Viewed 81

I am trying to still keep all rows in a summarise output even when one of the columns does not exist. I have a data frame that looks like this:

dat <- data.frame(id=c(1,1,2,2,2,3),
                  seq_num=c(0:1,0:2,0:0),
                  time=c(4,5,6,7,8,9))

I then need to summarize by all ids, where id is a row and there is a column for the first seq_num and second one. Even if the second one doesn't exist, I'd still like that row to be maintained, with an NA in that slot. I've tried the answers in this answer, but they are not working.

dat %>% 
  group_by(id, .drop=FALSE) %>% 
  summarise(seq_0_time = time[seq_num==0],
            seq_1_time = time[seq_num==1])

outputs

     id seq_0_time seq_1_time
  <dbl>      <dbl>      <dbl>
1     1          4          5
2     2          6          7

I would still like a 3rd row, though, with seq_0_time=9, and seq_1_time=NA since it doesn't exist.

How can I do this?

dplyr tidyverse r
3 Answers

If there are only max one observation per 'seq_num' for each 'id', then it is possible to coerce to NA where there are no cases with [1]

library(dplyr)
dat %>% 
  group_by(id) %>% 
  summarise(seq_0_time = time[seq_num ==0][1],
            seq_1_time = time[seq_num == 1][1], .groups = 'drop')

-output

# A tibble: 3 × 3
     id seq_0_time seq_1_time
  <dbl>      <dbl>      <dbl>
1     1          4          5
2     2          6          7
3     3          9         NA

It is just that the length of 0 can be modified to length 1 by assigning NA Or similarly this can be used to replicate NA to fill for 2, 3, etc, by specifying the index that didn't occur

> with(dat, time[seq_num==1 & id == 3])
numeric(0)
> with(dat, time[seq_num==1 & id == 3][1])
[1] NA
> numeric(0)
numeric(0)
> numeric(0)[1]
[1] NA
> numeric(0)[1:2]
[1] NA NA

Or using length<-

> `length<-`(numeric(0), 3)
[1] NA NA NA
user_37322710

My understanding is that you must use complete() on both the seq_num and id variables to achieve your desired result:

library(tidyverse)
dat <- data.frame(id=c(1,1,2,2,2,3),
                  seq_num=c(0:1,0:2,0:0),
                  time=c(4,5,6,7,8,9)) %>%
  complete(seq_num = seq_num,
           id = id)
dat %>% 
  group_by(id, .drop=FALSE) %>% 
  summarise(seq_0_time = time[seq_num==0],
            seq_1_time = time[seq_num==1])
#> # A tibble: 3 x 3
#>      id seq_0_time seq_1_time
#>   <dbl>      <dbl>      <dbl>
#> 1     1          4          5
#> 2     2          6          7
#> 3     3          9         NA

Created on 2022-04-20 by the reprex package (v2.0.1)

user_167508880

This can actually be pretty easily solved using reshape.

> reshape(dat, timevar='seq_num', idvar = 'id', direction = 'wide')
  id time.0 time.1 time.2
1  1      4      5     NA
3  2      6      7      8
6  3      9     NA     NA
user_8238590
Related