Search code examples
rdataframeextractdata-extraction

How to extract an unique element according a set of prefered conditions


Taking the data-frame df, I would like to extract the unique value according to the following preferred conditions per each Field:

1- if C1 exists, extract the respective value and ignore the others

2- if C2 exists, extract the respective value and ignore the others

... and so on up to C5

data:

df <- data.frame (Field=rep(c("F1","F2","F3","F4","F5"),each=3),
              Cond=rep(c("C1","C2","C3","C4","C5"),3),
              Value=c(1:15))

the desired output:

output <-  data.frame (F= c("F1","F2","F3","F4","F5"),
                   C= c("C1","C1","C2","C1","C3"),
                   Value= c(1,6,7,11,13))

(note1: the values were only set as such to a matter of exemplification, the real data values are not ordered)

(note2: The real conditional column is not ordered alphabetically at all. my though was to have something like, if A exists than chose "A value", otherwise pass to the next condition "if B exists ..." and so on)


Solution

  • Another option is using data.table

    library(data.table)
    setDT(df)[order(Field, Cond), head(.SD, 1), by = Field]
    #    Field Cond Value
    #1:    F1   C1     1
    #2:    F2   C1     6
    #3:    F3   C2     7
    #4:    F4   C1    11
    #5:    F5   C3    13