Aggregates in R

1.Introduction

In this lesson you will learn about aggregates in R using dplyr. An aggregate statistic is a way of creating a single number that describes a group of numbers. Common aggregate statistics include mean, median, and standard deviation.

Additionally, you will learn how you can group data into different subsets based on column values. This can help narrow the focus of a summary statistic to a subset of a dataset. R you ready to get started?

Instructions

In the workspace to the right we have loaded data from ShoeFly.com, a fictional e-commerce shoe store. The data includes information regarding customer orders as well as the source of page visits to ShoeFly.com’s website.

Review the code and the output. What calculations are made, and on which subsets of the data do they occur?

Throughout the rest of this lesson you will dig into ShoeFly.com’s data to learn more about its customer’s orders and how users are discovering the website. Proceed to the next exercise to get started!

# load packages
library(readr)
library(dplyr)

# load data
orders <- read_csv("orders.csv")
page_visits <- read_csv("page_visits.csv")

# inspect data frames
orders

page_visits

# average price of order
average_price <- orders %>% 
  summarize(mean_price = mean(price, na.rm = TRUE))
average_price

# page visits by UTM source
click_source <- page_visits %>%
  group_by(utm_source) %>%
  summarize(count = n())
click_source

# page visits by UTM source and month
click_source_by_month <- page_visits %>%
  group_by(utm_source,month) %>%
  summarize(count = n())
click_source_by_month

2.Calculating Column Statistics

In this exercise, you will learn how to combine all of the values from a column for a single calculation. This can be done with the help of the dplyr function summarize(), which returns a new data frame containing the desired calculation.

Some examples of this type of calculation include:

1.The data frame customers contains the names and ages of all of your customers. You want to find the median age:

customers <- data.frame(
  name = c("Alice", "Bob", "Claire", "Daniel", "Elaine", "Frank", "Grace"),
  age = c(23, 25, 31, 35, 35, 46, 62)
)

customers %>%
  select(age)

#c(23, 25, 31, 35, 35, 46, 62)
customers %>%
  summarize(median_age = median(age))

#35

2.The data frame shipments contains address information for all shipments that you’ve sent out in the past year. You want to know how many different states you have shipped to.

shipments <- data.frame(
  states = c('CA', 'CA', 'CA', 'CA', 'NY', 'NY', 'NJ', 'NJ', 'NJ', 'NJ', 'NJ', 'NJ', 'NJ')
)

shipments %>%
  summarize(n_distinct_states = n_distinct(states))

#3

3.The data frame inventory contains a list of types of t-shirts that your company makes. You want to know the standard deviation of the prices of your inventory.

inventory <- data.frame(
  price = c(31, 23, 30, 27, 30, 22, 27, 22, 39, 27, 36)
)

inventory %>%
  summarize(sd_price = sd(price))

The general syntax for these calculations is:

df %>% summarize(var_name = command(column_name))

1.df is the data frame you are working with

2.summarize is a dplyr function that reduces multiple values to a single value

3.var_name is the name you assign to the column that stores the results of the summary function in the returned data frame

4.command is the summary function that is applied to the column by summarize()

5.column_name is the name of the column of df that is being summarized

The following table includes common summary functions that can be given as an argument to summarize():

knitr::include_graphics("C:/Users/kuoan/Desktop/R Code/Aggregates1.png")

Instructions

1.ShoeFly.com has a new batch of orders stored in the data frame orders. Inspect the first 10 rows of the data frame using head().

# load order data
orders <- read_csv("orders.csv")

# inspect orders here:
head(orders, 10)

2.Our finance department wants to know the price of the most expensive pair of shoes purchased. Save your answer to the variable most_expensive.

# define most_expensive here:
most_expensive <- orders %>%
  summarize(most_expensive =  max(price))
most_expensive

3.Woah, wait a minute! Take a look at the output of the code you just ran. The result for the most expensive pair of shoes is coming back as NA. Why is this happening?

If you scroll up in the rendered notebook to where orders.csv is loaded, you can see a warning about row 99 of the file. There is a missing column of information! It appears that the price for row 99 was not in the file, and this is causing your maximum value calculation to return NA.

Add the following as an additional argument to max() so that it removes all missing values before computing the maximum value. ” na.rm = TRUE ”

# define most_expensive here:
most_expensive <- orders %>%
  summarize(most_expensive =  max(price, na.rm = TRUE))
most_expensive

4.Our fashion department wants to know how many different colors of shoes we are selling. Save your answer to the variable num_colors.

# define num_colors here:
num_colors <- orders %>%
  summarize(num_colors = n_distinct(shoe_color, na.rm = TRUE))
num_colors 

3.Calculating Aggregate Functions I

When we have a bunch of data, we often want to calculate aggregate statistics (mean, standard deviation, median, percentiles, etc.) over certain subsets of the data.

Suppose we have a grade book with columns student, assignment_name, and grade:

knitr::include_graphics("C:/Users/kuoan/Desktop/R Code/Aggregates2.png")

We want to get an average grade for each student across all assignments. We can do this using the helpful dplyr function group_by().

For this example, we’d use the following piece of code:

” grades <- df %>% group_by(student) %>% summarize(mean_grade = mean(grade)) ”

The output might look something like this:

knitr::include_graphics("C:/Users/kuoan/Desktop/R Code/Aggregates3.png")

In general, we use the following syntax to calculate aggregates:

” df %>% group_by(column_1) %>% summarize(aggregate_name = command(column_2)) ”

1.column_1 (student in our example) is the column that we want to group_by()

2.column_2 (grade in our example) is the column that we want to apply command(), a summary function, to using summarize()

3.aggregate_name is the name assigned to the calculated aggregate

In addition to the summary functions discussed in the last exercise (mean(), median(), sd(), var(), min(), max(), IQR() and n_distinct()), another helpful summary function, especially for grouped data, is n(). n() will return the count of the rows within a group, and does not require a column as an argument. To get the count of the rows in each group of students from our example:

” grades <- df %>% group_by(student) %>% summarize(count = n()) ”

Instructions

1. Let’s return to our orders data from ShoeFly.com.

In the previous exercise, our finance department wanted to know the most expensive shoe that we sold.

Now, they want to know the price of the most expensive shoe for each shoe_type (i.e., the price of the most expensive boot, the price of the most expensive ballet flat, etc.). Name the column that shows the most expensive shoe prices max_price.

Save your answer to the variable pricey_shoes, and view it.

# define pricey_shoes here:
pricey_shoes <- orders %>%
  group_by(shoe_type) %>%
  summarize(max_price = max(price, na.rm = TRUE))

pricey_shoes

2.The inventory team wants to know how many of each shoe_type has been sold so they can forecast inventory for the future.

Save your answer to the variable shoes_sold, and view it.

# define shoes_sold here:
shoes_sold <- orders %>%
  group_by(shoe_type) %>%
  summarize(count = n())

shoes_sold

4.Calculating Aggregate Functions II

Sometimes, we want to group by more than one column. We can do this by passing multiple column names as arguments to the group_by function.

Imagine that we run a chain of stores and have data about the number of sales at different locations on different days:

knitr::include_graphics("C:/Users/kuoan/Desktop/R Code/Aggregates4.png")

We suspect that sales are different at different locations on different days of the week. In order to test this hypothesis, we could calculate the average sales for each store on each day of the week across multiple months. The code would look like this:

” df %>% group_by(location,day_of_week) %>% summarize(mean_total_sales = mean(total_sales)) ”

And the results might look something like this:

knitr::include_graphics("C:/Users/kuoan/Desktop/R Code/Aggregates5.png")

Instructions

1.At ShoeFly.com, our Purchasing team thinks that certain shoe_type/shoe_color combinations are particularly popular this year (for example, blue ballet flats are all the rage in Paris).

Find the total number of shoes of each shoe_type/shoe_color combination purchased using group_by, summarize() and n(). Name the aggregate count column count. Save your result to the variable shoe_counts, and view it.

# define shoe_counts here:
shoe_counts <- orders %>%
  group_by(shoe_type, shoe_color) %>%
  summarize(count = n())

shoe_counts

2.The Marketing team wants to better understand the different price levels of the kinds of shoes that have been sold on the website, in particular looking at shoe_type/shoe_material combinations.

Find the mean price of each shoe_type/shoe_material combination purchased using group_by, summarize() and mean(). Assign the name mean_price to the calculated aggregate. Save your result to the variable shoe_prices, and view it.

Don’t forget to include na.rm = TRUE as an argument in the summary function that you call!

# define shoe_prices here:
shoe_prices <- orders %>%
  group_by(shoe_type, shoe_material) %>%
  summarize(mean_price = mean(price, na.rm = TRUE))

shoe_prices

5.Combining Grouping with Filters

While group_by() is most often used with summarize() to calculate summary statistics, it can also be used with the dplyr function filter() to filter rows of a data frame based on per-group metrics.

Suppose you work at an educational technology company that offers online courses and collects user data in an enrollments data frame:

# 建立資料框
enrollments <- data.frame(
  user_id = c(1234, 1234, 4567, 4567),
  course = c("learn_r", "learn_python", "learn_r", "learn_python"),
  quiz_score = c(80, 95, 90, 55)
)

enrollments

You want to identify all the enrollments in difficult courses, which you define as courses with an average quiz_score less than 80. To filter the data frame to just these rows:

enrollments %>%
  group_by(course) %>%
  filter(mean(quiz_score) < 80)

1.group_by() groups the data frame by course into two groups: learn-r and learn-python

2.filter() will keep all the rows of the data frame whose per-group (per-course) average quiz_score is less than 80

Rather than filtering rows by the individual column values, the rows will be filtered by their group value since a summary function is used!

1.The average quiz_score for the learn-r course is 85, so all the rows of enrollments with a value of learn-r in the course column are filtered out.

2.The average quiz_score for the learn-python course is 75, so all the rows of enrollments with a value of learn-python in the course column remain.

Instructions

1.Your boss at ShoeFly.com wants to gain a better insight into the orders of the most popular shoe_types.

Group orders by shoe_type and filter to only include orders with a shoe_type that has been ordered more than 7 times. Save the result to most_pop_orders, and view it.

You can include any of the summary functions as part of an argument to filter(), including n()!

# define most_pop_orders here:
most_pop_orders <- orders %>%
  group_by(shoe_type) %>%
  filter((count = n()) > 7)

most_pop_orders

6.Combining Grouping with Mutate

group_by() can also be used with the dplyr function mutate() to add columns to a data frame that involve per-group metrics.

Consider the same educational technology company’s enrollments table from the previous exercise:

# 建立資料框
enrollments <- data.frame(
  user_id = c(1234, 1234, 4567, 4567),
  course = c("learn_r", "learn_python", "learn_r", "learn_python"),
  quiz_score = c(80, 95, 90, 55)
)

enrollments

You want to add a new column to the data frame that stores the difference between a row’s quiz_score and the average quiz_score for that row’s course. To add the column:

enrollments %>% 
  group_by(course) %>% 
  mutate(diff_from_course_mean = quiz_score - mean(quiz_score))

NA

1.group_by() groups the data frame by course into two groups: learn-r and learn-python

2.mutate() will add a new column diff_from_course_mean which is calculated as the difference between a row’s individual quiz_score and the mean(quiz_score) for that row’s group (course)

1.The average quiz_score for the learn-r course is 85, so diff_from_course_mean is calculated as quiz_score - 85 for all the rows of enrollments with a value of learn-r in the course column.

2.The average quiz_score for the learn-python course is 75, so diff_from_course_mean is calculated as quiz_score - 75 for all the rows of enrollments with a value of learn-python in the course column.

Instructions

1.You want to be able to tell how expensive each order is compared to the average price of orders with the same shoe_type.

Group orders by shoe_type and create a new column named diff_from_shoe_type_mean that stores the difference in price between an orders price and the average price of orders with the same shoe_type. Save the result to diff_from_mean, and view it.

Don’t forget to include na.rm = TRUE as an argument in the summary function you call!

# define diff_from_mean here:
diff_from_mean <- orders %>%
  group_by(shoe_type) %>%
  mutate(diff_from_shoe_type_mean = price - mean(price, na.rm = TRUE))

diff_from_mean

7.Review

This lesson introduced you to aggregates in R using dplyr. You learned:

1.How to calculate summary statistics with summarize()

2.How to perform aggregate statistics over individual rows with the same value or values using group_by()

Instructions

1.Let’s examine some more data from ShoeFly.com. This time, in addition to the orders data, we’ll be looking at data about user visits to the website, stored in the page_visits data frame. Inspect the columns of the data frames using the rendered notebook.

Find the average price of an order in the orders data frame using summarize() and the mean() summary function. Save the resulting data frame to a variable named average_price and view it.

Don’t forget to include na.rm = TRUE as an argument in the call to mean()!

# load data
orders <- read_csv("orders.csv")
page_visits <- read_csv("page_visits.csv")

# inspect data frames
head(orders)

head(page_visits)

# define average_price here:
average_price <- orders %>%
  summarize(avg_price = mean(price, na.rm = TRUE))

average_price

2.In the page_visits data frame, the column utm_source contains information about how users got to ShoeFly’s homepage. For instance, if utm_source = Facebook, then the user came to ShoeFly by clicking on an ad on Facebook.com.

Use a group_by statement to calculate how many visits came from each of the different sources. Save your answer to the variable click_source, and view it.

# define click_source here:
click_source <- page_visits %>%
  group_by(utm_source) %>%
  summarize(count = n())

click_source

3.Our Marketing department thinks that the traffic to our site has been changing over the past few months. Use group_by to calculate the number of visits to our site from each utm_source for each month. Save your answer to the variable click_source_by_month, and view it.

# define click_source_by_month here:
click_source_by_month <- page_visits %>%
  group_by(utm_source, month) %>%
  summarize(count = n())

click_source_by_month