-
Notifications
You must be signed in to change notification settings - Fork 7
/
session3_notes.qmd
195 lines (146 loc) · 11.5 KB
/
session3_notes.qmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
# Data preparation and manipulation
```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE, collapse = FALSE, message = FALSE)
library(tidyverse)
```
## Data wrangling and summarising
### Combining two datasets
We may need to combine data from different files within R to perform an analysis. For example, in our case we have the core spending power for each year between 2015 and 2020. If our analysis required comparing this spending over the time period, we would need to combine these files together.
Before the data can be combined, it must be loaded into R. We will begin combining data from 2015 and 2016, then extend this to the entire period.
```{r Read in data separately, message=F}
# Return a list of files to copy from the working directory
list.files(path = "data")
# Load the 2015 data and attach as an object
CSP_2015 <- read_csv("data/CSP_2015.csv")
# Load the 2016 data and attach as an object
CSP_2016 <- read_csv("data/CSP_2016.csv")
```
Next, we will combine these datasets by joining them using key variable(s) which are shared between them. In this case, each local authority has a unique identifier code (`ons_code`) and naming variable (`authority`), they also should have the same `region` listed across both datasets.
In Tidyverse, there is a family of 'joining' functions that combine two datasets at a time. The choice of function depends on which observations we wish to keep where the joining variables do not match between data. In this example, we expect all local authority values to be the same across years, so will use the `full_join` function.
For more information about different joining options, check the helpfile via `?full_join`.
```{r full_join 2 datasets}
# Create a new object by joining the two datasets
csp_201516 <- full_join(CSP_2015, CSP_2016,
# List the key joining variables (in speech marks)
by = c("ons_code", "authority", "region"))
```
### Joining multiple datasets
R's joining functions can only be applied to two datasets at a time. To combine all 6 core spending power datasets from 2015 to 2020 in this way would require a lot of repetitive coding (which we want to avoid where necessary).
An alternative approach would be to automate this process by using **functional programming**, implemented using tidyverse's `purrr` package.
The first step of this process requires loading all csv files into R by repeatedly applying `read_csv`. This requires a list of file names from the working directory. The function `list.files` introduced earlier contains an optional argument, `pattern` which can be used to return files and folders that match a naming pattern. In this case, all csv files begin "CSP_20", so to return this list of names from the *data* folder, we use the function:
```{r List CSP files}
csp_201520 <- list.files(path = "data", pattern = "CSP_20")
```
Next, we apply `read_csv` to each element of the list of file names. The function `map` allows us to do this and return a list of tibbles. As the data lies in a folder in the working directory, we must add this file path to the file names:
```{r List of tibbles, results = 'hide', message = F}
# Return a list of files in the data folder containing CSP_20
csp_201520 <- list.files(path = "data", pattern = "CSP_20") %>%
# Add "data/" to each of these file names
paste0("data/", .) %>%
# Apply read_csv to every element of the list (of file names)
map(read_csv)
```
Finally, we require a function that apply `full_join` iteratively to the list of tibbles and reduce it to a single tibble containing core spending powers for all years. The function that does this is `reduce`:
```{r Reduce core spending power, results = 'hide', message = F}
# Return a list of files in the data folder containing CSP_20
csp_201520 <- list.files(path = "data", pattern = "CSP_20") %>%
# Add "data/" to each of these file names
paste0("data/", .) %>%
# Apply read_csv to every element of the list (of file names)
map(read_csv) %>%
# Reduce the list of tibbles to a single object by iteratively joining
reduce(full_join, by = c("ons_code", "authority", "region"))
```
### Transforming data
The dataset containing core spending power in England between 2015 and 2020 is currently in what is known as **wide format**. This means there is a variable per measure per year, making the object very wide.
Some analyses and visualisations, particularly those used for temporal data, require a time variable in the dataset (for example, year). This requires the data to be in a different format, known as **long format**. Long format is where each row contains an observation per year (making the data much longer and narrower).
To convert data between wide and long formats, we can use the tidyverse functions `pivot_longer` and `pivot_wider`.
The first argument required by `pivot_longer` is the data we wish to transform. This is followed by the columns we wish to pivot (in this case, all variable other than the local authority codes, names, and regions). The next steps will depend on the format of data we wish to transform, format of the data we would like to generate, the values we need to include in the long dataset, and where this information will be extracted from.
For worked examples and a detailed explanation of different approaches that can be used to pivot data, access the vignette for these function by entering `vignette("pivot")` into the R console.
In the core spending power example, the new dataset will contain a row per local authority per year. A new `year` variable will be created using the suffix of the original variable names, and the prefix of the original names (e.g. `sfa`) will be retained for the new variable names.
Using a combination of the helpfile (`?pivot_longer`) and vignette, the arguments required to convert this data are `names_to`, to specify the old variable names will be used in the new data, and `names_pattern` to define how the old variable names will be separated.
```{r Long format}
# Create an object csp_long by pivotting csp_201520
csp_long <- pivot_longer(csp_201520,
# Pivot columns sfa_2015 up to and including rsdg_2020
cols = sfa_2015:rsdg_2020,
# Separate the old variable names in two,
# keep the prefix as it was, and put the suffix
# into a new variable, year
names_to = c(".value", "year"),
# The name prefix and suffix were separated by an _,
# the prefix can take different lengths, the suffix
# is always the final 4 characters
names_pattern = "(.*)_(....)")
# Check the new, long dataset's structure
str(csp_long)
```
Notice that the new `year` variable is recognised as a character, not a numeric variable as we would like. This is because these values were taken from variable names, which R treats as characters. To fix this, we can use the `mutate` function to convert the new variable into a numeric variable.
We may also wish to calculate the total core spending power for each local authority per year to compare this over time:
```{r total_spend calculate}
# Create a new object based on the long data
csp_long2 <- mutate(csp_long,
# Convert year to a numeric variable
year = as.numeric(year),
# Create a new total spend variable
total_spend = sfa + under_index + ct_total + nhb +
nhb_return + rsdg)
```
After manipulating and transforming the data into the format we need for analysis and visualisation, we can save this object to reload later. Tibbles and data frame objects can be saved as CSV files using the `write_csv` function. Remember to save the data with a different name than the raw data to avoid overwriting these files.
```{r Save long data}
write_csv(csp_long2, file = "data/CSP_long_201520.csv")
```
### Summary tables
Summary tables can be created using the `summarise` function. This returns tables in a tibble format, meaning they can easily be customised and exported as CSV files (using the `write_csv` function).
The `summarise` function is set up similarly to the `mutate` function: summaries are listed and given variable names, separated by a comma. The difference between these functions is that `summarise` collapses the tibble into a single summary row, and the new variables must be created using a summary function.
Common examples of summary functions include:
- `mean`
- `median`
- `range` (gives the minimum and maximum values)
- `min`
- `max`
- `IQR` (interquartile range, gives the range of the middle 50% of the sample)
- `sd` (standard deviation, a measure of the spread when data are normally distributed)
- `sum`
- `n` (counts the number of rows the summary is calculated from)
For a full list of compatible summary functions, view the helpfile `?summarise`.
If we wanted to summarise the total core spending power between 2015 and 2020 across all local authorities, we can apply `summarise` to the long format data from the previous section:
```{r summarise no groups}
summarise(csp_long2,
# Return sum of the total_spend variable
total_spend_all = sum(total_spend),
# Return the mean total spend
mean_total_spend = mean(total_spend),
# Return the median total spend
median_total_spend = median(total_spend),
# Return the 10th percentile (the value that 10% of the sample lies below)
quantile10_total_spend = quantile(total_spend, 0.1),
# Count the number of rows that have been summarised
total_obs = n())
```
The `summarise` function can be used to produce grouped summaries. This is done by first grouping the data with the `group_by` function. For example, if we wished to produce a summary table with a row per local authority, summarising the total spending between 2015 and 2020, we would use the following:
```{r group summarise}
csp_long2 %>%
# Group by the local authority's unique identifiers
group_by(ons_code, authority) %>%
# Total spend 2015 - 2020
summarise(total_spend_all = sum(total_spend),
# Mean spend 2015 - 2020
mean_total_spend = mean(total_spend),
# Median spend 2015 - 2020
median_total_spend = median(total_spend),
# 10th percentile of total spend
quantile10_total_spend = quantile(total_spend, 0.1),
# Number of rows summarised over
total_obs = n()) %>%
# Remove grouping structure
ungroup()
```
:::{.callout-warning}
Whenever using `group_by`, make sure to `ungroup` the data before proceeding. The grouping structure can be large and slow analysis down, or may interact with other functions to produce unexpected analyses.
:::
### Exercise 4 {.unnumbered}
1. Create a data frame with the minimum, maximum and median total spend per year for each region.
2. Produce a frequency table containing the number and percentage of local authorities in each region.
3. Convert the data object `csp_long2` back into wide format, with one row per local authority and one variable per total spend per year (**HINT:** start by selecting only the variables you need from the long data frame). Use the help file `?pivot_wider` and `vignette("pivot")` for more hints.
4. Using your new wide data frame, calculate the difference in total spending for each local authority between 2015 and 2020. How many local authorities have had an overall reduction in spending since 2015?