-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathChild protection with parental drug or alcohol misuse.R
143 lines (97 loc) · 7.25 KB
/
Child protection with parental drug or alcohol misuse.R
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
# Child protection with parental drug or alcohol misuse.R
# 4 indicators generated by this script:
# 1. Child protection with parental drug misuse
# 2. Child protection with parental alcohol misuse
# 3. Child protection with parental drug or alcohol misuse
# 4. Children on the child protection register
# Full definition: (indicators 1-3)
# Rate of Child Protection Case Conference where parental drug, alcohol or substance misuse (ie drug &/or alcohol)
# has been identified for children on the register at 31st July, per 10,000 population aged under 18.
# Full definition: (indicator 4)
# Children on the child protection register; number and rate per 1,000 children under 16 years.
# Part 1 - Read in data downloaded from SG and format for functions
# Part 2 - Prepare file flagging which geographies/year combinations have been suppressed in source data
# Part 3 - Run analysis functions
###############################################################################.
## Analyst Notes on generating this indicator data ----
# This script analyses Scottish Government data on the number of children protection parental alcohol, drugs, substance misuse by a local authority
## Data are downloaded from the childrens social work publication on SG website:
## https://www.gov.scot/publications/childrens-social-work-statistics-scotland-2021-22/documents/
## See Excel files: Children's Social Work Statistics 2021-22 Additional Tables
## Table 4.6
# Figures are published for Scotland and local authorities only. (ScotPHO derive NHS board and ADP figures by aggregating council area figures)
# Each year (around March) the SG publish an additional year of data.
# The ScotPHO indicator raw dataset is generated by manually copying newly published data from the SG publication and adding this to historic indicator raw data excel file saved
# "\\Isdsf00d03\ScotPHO\Profiles\Data\Received Data\CP CSWS 2012_22 revised 19_20.xlsx"
# Upadte filename suffix when adding new year of data.
# HISTORIC DATA REVISIONS : In March 2022 revised figures for the previous year (2020) were released for some geographies
# Glasgow (drug & substance misuse), North Ayrshire all 3, South Lanarkshire all 3, West Dunbartonshire (alcohol & substance misuse)
###############################################################################.
###############################################.
## Packages/Filepaths/Functions ----
###############################################.
source("./1.indicator_analysis.R") #Normal indicator functions
library("stringr")#for string_replace() function
################################################################################.
## Part 1 - Read in data downloaded from SG and format for functions ----
################################################################################.
# open a lookup that will add la standard geography codes to the la names present in social work dataset
ca <- readRDS(paste0(lookups,"Geography/CAdictionary.rds")) %>%
rename("ca"="code")
# read in excel source data file - see notes at top about preparing this data.
# (note for 2022 update the 2020 data was overwritten with revised estimates and new 2021 data added)
# remember that SG apply suppression to data they provide to scotpho.
# Rows with LA = NA will not appear as a row in final dataset but do contribute to scottish total.
df_received<-read_excel("/PHI_conf/ScotPHO/Profiles/Data/Received Data/CP CSWS 2012_22 revised 19_20.xlsx") %>%
mutate(la = str_replace(la, "Glasgow","Glasgow City"),
la = str_replace(la, "Edinburgh, City of","City of Edinburgh"),
la = str_replace(la, "Eilean Siar","Na h-Eileanan Siar"),
la = str_replace(la, "&","and"),
across(contains(c("drug", "alcohol", "substance_misuse", "all")), as.numeric)) %>% #convert data columns to numeric (suppressed)
left_join(ca, by = c("la" = "areaname")) %>% # join with council area lookup
mutate(ca = ifelse(la == "Scotland", "S00000001", ca))
# prepare child protection with parental drug use - file to pass to analysis functions
parental_drug <- df_received %>% select(c(year,ca, drug)) %>%
rename("numerator"="drug")
saveRDS(parental_drug, file=paste0(data_folder, "Prepared Data/child_protection_parental_drug_raw.rds"))
# prepare child protection with parental drug use
parental_alcohol <- df_received %>% select(c(year,ca, alcohol)) %>%
rename("numerator"="alcohol")
saveRDS(parental_alcohol, file=paste0(data_folder, "Prepared Data/child_protection_parental_alcohol_raw.rds"))
# prepare child protection with substance misuse
parental_substance <- df_received %>% select(c(year,ca, substance_misuse)) %>%
rename("numerator"="substance_misuse")
saveRDS(parental_substance, file=paste0(data_folder, "Prepared Data/child_protection_parental_substance_misuse_raw.rds"))
# prepare all children on child protection register
child_protection_all <- df_received %>% select(c(year,ca, all_on_register)) %>%
rename("numerator"="all_on_register")
saveRDS(child_protection_all, file=paste0(data_folder, "Prepared Data/child_protection_all_raw.rds"))
#tidy up
rm(ca, parental_alcohol,parental_drug,parental_substance, child_protection_all)
################################################################################.
## Part 3 - Run analysis functions for 3 indicators----
################################################################################.
################################################################################.
## Child protection with parental drug use
analyze_first(filename = "child_protection_parental_drug", geography = "council",
measure = "crude", yearstart = 2012, yearend = 2021,
time_agg = 1, pop = "CA_pop_under18", adp = TRUE, source_suppressed = TRUE)
#remember the output of this file needs to have suppression reapplied so you need to keep running the script below to complete the update
analyze_second(filename = "child_protection_parental_drug", measure = "crude", time_agg = 1,
ind_id = 4130, year_type = "July snapshot", crude_rate = 10000)
################################################################################.
## Child protection with parental alcohol use
analyze_first(filename = "child_protection_parental_alcohol", geography = "council",
measure = "crude", yearstart = 2012, yearend = 2021,
time_agg = 1, pop = "CA_pop_under18", adp = TRUE, source_suppressed = TRUE)
#remember the output of this file needs to have suppression reapplied so you need to keep running the script below to complete the update
analyze_second(filename = "child_protection_parental_alcohol", measure = "crude", time_agg = 1,
ind_id = 4110, year_type = "July snapshot", crude_rate = 10000)
################################################################################.
## Child on child protection register
analyze_first(filename = "child_protection_all", geography = "council",
measure = "crude", yearstart = 2013, yearend = 2021,
time_agg = 1, pop = "CA_pop_under16", source_suppressed = TRUE)
analyze_second(filename = "child_protection_all", measure = "crude", time_agg = 1,
ind_id = 13035, year_type = "July snapshot", crude_rate = 1000)
###END ----