-
Notifications
You must be signed in to change notification settings - Fork 2
/
imd.qmd
175 lines (116 loc) · 6.95 KB
/
imd.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
---
title: "Index of Multiple Deprivation (IMD)"
---
```{r}
#| label: setup
#| echo: true
#| eval: false
```
# Index of Multiple Deprivation
The latest available IMD data for England is from 2019.
IMD is very useful for categorising the area a person lives in for deprivation. Deprivation is by country and do not include the other nations. Deciles are the most commonly used way of referring to IMD and are taken from the scores which are ordered and then cut into 10. For deciles the 1 is the most deprived area and 10 is the least deprived.
[Wikipedia link](https://en.wikipedia.org/wiki/Multiple_deprivation_index) [English IMD 2015 - Guidance](https://assets.publishing.service.gov.uk/media/5a7f0e5ded915d74e33f410b/English_Index_of_Multiple_Deprivation_2015_-_Guidance.pdf)
# Finding the IMD for an address
To get IMD scores or deciles for a local data a join will be needed to the postcode table (like a directory of postcodes) found:
https://digital.nhs.uk/services/organisation-data-service/data-downloads/ods-postcode-files
and then to the IMD data:
https://www.gov.uk/government/statistics/english-indices-of-deprivation-2019
Select File 7 for the dataset.
Other data is available from this dataset including IDAOPI which relates to only older people.
Note that the column headers change, in 2015 it was LADistrictCode2013 and in 2019 it is LADistrictCode2019. Also LADistrictName2013 has become LADistrictName2019.
### Postcode spaces
Postcode lengths vary in original data depending on whether one or two spaces are used between the parts. Consequently, it is always better when joining by postcodes to remove the spaces altogether.
In SQL this would be with the code:
`REPLACE(postcode, ' ', '')`
in R it can be
```{r}
#| label: postcode-no-space
postcode <- "NG16 1AA"
gsub(" ","",postcode)
```
### Partial postcodes
Partial postcodes are sometimes provided to protect the data and may be the first part (before spaces) and 1 or 2 characters from the second part. This will not give a sufficiently reliable IMD score.
# Example join code (SQL)
To get the IMD score the LSOA (Lower Super Output Area) code is required which is taken from the full postcode.
This code will not run and is dependent on the naming conventions of the SQL server. The column names of LSOA11 and `LSOAcode2011` will have come from the data sources. The column `PostCode_space` has been added to the original data.
```{sql}
#| label: sql-example-code
#| eval: false
SELECT imd.*
FROM DIM_AI.PatientData AS p
LEFT JOIN DIM_AI.PostCodes AS pc ON p.PostCode = pc.PostCode_space
LEFT JOIN DIM_AI.IMD AS i ON pc.PC.LSOA11 = i.LSOAcode2011
```
# Creating quintiles
To be able to join to the data scores will need to be put into quintiles (group of 5) rather than deciles (group of 10).
### Quintiles in SQL
```{sql}
#| label: sql-quintile
#| eval: false
SELECT DISTINCT IMDDecile,
FLOOR((IMDDecile-1)/2) + 1 AS IMDQuintile
FROM DIM_AI.IMD
ORDER BY IMDDecile
```
### Quintiles in R
```{r }
#| label: r-quintile
#| eval: true
library(tidyverse)
# Generate a dataset
data <- structure(list(IMDDecile = c(0L, 1L, 2L, 3L, 4L, 5L, 6L, 7L,
8L, 9L, 10L, NA)), row.names = c(NA, -12L), class = c("tbl_df",
"tbl", "data.frame"))
# Make the 0 generated into NA
data |>
replace_na(list(IMDDecile = 0)) |>
mutate(IMDQuintile = floor((IMDDecile-1)/2) + 1,
IMDQuintile = as.character(IMDQuintile)
)
```
# Creating local IMDs
In areas like Nottingham/Nottinghamshire the differences between the LSOA areas is diminished when ranked against England as a whole, but when ranked locally, the variation is much more pronounced. It is possible to take the original scores and apply deciles or quintiles to those scores in order to create a local IMD. Packages like [{NHSRpopulations}](https://github.com/nhs-r-community/NHSRpopulation) have functions to do this and more information in the [vignette](https://nhs-r-community.github.io/NHSRpopulation/articles/calc-imd-ranks-within-la.html).
## Local IMD creation in SQL
To apply a rank use the windows partition function `ROW_NUMBER() OVER(ORDER BY IMDRank)` to create a new ranking score and `NTILE(10) OVER (ORDER BY IMDRank)` to create new deciles.
## Local IMD creation in R
```{r}
#| label: localRank
#| eval: false
library(tidyverse)
library(PostcodesioR)
library(NHSRpostcodetools) # installed from GitHub not CRAN
library(fingertipsR)
library(janitor)
# Generate random example postcodes
postcodes <- purrr::map_chr(
1:10,
.f = ~PostcodesioR::random_postcode() |>
purrr::pluck(1)
) |>
as_tibble()
# [TODO] find way to get IMD without downloading 32,844 obs
imd <- read_csv("https://assets.publishing.service.gov.uk/government/uploads/system/uploads/attachment_data/file/833982/File_7_-_All_IoD2019_Scores__Ranks__Deciles_and_Population_Denominators.csv") %>%
clean_names()
pc_imd <- NHSRpostcodetools::postcode_data_join(.data = postcodes$value) |>
select(postcode,
lsoa_code) |>
left_join(imd |>
select(lsoa_code_2011,
score = index_of_multiple_deprivation_imd_score,
national_decile = index_of_multiple_deprivation_imd_decile_where_1_is_most_deprived_10_percent_of_lso_as), join_by(lsoa_code == lsoa_code_2011)) |>
mutate(imd_decile_local = ntile(-score, n = 10)) # creating new deciles from the data provided
```
# Other useful links
https://assets.publishing.service.gov.uk/government/uploads/system/uploads/attachment_data/file/464430/English_Index_of_Multiple_Deprivation_2015\_-\_Guidance.pdf
https://fingertips.phe.org.uk/search/imd
http://dclgapps.communities.gov.uk/imd/idmap.html
Technical report for 2019: https://assets.publishing.service.gov.uk/government/uploads/system/uploads/attachment_data/file/833951/IoD2019_Technical_Report.pdf
# Referencing IMD in a paper or research
From a journal check to see how IMD is referenced in published papers, this was from a 2016 BMJ article that cites the Index in the references as:
6. Department for Communities and Local Government. English indices of deprivation 2015. 2015. https://www.gov.uk/government/statistics/ english-indices-of-deprivation-2015
(Taken from https://bmjopen.bmj.com/content/bmjopen/6/11/e012750.full.pdf)
Another paper from 2016 cites as:
16. Department for Communities and Local Government. English Indices of Deprivation 2015. Available online: http://www.gov.uk/government/statistics/english-indices-of-deprivation-2015 (accessed on 27 April 2016).
(taken from https://www.mdpi.com/1660-4601/13/8/750)
Looking at the Government page that lists the full text the library assistant said: "I would reference it from the Ministry of Housing, Communities and Local Government which would be more up to date for 2019 and with online references you should always put the date you accessed it. So I would suggest amending to the following:"
Ministry of Housing, Communities and Local Government. English Indices of Deprivation 2015. 2015. https://www.gov.uk/government/statistics/english-indices-of-deprivation-2015 (Accessed 4 June 2019)