04-table-manipulation.Rmd

# Table/Data Frame manipulation {#tables}

```{r include=FALSE}
library(dplyr)
library(readr)
library(tidyr)

# Read in required data using public data.gov extract
road_accidents <- readr::read_rds("data/road_accidents_2017.RDS")

```


This chapter provides an overview of code examples for table or data frame manipulation (a tidyverse data frame is referred to as a tibble).

One of the main things you will have to do in any R project or RAP project will be manipulating the data that you are using in order to get it into the format you require.

One of the main packages used to manipulate data is the {dplyr} package which we recommend and use throughout this book. The {dplyr} package (and others e.g. {tidyr}) are all part of the tidyverse. The tidyverse is a group of packages developed by Hadley Wickham and others and are all designed to work with each other. See https://www.tidyverse.org/ for more info.

**Tidyverse packages and functions can be combined and layered using the pipe operator `%>%`.**

{dplyr} is built to work with **tidy data**. To find out more about tidy data please look at the following link https://r4ds.had.co.nz/tidy-data.html but the general principles are:

1. Each variables must have its own column
2. Each observation must have its own row
3. Each value must have its own cell



## Pivoting tables

There are two packages still in common use for pivoting data into tidy format.

* The {tidyr} package is a tidyverse package which contains functions used to create tidy data (among other data cleaning functions)
* The {reshape2} package is the non-tidyverse equivalent used to pivot tables. No updates have been made to this package in the last few years, and where possible tidyr should always be used in preference to it.


We want to have the day of the week variable running along the top so each day of the week is its own column.

```{r, echo = FALSE, results='asis'}
# Create smaller dataset for example
road_accidents_small <- road_accidents %>%
  dplyr::group_by(Accident_Severity, Day_of_Week) %>%
  dplyr::tally()

knitr::kable(head(road_accidents_small),
caption = "Number of road accidents by accident severity and weekday")

```



**{tidyr} package**

The {tidyr} package is a tidyverse package for manipulating data into a tidy format, and should be the first choice for gathering and pivoting functions. Use of the tidyr package is made slightly more complicated by the fact that there are both legacy (prior to v1.2) and more recent (v1.2 onwards) pivoting verbs available:

**gather and spread**

The functions gather and spread were the tidyr functions used prior to version 1.2 of the package. These verbs are still supported in more recent versions of the package so old code will still work, but you should aim not to use these in new code.

- **gather** makes wide data longer: equivalent is now pivot_longer()

- **spread** makes long data wider: equivalent is now pivot_wider()


**pivot_longer and pivot_wider**

The pivot_longer and pivot_wider functions are the updated version of gather and spread, available in newer versions of the tidyr package. These are designed to have more intuative names, and more logical arguments in comparison to the older versions. You should aim to use these in all new code you are writing.

- **pivot_longer** makes wide data longer i.e. variables running along the top can be "gathered" into rows running down.

- **pivot_wider** makes long data wider i.e. one variable can be spread and run along the top with each value being a variable.


```{r}
# Pivot table using newer tidyr package
road_accidents_weekdays <- road_accidents_small %>%
  tidyr::pivot_wider(names_from = Day_of_Week,
                     values_from = n)
  
```

In the pivot_wider function, you need to specify the dataset you want to transform, as well as the column you want to take the *names from* and the one you want to take the *values from*, in this case `Day_of_Week` and `n` respectively.

```{r, echo = FALSE, results='asis'}

knitr::kable(head(road_accidents_weekdays),
caption = "Number of road accidents by accident severity and weekday, tidyr::pivot_wider")

```


The opposite can also be done using the pivot_longer function:

```{r}
# Pivot table using newer tidyr package
road_accidents_gather <- road_accidents_weekdays %>%
  tidyr::pivot_longer(cols = c(`1`, `2`, `3`, `4`, `5`, `6`, `7`), names_to = "weekday", values_to = "n")


  
```

To use pivot_longer, specify which columns you want to be gathered into one column (in this case the individual weekday columns). Then specify the column name you'd like to move the *names to* (`weekday`) and the one you'd like to move the *values to* (`n`). 

It is also possible to pivot all columns except the ones you choose to exclude by specifying only those you **do not** want to pivot, using `-` to indicate this. In the example below, this is done by excluding the Accident_Severity column and pivoting all other columns by specifying `cols = -Accident_Severity`. This is useful when you may have a large number of columns or columns with unknown names that you want to turn into tidy data.

```{r}
# Pivot by excluding the accident severity column 
road_accidents_gather <- road_accidents_weekdays %>%
  tidyr::pivot_longer(cols = -Accident_Severity, names_to = "weekday", values_to = "n")

```
  

```{r, echo = FALSE, results='asis'}

knitr::kable(head(road_accidents_gather),
caption = "Number of road accidents by accident severity and weekday, tidyr::pivot_longer")

```

**Extended data transpose example**

This section shows how to transpose data using the **pivot_wider** and **pivot_longer** functions with the **tidyr** package.

<p style="background-color: #D9D9D9"><br>The aim is to take a published table of reported road accidents in which each row contains the data for one year with the severity of accident recorded in the columns. The tranposition will enable the calculation of the percentage increases for each of the last two years.<br><br></p>

Firstly, the **current year** is specified. 

```{r DeclareYear, include=TRUE}
Yr <- 2018   ## select for the latest of the three years you require 

```

This example uses data from RAS30013 *"Reported casualty rates by road user type and severty: Great Britain"*. https://www.gov.uk/government/statistical-data-sets/ras30-reported-casualties-in-road-accidents#casualty-rates-and-risk

The contents of the published table is read in (the last 10 years only), and the adjusted data is rounded to the nearest whole number. 

```{r PublishedTable, include=TRUE}
Accidents <- readxl::read_excel("data/ras10013.xlsx") %>%
  dplyr::filter(Year >= 2009) %>%
  dplyr::mutate(SeriousAdjusted = round(SeriousAdjusted),
                FatalSeriousAdjusted = round(FatalSeriousAdjusted),
                SlightAdjusted = round(SlightAdjusted))

```

```{r Table1, echo = FALSE, results = 'asis'}

knitr::kable(Accidents, format.args = list(big.mark = ",")) %>% 
  kableExtra::kable_styling(latex_options="scale_down")
```

The data is now filtered to keep only the last three years of data. 

We could have hard coded the year in the filter below e.g. **Year %in% c(2017,2018,2019)**. To save having to change the code every time we wish to run for different years, we can derive the required years from a previously defined variable. This picks up the most recent year from **yr** defined above and calculates the other years as 1 and 2 years before **yr** as **yr-1** and **yr-2**. 

A new variable **YearLab** with the respective text ThisYear, LastYear and YearBefore is created to automate calculations below. 

``` {r Last3Years, include=TRUE}
Last3Years <- dplyr::filter(Accidents,
                Year %in% c(Yr, Yr-1, Yr-2)) %>%
  dplyr::mutate(YearLab = dplyr::case_when(
    Year == Yr ~ "ThisYear",
    Year == Yr-1 ~ "LastYear",
    TRUE ~ "YearBefore" # else option 
  )) %>%
  dplyr::select(-Year)
```

```{r Table2, echo = FALSE, results = 'asis'}
knitr::kable(Last3Years, format.args = list(big.mark = ",")) %>% 
  kableExtra::kable_styling(latex_options="scale_down")
```

The table above holds the number of accidents for each severity (in columns) with a row for each of the last three years. We wish to calculate the annual growth in accidents by severity. To achieve this we need to transpose the table. This can be achieved using the **pivot_longer** function within the **tidyr** package which allows us to put data into a tidy format which makes it easier to manipulate.

The template of the **pivot_longer** function is:
    
    tidyr::pivot_longer(
          cols = variables_to_input_into_one_column, 
          names_to = column to move data names to, 
          values_to = column to move data values to)

The variables we want to input into one column are the accident severities (*Fatal, SeriousUnadjusted, ..., AllAccidents*); every column except for the **YearLab**. The **names_to** parameter we will call *SeverityType*.  The **values_to** parameter is the name of the column holding the number of accidents (*n*). The variable **YearLabel** is the only one that will be unchanged, so can be specifically excluded from the **pivot_longer** call.

A new field **SeverityNum** is created so that the severity types can be sorted in a logical order in the final output.

```{r Gather, include=TRUE}
Severity <- Last3Years %>% 
  tidyr::pivot_longer(cols = -YearLab,
                names_to = "SeverityType",
                values_to = "n") %>%
  dplyr::mutate(SeverityNum = dplyr::case_when(
  SeverityType=='Fatal' ~ 1,
  SeverityType=='SeriousUnadjusted' ~ 2,
  SeverityType=='SeriousAdjusted' ~ 3,
  SeverityType=='FatalSeriousUnadjusted' ~ 4,
  SeverityType=='FatalSeriousAdjusted' ~ 5,
  SeverityType=='SlightUnadjusted' ~ 6,
  SeverityType=='SlightAdjusted' ~ 7,
  SeverityType=='AllAccidents' ~ 8,
  TRUE ~ 0 # else option 
))
```

```{r Table3, echo = FALSE, results = 'asis'}
knitr::kable(Severity, format.args = list(big.mark = ",")) %>% 
  kableExtra::kable_styling(latex_options="scale_down")
```

We now wish to transpose this table so that the three years (*ThisYear, LastYear and YearBefore*) will be in separate columns rather than one.

To do this, we use the **pivot_wider** function within the **tidyr** package.

The template of the **pivot_wider** function is:
    
    tidyr::pivot_wider(cols = variables_to_change_from rows_into_columns,
          names_from = column_containing_variable_names,
          values_form = column_containing_values)

Here the column containing the names is *YearLab*, and the column containing the values is the number of accidents *n*. The variables **SeverityType** and **SeverityNum** will each remain in one column so are not listed in the **pivot_wider** function.

By default, the data would be sorted in alphabetical order of **SeverityType**. Instead the data is sorted in a pre-defined logical order using the **SeverityNum** variable defined above. The **SeverityNum**, having done its job, is then dropped from the final output.

Now that the number of accidents for each year are in a separate column, the percentage growth for one year to the next can be calculated using the **mutate** function.

``` {r Spread, include=TRUE}
Transposed <- Severity %>% 
  tidyr::pivot_wider(names_from = YearLab, 
                     values_from = n) %>%
  dplyr::arrange(SeverityNum) %>%
  dplyr::select(-SeverityNum) %>%
  dplyr::mutate(PerGrowthThisYear = round(ThisYear/LastYear *100-100,1),
                PerGrowthLastYear=round(LastYear/YearBefore *100-100,1))
```

```{r Table4, echo = FALSE, results = 'asis'}
knitr::kable(Transposed, format.args = list(big.mark = ",")) %>% 
  kableExtra::kable_styling(latex_options="scale_down")
```

## Dropping and selecting columns

Use the {dplyr} select function to both select and drop columns.

**Select columns**

```{r}
road_accidents_4_cols <- road_accidents %>%
  dplyr::select(acc_index, Accident_Severity, Date, Police_Force)
```

```{r, echo = FALSE, results='asis'}
knitr::kable(head(road_accidents_4_cols),
caption = "Four columns from road accidents 2017")
```


**Drop columns**

As for pivoting, to drop columns the difference is putting a "-" in front of the variable name.
 
```{r}
road_accidents_3_cols <- road_accidents_4_cols %>%
  dplyr::select(-Police_Force)
```


```{r, echo = FALSE, results='asis'}
knitr::kable(head(road_accidents_3_cols),
caption = "Three columns from road accidents 2017")
```
 

## Rename variables

Use the **rename** function from {dplyr} to rename variables where the new variable name is on the left hand side of the **=** equals sign, and the old variable name is on the right hand.

```{r}
road_accidents_rename <- road_accidents_4_cols %>%
  dplyr::rename(Date_of_Accident = Date)

```

```{r, echo = FALSE, results='asis'}
knitr::kable(head(road_accidents_rename),
caption = "Rename date column to Date_of_Accident")
```

## Filtering data

Use the {dplyr} filter function to filter data.

This example filters the data for slight severity accidents (accident severity 3).

```{r}
road_accidents_slight <- road_accidents_4_cols %>%
  dplyr::filter(Accident_Severity == 3)
```


```{r, echo = FALSE, results='asis'}
knitr::kable(head(road_accidents_slight),
caption = "Slight severity road accidents 2017")
```

To filter multiple conditions:

And operator
```{r}
road_accidents_filter <- road_accidents_4_cols %>%
  dplyr::filter(Accident_Severity == 3 & Police_Force == 4)
```

Or operator
```{r}
road_accidents_filter2 <- road_accidents_4_cols %>%
  dplyr::filter(Accident_Severity == 3 | Accident_Severity == 2)
```

**Note: filtering with characters must be wrapped in "quotation marks" e.g:**
```{r, eval = FALSE}
road_accidents_filter3 <- road_accidents %>%
dplyr::filter(`Local_Authority_(Highway)` == "E09000010")

```
Also note that in the above example the variable is quoted in back ticks (`). This is necessary when a column or variable name contains non-standard characters (anything other than alphanumerics, and selected punctuation marks: _, - or .) or start with numbers; they need to be wrapped in back ticks so R knows that everything inside the back ticks is a variable name.

## Group data 

Use the {dplyr} group_by function to group data. This works in a similar manner to "GROUP BY" in SQL.

The below example groups the data by accident severity and weekday, and creates totals for each group using the "tally" function.

```{r}
# Create grouped data set with counts
road_accidents_small <- road_accidents %>%
  dplyr::group_by(Accident_Severity, Day_of_Week) %>%
  dplyr::tally()
```

```{r, echo = FALSE, results='asis'}
knitr::kable(head(road_accidents_small),
caption = "Road accidents 2017 by accident severity and weekday")
```

## Order data

Use the {dplyr} arrange function to order data. This works in a similar manner to "ORDER BY" in SQL.

This example orders the data by date and number of casualties.


```{r}
# Order data by date and number of casualties
road_accidents_ordered <- road_accidents %>%
  dplyr::sample_n(7) %>%
  dplyr::select(acc_index, Accident_Severity, Police_Force, Number_of_Casualties, Date) %>%
  dplyr::arrange(Date, Number_of_Casualties)
  
```

```{r, echo = FALSE, results='asis'}
knitr::kable(head(road_accidents_ordered),
caption = "Road accidents 2017 ordered by date and number of casualties")
```

## Get counts of data

To get counts for groups of data, the {dplyr} tally function can be used in conjunction with the {dplyr} group by function. This groups the data into the required groups and then tallys how many records are in each group.

```{r}
# Create grouped data set with counts
road_accidents_small <- road_accidents %>%
  dplyr::group_by(Accident_Severity, Day_of_Week) %>%
  dplyr::tally()
```

The above example creates groups by accident severity and weekday and counts how many accidents are in each group (one row equals one accident therefore the tally is counting accidents).

```{r, echo = FALSE, results='asis'}
knitr::kable(head(road_accidents_small),
caption = "Road accidents 2017 by accident severity and weekday")
```



## Combine tables

When combining data from two tables there are two ways to do this in R:

* Bind the tables by basically either appending the tables on the rows or columns
* Join the tables using the {dplyr} version of SQL joins

**Binding tables**

Binding tables is mainly done to append tables by creating more rows, however tables can also be bound by adding more columns. Although it is recommended to use the {dplyr} join functions to combine columns (see 5.6).

```{r include=FALSE}
library(dplyr)

# create three tables for example
accidents_1 <- dplyr::filter(road_accidents_small, Accident_Severity == 1)
accidents_2 <- dplyr::filter(road_accidents_small, Accident_Severity == 2)
accidents_3 <- dplyr::filter(road_accidents_small, Accident_Severity == 3)

```

Here are three tables, one shows data for accident severity of 1, one for accident severity of 2, and one for accident severity of 3.

```{r, echo = FALSE, results='asis'}

knitr::kable(accidents_1, caption = "Number of fatal road accidents in 2017, by weekday")
knitr::kable(accidents_2, caption = "Number of serious injury road accidents in 2017, by weekday")
knitr::kable(accidents_3, caption = "Number of slight injury road accidents in 2017, by weekday")

```

To combine these tables we can use the bind_rows function from the {dplyr} package. Use bind_rows when you want to append the tables underneath one another to make one longer table, i.e. you want to add more rows.

**Ensure that the column names for each table are exactly the same in each table.**

```{r}
# combine tables using bind_rows
library(dplyr)

all_accidents <- accidents_1 %>%
  dplyr::bind_rows(accidents_2, accidents_3)
  
```


```{r, echo = FALSE, results='asis'}

knitr::kable(all_accidents, caption = "Road accident data 2017, bind_rows")

```

```{r include=FALSE}
library(dplyr)

# create two tables for example
road_acc_1 <- head(dplyr::select(road_accidents, acc_index, Police_Force, Accident_Severity))
road_acc_2 <- head(dplyr::select(road_accidents, acc_index, Date, Day_of_Week))

```


## Joining tables


Joins in R can be done using {dplyr}. This is generally to combine columns of data from two tables:


```{r}
# combine tables using left join
library(dplyr)

all_accidents_cols_join <- road_acc_1 %>%
  dplyr::left_join(road_acc_2, by = "acc_index")
```

This uses the same principles as SQL, by specifying what the tables should be joined on using the **by =** argument. 


{dplyr} has all the usual SQL joins for example, `inner_join`, `full_join`, `right_join`. All of these are used in the same way as the left join example above.

Another useful join for data manipulation is an `anti_join`. This provides all the data that is not in the joined table. For example, the below snapshot of a table displays road accident totals broken down by accident severity and weekday:

```{r, echo = FALSE, results='asis'}

knitr::kable(head(road_accidents_small, caption = "Road accident data 2017 by accident severity and weekday"))

```

I am interested in creating two sub-groups of this data, a table for all accidents on a Monday (weekday 2), and all other accidents.

First, I get the **Monday** data using the {dplyr} filter function (see 5.3).

```{r include=FALSE}
library(dplyr)

# create filtered Monday table for example

accidents_monday <- dplyr::filter(road_accidents_small, Day_of_Week == 2)

```

```{r, echo = FALSE, results='asis'}

knitr::kable(head(accidents_monday, caption = "Road accident data 2017 on a Monday by accident severity"))

```

Then, I can use an `anti-join` to create a table which has all of the data that is not in the above table:

```{r}
# create table of all rows not in the joined table
library(dplyr)

all_accidents_not_monday <- road_accidents_small %>%
  dplyr::anti_join(accidents_monday, by = c("Accident_Severity", "Day_of_Week"))
```

The above code takes the initial table we want to get our data from (road_accidents_small) and anti joins accidents_monday. This says, "get all the rows from road_accidents_small that are not in accidents_monday". Again, note the need to specify what the join rows would be joined and compared by.


```{r, echo = FALSE, results='asis'}

knitr::kable(all_accidents_not_monday, caption = "Road accident data 2017 not on a Monday by accident severity")
```

## Select specific columns in a join

Doing a join with {dplyr} will join all columns from both tables, however sometimes not all columns from each table are needed.

Let's look at some previous tables again:

```{r, echo = FALSE, results='asis'}

knitr::kable(road_acc_1, caption = "Police force and accident severity information for accidents")
knitr::kable(road_acc_2, caption = "Date and weekday information for accidents")
```

Let's say we want **acc_index** and **Police_Force** from the first table, and **Date** from the second table.

```{r}
# select specific columns from each table and left join
library(dplyr)

road_acc_3 <- road_acc_1 %>%
  dplyr::select(acc_index, Police_Force) %>%
  dplyr::left_join(select(road_acc_2, acc_index, Date), by = "acc_index")
```

The above code takes the first table and uses the `select` statement to select the required columns from the first table. 

Then within the `left_join` command, to select the data from the second table, you again add the `select` statement.

**Note: you will need to select the joining variable in both tables but this will only appear once**

```{r, echo = FALSE, results='asis'}

knitr::kable(road_acc_3, caption = "Police force and Date information for specific accidents")
```

## Sum rows or columns

### Sum rows

Summing across a row can be done using the dplyr function **c_across**; you just need to specify that this should be done row-wise before performing the calculation:

```{r}
# sum across a row 
road_accidents_weekdays <- road_accidents_weekdays %>% 
  dplyr::rowwise() %>%
  dplyr::mutate(rowsum = sum(c_across())) 
```

```{r, echo = FALSE, results='asis'}

knitr::kable(road_accidents_weekdays, caption = "Road accidents 2017 by accident severity and weekday")
```

To sum across specific rows, you can name these within the c_across function:

```{r}
# sum across specific rows 
road_accidents_weekdays <- road_accidents_weekdays %>% 
  dplyr::rowwise() %>%
  dplyr::mutate(alldays = sum(c_across(`1`:`5`))) 

```

```{r, echo = FALSE, results='asis'}

knitr::kable(road_accidents_weekdays[,-9], caption = "Road accidents 2017 by accident severity and weekday")
```

### Sum columns
 
Columns can also be summed, although it isn't recommended that these sums are added to a data table:

```{r, eval = FALSE}
# sum columns
road_accidents_weekdays %>%
  dplyr::summarise_if(is.numeric, sum)
```


## Replace NAs or other values

```{r, include = FALSE}
# Create dataset for example with nas (need to change -1 value to na as this is how NAs are represented in the road accident open data)

# create nas
road_accidents_na <- road_accidents %>%
  dplyr::na_if(-1)

# get smaller data set for example
road_accidents_na <- road_accidents_na %>%
  head(n = 7) %>%
  dplyr::select(acc_index, `1st_Road_Class`, `2nd_Road_Class`, Junction_Control)

```

To replace all NAs in one column (Junction Control column) with a specific value:

```{r}
library (tidyr)
# replace all NAs with value -1
road_accidents_na <- road_accidents_na %>%
  dplyr::mutate(Junction_Control = tidyr::replace_na(Junction_Control, -1))

```

**Note: To replace NA with a character the character replacement must be wrapped in "quotation marks"**

To replace all NAs in a data frame or tibble:

```{r}

# replace all NAs with value -1
road_accidents_na <- road_accidents_na %>%
  mutate_if(is.numeric, tidyr::replace_na, -1)
  

```

To replace values with NA, specify what value you want to be replaced with NA using the na_if function:

```{r}

# create nas
road_accidents_na <- road_accidents_na %>%
  dplyr::na_if(-1)
  

```
**Note: to only create NAs in a specific column specify the column name in a similar manner to the first example in this section.**

To replace values:
```{r}

# replace 1st_road_class 
road_accidents_na <- road_accidents_na %>%
  dplyr::mutate(`1st_Road_Class` = dplyr::case_when(`1st_Road_Class` == 3 ~ "A Road",
                                      TRUE ~ as.character(`1st_Road_Class`)))
  

```

The case_when function is similar to using CASE WHEN in SQL. 

The TRUE argument indicates that if the values aren't included in the case_when then they should be whatever is after the tilda (~)  i.e. the equivalent of the ELSE statement in SQL.

The "as.character" function says that everything that in `1st_Road_Class` isn't 3 should be kept as it is, this could be replaced by an arbitrary character or value e.g. "Other". This would make everything that is not a 3, coded as "Other". 

You can have multiple case_when arguments for multiple values, they just need to be seperated with a comma. Multiple case_when statements for different variables can be layered using the pipe operator `%>%`.


## Reordering rows/columns

### Reordering rows

Rows can be reordered by certain variables using the {dplyr} arrange function with examples in the **4.5 Order data** sub-chapter of this book. This will order the data in ascending order by the variables quoted. To order rows in descending order the ``desc()`` command can be used within the arrange function.

```{r}
# Order data by date and number of casualties
road_accidents_ordered_desc <- road_accidents %>%
  dplyr::select(acc_index, Accident_Severity, Police_Force, Number_of_Casualties, Date) %>%
  dplyr::arrange(desc(Date), Number_of_Casualties)
```


```{r, echo = FALSE, results='asis'}
knitr::kable(head(road_accidents_ordered_desc),
caption = "Road accidents 2017 ordered by date (descending) and number of casualties")
```

### Reordering columns

Use the {dplyr} select statement to reorder columns, where the order of the variables quoted represents the order of the columns in the table.

```{r, echo = FALSE, results='asis'}

knitr::kable(head(road_accidents_4_cols),
caption = "Four columns from road accidents 2017")
```

To reorder this table we do:

```{r}
table_reordered <- road_accidents_4_cols %>%
  dplyr::select(Accident_Severity, Date, acc_index, Police_Force)
```



## Creating new variables

The {dplyr} mutate function can be used to create new variables based on current variables or other additional information. 

For example, to create a new variable which is speed limit in km:

```{r}
road_acc_km <- road_accidents %>%
  dplyr::mutate(speed_km = Speed_limit * 1.6) %>%
  dplyr::select(acc_index, Police_Force, Speed_limit, speed_km)

```


```{r, echo = FALSE, results='asis'}

knitr::kable(head(road_acc_km),
caption = "Road accidents by km/h")
```

## Summarising data

The {dplyr} summarise function can be used to summarise data (mean, median, sd, min, max, n_distinct). See https://dplyr.tidyverse.org/reference/summarise.html for more examples.

For example, to get the mean number of accidents for each weekday:

```{r, echo = FALSE, results='asis'}
knitr::kable(head(road_accidents_small),
caption = "Road accidents 2017, by severity and weekday")
```

The group by function is used with the summarise function to specify what groups the mean will be applied to, in this case weekday. 

```{r}
road_acc_mean <- road_accidents_small %>%
  dplyr::group_by(Day_of_Week) %>%
  dplyr::summarise(mean = mean(n))

```

```{r, echo = FALSE, results='asis'}
knitr::kable(head(road_acc_mean),
caption = "Mean number of accidents in 2017, by weekday")
```


## Look up tables

Aside from importing a separate lookup data file into R, named vectors can be used as lookup tables.

For example, to assign accident severity values with labels, named vectors can be used (**note: numbers must also be in quotation marks**):

```{r}
lookup_severity <- c("1" = "Fatal", "2" = "Serious", "3" = "Slight")
```

To convert the data and create a label variable (**note: the Accident_Severity variable values can be replaced with the lookup values by changing the name of the variable on the left to Accident_Severity**):

```{r}
road_accidents_small$Accident_Severity_label <- lookup_severity[road_accidents_small$Accident_Severity]
```

```{r, echo = FALSE, results='asis'}
knitr::kable(head(road_accidents_small),
caption = "Road accidents 2017, by severity and weekday")
```