CODEWITHSUNDEEP
raggregate
Matteo
Matteo

Reputation: 73

How to aggregate data several times on the same table

I am working in R and have a list with 3 columns:

Fruit     Drawer      Amount
Banana    Top         1
Peach     Top         2
Apple     Top         3
Banana    Mid         4
Peach     Mid         5
Apple     Mid         6
Banana    Bottom      7
Peach     Bottom      8
Apple     Bottom      9

and I want to create the smallest ratio of fruit type (ex. bananas) in each drawer (ex. Top) to total fruit (all the bananas).

I am using table:

x <- table(fruits)

but I get a type of data that I don't know how to work with.

Ultimately I want to get "bananas per drawer" divided by the "total bananas" in all the drawers. I guess I could do it column by column but I am sure there are better ways to go about it. Any suggestion?

Sorry for any etiquette mishaps, I haven't been programming for long.

Thanks.

Upvotes: 0

Views: 110

Answers (1)

bramtayl
bramtayl

Reputation: 4024

Do you want something like this:

library(dplyr)

fruit__drawer = 
"Fruit     Drawer      Amount
Banana    Top         1
Peach     Top         2
Apple     Top         3
Banana    Mid         4
Peach     Mid         5
Apple     Mid         6
Banana    Bottom      7
Peach     Bottom      8
Apple     Bottom      9" %>% 
  read.table(text = . , header = TRUE)

fruit =
  fruit__drawer %>%
  group_by(Fruit) %>%
  summarize(Amount.fruit = sum(Amount)) %>%
  mutate(Proportion.overall = Amount.fruit / sum(Amount.fruit))

result = 
  fruit__drawer %>%
  left_join(fruit) %>%
  group_by(Drawer) %>%
  mutate(Proportion= Amount/sum(Amount),
         Proportion.ratio = Proportion/Proportion.overall)

Upvotes: 1

Related Questions