How to convert diagonal rows into single row in R?

Question

I have a dataset1 which is as follows:

dataset1 <- data.frame(  
   id1 = c(1, 1, 1, 2, 2, 2),    
   id2 = c(122, 122, 122, 133, 133, 133),  
   num1 = c(1, NA, NA, 50,NA, NA),  
   num2 = c(NA, 2, NA, NA, 45, NA),  
   num3 = c(NA, NA, 3, NA, NA, 4)  
 )

How to convert multiple rows into a single row?

The desired output is:

id1, id2, num1, num2, num3   
1    122   1     2      3      
2    133   50    45     4

`library(dplyr); dataset1 %>% group_by(id1, id2) %>% summarise_all(funs(sum(.,na.rm = TRUE)))` — Jaap, Apr 28 '18 at 09:55
using `diag` : `dataset1 %>% group_by(id1,id2) %>% do(data.frame(t(diag(as.matrix(.[-(1:2)])))))` — moodymudskipper, Apr 28 '18 at 19:36

score 1 · Answer 1 · answered Apr 28 '18 at 09:56

library(dplyr)

dataset1 %>% group_by(id1, id2) %>%
  summarise_all(funs(.[!is.na(.)])) %>%
  as.data.frame()

#   id1 id2 num1 num2 num3
# 1   1 122    1    2    3
# 2   2 133   50   45    4

Note: Assuming there will be only 1 non-NA item in a column.

score 0 · Answer 2 · answered Apr 28 '18 at 09:57

0

Using data.table

library(data.table)
data.table(dataset1)[, lapply(.SD, sum, na.rm = TRUE), by = c("id1", "id2")]

#   id1 id2 num1 num2 num3
#1:   1 122    1    2    3
#2:   2 133   50   45    4

answered Apr 28 '18 at 09:57

nghauran

6,648
2
20
29

score 0 · Answer 3 · answered Apr 28 '18 at 09:57

You can use dplyr to achieve that:

library(dplyr)
dataset1 %>% 
  group_by(id1, id2) %>% 
  mutate(
    num1 = sum(num1, na.rm=T),
    num2 = sum(num2, na.rm=T),
    num3 = sum(num3, na.rm=T)
  ) %>% 
  distinct()

Output:

This is also assuming if there's a repeated value in any of the variable we're going to sum it (if id1 = 1 has two values for num1, we're going to sum the value). If you're confident that every id has only one possible value for each of the num (num1 to num3), then don't worry about it.

How to convert diagonal rows into single row in R?

3 Answers3