0

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.

codable
  • 19
  • 7
  • Please add data using `dput` or something that we can copy and use to provide answers. Read about [how to ask a good question](http://stackoverflow.com/help/how-to-ask) and how to give a [reproducible example](http://stackoverflow.com/questions/5963269). – Ronak Shah Oct 20 '20 at 02:37

1 Answers1

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)
Abdurrahman Yavuz
  • 114
  • 8
  • Thank you, @Abdurrahman. Is there a way to code for something similar except with C being a nested variable of B which is a nested variable of A? For example, if A is the country, B is the state, C is the city, and Z are the amount of people in each city. Following this analogy, I want to find the average amount of people in 3 cities in the state of California in the country of United States. Your help would be greatly appreciated. Thank you. – codable Oct 20 '20 at 22:59
  • I think the code in my answer is exactly doing what you have described. You just need to repopulate columns with country(A), state(B), city(C). – Abdurrahman Yavuz Oct 21 '20 at 20:33