-
Notifications
You must be signed in to change notification settings - Fork 4
/
Pivot tables in R - adding a calculated field.R
52 lines (38 loc) · 1.82 KB
/
Pivot tables in R - adding a calculated field.R
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
# clean up the environment
rm(list = ls())
# we'll need dplyr and tidyr for this, by far easier than with base R
library(dplyr)
library(tidyr)
# read in the Canadian SuperStore dataset from this dropbox address:
df <- read.csv("https://www.dropbox.com/s/kj9yioc24iq4pdb/superstore.csv?dl=1")
write.csv(df, "superstore.csv")
# in a previous tutorial, we created this summary table:
pivot <- df %>%
select(Product.Category, Region, Customer.Segment, Sales)%>%
group_by(Product.Category, Region, Customer.Segment) %>%
summarise(TotalSales = sum(Sales), AvgSales = mean(Sales,na.rm = TRUE),
NumSales = length(!is.na(Sales)))
head(pivot)
# Now, let's add a calculated column from two other columns and calculate
# profitability. we'll add "Profit" to the summarize function, and use profit/sales
# in the calculated column:
pivot <- df %>%
select(Product.Category, Region, Customer.Segment, Sales, Profit)%>%
group_by(Product.Category, Region, Customer.Segment) %>%
summarise(TotalSales = sum(Sales), TotalProfit = sum(Profit),
AvgSales = mean(Sales,na.rm = TRUE),
NumSales = length(!is.na(Sales))) %>%
mutate(AvgProfitMar = sum(TotalProfit)/TotalSales) %>%
arrange(Region, desc(AvgSales))
head(pivot)
# note that MUTATE can be used here (creating a new column) but since the new column is a summary
# operation, it can also simply be added to the summary function:
pivot <- df %>%
select(Product.Category, Region, Customer.Segment, Sales, Profit)%>%
group_by(Product.Category, Region, Customer.Segment) %>%
summarise(TotalSales = sum(Sales), TotalProfit = sum(Profit),
AvgSales = mean(Sales,na.rm = TRUE),
NumSales = length(!is.na(Sales)),
AvgProfitMar = sum(TotalProfit)/TotalSales) %>%
arrange(Region, desc(AvgSales))
head(pivot)