Add a column to indicate the repetition rate of selected columns across each row-CodePudding

I have a dataframe like this:

df <- data.frame(ID = c(1,2,3,4,5), 
                 Total = c(1,1,2,1,2), 
                 Ma = c(1,2,1,2,1), 
                 Mb = c(1,2,1,2,2), 
                 Md = c(1,2,1,2,1), 
                 Me = c(1,1,1,2,2))

I'd like to add a column to indicate the maximum of repetition rate, from Total through Me column for each row. It should be something like:

rep.rate = c(1,0.6,0.8,0.8,0.6)

These values indicate the rate of repetition for the most common value across the five columns in each row.

CodePudding user response：

You can try,

apply(df[-1], 1, function(i)max(prop.table(table(i))))
#[1] 1.0 0.6 0.8 0.8 0.6

CodePudding user response：

df <- data.frame(ID = c(1,2,3,4,5), Total = c(1,1,2,1,2), Ma = c(1,2,1,2,1), Mb = c(1,2,1,2,2), Md = c(1,2,1,2,1), Me = c(1,1,1,2,2))

library(dplyr, warn.conflicts = FALSE)

get_repeat_rate <- function(x){
  table <- table(x)
  props <- table/sum(table
  max_prop <- max(props)
  return(max_prop)
}

df |> 
  rowwise() |> 
  mutate(repeat_rate = get_repeat_rate(c_across(-ID)))

#> # A tibble: 5 × 7
#> # Rowwise: 
#>      ID Total    Ma    Mb    Md    Me repeat_rate
#>   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>       <dbl>
#> 1     1     1     1     1     1     1         1  
#> 2     2     1     2     2     2     1         0.6
#> 3     3     2     1     1     1     1         0.8
#> 4     4     1     2     2     2     2         0.8
#> 5     5     2     1     2     1     2         0.6

^{Created on 2022-12-02 with reprex v2.0.2}

CodePudding user response：

library(dplyr)

df <- data.frame(ID = c(1,2,3,4,5), Total = c(1,1,2,1,2), Ma = c(1,2,1,2,1), Mb = c(1,2,1,2,2), Md = c(1,2,1,2,1), Me = c(1,1,1,2,2))

cat_mode <-
  function(x){
    
    cat_levels <- unique(x)
    
    out <- cat_levels[which.max(tabulate(match(x, cat_levels)))]
    
    return(out)
    
  }

df %>% 
  rowwise() %>% 
  mutate(rep.rate = sum(c_across(Total:Me) == cat_mode(c_across(Total:Me)),na.rm =TRUE)/5 )

# A tibble: 5 x 7
# Rowwise: 
     ID Total    Ma    Mb    Md    Me rep.rate
  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>    <dbl>
1     1     1     1     1     1     1      1  
2     2     1     2     2     2     1      0.6
3     3     2     1     1     1     1      0.8
4     4     1     2     2     2     2      0.8
5     5     2     1     2     1     2      0.6

CodePudding user response：

Here's a more simplified dplyr solution that does not need a user-defined function:

library(dplyr)

df %>% 
  rowwise %>% 
  mutate(rep.rate = max(table(c_across(-ID)))/(ncol(.)-1)) %>% 
  ungroup

# # A tibble: 5 x 7
#      ID Total    Ma    Mb    Md    Me rep.rate
#   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>    <dbl>
# 1     1     1     1     1     1     1      1  
# 2     2     1     2     2     2     1      0.6
# 3     3     2     1     1     1     1      0.8
# 4     4     1     2     2     2     2      0.8
# 5     5     2     1     2     1     2      0.6

CodePudding user response：

The steps to approach this problem are the following:

df%>%
rowwise()%>%
mutate(rep.rate=sum(across(Total:Me)== max(Total:Me))/5)

The rowwise() make all operations row wise. Then mutate is used to create the new column which is according to this: max(Total:Me) finds the max value. Then sum(across(Total:Me)== max) finds how many occurences are there of the max value in the current row. Then we divide this number by 5 to get the needed proportion.

CodePudding user response：

If the columns take only 2 values as in the example data:

0.5   abs(rowMeans(df[,-1] == df[1, 2]) - 0.5)
#> [1] 1.0 0.6 0.8 0.8 0.6

If they take more than 2 values, a vectorized solution using matrixStats::rowTabulates:

library(matrixStats)

rowMaxs(
  rowTabulates(
    matrix(
      match(
        unlist(df[,-1]),
        unique(unlist(df[,-1]))
      ), nrow(df)
    )
  )
)/(ncol(df) - 1)
#> [1] 1.0 0.6 0.8 0.8 0.6