subset dataframe based on hierarchical preference of factor levels within column in R

user_12770249 0
Viewed 113

I have a dataframe which I would like to subset based on hierarchical preference of factor levels within a column. With following example I want to show, that per level of "ID" I want to select only one "method". Specifically, if possible keeping CACL, if CACL doesn't exist for this level, then subset for "KCL" and if that doesn't exist, then subset for "H2O". 

ID<-c(1,1,1,2,2,3)
method<-c("CACL","KCL","H2O","H2O","KCL","H2O")
df1<-data.frame(ID,method)

  ID  method
1  1    CACL
2  1     KCL
3  1     H2O
4  2     H2O
5  2     KCL
6  3     H2O

ID<-c(1,2,3)
method<-c("CACL","KCL","H2O")
df2<-data.frame(ID,method)

  ID  method
1  1    CACL
2  2     KCL
3  3     H2O

I have done something similar subsetting by selecting a minimum number within a level, but am not able to adapt it. Am wondering whether I should use ifelse here too?

#if present, choose rows containing "number" 2 instead of 1 (this column contained only the two numbers 1 and 2)

library(dplyr)
new<-df %>%
group_by(col1,col2,col3) %>%
summarize(number = ifelse(any(number > 1), min(number[number>1]),1))
dfnew<-merge(new,df,by=c("colxyz","number"),all.x=T)
subset dataframe r
2 Answers

You can use order with match and then simply !duplicated:

df1 <- df1[order(match(df1$method, c("CACL","KCL","H2O"))),]
df1[!duplicated(df1$ID),]
#  ID method
#1  1   CACL
#5  2    KCL
#6  3    H2O

#Variant not changing df1
i <- order(match(df1$method, c("CACL","KCL","H2O")))
df1[i[!duplicated(df1$ID[i])],]
user_104885040

An option using dplyr:

df1 %>% 
  mutate(preference = match(method,  c("CACL","KCL","H2O"))) %>% 
  group_by(ID) %>% 
  filter(preference == min(preference)) %>% 
  select(-preference)

# A tibble: 3 x 2
# Groups:   ID [3]
     ID method
  <dbl> <fct> 
1     1 CACL  
2     2 KCL   
3     3 H2O
user_62421320
Related