1

I have a dataset like below. I want to create the score variable with code.

Calculation of the score; Total of the entire row / Number of full columns

For example, the score calculation for row 1: (1+2+1) / 3 = 1.33

For example, the score calculation for row 2: (1+1) / 2 = 1

df <- data.frame(
   X1 = c(1,1,2,3,NA), 
   X2 = c(2,1,NA,2,2), 
   X3 = c(1,NA,2,2,1),
   Skor=c(1.33,1,2,2.33,1.5)
)
df

> df
  X1 X2 X3 Skor
1  1  2  1 1.33
2  1  1 NA 1.00
3  2 NA  2 2.00
4  3  2  2 2.33
5 NA  2  1 1.50
>
s_baldur
  • 29,441
  • 4
  • 36
  • 69
Coder
  • 41
  • 6

4 Answers4

3

You could actually take rowwise mean ignoring NA values.

df$Skor <- rowMeans(df, na.rm = TRUE)
df

#  X1 X2 X3     Skor
#1  1  2  1 1.333333
#2  1  1 NA 1.000000
#3  2 NA  2 2.000000
#4  3  2  2 2.333333
#5 NA  2  1 1.500000

data

Assuming you don't have Skor column originally in your data.

df <- data.frame(
  X1 = c(1,1,2,3,NA), 
  X2 = c(2,1,NA,2,2), 
  X3 = c(1,NA,2,2,1),
)
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
1

Using apply():

cols <- paste0('X', 1:3)

df$Skor2 <- 
  apply(df[cols], 1, function(x) sum(x, na.rm = TRUE) / sum(!is.na(x)))
df
#   X1 X2 X3 Skor    Skor2
# 1  1  2  1 1.33 1.333333
# 2  1  1 NA 1.00 1.000000
# 3  2 NA  2 2.00 2.000000
# 4  3  2  2 2.33 2.333333
# 5 NA  2  1 1.50 1.500000
s_baldur
  • 29,441
  • 4
  • 36
  • 69
1

You can use addmargins:

addmargins(as.matrix(df[,-4]), 2, list(Skor=function(x) mean(x, na.rm=TRUE)))
#     X1 X2 X3     Skor
#[1,]  1  2  1 1.333333
#[2,]  1  1 NA 1.000000
#[3,]  2 NA  2 2.000000
#[4,]  3  2  2 2.333333
#[5,] NA  2  1 1.500000
GKi
  • 37,245
  • 2
  • 26
  • 48
1

The rowMeans approach by Ronak Shah is super concise already, here is another option

df$Skor <- rowSums(df,na.rm = TRUE)/rowSums(!is.na(df))
ThomasIsCoding
  • 96,636
  • 9
  • 24
  • 81