Home > Enterprise >  Collapsing multiple combinations of disjoint rows with NA
Collapsing multiple combinations of disjoint rows with NA

Time:09-23

Similar to this question but with an added twist:

Given the following data frame:

txt <- "ID    Col1    Col2    Col3    Col4
        1     6       10      NA      NA
        1     5       10      NA      NA
        1     NA      10      15      20
        2     17      25      NA      NA
        2     13      25      NA      NA
        2     NA      25      21      34
        2     NA      25      35      40"
DF <- read.table(text = txt, header = TRUE)

DF
  ID Col1 Col2 Col3 Col4
1  1    6   10   NA   NA
2  1    5   10   NA   NA
3  1   NA   10   15   20
4  2   17   25   NA   NA
5  2   13   25   NA   NA
6  2   NA   25   21   34
7  2   NA   25   35   40

I wish to collapse the rows by group ID (analogous to Col2 in this example), and when more than 1 combination is present per group, to return all combinations, as so:

  ID Col1 Col2 Col3 Col4
1  1    6   10   15   20
2  1    5   10   15   20
3  2   17   25   21   34
4  2   13   25   21   34
5  2   17   25   35   40
6  2   13   25   35   40

Importantly, down the road I'll need this to work on non-numerical data. Any suggestions? Thanks!

CodePudding user response:

Grouped by 'ID', fill other columns, ungroup to remove the group attribute and keep the distinct rows

library(dplyr)
library(tidyr)
DF %>% 
    group_by(ID) %>% 
    fill(everything(), .direction = 'updown') %>%
    ungroup %>% 
    distinct(.keep_all = TRUE)

Or may also be

DF %>% 
   group_by(ID) %>% 
   mutate(across(everything(), ~ replace(., is.na(.), 
           rep(.[!is.na(.)], length.out = sum(is.na(.))))))
  • Related