I'm assuming you know for sure that there are never going to be any mismatches between the length of the non-NA values per column per group (like, maybe you're cleaning up some sort of join?). It's an interesting puzzle trying to sort out how to handle it if the columns are ragged, but like @MikeBadescu said, the possibilities quickly multiply.
Here's a naive solution to the ragged column situation that just pads out the shorter columns with NAs. Note that in cases where there was already a fully filled-in row, the correspondence will be broken! data.table does something different, recycling values from the shorter column instead of filling with NAs. The correspondence between values in rows that started out complete still gets broken.
library(tidyverse)
dat2 <- tribble(
~Group, ~Month, ~Value,
1 , "Jan" , NA ,
1 , "Feb" , NA ,
1 , "Mar" , NA ,
1 , "Jun" , NA ,
1 , NA , "4" ,
1 , NA , "5" ,
1 , NA , "6" ,
1 , NA , NA ,
2 , "Jan" , NA ,
2 , "Mar" , NA ,
2 , NA , NA ,
2 , NA , NA ,
2 , NA , "3" ,
2 , NA , "2" ,
2 , NA , "1" ,
2 , NA , NA ,
3 , "Feb" , NA ,
3 , "Mar" , NA ,
3 , "Jun" , NA ,
3 , NA , NA ,
3 , NA , "8" ,
3 , "Jul" , "7" ,
3 , NA , "4" ,
3 , "Dec" , NA )
# Collapse each column independently, and fill out
# the shorter columns with trailing NAs
collapse_fill_na <- function(dfr) {
collapsed <- map(dfr, na.omit)
max_len <- max(map_int(collapsed, length))
map_if(collapsed,
~ length(.) < max_len,
~ c(., rep(NA_character_, max_len - length(.)))) %>%
bind_cols()
}
dat2 %>%
nest(-Group) %>%
mutate(data = map(data, collapse_fill_na)) %>%
unnest()
#> # A tibble: 12 x 3
#> Group Month Value
#> <dbl> <chr> <chr>
#> 1 1 Jan 4
#> 2 1 Feb 5
#> 3 1 Mar 6
#> 4 1 Jun <NA>
#> 5 2 Jan 3
#> 6 2 Mar 2
#> 7 2 <NA> 1
#> 8 3 Feb 8
#> 9 3 Mar 7
#> 10 3 Jun 4
#> 11 3 Jul <NA>
#> 12 3 Dec <NA>
# data.table recycles values instead (with a warning)
library(data.table)
#>
#> Attaching package: 'data.table'
#> The following objects are masked from 'package:dplyr':
#>
#> between, first, last
#> The following object is masked from 'package:purrr':
#>
#> transpose
setDT(dat2)[, lapply(.SD, na.omit), by = Group]
#> Warning in `[.data.table`(setDT(dat2), , lapply(.SD, na.omit), by = Group):
#> Column 2 of result for group 1 is length 3 but the longest column in this
#> result is 4. Recycled leaving remainder of 1 items. This warning is once
#> only for the first group with this issue.
#> Group Month Value
#> 1: 1 Jan 4
#> 2: 1 Feb 5
#> 3: 1 Mar 6
#> 4: 1 Jun 4
#> 5: 2 Jan 3
#> 6: 2 Mar 2
#> 7: 2 Jan 1
#> 8: 3 Feb 8
#> 9: 3 Mar 7
#> 10: 3 Jun 4
#> 11: 3 Jul 8
#> 12: 3 Dec 7
Created on 2018-10-08 by the reprex package (v0.2.1)