library(forecast)
## Registered S3 method overwritten by 'quantmod':
##   method            from
##   as.zoo.data.frame zoo
library(dplyr)
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
library(readxl)
library(ggplot2)

df <- read.csv("flinfo2022.csv") #load the data
Avg_age_2021 <- read.csv("Avg_age_2021.csv")
vaC_R <- read.csv("Vax Rate.csv")

Avg_age_2021 <-data.frame(Avg_age_2021)
vaC_R <- data.frame(vaC_R)

df <- df[,-c(1,2,4:6,11:13)]   #keep numerical values only
df <- df %>%
  rename(County = Admin2)  # Name the County Column

Avg_age_2021 <- Avg_age_2021[,-1]
vaC_R <- vaC_R[,-1]

df <- na.omit(df)  # delete NA values
Pbed <- read.csv("PatientBed.csv")
Pbed <- Pbed[,-c(2,5,6)]


# rename the column
Pbed <- Pbed %>%
  rename(BedInUse = X, InUsePercentage = Inpatient.beds)


# remove "County" in first column
FN <- function(County) {
  C_name <- gsub(" County", "", County)
  return(C_name)
}
Pbed$County <- FN(Pbed$County) 


# Remove extra info after num
FN2 <- function(Bed) {
comma_loc <- regexpr("o", Bed)
Number <- substr(Bed, 1, comma_loc-1)
return(Number)
}
Pbed$BedInUse <- FN2(Pbed$BedInUse)

Pbed$InUsePercentage <- as.numeric(Pbed$InUsePercentage)
## Warning: NAs introduced by coercion
Pbed$BedInUse <- as.numeric(Pbed$BedInUse)
## Warning: NAs introduced by coercion
Pbed <- na.omit(Pbed)
df$Avg_age <-  Avg_age_2021
df$Vac_R <- vaC_R
# merge two dataset group by County
merged_df <- merge(df, Pbed, by = "County", all = TRUE)
df <- na.omit(merged_df)
df$AllBed <- df$BedInUse/df$InUsePercentage
Pred.lm <- lm(Deaths ~   population + Confirmed + Vac_R
                 + Avg_age + InUsePercentage + AllBed
               , data = df)
summary(Pred.lm)
## 
## Call:
## lm(formula = Deaths ~ population + Confirmed + Vac_R + Avg_age + 
##     InUsePercentage + AllBed, data = df)
## 
## Residuals:
##     Min      1Q  Median      3Q     Max 
## -446.08  -91.53    3.98   76.61  724.30 
## 
## Coefficients:
##                   Estimate Std. Error t value Pr(>|t|)   
## (Intercept)      9.514e+01  2.607e+02   0.365  0.71736   
## population       5.004e-03  1.446e-03   3.461  0.00147 **
## Confirmed       -9.293e-03  4.881e-03  -1.904  0.06542 . 
## Vac_R           -4.136e+02  3.631e+02  -1.139  0.26271   
## Avg_age          7.685e-01  5.630e+00   0.137  0.89223   
## InUsePercentage  3.020e+02  2.097e+02   1.440  0.15893   
## AllBed           5.930e-01  1.878e-01   3.158  0.00333 **
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 215 on 34 degrees of freedom
## Multiple R-squared:  0.8849, Adjusted R-squared:  0.8646 
## F-statistic: 43.57 on 6 and 34 DF,  p-value: 1.484e-14
#According to the summary above, the model should be

Predl.lm <- lm(Deaths ~   population + Incident_Rate + Vac_R + AllBed 
               , data = df)
summary(Predl.lm)
## 
## Call:
## lm(formula = Deaths ~ population + Incident_Rate + Vac_R + AllBed, 
##     data = df)
## 
## Residuals:
##     Min      1Q  Median      3Q     Max 
## -438.71 -102.98   -6.16  103.49  778.35 
## 
## Coefficients:
##                 Estimate Std. Error t value Pr(>|t|)    
## (Intercept)    9.765e+02  3.899e+02   2.505  0.01692 *  
## population     2.557e-03  4.702e-04   5.438 3.92e-06 ***
## Incident_Rate -1.941e-02  9.312e-03  -2.085  0.04425 *  
## Vac_R         -6.124e+02  3.506e+02  -1.747  0.08924 .  
## AllBed         5.743e-01  1.855e-01   3.095  0.00379 ** 
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 214.9 on 36 degrees of freedom
## Multiple R-squared:  0.8783, Adjusted R-squared:  0.8648 
## F-statistic: 64.94 on 4 and 36 DF,  p-value: 5.775e-16
# look at the predicted values
y_hat <- predict(Predl.lm, se.fit = TRUE)
names(y_hat)
## [1] "fit"            "se.fit"         "df"             "residual.scale"
# adding the predictions to the mydata data frame
df$Predictions <- y_hat$fit

#  compare the actuals versus the predicted values
ggplot(data = df, aes(Predictions, Deaths)) +
  geom_point() +
  geom_smooth(method = "lm")
## `geom_smooth()` using formula = 'y ~ x'