finding/assigning average across multiple columns

Question

I am trying to give participant in my study an average value across a subscale of 20 questions. I first converted the string responses (ex. "Never (zero occasions)", "Rarely (one or two occasions", etc) to 1 through 5 numerical outputs using the following code:

    dataset.clean1$Q11_22 <- revalue(dataset.clean1$Q11_22 , c("Never (zero occasions)" = "1" , "Rarely (one or two occasions)" = "2", "Multiple times (three or more occasions)" = "3", "Regularly (at least once every 1-3 months)" = "4", "Weekly (at least once a week)" = "5"))

i did this 20 times for each question (i know it's probably the least elegant method but it is all i know so far).

Now i am trying to get each participants average across Q11_22 to Q32_43 and get R to skip any blank/empty rows. i tried the following (not all twenty columns included so i could quickly test it out):

    ave.exposure <- dataset.clean1$ave.exposure <- mean(dataset.clean1$Q11_22, dataset.clean1$Q12_23, dataset.clean1$Q13_24, na.rm=TRUE)

i got the following error:

Warning message: In mean.default(dataset.clean1$Q11_22, dataset.clean1$Q12_23, dataset.clean1$Q13_24, :argument is not numeric or logical: returning NA

Now i am confused about two things:

do i need to not put the numeric values i am trying to replace my string with in " " in step 1 (ex.

dataset.clean1$Q11_22 <- revalue(dataset.clean1$Q11_22 , c("Never (zero occasions)" = 1

instead of:

dataset.clean1$Q11_22 <- revalue(dataset.clean1$Q11_22 , c("Never (zero occasions)" = "1"`) ?

how (in an easy to understand way, no matter how tedious) do i find the average for each participant across all these columns?

You've converted your text strings to other text strings using `revalue` - `"1"` (character) is not the same as `1` (number). Which is you get an error saying that your data isn't numeric. If you're working across rows, then you can use something like `rowMeans(dataset[ c("var1","var2") ], na.rm=TRUE)` I think. See here for a clearer explanation - https://stackoverflow.com/questions/22458644/calculate-mean-across-rows-with-na-values-in-r — thelatemail, Feb 25 '21 at 22:31
It would be easier to help if you create a small reproducible example along with expected output. Read about [how to give a reproducible example](http://stackoverflow.com/questions/5963269). — Ronak Shah, Feb 26 '21 at 03:02
hiya thelatemail. i have chosen to convert the characters into numbers using a similar solution dataset.clean1$Q11_22 <- as.numeric(dataset.clean1$Q11_22, c("Never (zero occasions)" = 1 , "Rarely (one or two occasions)" = 2, "Multiple times (three or more occasions)" = 3, "Regularly (at least once every 1-3 months)" = 4, "Weekly (at least once a week)" = 5)) which worked at first but is now saying "NA's introduced by coercion" and turning my column full of strings to all NA. I've not been able to find a solution for this online, do you know how this might be resolved? — psychNmesoma, Mar 02 '21 at 16:24

score 0 · Answer 1 · answered Feb 26 '21 at 03:43

So there are two steps to this:

Converting all your factor columns into numeric (Link)

Averaging over your numeric columns (Q11_22 to Q32_43)

# Step 1. I assume that your columns are all together
library(tidyverse)
dataset.clean1 <- dataset.clean1 %>%
      mutate_at(vars(Q11_22:Q32_43),
                 function(x) as.numeric(levels(x))[x])

 # Step 2. Averaging over the columns
 # I also assume that you have other columns in the dataset 
 # which you do not want to delete

 start_col <- which(names(dataset.clean1),"Q11_22")
 end_col <- which(names(dataset.clean1),"Q32_43")
 # Specifying the vector of columns you want to row-average over
 indices = c(start_col:end_col)
 dataset.clean1 <- dataset.clean1 %>%
    mutate(average_of_20qn = apply(.[,indices], 
            MARGIN=1, FUN=mean))

Hiya jvargh7, unfortunately i was not able to correctly apply your suggested solution as i do not yet have the knowledge to do so in r but i appreciate your help a lot! and will try again next time i can get in-person help. — psychNmesoma, Mar 02 '21 at 16:31

finding/assigning average across multiple columns

1 Answers1