Pivoting with Multiple Variables?

Question

I am trying to build a pivot table in the following way:

Within variable "A", I am trying to look at "B", to look at the average of the Z values of C1, C2, C3, and C4.

I have tried using the rpivottable, but the average(Z) is not the same for C1, C2, C3, and C4 and I am unable to save these values to a variable. I also tried using the following code, but it also does not give me the same average for the variable "Z" for C1:4 of B1 of A1. Here is an example:

pivot<- data                    
   group_by(A, B, C) %>%         
   summarize(mean(Z))
View(pivot)

Here is an example of the pivot table I would like to make: image

I would like to save this average(Z) value and add it to my dataframe.

Thank you.

Answer 1

I created a random dataset to make your dataset, that you attached as an image, reproducible. I hope this works for you. I created random numbers to represent the column Z.

library(dplyr)
A <- rep(0:1,each=8)
B <- rep(rep(1:2,each=4),2)
C <- rep(1:4,4)
Z <- runif(16)*10
data <- data.frame(A,B,C,Z)
          pivot<- data %>% mutate(A=as.character(A),as.character(B)) %>% 
            group_by(A, B) %>%         
           summarize(mean(Z))
          View(pivot)