CODEWITHSUNDEEP
rpivot-table
victor8910
victor8910

Reputation: 203

How to create a crosstab table using two tables in R?

I have excel dataset as follows:

Weight Quantity Price
72       5      460
73       8      720
75       20     830
95       2      490
91       15     680
82       14     340
88       30     250
89       6      770
78       27     820
98       24     940
99       29     825

I want to get a weight vs Quantity pivot table with sum of prices for each category as follows:

        0-10     10-20     20-30
70-80   1180     830        820
80-90   770      340        250
90-100  490      680        1765

I had created two tables for the individual categories to get the average and count using dplyr package as follows:

table1 <- group_by(dataset, Weight = cut(Weight, breaks = c(70,80,90,100))
result1 <- summarise(table1, Count = n(), Avg_Price = mean(Price, na.rm = T))
table2 <- group_by(dataset, Quantity = cut(Quantity, breaks = c(0,10,20,30))
result2 <- summarise(table2, Count = n(), Avg_Price = mean(Price, na.rm = T))

Now, How do i use table1 and table2 to create a crosstab table as above?

Upvotes: 3

Views: 615

Answers (2)

phiver
phiver

Reputation: 23608

A dplyr and tidyr solution:

library(dplyr)
library(tidyr)

df %>% 
  mutate(Weight = cut(Weight, breaks = c(70,80,90,100)),
         Quantity = cut(Quantity, breaks = c(0,10,20,30))) %>% 
  group_by(Weight, Quantity) %>% 
  summarise(Price = sum(Price)) %>% 
  spread(Quantity, Price)

# A tibble: 3 x 4
# Groups:   Weight [3]
  Weight   `(0,10]` `(10,20]` `(20,30]`
* <fct>       <int>     <int>     <int>
1 (70,80]      1180       830       820
2 (80,90]       770       340       250
3 (90,100]      490       680      1765

Data:

df <- structure(list(Weight = c(72L, 73L, 75L, 95L, 91L, 82L, 88L, 
89L, 78L, 98L, 99L), Quantity = c(5L, 8L, 20L, 2L, 15L, 14L, 
30L, 6L, 27L, 24L, 29L), Price = c(460L, 720L, 830L, 490L, 680L, 
340L, 250L, 770L, 820L, 940L, 825L)), .Names = c("Weight", "Quantity", 
"Price"), class = "data.frame", row.names = c(NA, -11L))

Upvotes: 2

Rui Barradas
Rui Barradas

Reputation: 76611

Maybe the following is what you want. It uses cut like you have, then xtabs.

Weight = cut(dataset$Weight, breaks = c(70,80,90,100))
Quantity = cut(dataset$Quantity, breaks = c(0,10,20,30))
dt2 <- data.frame(Weight, Quantity, Price = dataset$Price)
xtabs(Price ~ Weight + Quantity, dt2)
#          Quantity
#Weight     (0,10] (10,20] (20,30]
#  (70,80]    1180     830     820
#  (80,90]     770     340     250
#  (90,100]    490     680    1765

Upvotes: 5

Related Questions