Combining rows with duplicate values and NAs [without using tidyverse]

Question

I have a data frame which has multiple rows per ID. I would like to combine those rows to get 1 row for each ID and combine the respective values which can be either a number or NA (which I would like to ignore).

I am not sure I can use aggregate() here; Generally I want for each ID values in columns (V1-V4), to be set to a number if there is a value which is not NA.

Input:

    ID V1 V2 V3 V4
1  04C  6 NA NA  9
2  04C NA  9 NA  9
3  0F0 NA  5 NA  4
4  0F0 NA NA  7  4
5  0F0 NA  5  7 NA
6  167  8 NA NA NA
7  167  8 10  5 NA
8  167  8 10 NA NA
9  167  8 NA  5 NA
10 2D7  3  3 NA  1

Output:

   ID V1 V2 V3 V4
1 04C  6  9 NA  9
2 0F0 NA  5  7  4
3 167  8 10  5 NA
4 2D7  3  3 NA  1

I came up with a solution, unfortunately it is very slow but at least it avoids creating a tibble.

for(i in 2:nrow(df)) {
   row0 <- df[i-1,1]
   row1 <- df[i,1]  
   if (row0==row1) {
      for(j in 2:5) {
         if (is.na(df[i,j])) {
            df[i,j] <- df[i-1,j]
         }
      }
      df[i-1,1] <- "NA"
   }
}
dfclean <- subset(df, V0!="NA")

Duplicate of [combine rows in data frame containing NA to make complete row](https://stackoverflow.com/questions/45515218/combine-rows-in-data-frame-containing-na-to-make-complete-row) — M--, Mar 21 '19 at 17:21

score 1 · Accepted Answer · answered Mar 21 '19 at 15:01

1

One tidyverse possibility could be:

df %>%
 gather(var, val, -ID, na.rm = TRUE) %>%
 group_by(ID, var) %>%
 distinct(val) %>%
 spread(var, val)

  ID       V1    V2    V3    V4
  <chr> <int> <int> <int> <int>
1 04C       6     9    NA     9
2 0F0      NA     5     7     4
3 167       8    10     5    NA
4 2D7       3     3    NA     1

answered Mar 21 '19 at 15:01

tmfmnk

38,881
4
47
67

`tidyverse`is very interesting, thank you. I had to install it first of course and also install `bindrcpp`. – Gecko Mar 21 '19 at 15:26
Works great but how can I save the output to a new data frame? – Gecko Mar 21 '19 at 15:30
`df_new <- df %>% ... ` – tmfmnk Mar 21 '19 at 15:31

score 0 · Answer 2 · answered Mar 21 '19 at 15:11

Could also go for:

library(dplyr)

df %>%
  group_by(ID) %>%
  summarise_all(~ if (any(!is.na(.))) first(na.omit(.)) else NA)

Output:

# A tibble: 4 x 5
  ID       V1    V2    V3    V4
  <fct> <int> <int> <int> <int>
1 04C       6     9    NA     9
2 0F0      NA     5     7     4
3 167       8    10     5    NA
4 2D7       3     3    NA     1

Combining rows with duplicate values and NAs [without using tidyverse]

2 Answers2