select row matrix based on same name in other row in r

Question

I have a matrix and i want to select part of row based column first, like below example:

original matrix:

x1     x2     x3     x4     x5
a       a     15     16      0  
b       j     12     10      8
c       h     14     0       0
d       b     28     41      0
        d     13     60      9
        c     12     78      98

The matrix I want to get:

x1     x2     x3     x4     x5
a       a     15     16      0  
b       b     28     41      0
c       c     12     78      98
d       d     13     60      9

Please read the information at the top of the [tag:r] tag page. In particular the input should be shown using `dput`. — G. Grothendieck, Jul 15 '21 at 13:38

score 3 · Answer 1 · answered Jul 15 '21 at 13:41

Considering you have a dataframe and the empty cells are NA, you can do:

library(tidyverse)
df <- data.frame(x1 = c("a",'b','c','d',NA,NA),
              x2 = c("a",'j','h','b','d','c'),
              x3 = c(15,12,14,28,13,12),
              x4 = c(16,10,0,41,60,78),
              x5 = c(0,8,0,0,9,98))


df <- full_join(df %>% 
                  filter(!is.na(x1)) %>% 
                  select(x1),
                df %>% 
                  filter(x2 %in% x1) %>% 
                  select(-x1),
                by = c("x1" = "x2")) %>% 
  mutate(x2 = x1) %>% 
  relocate(x1, x2)

Output:

  x1 x2 x3 x4 x5
1  a  a 15 16  0
2  b  b 28 41  0
3  c  c 12 78 98
4  d  d 13 60  9

select row matrix based on same name in other row in r

1 Answers1