library(tidyverse)
library(janitor)Cleaning
This notebook is a starting point for you in the Special Education project following up the Houston Chronicle’s Denied series. This project has the data necessary to complete the Denied Cleaning chapter in Reporting with Data in R.
Setup
Setting up the packages we’ll use, tidyverse and janitor.
I am looking at the data.
read_csv("data-raw/DSTUD_23.csv") |> head()Rows: 1209 Columns: 64
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (4): DISTRICT, DPEMSPEC, DPEMSPEP, DPEMSPET
dbl (60): DPET504C, DPET504P, DPETALLC, DPETASIC, DPETASIP, DPETATTC, DPETAT...
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.Working on DSTUD data
I am building our vector of files to import.
dstud_files <- list.files(
"data-raw",
pattern = "DSTUD",
full.names = TRUE
)
dstud_files [1] "data-raw/DSTUD_13.csv" "data-raw/DSTUD_14.csv" "data-raw/DSTUD_15.csv"
[4] "data-raw/DSTUD_16.csv" "data-raw/DSTUD_17.csv" "data-raw/DSTUD_18.csv"
[7] "data-raw/DSTUD_19.csv" "data-raw/DSTUD_20.csv" "data-raw/DSTUD_21.csv"
[10] "data-raw/DSTUD_22.csv" "data-raw/DSTUD_23.csv"Importing DSTUD files
I am importing the DSTUD files.
dstud_raw <- dstud_files |>
set_names(basename) |>
map(
read_csv,
col_select = c(DISTRICT, DPETALLC, DPETSPEC, DPETSPEP)
) |>
list_rbind(names_to = "source") |>
clean_names()Rows: 1228 Columns: 4
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): DISTRICT
dbl (3): DPETALLC, DPETSPEC, DPETSPEP
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
Rows: 1227 Columns: 4
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): DISTRICT
dbl (3): DPETALLC, DPETSPEC, DPETSPEP
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
Rows: 1219 Columns: 4
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): DISTRICT
dbl (3): DPETALLC, DPETSPEC, DPETSPEP
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
Rows: 1207 Columns: 4
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): DISTRICT
dbl (3): DPETALLC, DPETSPEC, DPETSPEP
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
Rows: 1203 Columns: 4
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): DISTRICT
dbl (3): DPETALLC, DPETSPEC, DPETSPEP
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
Rows: 1200 Columns: 4
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): DISTRICT
dbl (3): DPETALLC, DPETSPEC, DPETSPEP
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
Rows: 1201 Columns: 4
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): DISTRICT
dbl (3): DPETALLC, DPETSPEC, DPETSPEP
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
Rows: 1202 Columns: 4
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): DISTRICT
dbl (3): DPETALLC, DPETSPEC, DPETSPEP
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
Rows: 1204 Columns: 4
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): DISTRICT
dbl (3): DPETALLC, DPETSPEC, DPETSPEP
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
Rows: 1207 Columns: 4
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): DISTRICT
dbl (3): DPETALLC, DPETSPEC, DPETSPEP
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
Rows: 1209 Columns: 4
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): DISTRICT
dbl (3): DPETALLC, DPETSPEC, DPETSPEP
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.dstud_raw |> nrow()[1] 13307dstud_raw |> head()dstud_raw |> tail()Clean up DSTUD file
I am cleaning up the DSTUD file to remove the apostrophe from district and to build a year variable out of source.
dstud <- dstud_raw |>
mutate(
district = str_remove(district, "'"),
year = str_c("20", str_sub(source, 7, 8))
) |>
select(!source)
dstud |> head()dstud |> tail()The DREF data
Within the TAPR data download tool, there is another file called District Reference that has the district ID, names and other information we need. I am looking at the 2022 version, downloaded as DREF_22.csv.
read_csv("data-raw/DREF_22.csv") |> head()Rows: 1207 Columns: 11
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (10): DISTRICT, CNTYNAME, COUNTY, DFLALTED, DFLCHART, DISTNAME, D_RATING...
dbl (1): DAD_POST
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.Import
I am using read_csv to pull in the variables I need.
dref_raw <- read_csv(
"data-raw/DREF_22.csv",
col_select = c(
DISTRICT,
CNTYNAME,
DISTNAME,
DFLCHART,
DFLALTED
)
) |>
clean_names()Rows: 1207 Columns: 5
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (5): DISTRICT, CNTYNAME, DFLALTED, DFLCHART, DISTNAME
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.dref_raw |> head()Clean up DREF file
I am cleaning up the apostrophes.
dref <- dref_raw |>
mutate(district = str_remove(district, "'"))
dref |> head()Joining our reference table
I am starting with the dref data and then using an inner_join to add all the yearly data values. We’re doing it in this order so the dref values are listed first in our resulting table.
sped_joined <- dref |>
inner_join(dstud, by = "district")
sped_joined |> head()I am peeking at all the columns.
sped_joined |> glimpse()Rows: 13,087
Columns: 9
$ district <chr> "001902", "001902", "001902", "001902", "001902", "001902", "…
$ cntyname <chr> "ANDERSON", "ANDERSON", "ANDERSON", "ANDERSON", "ANDERSON", "…
$ distname <chr> "CAYUGA ISD", "CAYUGA ISD", "CAYUGA ISD", "CAYUGA ISD", "CAYU…
$ dflchart <chr> "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "…
$ dflalted <chr> "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "…
$ dpetallc <dbl> 595, 553, 577, 568, 576, 575, 564, 557, 535, 574, 593, 1236, …
$ dpetspec <dbl> 73, 76, 76, 78, 82, 83, 84, 82, 78, 84, 83, 113, 107, 126, 14…
$ dpetspep <dbl> 12.3, 13.7, 13.2, 13.7, 14.2, 14.4, 14.9, 14.7, 14.6, 14.6, 1…
$ year <chr> "2013", "2014", "2015", "2016", "2017", "2018", "2019", "2020…Filter and select
I am cleaning up my data.
sped_cleaned <- sped_joined |>
filter(dflalted == "N" & dflchart == "N") |>
select(
district,
distname,
cntyname,
year,
all_count = dpetallc,
sped_count = dpetspec,
sped_percent = dpetspep
) |>
glimpse()Rows: 11,225
Columns: 7
$ district <chr> "001902", "001902", "001902", "001902", "001902", "001902…
$ distname <chr> "CAYUGA ISD", "CAYUGA ISD", "CAYUGA ISD", "CAYUGA ISD", "…
$ cntyname <chr> "ANDERSON", "ANDERSON", "ANDERSON", "ANDERSON", "ANDERSON…
$ year <chr> "2013", "2014", "2015", "2016", "2017", "2018", "2019", "…
$ all_count <dbl> 595, 553, 577, 568, 576, 575, 564, 557, 535, 574, 593, 12…
$ sped_count <dbl> 73, 76, 76, 78, 82, 83, 84, 82, 78, 84, 83, 113, 107, 126…
$ sped_percent <dbl> 12.3, 13.7, 13.2, 13.7, 14.2, 14.4, 14.9, 14.7, 14.6, 14.…I am adding an audit flag column.
sped_flag <- sped_cleaned |>
mutate(audit_flag = if_else(
sped_percent > 8.5,
"ABOVE",
"BELOW"
))
# this pulls 10 random rows so I can check results
sped_flag |>
sample_n(10) |>
select(distname, sped_percent, audit_flag)Export
I am exporting the cleaned data.
sped_flag |>
write_rds("data-processed/01-sped.rds")