Ifelse: combine 3 categories into one

Question

Using this data.frame:

df <- read.table(text = c("
ID  cat1    cat2    cat3
site1   High    High    High
site1   High    High    Medium
site1   High    High    Low
site1   High    Medium  High
site1   High    Medium  Medium
site1   High    Medium  Low
site1   High    Low High
site1   High    Low Medium
site1   High    Low Low
site1   Medium  High    High
site1   Medium  High    Medium
site1   Medium  High    Low
site1   Medium  Medium  High
site1   Medium  Medium  Medium
site1   Medium  Medium  Low
site1   Medium  Low High
site1   Medium  Low Medium
site1   Medium  Low Low
site1   Low High    High
site1   Low High    Medium
site1   Low High    Low
site1   Low Medium  High
site1   Low Medium  Medium
site1   Low Medium  Low
site1   Low Low High
site1   Low Low Medium
site1   Low Low Low
"), header =T)

I want to create a new column called new_category based on cat1, cat2 and cat3.

I want each row in new_category to be have the common class "or word" in cat1, cat2 and cat3. If all values are different (High, Medium and Low), new_category will take the highest class (High in this case).

For example

If cat1 = High, cat2 = High, cat3= Medium, then new_category = High

If cat1 = High, cat2 = Medium, cat3= Low, then new_category = High

If cat1 = Medium, cat2 = Medium, cat3= Low, then new_category = Medium

I can do this using ifelse. However, there are many combinations of cat1 and cat2 and cat3.

Any suggestion for a faster or easier way to do that?

Answer 1

for rows with recurrent levels, choose most frequent one if there are such level; else choose the one with lowest rank;

# convert each row to ordered vector and find the entry with min rank;
df$new_category <- apply(
    df[2:4], 1, function(x){
        f <- table(x)
        if(max(f) > 1){
            names(f)[which.max(f)]
        }else{
            y <- factor(x, levels = c('High', 'Medium', 'Low'), ordered = T)
            as.character(min(y))
        }
    }
)
# > df
#       ID   cat1   cat2   cat3 new_category
# 1  site1   High   High   High         High
# 2  site1   High   High Medium         High
# 3  site1   High   High    Low         High
# 4  site1   High Medium   High         High
# 5  site1   High Medium Medium       Medium
# 6  site1   High Medium    Low         High
# 7  site1   High    Low   High         High
# 8  site1   High    Low Medium         High
# 9  site1   High    Low    Low          Low
# 10 site1 Medium   High   High         High
# 11 site1 Medium   High Medium       Medium
# 12 site1 Medium   High    Low         High
# 13 site1 Medium Medium   High       Medium
# 14 site1 Medium Medium Medium       Medium
# 15 site1 Medium Medium    Low       Medium
# 16 site1 Medium    Low   High         High
# 17 site1 Medium    Low Medium       Medium
# 18 site1 Medium    Low    Low          Low
# 19 site1    Low   High   High         High
# 20 site1    Low   High Medium         High
# 21 site1    Low   High    Low          Low
# 22 site1    Low Medium   High         High
# 23 site1    Low Medium Medium       Medium
# 24 site1    Low Medium    Low          Low
# 25 site1    Low    Low   High          Low
# 26 site1    Low    Low Medium          Low
# 27 site1    Low    Low    Low          Low