What does it mean when we say “mean by group”? It means grouping the data based on the values of single or multiple columns and then calculating the mean (average) of those values, but doing so separately for each group.
Here are three ways to calculate the mean by group for single or multiple columns in the R data frame:
- Using base R’s aggregate()
- Using dplyr’s group_by() and summarize()
- Using data.table package
Mean is a statistical operation used in data summarization, comparative analysis, and preprocessing. It helps us create meaningful summaries from condensed data.
We will use the below sample data frame to perform practicals:
df_sales_data <- data.frame(
Product = c("Apple", "Banana", "Apple", "Milk", "Bread", "Butter", "Milk"),
Category = c("Fruit", "Fruit", "Fruit", "Dairy", "Bakery", "Dairy", "Dairy"),
Price = c(1.2, 0.5, 1.2, 2.5, 1.8, 2.0, 2.5),
Quantity = c(5, 10, 5, 2, 3, 12, 2),
stringsAsFactors = FALSE
)
print(df_sales_data)
Method 1: Using base R’s aggregate()
The aggregate() function splits the data into subgroups and calculates the summary for each group. In the context of our article, we can split the data based on the specific column(s) groups and calculate the mean for each group.
Mean of a single column grouped by a single column
In our df_sales_data dataset, we can group the data frame based on the Category column and find the mean of only one column (Price) for each Category.
The aggregate() function accepts three arguments:
- Price ~ Category: The Price column values should be grouped by Category.
- df_sales_data: It is an input data frame
- FUN: It is mean because we want the mean of Price grouped by Category.
df_sales_data <- data.frame(
Product = c("Apple", "Banana", "Apple", "Milk", "Bread", "Butter", "Milk"),
Category = c("Fruit", "Fruit", "Fruit", "Dairy", "Bakery", "Dairy", "Dairy"),
Price = c(1.2, 0.5, 1.2, 2.5, 1.8, 2.0, 2.5),
Quantity = c(5, 10, 5, 2, 3, 12, 2),
stringsAsFactors = FALSE
)
print(df_sales_data)
# Mean Price by Category
aggregate(Price ~ Category, data = df_sales_data, FUN = mean)Output
In our data frame,
- The bakery category has only one product, Bread, which has a price of 1.8, so the mean price is 1.8000000.
- The Dairy category has three products: Milk, Butter, and Milk, and their prices are 2.5, 2, and 2.5. The total price is 7, and if you divide 7 by 3, the mean is 2.333333.
- The Fruit category has three products: Apple, Banana, and Apple. Their respective prices are 1.2, 0.5, and 1.2. The mean of these prices is 0.9666667.
Mean of multiple columns grouped by a single column
You can calculate the mean of multiple columns (e.g., Price and Quantity) grouped by a single column (Category) using the aggregate() function.
df_sales_data <- data.frame(
Product = c("Apple", "Banana", "Apple", "Milk", "Bread", "Butter", "Milk"),
Category = c("Fruit", "Fruit", "Fruit", "Dairy", "Bakery", "Dairy", "Dairy"),
Price = c(1.2, 0.5, 1.2, 2.5, 1.8, 2.0, 2.5),
Quantity = c(5, 10, 5, 2, 3, 12, 2),
stringsAsFactors = FALSE
)
print(df_sales_data)
# Mean Price and Quantity by Category
aggregate(cbind(Price, Quantity) ~ Category, data = df_sales_data, FUN = mean)
Output
Mean of multiple columns grouped by multiple variables
As the name suggests, we can calculate the mean of multiple columns (Price and Quantity) grouped by multiple categorical variables (Category and Product).
df_sales_data <- data.frame(
Product = c("Apple", "Banana", "Apple", "Milk", "Bread", "Butter", "Milk"),
Category = c("Fruit", "Fruit", "Fruit", "Dairy", "Bakery", "Dairy", "Dairy"),
Price = c(1.2, 0.5, 1.2, 2.5, 1.8, 2.0, 2.5),
Quantity = c(5, 10, 5, 2, 3, 12, 2),
stringsAsFactors = FALSE
)
print(df_sales_data)
# Mean Price and Quantity group by Category and Product
aggregate(cbind(Price, Quantity) ~ Category + Product, data = df_sales_data, FUN = mean)
Output
Method 2: Using dplyr’s group_by() and summarise()
If you combine group_by() and summarise() functions, the output will be in tibble.
The group_by() method accepts a data frame and single or multiple columns as arguments and groups the data frame based on the provided columns’ unique values.
Then, we use the summarise() function, which accepts a function—mean in our case—to calculate the mean for each group.
Before using dplyr, you must install it in your environment and then load it using the code below:
library(dplyr)Mean of a Single Column Grouped by a Single Column
Let’s calculate the mean of Price grouped by Category:
library(dplyr)
df_sales_data <- data.frame(
Product = c("Apple", "Banana", "Apple", "Milk", "Bread", "Butter", "Milk"),
Category = c("Fruit", "Fruit", "Fruit", "Dairy", "Bakery", "Dairy", "Dairy"),
Price = c(1.2, 0.5, 1.2, 2.5, 1.8, 2.0, 2.5),
Quantity = c(5, 10, 5, 2, 3, 12, 2),
stringsAsFactors = FALSE
)
print(df_sales_data)
# Calculate the mean of Price grouped by Category:
df_sales_data %>%
group_by(Category) %>%
summarise(Mean_Price = mean(Price, na.rm = TRUE))Output
Mean of multiple columns grouped by a single column
Let’s find the mean of Price and Quantity, grouped by Category.
library(dplyr)
df_sales_data <- data.frame(
Product = c("Apple", "Banana", "Apple", "Milk", "Bread", "Butter", "Milk"),
Category = c("Fruit", "Fruit", "Fruit", "Dairy", "Bakery", "Dairy", "Dairy"),
Price = c(1.2, 0.5, 1.2, 2.5, 1.8, 2.0, 2.5),
Quantity = c(5, 10, 5, 2, 3, 12, 2),
stringsAsFactors = FALSE
)
print(df_sales_data)
# Calculate the mean of Price and Quantity grouped by Category
df_sales_data %>%
group_by(Category) %>%
summarise(
Mean_Price = mean(Price, na.rm = TRUE),
Mean_Quantity = mean(Quantity, na.rm = TRUE)
)Output
Mean of Multiple Columns Grouped by Multiple Variables
Let’s calculate the mean of Price and Quantity grouped by Category and Product:
library(dplyr)
df_sales_data <- data.frame(
Product = c("Apple", "Banana", "Apple", "Milk", "Bread", "Butter", "Milk"),
Category = c("Fruit", "Fruit", "Fruit", "Dairy", "Bakery", "Dairy", "Dairy"),
Price = c(1.2, 0.5, 1.2, 2.5, 1.8, 2.0, 2.5),
Quantity = c(5, 10, 5, 2, 3, 12, 2),
stringsAsFactors = FALSE
)
print(df_sales_data)
# Calculate the mean of Price and Quantity grouped by Category and Product
df_sales_data %>%
group_by(Category, Product) %>%
summarise(
Mean_Price = mean(Price, na.rm = TRUE),
Mean_Quantity = mean(Quantity, na.rm = TRUE)
)Output
Weighted mean
A weighted mean (also called a weighted average) is a mean where some values contribute more than others to the final result.
library(dplyr)
df_sales_data <- data.frame(
Product = c("Apple", "Banana", "Apple", "Milk", "Bread", "Butter", "Milk"),
Category = c("Fruit", "Fruit", "Fruit", "Dairy", "Bakery", "Dairy", "Dairy"),
Price = c(1.2, 0.5, 1.2, 2.5, 1.8, 2.0, 2.5),
Quantity = c(5, 10, 5, 2, 3, 12, 2),
stringsAsFactors = FALSE
)
print(df_sales_data)
# Calculate weighted mean
df_sales_data %>%
group_by(Category) %>%
summarize(weighted_price = weighted.mean(Price, Quantity))Output
Method 3: Using data.table
Install the data.table package if you have not installed it already:
Let’s find the basic grouped mean of Price and Quantity by Category:
library(dplyr)
df_sales_data <- data.frame(
Product = c("Apple", "Banana", "Apple", "Milk", "Bread", "Butter", "Milk"),
Category = c("Fruit", "Fruit", "Fruit", "Dairy", "Bakery", "Dairy", "Dairy"),
Price = c(1.2, 0.5, 1.2, 2.5, 1.8, 2.0, 2.5),
Quantity = c(5, 10, 5, 2, 3, 12, 2),
stringsAsFactors = FALSE
)
print(df_sales_data)
# Basic Grouped Mean using data.table
dt <- as.data.table(df_sales_data)
dt[, .(mean_price = mean(Price), mean_quantity = mean(Quantity)), by = Category]
Output
Conclusion
For small-to-medium-sized data frames, you can use aggregate() or dplyr’s group_by() and summarise() functions. For large data frames, you can use the data.table package.
Krunal Lathiya is a seasoned Computer Science expert with over eight years in the tech industry. He boasts deep knowledge in Data Science and Machine Learning. Versed in Python, JavaScript, PHP, R, and Golang. Skilled in frameworks like Angular and React and platforms such as Node.js. His expertise spans both front-end and back-end development. His proficiency in the Python language stands as a testament to his versatility and commitment to the craft.
