Rearranging data according to rater and subject, simultaneously creating new row names-CodePudding

I have a dataset where multiple raters rate multiple subjects.

I'd like to rearrange the data that looks like this:

data <- data.frame(rater=c("A", "B", "C", "A", "B", "C"),
                   subject=c(1, 1, 1, 2, 2, 2),
                   measurment1=c(1, 2, 3, 4, 5,6),
                   measurment2=c(11, 22, 33, 44, 55,66),
                   measurment3=c(111, 222, 333, 444, 555, 666))

data
#     rater   subject  measurment1 measurment2 measurment3
# 1     A       1           1          11         111
# 2     B       1           2          22         222
# 3     C       1           3          33         333
# 4     A       2           4          44         444
# 5     B       2           5          55         555
# 6     C       2           6          66         666

into data that looks like this:

data_transformed <- data.frame( A = c(1,11,111,4,44,444),
                                B = c(2,22,222,5,55,555),
                                C = c(3,33,333,6,66,666) 
)

row.names(data_transformed) <- c("measurment1_1", "measurment2_1", "measurment3_1", "measurment1_2", "measurment2_2", "measurment3_2") 

data_transformed
#                 A   B   C
# measurment1_1   1   2   3
# measurment2_1  11  22  33
# measurment3_1 111 222 333
# measurment1_2   4   5   6
# measurment2_2  44  55  66
# measurment3_2 444 555 666

In the new data frame, the raters (A, B and C) should become the columns. The measurement should become the rows and I'd also like to add the subject number as a suffix to the row-names.

For the rearranging one could probably use the pivot functions, yet I have no idea on how to combine the measurement-variables with the subject number.

Thanks for your help!

CodePudding user response：

We could use pivot_longer, pivot_wider and unite from the tidyr package.

pivot_longer makes our data in a vertical format, it transforms the measurment columns into a sigle variable

pivot_wider does the opposite of pivot_longer, transform a variable into multiple columns for each unique value from the variable

  data |> 
      pivot_longer(measurment1:measurment3) |> 
      pivot_wider(names_from = rater, values_from = value, values_fill = 0 ) |> 
      unite("measure_subjet",name,subject, remove = TRUE)

CodePudding user response：

Please try the below code where we can accomplish the expected result using pivot_longer, pivot_wider and column_to_rownames.

library(tidyverse)

data_transformed <- data %>% 
  pivot_longer(c('measurment1', 'measurment2', 'measurment3')) %>% 
  mutate(rows = paste0(name, '_', subject)) %>% 
  pivot_wider(rows, names_from = rater, values_from = value) %>% 
  column_to_rownames(var = "rows")