R Notebook

This is an R Markdown Notebook. When you execute code within the notebook, the results appear beneath the code.

Try executing this chunk by clicking the Run button within the chunk or by placing your cursor inside it and pressing Cmd+Shift+Enter.

plot(cars)

Add a new chunk by clicking the Insert Chunk button on the toolbar or by pressing Cmd+Option+I.

When you save the notebook, an HTML file containing the code and output will be saved alongside it (click the Preview button or press Cmd+Shift+K to preview the HTML file).

The preview shows you a rendered HTML copy of the contents of the editor. Consequently, unlike Knit, Preview does not run any R code chunks. Instead, the output of the chunk when it was last run in the editor is displayed.

#Resources

#Plans convert to date type make data skinny. mutate import county pop get per capita infection and death rates. get population density per sq mile. merge demographics : age, do all counties have same male/female ratio? income

#PACKAGES
library(tidyverse)

Registered S3 methods overwritten by 'dbplyr':
  method         from
  print.tbl_lazy     
  print.tbl_sql      
── Attaching packages ──────────────────────────── tidyverse 1.3.0 ──
✓ ggplot2 3.3.0     ✓ dplyr   0.8.5
✓ tibble  3.0.0     ✓ stringr 1.4.0
✓ tidyr   1.0.2     ✓ forcats 0.5.0
✓ purrr   0.3.3     
── Conflicts ─────────────────────────────── tidyverse_conflicts() ──
x dplyr::filter() masks stats::filter()
x dplyr::lag()    masks stats::lag()

library(ggplot2)
library(lubridate)

Attaching package: ‘lubridate’

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

    date

getwd()

[1] "/Users/joefoley/Covid/Covid"

NYS_Covid_Data <- read_csv("NYS_Statewide_COVID-19_Testing.csv")

Parsed with column specification:
cols(
  Test_Date = col_character(),
  County = col_character(),
  New_Positives = col_double(),
  Cumulative_Number_of_Positives = col_double(),
  Total_Number_of_Tests_Performed = col_double(),
  Cumulative_Number_of_Tests_Performed = col_double()
)

head(NYS_Covid_Data,3)

View(NYS_Covid_Data)

dim(NYS_Covid_Data)

[1] 2232    6

str(NYS_Covid_Data)

tibble [2,232 × 6] (S3: spec_tbl_df/tbl_df/tbl/data.frame)
 $ Test_Date                           : chr [1:2232] "04/06/2020" "04/06/2020" "04/06/2020" "04/06/2020" ...
 $ County                              : chr [1:2232] "Albany" "Allegany" "Bronx" "Broome" ...
 $ New_Positives                       : num [1:2232] 14 0 1021 10 1 ...
 $ Cumulative_Number_of_Positives      : num [1:2232] 333 17 15348 86 13 ...
 $ Total_Number_of_Tests_Performed     : num [1:2232] 86 3 1996 47 14 ...
 $ Cumulative_Number_of_Tests_Performed: num [1:2232] 4330 199 29367 654 265 ...
 - attr(*, "spec")=
  .. cols(
  ..   Test_Date = col_character(),
  ..   County = col_character(),
  ..   New_Positives = col_double(),
  ..   Cumulative_Number_of_Positives = col_double(),
  ..   Total_Number_of_Tests_Performed = col_double(),
  ..   Cumulative_Number_of_Tests_Performed = col_double()
  .. )

#convert Test Date from character to date type
NYS_Covid_Data$Test_Date <- as.Date(NYS_Covid_Data$Test_Date , format= "%m/%d/%Y")

head(NYS_Covid_Data)

Add population data from the Census

county_pop <- read_csv("NY_counties.csv")

Parsed with column specification:
cols(
  .default = col_double(),
  SUMLEV = col_character(),
  COUNTY = col_character(),
  STNAME = col_character(),
  CTYNAME = col_character()
)
See spec(...) for full column specifications.

dim(county_pop)

[1] 12958    80

Since there is only a single row we are intersted in let us filter this down. Each county has many rows, age groups , but AGEGRP = 0 is the total population for that county

pop <- county_pop%>%
          filter(AGEGRP==0) %>%
              select(c(CTYNAME, TOT_POP, AGEGRP))

head(pop)

dim(pop)

[1] 682   3

head(pop,10)

pop <- pop%>%
          group_by(CTYNAME)%>%
            top_n(n=1 ,wt=TOT_POP)

head(pop)

Make col names uniform new col anme = exisitng col name

pop <- pop %>% 
          rename(County = CTYNAME)

Next remove the word COUNTY from the name of each county. use SEPARATE

pop<- pop%>%
          separate(County, c("County", NA))       #df %>% separate(x, c("a", NA))

Expected 2 pieces. Additional pieces discarded in 2 rows [31, 46].

head(pop)   

head(NYS_Covid_Data)

Next join the two datasets on the county name.

pop_and_count <- full_join(NYS_Covid_Data,pop, by ="County")

View(pop_and_count)

write_csv(pop_and_count,"Pop_And_Count.csv")

######################3

Lets add the days of the week to our Df

pop_and_count<- pop_and_count %>%
mutate(day_week = wday(Test_Date,label = TRUE))

Now let us look at every Monday

pop_and_count<-filter(pop_and_count, day_week == "Mon")

View(pop_and_count)

########3

pop1<- pop1 %>%
mutate(day_week = wday(Test_Date,label = TRUE))

View(pop1)

pop2<-filter(pop1, day_week == "Mon")

View(pop2)

#Next we will import 3 census tables: #Race & Hispanics to get ethnicity and Income to our Covide data.

#RACE

Race<- read_csv("Race.csv")

Parsed with column specification:
cols(
  County = col_character(),
  Total = col_character(),
  White = col_character(),
  African_American = col_character(),
  `American_Indian_and_ Alaska_Native` = col_character(),
  Asian = col_character(),
  Hawaiian_Pacific_Islander = col_character(),
  Other_race = col_character()
)

View(Race)

###Hispanics

Hispanic_Age_NYS<- read_csv("Hispanic_Age_NYS.csv")

Duplicated column names deduplicated: 'Female_5_to_9' => 'Female_5_to_9_1' [21], 'Female_10_to_14' => 'Female_10_to_14_1' [23], 'Female_75_to_84' => 'Female_75_to_84_1' [34]Parsed with column specification:
cols(
  .default = col_double(),
  County = col_character()
)
See spec(...) for full column specifications.

#INCOME

NYS_Income <- read_csv("Income_NYS.csv")

Parsed with column specification:
cols(
  .default = col_double(),
  County = col_character(),
  Married_couple_families_Mean_income_dollars = col_character()
)
See spec(...) for full column specifications.

View(NYS_Income)

Next join all of the tables: Covid+Race+Hispanics+Income