RIP data wrangling

library(tidyverse)

# Read the data 
df <- read_csv("RIP_towns_6May_w_county_edited.csv")

# Change the format from wide to long
df_long <- df %>% 
  gather(key = date, value = deaths, -Town, -County) %>% 
  mutate(year = paste0("20", str_extract(date, "[0-9]{2}")), 
         year = as.numeric(year), 
         month = str_extract(date, "[aA-zZ]{3}"))

# Just lower case the names of the df
names(df_long) <- str_to_lower(names(df_long))

head(df_long)

# Filter by whatever you want, e.g.
df_april <- df_long %>% 
  filter(month == "Apr")

# Create tables with the means 
df_april %>% 
  group_by(town) %>% 
  summarise(mean_deaths = mean(deaths)) %>% 
  arrange(desc(mean_deaths))

# If you need more than one filter, e.g.
df_april_2010 <- df_long %>% 
  filter(month == "Apr", year == 2010)

print(df_april_2010, n = 10)

# If you need to filter by year interval
df_april_2010_19 <- df_long %>% 
  filter(month == "Apr", year >= 2010, year <= 2019)
# Checking that the years are correct
table(df_april_2010_19$year)