Home > Mobile >  Selective merging rows by conditions in R
Selective merging rows by conditions in R

Time:10-11

I have a data.frame that looks like this:

ID Time value
A T1 up
A T2 down
A T3 no
B T1 up
B T2 down
B T3 no
B T1 no
B T2 no
B T3 up
C T1 up
C T2 no
C T3 no
C T1 no
C T2 no
C T3 down

I would like to merge it like this

ID Time value
A T1 up
A T2 down
A T3 no
B T1 up
B T2 down
B T3 up
C T1 up
C T2 no
C T3 down

To explain: If A (ID) and B (Time) column are the same, then merge C (value) column; However, C (value) column is category type (only three kinds: up, down and no). When merging, always keep up or down, but not no if the merged rows contain both (up and no, or down and no). If merged rows only contain no, then keep no.

I have more than 5000 rows in the file, so only want to keep the unique ID at specific Time with specific value (either up or down, or no if neither both).

CodePudding user response:

Does this work:

library(dplyr)
df %>% group_by(ID, Time) %>% filter(all(value == 'no') | value !='no') %>% distinct()
# A tibble: 9 x 3
# Groups:   ID, Time [9]
  ID    Time  value
  <chr> <chr> <chr>
1 A     T1    up   
2 A     T2    down 
3 A     T3    no   
4 B     T1    up   
5 B     T2    down 
6 B     T3    up   
7 C     T1    up   
8 C     T2    no   
9 C     T3    down 

CodePudding user response:

This could also handle cases where both up and down appear, by prioritizing one of them in the ifelse clause:

df %>% group_by(A, B) %>% summarise(C = ifelse("up" %in% C, "up", ifelse("down" %in% C, "down", "no")))
  • Related