WV County Education Outcomes Prediction

Load assessment data

library(tidyverse)
library(caret)

Loading required package: lattice
Registered S3 method overwritten by 'data.table':
  method           from
  print.data.table     

Attaching package: ‘caret’

The following object is masked from ‘package:purrr’:

    lift

library(rpart)
library(readxl)
library(usmap)

getwd()

[1] "/Users/elireeves/Documents/LAST HALF SEMESTER/ACCT 426/Project_2"

library(readxl)


t_assess_raw_school <- read_excel("Historical_AssessmentResults_SY15-to-SY21.xlsx", 
     sheet = 'SY21 School & District',
     range = 'b2:f7312',
     skip = 1)



t_assess_raw_science <- read_excel("Historical_AssessmentResults_SY15-to-SY21.xlsx",
                           sheet = 'SY21 School & District',
                           range = 'db3:db7312', 
                           col_names = c('science_proficiency'),
                           na = '**')

t_assess_raw <- t_assess_raw_school %>%
  bind_cols(t_assess_raw_science) %>% 
  janitor::clean_names()  


# Remove subgroups
t_assess <- t_assess_raw %>% 
  filter(school == 999) %>% 
  filter(population_group == 'Total Population') %>% 
  filter(county != 'Statewide') %>% 
  mutate(proficiency = science_proficiency)  

print(t_assess)

Load spending data

t_spending_raw <- read_excel("~/Documents/LAST HALF SEMESTER/ACCT 426/Project_2/elsec22t.xls",
                           sheet = 'elsec22t',
                           range = 'a1:gb14106') %>% 
  janitor::clean_names()

New names:

Load demographic data

Joined data

# Merge data
t <- t_assess %>% 
  full_join(t_spending, by = "county") %>%
  full_join(t_demographics, by = "county") %>% 
  mutate(fips = fips(state = "WV", county = county))

Error: object 't_demographics' not found

Correlations

Linear Regression Model

summary(model)

Call:
lm(formula = proficiency ~ totalexp + tlocrev + unemployed, data = t)

Residuals:
     Min       1Q   Median       3Q      Max 
-10.0422  -3.9992  -0.1445   3.1993  10.4680 

Coefficients:
              Estimate Std. Error t value Pr(>|t|)    
(Intercept)  2.543e+01  2.351e+00  10.817 8.27e-15 ***
totalexp    -4.228e-05  2.936e-05  -1.440   0.1560    
tlocrev      1.887e-04  8.063e-05   2.341   0.0232 *  
unemployed  -2.705e-01  2.694e-01  -1.004   0.3200    
---
Signif. codes:  0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 5.182 on 51 degrees of freedom
Multiple R-squared:  0.2505,    Adjusted R-squared:  0.2064 
F-statistic: 5.682 on 3 and 51 DF,  p-value: 0.001954