Hits: 25

# (R Tutorials for Business Analyst)

In this end-to-end example, you will know how we can use aggregate function in R and summarise a dataset to get descriptive statistics.

# R Aggregate Function: Summarise & Group_by()

Summary of a variable is important to have an idea about the data. Although, summarizing a variable by group gives better information on the distribution of the data.

In this tutorial, you will learn how summarize a dataset by group with the dplyr library.

In this tutorial, you will learn

- Summarise()
- Group_by vs no group_by
- Function in summarise()
- Basic function
- Subsetting
- Sum
- Standard deviation
- Minimum and maximum
- Count
- First and last
- nth observation
- Multiple groups
- Filter
- Ungroup

For this tutorial, you will use the batting dataset. The original dataset contains 102816 observations and 22 variables. You will only use 20 percent of this dataset and use the following variables:

- playerID: Player ID code. Factor
- yearID: Year. Factor
- teamID: Team. factor
- lgID: League. Factor: AA AL FL NL PL UA
- AB: At bats. Numeric
- G: Games: number of games by a player. Numeric
- R: Runs. Numeric
- HR: Homeruns. Numeric
- SH: Sacrifice hits. Numeric

Before you perform summary, you will do the following steps to prepare the data:

- Step 1: Import the data
- Step 2: Select the relevant variables
- Step 3: Sort the data

library(dplyr) # Step 1 data <- read.csv("/datafolder/lahman-batting.csv") % > % # Step 2 select(c(playerID, yearID, AB, teamID, lgID, G, R, HR, SH)) % > % # Step 3 arrange(playerID, teamID, yearID)

A good practice when you import a dataset is to use the glimpse() function to have an idea about the structure of the dataset.

# Structure of the data glimpse(data)

**Output:**

Observations: 104,324 Variables: 9 $ playerID <fctr> aardsda01, aardsda01, aardsda01, aardsda01, aardsda01, a... $ yearID <int> 2015, 2008, 2007, 2006, 2012, 2013, 2009, 2010, 2004, 196... $ AB <int> 1, 1, 0, 2, 0, 0, 0, 0, 0, 603, 600, 606, 547, 516, 495, ... $ teamID <fctr> ATL, BOS, CHA, CHN, NYA, NYN, SEA, SEA, SFN, ATL, ATL, A... $ lgID <fctr> NL, AL, AL, NL, AL, NL, AL, AL, NL, NL, NL, NL, NL, NL, ... $ G <int> 33, 47, 25, 45, 1, 43, 73, 53, 11, 158, 155, 160, 147, 15... $ R <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 117, 113, 84, 100, 103, 95, 75... $ HR <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 44, 39, 29, 44, 38, 47, 34, 40... $ SH <int> 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 6, ...

### Summarise()

The syntax of summarise() is basic and consistent with the other verbs included in the dplyr library.

summarise(df, variable_name=condition) arguments: - `df`: Dataset used to construct the summary statistics - `variable_name=condition`: Formula to create the new variable

Look at the code below:

summarise(data, mean_run =mean(R))

Code Explanation

- summarise(data, mean_run = mean(R)): Creates a variable named mean_run which is the average of the column run from the dataset data.

**Output:**

## mean_run ## 1 19.20114

You can add as many variables as you want. You return the average games played and the average sacrifice hits.

summarise(data, mean_games = mean(G), mean_SH = mean(SH, na.rm = TRUE))

Code Explanation

- mean_SH = mean(SH, na.rm = TRUE): Summarize a second variable. You set na.rm = TRUE because the column SH contains missing observations.

**Output:**

## mean_games mean_SH ## 1 51.98361 2.340085

### Group_by vs no group_by

The function summerise() without group_by() does not make any sense. It creates summary statistic by group. The library dplyr applies a function automatically to the group you passed inside the verb group_by.

Note that, group_by works perfectly with all the other verbs (i.e. mutate(), filter(), arrange(), …).

It is convenient to use the pipeline operator when you have more than one step. You can compute the average homerun by baseball league.

data % > % group_by(lgID) % > % summarise(mean_run = mean(HR))

Code Explanation

- data: Dataset used to construct the summary statistics
- group_by(lgID): Compute the summary by grouping the variable `lgID
- summarise(mean_run = mean(HR)): Compute the average homerun

**Output:**

## # A tibble: 7 x 2 ## lgID mean_run ## <fctr> <dbl> ## 1 AA 0.9166667 ## 2 AL 3.1270988 ## 3 FL 1.3131313 ## 4 NL 2.8595953 ## 5 PL 2.5789474 ## 6 UA 0.6216216 ## 7 <NA> 0.2867133

The pipe operator works with ggplot() as well. You can easily show the summary statistic with a graph. All the steps are pushed inside the pipeline until the grap is plot. It seems more visual to see the average homerun by league with a bar char. The code below demonstrates the power of combining group_by(), summarise() and ggplot() together.

You will do the following step:

- Step 1: Select data frame
- Step 2: Group data
- Step 3: Summarize the data
- Step 4: Plot the summary statistics

library(ggplot2) # Step 1 data % > % #Step 2 group_by(lgID) % > % #Step 3 summarise(mean_home_run = mean(HR)) % > % #Step 4 ggplot(aes(x = lgID, y = mean_home_run, fill = lgID)) + geom_bar(stat = "identity") + theme_classic() + labs( x = "baseball league", y = "Average home run", title = paste( "Example group_by() with summarise()" ) )

**Output:**

## Function in summarise()

The verb summarise() is compatible with almost all the functions in R. Here is a short list of useful functions you can use together with summarise():

Objective | Function | Description |
---|---|---|

Basic | mean() | Average of vector x |

median() | Median of vector x | |

sum() | Sum of vector x | |

variation | sd() | standard deviation of vector x |

IQR() | Interquartile of vector x | |

Range | min() | Minimum of vector x |

max() | Maximum of vector x | |

quantile() | Quantile of vector x | |

Position | first() | Use with group_by() First observation of the group |

last() | Use with group_by(). Last observation of the group | |

nth() | Use with group_by(). nth observation of the group | |

Count | n() | Use with group_by(). Count the number of rows |

n_distinct() | Use with group_by(). Count the number of distinct observations |

We will see examples for every functions of table 1.

### Basic function

In the previous example, you didn’t store the summary statistic in a data frame.

You can proceed in two steps to generate a date frame from a summary:

- Step 1: Store the data frame for further use
- Step 2: Use the dataset to create a line plot

**Step 1)** You compute the average number of games played by year.

## Mean ex1 <- data % > % group_by(yearID) % > % summarise(mean_game_year = mean(G)) head(ex1)

Code Explanation

- The summary statistic of batting dataset is stored in the data frame ex1.

**Output:**

## # A tibble: 6 x 2 ## yearID mean_game_year ## <int> <dbl> ## 1 1871 23.42308 ## 2 1872 18.37931 ## 3 1873 25.61538 ## 4 1874 39.05263 ## 5 1875 28.39535 ## 6 1876 35.90625

**Step 2)** You show the summary statistic with a line plot and see the trend.

# Plot the graph ggplot(ex1, aes(x = yearID, y = mean_game_year)) + geom_line() + theme_classic() + labs( x = "Year", y = "Average games played", title = paste( "Average games played from 1871 to 2016" ) )

**Output:**

### Subsetting

The function summarise() is compatible with subsetting.

## Subsetting + Median data % > % group_by(lgID) % > % summarise(median_at_bat_league = median(AB), #Compute the median without the zero median_at_bat_league_no_zero = median(AB[AB > 0]))

Code Explanation

- median_at_bat_league_no_zero = median(AB[AB > 0]): The variable AB contains lots of 0. You can compare the median of the
**at bat**variable with and without 0.

**Output:**

## # A tibble: 7 x 3 ## lgID median_at_bat_league median_at_bat_league_no_zero ## <fctr> <dbl> <dbl> ## 1 AA 130 131 ## 2 AL 38 85 ## 3 FL 88 97 ## 4 NL 56 67 ## 5 PL 238 238 ## 6 UA 35 35 ## 7 <NA> 101 101

### Sum

Another useful function to aggregate the variable is sum().

You can check which leagues have the more homeruns.

## Sum data % > % group_by(lgID) % > % summarise(sum_homerun_league = sum(HR))

**Output:**

## # A tibble: 7 x 2 ## lgID sum_homerun_league ## <fctr> <int> ## 1 AA 341 ## 2 AL 29426 ## 3 FL 130 ## 4 NL 29817 ## 5 PL 98 ## 6 UA 46 ## 7 <NA> 41

### Standard deviation

Spread in the data is computed with the standard deviation or sd() in R.

# Spread data % > % group_by(teamID) % > % summarise(sd_at_bat_league = sd(HR))

**Output:**

## # A tibble: 148 x 2 ## teamID sd_at_bat_league ## <fctr> <dbl> ## 1 ALT NA ## 2 ANA 8.7816395 ## 3 ARI 6.0765503 ## 4 ATL 8.5363863 ## 5 BAL 7.7350173 ## 6 BFN 1.3645163 ## 7 BFP 0.4472136 ## 8 BL1 0.6992059 ## 9 BL2 1.7106757 ## 10 BL3 1.0000000 ## # ... with 138 more rows

There are lots of inequality in the quantity of homerun done by each team.

### Minimum and maximum

You can access the minimum and the maximum of a vector with the function min() and max().

The code below returns the lowest and highest number of games in a season played by a player.

# Min and max data % > % group_by(playerID) % > % summarise(min_G = min(G), max_G = max(G))

**Output:**

## # A tibble: 10,395 x 3 ## playerID min_G max_G ## <fctr> <int> ## 1 aardsda01 53 73 ## 2 aaronha01 120 156 ## 3 aasedo01 24 66 ## 4 abadfe01 18 18 ## 5 abadijo01 11 11 ## 6 abbated01 3 153 ## 7 abbeybe01 11 11 ## 8 abbeych01 80 132 ## 9 abbotgl01 5 23 ## 10 abbotji01 13 29 ## # ... with 10,385 more rows

### Count

Count observations by group is always a good idea. With R, you can aggregate the the number of occurence with n().

For instance, the code below computes the number of years played by each player.

# count observations data % > % group_by(playerID) % > % summarise(number_year = n()) % > % arrange(desc(number_year))

**Output:**

## # A tibble: 10,395 x 2 ## playerID number_year ## <fctr> <int> ## 1 pennohe01 11 ## 2 joosted01 10 ## 3 mcguide01 10 ## 4 rosepe01 10 ## 5 davisha01 9 ## 6 johnssi01 9 ## 7 kaatji01 9 ## 8 keelewi01 9 ## 9 marshmi01 9 ## 10 quirkja01 9 ## # ... with 10,385 more rows

### First and last

You can select the first, last or nth position of a group.

For instance, you can find the first and last year of each player.

# first and last data % > % group_by(playerID) % > % summarise(first_appearance = first(yearID), last_appearance = last(yearID))

**Output:**

## # A tibble: 10,395 x 3 ## playerID first_appearance last_appearance ## <fctr> <int> <int> ## 1 aardsda01 2009 2010 ## 2 aaronha01 1973 1975 ## 3 aasedo01 1986 1990 ## 4 abadfe01 2016 2016 ## 5 abadijo01 1875 1875 ## 6 abbated01 1905 1897 ## 7 abbeybe01 1894 1894 ## 8 abbeych01 1895 1897 ## 9 abbotgl01 1973 1979 ## 10 abbotji01 1992 1996 ## # ... with 10,385 more rows

### nth observation

The fonction nth() is complementary to first() and last(). You can access the nth observation within a group with the index to return.

For instance, you can filter only the second year that a team played.

# nth data % > % group_by(teamID) % > % summarise(second_game = nth(yearID, 2)) % > % arrange(second_game)

**Output:**

## # A tibble: 148 x 2 ## teamID second_game ## <fctr> <int> ## 1 BS1 1871 ## 2 CH1 1871 ## 3 FW1 1871 ## 4 NY2 1871 ## 5 RC1 1871 ## 6 BR1 1872 ## 7 BR2 1872 ## 8 CL1 1872 ## 9 MID 1872 ## 10 TRO 1872 ## # ... with 138 more rows

### Distinct number of observation

The function n() returns the number of observations in a current group. A closed function to n() is n_distinct(), which count the number of unique values.

In the next example, you add up the total of players a team recruited during the all periods.

# distinct values data % > % group_by(teamID) % > % summarise(number_player = n_distinct(playerID)) % > % arrange(desc(number_player))

Code Explanation

- group_by(teamID): Group by year
**and**team - summarise(number_player =
**n_distinct**(playerID)): Count the distinct number of players by team - arrange(desc(number_player)): Sort the data by the number of player

**Output:**

## # A tibble: 148 x 2 ## teamID number_player ## <fctr> <int> ## 1 CHN 751 ## 2 SLN 729 ## 3 PHI 699 ## 4 PIT 683 ## 5 CIN 679 ## 6 BOS 647 ## 7 CLE 646 ## 8 CHA 636 ## 9 DET 623 ## 10 NYA 612 ## # ... with 138 more rows

### Multiple groups

A summary statistic can be realized among multiple groups.

# Multiple groups data % > % group_by(yearID, teamID) % > % summarise(mean_games = mean(G)) % > % arrange(desc(teamID, yearID))

Code Explanation

- group_by(yearID, teamID): Group by year
**and**team - summarise(mean_games = mean(G)): Summarize the number of game player
- arrange(desc(teamID, yearID)): Sort the data by team and year

**Output:**

## # A tibble: 2,829 x 3 ## # Groups: yearID [146] ## yearID teamID mean_games ## <int> <fctr> <dbl> ## 1 1884 WSU 20.41667 ## 2 1891 WS9 46.33333 ## 3 1886 WS8 22.00000 ## 4 1887 WS8 51.00000 ## 5 1888 WS8 27.00000 ## 6 1889 WS8 52.42857 ## 7 1884 WS7 8.00000 ## 8 1875 WS6 14.80000 ## 9 1873 WS5 16.62500 ## 10 1872 WS4 4.20000 ## # ... with 2,819 more rows

### Filter

Before you intend to do an operation, you can filter the dataset. The dataset starts in 1871, and the analysis does not need the years prior to 1980.

# Filter data % > % filter(yearID > 1980) % > % group_by(yearID) % > % summarise(mean_game_year = mean(G))

Code Explanation

- filter(yearID > 1980): Filter the data to show only the relevant years (i.e. after 1980)
- group_by(yearID): Group by year
- summarise(mean_game_year = mean(G)): Summarize the data

**Output:**

## # A tibble: 36 x 2 ## yearID mean_game_year ## <int> <dbl> ## 1 1981 40.64583 ## 2 1982 56.97790 ## 3 1983 60.25128 ## 4 1984 62.97436 ## 5 1985 57.82828 ## 6 1986 58.55340 ## 7 1987 48.74752 ## 8 1988 52.57282 ## 9 1989 58.16425 ## 10 1990 52.91556 ## # ... with 26 more rows

### Ungroup

Last but not least, you need to remove the grouping before you want to change the level of the computation.

# Ungroup the data data % > % filter(HR > 0) % > % group_by(playerID) % > % summarise(average_HR_game = sum(HR) / sum(G)) % > % ungroup() % > % summarise(total_average_homerun = mean(average_HR_game))

Code Explanation

- filter(HR >0) : Exclude zero homerun
- group_by(playerID): group by player
- summarise(average_HR_game = sum(HR)/sum(G)): Compute average homerun by player
- ungroup(): remove the grouping
- summarise(total_average_homerun = mean(average_HR_game)): Summarize the data

**Output:**

## # A tibble: 1 x 1 ## total_average_homerun ## <dbl> ## 1 0.06882226

## Summary

When you want to return a summary by group, you can use:

# group by X1, X2, X3 group(df, X1, X2, X3)

you need to ungroup the data with:

ungroup(df)

The table below summarizes the function you learnt with summarise()

method | function | code |
---|---|---|

mean | mean |
summarise(df,mean_x1 = mean(x1)) |

median | median |
summarise(df,median_x1 = median(x1)) |

sum | sum |
summarise(df,sum_x1 = sum(x1)) |

standard deviation | sd |
summarise(df,sd_x1 = sd(x1)) |

interquartile | IQR |
summarise(df,interquartile_x1 = IQR(x1)) |

minimum | min |
summarise(df,minimum_x1 = min(x1)) |

maximum | max |
summarise(df,maximum_x1 = max(x1)) |

quantile | quantile |
summarise(df,quantile_x1 = quantile(x1)) |

first observation | first |
summarise(df,first_x1 = first(x1)) |

last observation | last |
summarise(df,last_x1 = last(x1)) |

nth observation | nth |
summarise(df,nth_x1 = nth(x1, 2)) |

number of occurrence | n |
summarise(df,n_x1 = n(x1)) |

number of distinct occurrence | n_distinct |
summarise(df,n_distinct _x1 = n_distinct(x1)) |

Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause.The information presented here could also be found in public knowledge domains.

# Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners

Latest end-to-end Learn by Coding Projects (Jupyter Notebooks) in Python and R:

**All Notebooks in One Bundle: Data Science Recipes and Examples in Python & R****. **

**End-to-End Python Machine Learning Recipes & Examples.**

**End-to-End R Machine Learning Recipes & Examples.**

**Applied Statistics with R for Beginners and Business Professionals**

**Data Science and Machine Learning Projects in Python: Tabular Data Analytics**

**Data Science and Machine Learning Projects in R: Tabular Data Analytics**

**Python Machine Learning & Data Science Recipes: Learn by Coding**

**R Machine Learning & Data Science Recipes: Learn by Coding**

**Comparing Different Machine Learning Algorithms in Python for Classification (FREE)**

There are 2000+ End-to-End Python & R Notebooks are available to build **Professional Portfolio as a Data Scientist and/or Machine Learning Specialist**. All Notebooks are only $29.95. We would like to request you to have a look at the website for FREE the end-to-end notebooks, and then decide whether you would like to purchase or not.

R tutorials for Business Analyst – Functions in R Programming

Excel formula for Beginners – How to Sum if date is greater than in Excel

R tutorials for Business Analyst – R Data Frame Sorting using Order()