Rearranging rows and columns + using mean score in R

user_14409664 0
Viewed 37

I have a dataset that looks something like this right now where each row represents one subject:

score   item1   item2   item3
50     always   never   some
60     some     always  never
70     never    some    always
80     always   never   some
90     some     never   always

And I'm trying to create a table that shows the means of the scores for each item for each level (i.e. mean score for item 1, always? mean score for item1, some?)

Any suggestions on how to modify the dataset so it looks something like this:

       item1    item2   item3
always  mean#   mean#   mean#
some    mean#   mean#   mean#
never   mean#   mean#   mean#

Thanks!

reshape mean r
2 Answers

This is an excellent use of the pivot_ functions in tidyr.

library(tidyr)
library(dplyr) # for the pipe and across
# here is the data
df <- tructure(list(score = c(50, 60, 70, 80, 90), item1 = c("always", 
"some", "never", "always", "some"), item2 = c("never", "always", 
"some", "never", "never"), item3 = c("some", "never", "always", 
"some", "always")), class = c("spec_tbl_df", "tbl_df", "tbl", 
"data.frame"), row.names = c(NA, -5L), spec = structure(list(
    cols = list(score = structure(list(), class = c("collector_double", 
    "collector")), item1 = structure(list(), class = c("collector_character", 
    "collector")), item2 = structure(list(), class = c("collector_character", 
    "collector")), item3 = structure(list(), class = c("collector_character", 
    "collector"))), default = structure(list(), class = c("collector_guess", 
    "collector")), skip = 1), class = "col_spec"))

df %>%
  pivot_longer(starts_with("item"), values_to = "response") %>%
  pivot_wider(id_cols = response, names_from = name, values_from = score, values_fn = mean)

# A tibble: 3 x 4
  response item1 item2 item3
  <chr>    <dbl> <dbl> <dbl>
1 always      65  60      80
2 never       70  73.3    60
3 some        75  70      65
user_129294470

Here are some base R options:

  • Using stack + tapply
with(
  cbind(score = df$score, stack(df[-1])),
  tapply(score, list(values, ind), mean, na.rm = TRUE)
)

which gives

        ind
values   item1    item2 item3
  always    65 60.00000    80
  never     70 73.33333    60
  some      75 70.00000    65
  • Using lapply + tapply
do.call(
  cbind,
  lapply(df[-1], function(k) tapply(df$score, k, mean, na.rm = TRUE))
)

which gives

       item1    item2 item3
always    65 60.00000    80
never     70 73.33333    60
some      75 70.00000    65
user_121587570
Related