The group_by() function in R is from dplyr package that is used to group rows by column values in the DataFrame, It is similar to GROUP BY clause in SQL. R dplyr groupby is used to collect identical data into groups on DataFrame and perform aggregate functions on the grouped data.
In general Group by operation involves splitting the data, applying some functions, and finally aggregating the results.
So it is a two-step process, first, you need to perform grouping and then run the aggregate for each group. In this article, I will explain group_by() function syntax and usage on DataFrame with R programming examples.
1.Syntax of group_by() Function
Following is the syntax of the dplyr group_by() function.
# Syntax group by
group_by(.data, ..., add = FALSE)
Parameters
.data– tbl...– Variables or Columns to group by.add– Defaults to FALSE
Following are the types of tbl (tables) it supports.
- data.frame
- data.table
- SQLite
- PostgreSQL
- MySQL
First, let’s create a DataFrame by reading from a CSV file.
# Read CSV file into DataFrame
df = read.csv('/Users/admin/apps/github/r-examples/resources/emp.csv')
df
Yields below output.
2. Dplyr group_by Function Example
Use group_by() function in R to group the rows in DataFrame by columns, to use this function, you have to install dplyr first using install.packages(‘dplyr’) and load it using library(dplyr).
All functions in dplyr package take data.frame as a first argument. When we use dplyr package, we mostly use the infix operator %>% from magrittr, it passes the left-hand side of the operator to the first argument of the right-hand side of the operator. For example, x %>% f(y) converted into f(x, y) so the result from the left-hand side is then “piped” into the right-hand side.
The group_by() function doesn’t change the dataframe data how it looks and it just returns the grouped tbl (tibble table) where we can perform summarise on. Let’s perform the group by on the column department and summarize to get the sum of salary for each group.
# Load dplyr
library(dplyr)
# group_by() on department
grp_tbl <- df %>% group_by(department)
grp_tbl
# summarise on groupped data.
agg_tbl <- grp_tbl %>% summarise(sum(salary))
agg_tbl
Note that the output of group_by() and summarise() is tibble hence, to convert it to data.frame use as.data.frame() function.
# Convert tibble to DataFrame
df2 <- agg_tbl %>% as.data.frame()
3. Assign Name to the Summarize Column
If you notice above the second output, the summarise column name has sum(salary) which is not user-friendly, let’s see how to add a custom user-friendly name to it. Also, I will rewrite the above 2 statements into a single statement using dplyr piping.
# Assign column Name to the aggregated column
# Group by on multiple columns
agg_tbl <- df %>% group_by(department) %>%
summarise(total_salary=sum(salary))
agg_tbl
Yields below output.
4. Using dplyr group_by() on Multiple Columns
The group_by() and summarise() also support group by on multiple columns and summarise on multiple columns.
# Group by on multiple columns
# & multiple aggregations
agg_tbl <- df %>% group_by(department, state) %>%
summarise(total_salary=sum(salary),
total_bonus = sum(bonus),
min_salary = min(salary),
max_salary = max(salary),
.groups = 'drop'
)
agg_tbl
Yields below output.
5. Apply List of Summarise Functions
This example does the group by on department and state columns, summarises on salary and bonus columns, and apply the sum & mean functions on each summarised column.
# Apply multiple summaries
df2<- df[,c("department","state","salary","bonus")]
agg_tbl <- df2 %>% group_by(department, state) %>%
summarise(across(c(salary, bonus), list(mean = mean, sum = sum)))
Yields below output.
6. Summarise All Columns Except Grouping Columns
This example does the group by on department and state columns, summarises on all columns except grouping columns, and apply the sum & mean functions on all summarised columns.
# Summarise all columns except grouping columns
df2<- df[,c("department","state","age","salary","bonus")]
agg_tbl <- df2 %>% group_by(department, state) %>%
summarise(across(everything(), list(mean = mean, sum = sum)))
agg_tbl
Yields below output.
7. Complete Example
Following is a complete example of an R group by function.
# Create Data Frame
df = read.csv('/Users/admin/apps/github/r-examples/resources/emp.csv')
df
# Load dplyr
library(dplyr)
# group_by() on department
grp_tbl <- df %>% group_by(department)
grp_tbl
# summarise on groupped data.
agg_tbl <- grp_tbl %>% summarise(sum(salary))
agg_tbl
# Assign column Name to the aggregated column
agg_tbl <- df %>% group_by(department) %>%
summarise(total_salary=sum(salary))
agg_tbl
# Group by on multiple columns
# & multiple aggregations
agg_tbl <- df %>% group_by(department, state) %>%
summarise(total_salary=sum(salary),
total_bonus = sum(bonus),
min_salary = min(salary),
max_salary = max(salary),
.groups = 'drop'
)
agg_tbl
# Apply multiple summaries
df2<- df[,c("department","state","salary","bonus")]
agg_tbl <- df2 %>% group_by(department, state) %>%
summarise(across(c(salary, bonus), list(mean = mean, sum = sum)))
# Summarise all columns except grouping columns
df2<- df[,c("department","state","age","salary","bonus")]
agg_tbl <- df2 %>% group_by(department, state) %>%
summarise(across(everything(), list(mean = mean, sum = sum)))
agg_tbl
8. Conclusion
In this article, you have learned the syntax of group_by() function in R from the dplyr package and how to use this to group the rows in DataFrame and apply the summarise.
Related Articles
- R Summarise on Group By in Dplyr
- R Group by Mean With Examples
- R Group by Sum With Examples
- R Group by Count With Examples
- R Group by Multiple Columns or Variables
- R solve() Equation with Examples
- Pipe in R with Examples
