Slicing-Dicing Data 10-14-2020.Rmd

---
title: "Slicing and Dicing Covid Data"
author: "Rob Wells"
date: "November 1, 2020"
output: html_document
---


#Load Software
```{r include=FALSE}
library(tidyverse)
#install.packages("rio") 
```
#rio handles more than two dozen formats including tab-separated data (with the extension .tsv), 
#JSON, Stata, and fixed-width format data (.fwf).




```{r}
hospital <- rio::import('https://raw.githubusercontent.com/Arkansascovid/Main/master/hospital_master.csv')
head(hospital)
```
# Number columns
```{r}
ncol(hospital)
```
# Number rows
```{r}
nrow(hospital)
```

- **Clean Names**
```{r}

hospital <- janitor::clean_names(hospital)
```

##Check it out - Look at the data types.
```{r}
 
#chr stands for character vectors, or strings.
#int stands for integers.
#dbl stands for doubles, or real numbers.
#dttm stands for date-times (a date + a time).
glimpse(hospital)
```


- **Cleaning: Convert data from List variables to numeric**

```{r include=FALSE}

#text to date
hospital$date <- as.Date(hospital$date)
#text to numeric
hospital[3:6] <- lapply((hospital)[3:6], as.numeric)

```

```{r}
#Eliminate junk first column
hospital <- hospital [ -c(1) ]
```

```{r}
#Rename columns
#colnames(hospital)[2:5] <- c("Hospitalized", "Vent", "Ever_Hospitalized", "Ever_on_a_Vent")
```

#----------------------------------------------------------------------------------#
#Filtering and Selecting Columns
#----------------------------------------------------------------------------------#


#Create a table sorted by number of hospitalized
```{r include=FALSE}

#Always a good idea to rename the target as something different in case you mess up something
#this is hospital1
hospital1 <- hospital %>% 
  arrange(desc(hospitalized))
```

```{r}
#Check out the new table, all sorted cool
head(hospital1)

```

#YOUR TURN: Create a Table sorted by vent
```{r}

vent <- hospital %>% 
  arrange(desc(vent))
head(vent)
```

#Filtering and Selecting Columns
```{r}
#Filter just the October entries
hospital1 <- hospital %>% 
  filter(date > "2020-09-30")

(hospital1)

```

#Fix the dates for chronological order
```{r}
#Fix the dates for chronological order
hospital1 <- hospital1 %>% 
  arrange(desc(date))

(hospital1)

```

#YOUR TURN: Create a Table just with the August entries
#HINT: https://stackoverflow.com/questions/22420487/subsetting-data-table-set-by-date-range-in-r
#https://rstudio.com/wp-content/uploads/2015/02/data-wrangling-cheatsheet.pdf
testset[date>="2013-08-02" & date<="2013-11-01"]
```{r}

hospital1 <- hospital %>% 
  filter(date >= "2020-08-01" & date <= "2020-08-31")
hospital1

```

#dplyr
#We are using basic commands in the dplyr package
#https://dplyr.tidyverse.org/

#Select just three columns
```{r}
vent2 <- hospital %>% 
  select(date, vent, ever_on_a_vent)
head(vent2)
write.csv(vent2, "vent2.csv")
```


#YOUR TURN
#IMPORT the master file from the Arkansascovid GitHub site
#1) Create a new filtered table with October only files
#2) Create a new table just with deaths, but for all dates
#3) Create a new table with all of the testing data

#Bonus: Take tables 1, 2 & 3 and build Flourish Charts with them

#IF YOU FINISH THAT, REVEL IN YOUR NERD POWERS

#IMPORT the master file from the Arkansascovid GitHub site
```{r}
master <- rio::import("https://raw.githubusercontent.com/Arkansascovid/Main/master/master_file.csv")

master$mydate <- as.Date(master$mydate)
head(master)
```

#2) Create a new table just with deaths, but for all dates
#Build Confirmed Table


```{r}
deaths <- master %>% 
  select(mydate, deaths, New_Deaths_Today, confirmed_death, probable_death, Pct_Deaths_vs_Recoveries, Pct_Recoveries_vs_Deaths )
head(deaths)

```

#2a) Using advanced dplyr command
```{r}
#Use contains to search by string to find the variables by name
#this grabs everything except date
x <- select(master, contains("death"))
names(x)
```

```{r}
#copy those names into a select sequence
death2 <- master %>% 
  select("mydate", "deaths", "confirmed_death",  "probable_death", "New_Deaths_Today", "Pct_Deaths_vs_Recoveries", "Pct_Recoveries_vs_Deaths" )

```


#3) Create a new table with all of the testing data
```{r}
test <- master %>% 
  select("mydate", "total_tests", "pcr_test",  "antigen_test", "pcr_pos_test",  "pcr_neg_test", "antigen_pos_test", "antigen_neg_test",    "total_pos_test", "total_neg_test", "New_Tests_Dashboard", "Tested_Population" )
```


#3a) Using advanced dplyr command
```{r}
#Use contains to search by string to find the variables by name
#this grabs everything except date
x <- select(master, contains("test"))
names(x)
```

```{r}
#copy those names into a select sequence
test <- master %>% 
  select("mydate", "total_tests",   "pcr_test",            "antigen_test",        "pcr_pos_test",        "pcr_neg_test",        "antigen_pos_test",   
"antigen_neg_test",    "total_pos_test",      "total_neg_test",      "New_Tests_Dashboard", "Tested_Population" )

```

#----------------------------------------------------------------------------------#
# Math
#----------------------------------------------------------------------------------#
###Summary Statistics
Here is a quick way to view the range of your data  

```{R}
summary(master$death)
```

```{r}
#Crime$Murder.Robbery <- Crime$Murder + Crime$Robbery

death2$NEWtotal <- death2$confirmed_death + death2$probable_death
head(death2)

```

```{r}
#percentage of a column
#calculate confirmed as percentage of total deaths

death2$pct_confirmed <- (death2$confirmed_death / death2$deaths)*100
head(death2)
glimpse(master)
```

#YOUR TURN
#1) Antigen positivity rate 
#2) Antigen positive rate, just for state of Arkansas
#3) Percent positive rate overall (positive / total tests)
#4) Percent positive rate overall, just for state of Arkansas
#5) Percent positive rate, just Arkansas, highest to lowest by date
#6) Create a heatmap with results from #5

#----------------------------------------------------------------------#
#Student Feedback
#----------------------------------------------------------------------#
#1) Antigen positivity rate
```{r}
#Error - you overwrote your data with this calculation. Need to create a new column
#master2$antigen_pos_test <- (master2$antigen_pos_test / master2$antigen_test)*100

master$antigen_pct <- (master$antigen_pos_test / master$antigen_test)*100


#Wells edit - create mini table to look at results
antigen <- master %>% 
  select(county_nam, mydate, antigen_pos_test, antigen_test, antigen_pct)
  

head(antigen)
```

#1 Antigen positivity rate
```{r}
#Error - divided antigen into the wrong denominator: should be antigen_test. total_pos_test is all PCR and antigen
# master2 <- master %>% mutate(Antigen_positivity_rate = (antigen_test / total_pos_test)*100)
# print(master2)

#However: GOOD to use mutate and the dplyr syntax

#Wells Edit. 
#Fixed Math
#Create a mini table to check your math

master <- master %>% 
  mutate(Antigen_positivity_rate1 = (antigen_test / total_pos_test)*100) %>% 
  mutate(Antigen_positivity_rate2 = (antigen_pos_test / antigen_test)*100)

test2 <- master %>% 
  select(county_nam, mydate, antigen_pos_test, antigen_test, total_pos_test, Antigen_positivity_rate1, Antigen_positivity_rate2)

head(test2)

```


#Wells Edit
#Good to see you find a package, writexl.
#write.csv is base R and does a similar thing.

```{r}
library(writexl)
write_xlsx(master2,"/Users/obedlamy/Desktop/Data Journalism/Antigen.xlsx")
```


#3) Percent positive rate overall (positive / total tests)
```{r}
#Wells edit: I would not create another table master3. Just do it to master and the create a mini table to view results
# master3 <- master %>% mutate(Percent_positive = (positive / total_tests)*100)
# print(master3)

#Wells edit
#I would be very specific on the type of test and divide an apple into an apple.
#total_tests is problematic since positive and negative do not add up to total_tests
#instead divide pcr_pos into pcr_test

master <- master %>% 
#drop the next line down to better keep track of things
  mutate(PCR_Percent_positive = (pcr_pos_test / pcr_test)*100) %>% 
  mutate(Percent_positive = (positive / total_tests)*100)

test4 <- master %>% 
  select(county_nam, mydate, Percent_positive, PCR_Percent_positive, positive, total_tests, pcr_pos_test, pcr_test )

head(test4)


```


#5) Percent positive rate, just Arkansas, highest to lowest by date
```{r}

test <- master %>% 
  select(county_nam, mydate, PCR_Percent_positive) %>% 
  filter(county_nam == "Arkansas_all_counties") %>% 
  #arrange(desc(PCR_Percent_positive))
  arrange(desc(mydate))
test

```



#----------------------------------------------------------------------------------#
# Lubridate Introduction
#      Lubridate vignette   
```{r}
browseVignettes("lubridate")   
```
#Introduction to Lubridate
#Tutorials Based on Andrew Ba Tran dealing-with-dates.R

#Install lubridate if you haven't already
```{r}
#install.packages("lubridate")
#library(lubridate)
```
#Load some data

```{r}
some_date <- "12-31-1999"
```

```{r}
# NOTE: IF YOU GET AN ERROR ABOUT NOT HAVING A PACKAGE CALLED stringi
# UNCOMMENT AND RUN THE LINES BELOW IF YOU HAVE A MAC MACHINE

#install.packages("glue", type="mac.binary")
#install.packages("stringi", type="mac.binary")
#install.packages("stringr", type="mac.binary")
#install.packages("lubridate", type="mac.binary")
```
#Ok, here we go
```{r}
library(lubridate)

mdy(some_date)
```

```{r}
data <- data.frame(First=c("Charlie", "Lucy", "Peppermint"),
                   Last=c("Brown", "van Pelt", "Patty"),
                   birthday=c("10-31-06", "2/4/2007", "June 1, 2005"))
data$DOB <- mdy(data$birthday)

data
```

```{r}
data$year <- year(data$DOB)
data$month <- month(data$DOB, label=TRUE)
data$week <- week(data$DOB)
data$day <- day(data$DOB)
data$weekday <- wday(data$DOB, label=TRUE, abbr=FALSE)

data
```


#------------------------------------------------------#
#Now, apply this to the Covid data
#------------------------------------------------------#


#Load the Covid master_file
#IMPORT the master file from the Arkansascovid GitHub site
```{r}
master <- rio::import("https://raw.githubusercontent.com/Arkansascovid/Main/master/master_file.csv")
str(master)
```

#Question 1: Convert date to mdy -- see line 376
```{r}
master$date <- ymd(master$mydate)
#str(master)
```


#Question 2: Mini table for Master to just county_nam, date, positive, Pct_Positive_Cumulative
```{r}
test5 <- master %>% 
  select(county_nam, date, positive, Pct_Positive_Cumulative)
head(test5)
```

#Create Separate Year Field
```{r}
test5$year <- year(test5$date)
test5
```

#Create Separate Month Column
```{r}
test5$month <- month(test5$date, label=TRUE)
test5
```

#Create Separate Week Column

```{r}
test5$week <- week(test5$date)
test5
```

#Create Separate Day Column
```{r}
test5$day <- day(test5$date)
test5
```
#Create Separate Weekday Column
```{r}
test5$weekday <- wday(test5$date, label=TRUE, abbr=FALSE)
test5
```

#Using master_file, answer a recent reader's question:
#Was this a record number of cases for a Saturday?
```{r}
#format dates
library(lubridate)
master$date <- ymd(master$mydate)
master$year <- year(master$date)
master$month <- month(master$date, label=TRUE)
master$week <- week(master$date)
master$day <- day(master$date)
master$weekday <- wday(master$date, label=TRUE, abbr=FALSE)

#Mini table
mini <- master %>% 
  select(county_nam, date, year, month, week, day, weekday)
mini
```


#--------------------------------------------------------------------------------#
#Weekly Change in Cases Calculations
#--------------------------------------------------------------------------------#
#Set Dates
```{r}
#today's date
today <- Sys.Date()

#NOTE: IF YOU ARE RUNNING THIS A DAY LATE, USE THIS CODE TO WORK PROPERLY
#today <- Sys.Date()-1
#today_county$mydate <-"2020-09-22- THE OLD DATE...."

#yesterday's date
yesterday <- (today-1)

```

```{r}
df1 <- master %>% 
  filter(County_Name=="Arkansas_all_counties")

df1 <- df1 %>%
  mutate(DATE = ymd(Date))
```

#date formatting
```{r}
df1$week <- isoweek(df1$DATE)
df1$month <- month(df1$DATE)
```

#calculations by week
```{r}
newcases <- df1 %>%
  group_by(week) %>%
  summarise(week_newcases = sum(New_Cases_Today, na.rm = TRUE))
```

#import table to name the weeks beginning Monday
```{r}
weeks_numbers <- rio::import("https://raw.githubusercontent.com/profrobwells/CovidFall2020/master/weeks_numbers.csv")

weeks_numbers$Date <- lubridate::mdy(weeks_numbers$Date)
```

#Join with cases; manually change date filter after a week
```{r}
weeklycases <- newcases %>% 
  right_join(weeks_numbers, by=c("week"="Number")) %>% 
  filter(Date<today) %>% 
  distinct() %>% 
  arrange(desc(Date))

weeklycases <- weeklycases %>%
  rename(Weekly_Total_New_Cases = week_newcases, Week_Beginning = Date)

#write.csv(weeklycases, "weeklycases.csv")
```

#  Group_By
#Takes existing data and groups specific variables together for future operations. Many operations are performed on groups.

```{r}
mini2 <- mini %>%
  filter(county_nam =="Arkansas_all_counties") %>% 
  group_by(month)
mini2
```

#----------------------------------------------------------------------------------#
#  Make a List of Top 10, or Top 5 or Top anything
#----------------------------------------------------------------------------------#
#top_n
#https://www.rdocumentation.org/packages/dplyr/versions/0.7.8/topics/top_n


#Top 10 days with new deaths
```{r}
top_deaths <- master %>% 
  select(county_nam, mydate, New_Deaths_Today) %>% 
  filter(county_nam == "Arkansas_all_counties") %>% 
  top_n(10, New_Deaths_Today) %>% 
  arrange(desc(New_Deaths_Today))
top_deaths
```

#Table with the top five counties by deaths for November

```{r}
#Mini table with 
novdeath <- master %>% 
  select(month, county_nam, New_Deaths_Today) %>% 
  filter(month =="Nov") %>% 
  filter(!(county_nam == "Arkansas_all_counties")) %>% 
  group_by(county_nam, month) %>% 
  summarise(Nov_deaths = sum(New_Deaths_Today, na.rm = TRUE)) 
novdeath
```

```{r}

novdeath1 <- top_n(ungroup(novdeath), 5, Nov_deaths) 
novdeath1 <- novdeath1 %>% 
  arrange(desc(Nov_deaths))
novdeath1
```


#---------------------------------------------------------
#Resume Here Monday
#---------------------------------------------------------


#Using master_file, answer record number of cases for a Monday

#Bring in Master file, format dates
```{r}

master <- rio::import("https://raw.githubusercontent.com/Arkansascovid/Main/master/master_file.csv")

#format dates
library(lubridate)
master$date <- ymd(master$mydate)
master$year <- year(master$date)
master$month <- month(master$date, label=TRUE)
master$week <- week(master$date)
master$day <- day(master$date)
master$weekday <- wday(master$date, label=TRUE, abbr=FALSE)

#Mini table
mini <- master %>% 
  select(county_nam, date, year, month, week, day, weekday)
mini
```

#Create Monday-only table, statewide data, call is "mon"
```{r}

mon <- master


```

#Make a Basic Chart
```{r}

ggplot(mon, aes(x=date, y=New_Cases_Today, color = New_Cases_Today, fill=New_Cases_Today)) +
  geom_col() +
  theme(axis.text.x = element_text(angle = 45, vjust = 1.2, hjust = 2.1)) +
  labs(y="Cases Per Day", title="Record COVID-19 Cases on Wednesdays in Arkansas", caption="Source: ADH")+
  theme(legend.position="none") 

```

#Improve on Chart, flipped and with labels

```{r}
mon %>% 
  filter(date > "2020-03-26") %>% 
  filter(New_Cases_Today > 500) %>% 
  ggplot(aes(x = reorder(date, New_Cases_Today), 
             y = New_Cases_Today,
             fill = New_Cases_Today)) +
  geom_col(position = "dodge", show.legend = FALSE) +
  theme(axis.text.x = element_text(angle = 90, hjust = 1)) +
  #label formatting. Scales, into percentages. hjust moves to the grid
  geom_text(aes(label = (New_Cases_Today)), position = position_stack(vjust = 1), hjust = -5., size = 2.5)  +
  #format the x axis. sets the grid to maximum 30%
  scale_y_continuous(limits=c(0, 1800)) +
  coord_flip() +
  labs(title = "Record COVID-19 Cases on Wednesdays in Arkansas",
       subtitle = "Cases 500 and Greater",
       caption = "Graphic by Rob Wells, 11-11-2020",
       y="Source: Arkansas Dept of Health",
       x="")

ggsave("Wednesday top cases 11_11.png", device = "png", width=10,height=8, dpi=800)

```

#Or Use top_n
```{r}
mon %>% 
  top_n(10, New_Cases_Today) %>% 
  ggplot(aes(x = reorder(date, New_Cases_Today), 
             y = New_Cases_Today,
             fill = New_Cases_Today)) +
  geom_col(position = "dodge", show.legend = FALSE) +
  theme(axis.text.x = element_text(angle = 90, hjust = 1)) +
  #label formatting. Scales, into percentages. hjust moves to the grid
  geom_text(aes(label = (New_Cases_Today)), position = position_stack(vjust = 1), hjust = -5., size = 2.5)  +
  #format the x axis. sets the grid to maximum 30%
  scale_y_continuous(limits=c(0, 1800)) +
  coord_flip() +
  labs(title = "Record COVID-19 Cases on Wednesdays in Arkansas",
       subtitle = "Cases 500 and Greater",
       caption = "Graphic by Rob Wells, 11-11-2020",
       y="Source: Arkansas Dept of Health",
       x="")

ggsave("Wednesday top cases 11_11.png", device = "png", width=10,height=8, dpi=800)

```

#Reader Question: Number of Deaths since Oct 8

```{r}

```


#Using master_file, develop monthly totals and percentage changes for new cases and deaths
```{r}

```

#Percentage change by month new cases
```{r}

```

#Percentage change by month new deaths

```{r}

```

#White House Report Question
# Arkansas had 193 new cases per 100,000 population in the last week, compared to a national average of 90 per 100,000.
#CALCULATE total new cases by week, divide into our population

```{r}

```

#Your Turn
#Top 5 Deaths on Saturdays: A table just with date, New Deaths
```{r}



```

#Make a chart
```{r}


```


#----------------------------------------------------------------------------------#
## NOTES: HOW TO Retrieve Data from Your Hard Drive
#----------------------------------------------------------------------------------#
- **Download Daily Hospital Data - Manual Input" from Google Sheet**
https://docs.google.com/spreadsheets/d/1ikblX8tikM59ma1AftkqgGbyeZkXB6DuBtwMsVeoGYw/edit#gid=701586163

- **Get the File Path on Your Hard Drive**
    
    1) Find the File and LEFT CLICK on it (or Control + Click).
        A menu appears. 
    2) Continue holding down CONTROL. 
    3) Now Hold down CONTROL + OPTION at same time. 
    Copy "Daily State Data - Manual Input" as Path. 
    It will look like this:
    /Users/rswells/Downloads/Daily State Data - Manual Input.xlsx
    4) Paste that path below


```{r}
#An example of downloading data from your hard drive
#hospital <- rio::import("/Users/rswells/Downloads/Daily State Data - Manual Input.xlsx")
#This loads in "Daily State Data - Manual Input" from Google Sheet

```