Methods 1, Week 3

# Processing and exploring the 2019 poverty data from the American Community Survey for New York

library(tidyverse)

# import the county poverty rate dataset
raw_county_pov19 <- read_csv("data/raw/nhgis0042_csv/county_poverty_2019.csv") 

## variable definition from metadata in "data/raw/nhgis0042_csv/county_poverty_2019_codebook.txt"
# ALWVE001:    Total Population
# ALWVE002:    Under .50 (Population living at less than 50% of poverty level)
# ALWVE003:    .50 to .99 (Population living at 50% to 99% of poverty level)

## process the data to create New York poverty rate dataset
county_pov_19 <- raw_county_pov19 |> 
  select(STATE, COUNTY, GEOID, ALWVE001, ALWVE002, ALWVE003) |> 
  filter(STATE == "New York") |> 
  rename(county_pop = ALWVE001) |> 
  mutate(county_pov = ALWVE002 + ALWVE003,
         county_pov_rate = county_pov/county_pop,
         # use the substring function to select the county number from the GEOID
         CONUM = substr(GEOID, 8, 13)) |> 
  select(STATE, COUNTY, CONUM, county_pop, county_pov, county_pov_rate) 

# write out county poverty rate
write_csv(county_pov_19, "data/processed/county_pov_rate_2019.csv")

# Create analysis dataframe

library(tidyverse)
# Import processed dataframes
# Calculate state-wide student poverty rate
# Join county poverty dataframe to school district data
# Create new variables 
### difference from county rate = district poverty rate - county poverty rate
### difference from state rate = district poverty rate - state poverty rate
# Calculate summary statistics of poverty rate

# Create analysis dataframe
library(tidyverse)

# Import processed dataframes
county_pov_19 <- read_csv("data/processed/county_pov_rate_2019.csv")
stpov19 <- read_csv("data/processed/school_district_student_pov_rate_2019.csv")

# Calculate the state-wide poverty rate
# Join county poverty dataframe to school district data
# Create new variables 
### difference from county rate = district poverty rate - county poverty rate
### difference from state rate = district poverty rate - state poverty rate
# Calculate summary statistics of poverty rate

# Create analysis dataframe
library(tidyverse)

# Import processed dataframes
county_pov_19 <- read_csv("data/processed/county_pov_rate_2019.csv")
stpov19 <- read_csv("data/processed/school_district_student_pov_rate_2019.csv")

# Calculate the state-wide poverty rate
state_stpovrate = sum(county_pov_19$county_pov)/sum(county_pov_19$county_pop)

# Create a copy of the school district  dataframe and join the county poverty dataframe to to it
sd_county_pov <- stpov19 |> 
  left_join(county_pov_19, by = "CONUM")

# Create new variables 
### difference from county rate = district poverty rate - county poverty rate
### difference from state rate = district poverty rate - state poverty rate
# Calculate summary statistics of poverty rate

# sd_county_pov <- sd_pov |> 
#   left_join(county_pov, by = "CONUM")

# Create analysis dataframe
library(tidyverse)

# Import processed dataframes
county_pov_19 <- read_csv("data/processed/county_pov_rate_2019.csv")
stpov19 <- read_csv("data/processed/school_district_student_pov_rate_2019.csv")

# Calculate state-wide student poverty rate
state_stpovrate = sum(county_pov_19$county_pov)/sum(county_pov_19$county_pop)

# Join county poverty dataframe to school district data
# Create a copy of the school district  dataframe and join the county poverty dataframe to to it
sd_county_pov <- stpov19 |> 
  left_join(county_pov_19, by = "CONUM") |> 
  select(id, district, COUNTY, CONUM, tpop, stpop, 
         stpovrate, county_pop, county_pov_rate) 

# Create new variables 
### difference from county rate = district poverty rate - county poverty rate
### difference from state rate = district poverty rate - state poverty rate
# Calculate summary statistics of poverty rate

# Create analysis dataframe
library(tidyverse)

# Import processed dataframes
county_pov_19 <- read_csv("data/processed/county_pov_rate_2019.csv")
stpov19 <- read_csv("data/processed/school_district_student_pov_rate_2019.csv")

# Calculate the state-wide poverty rate
state_stpovrate = sum(county_pov_19$county_pov)/sum(county_pov_19$county_pop)

# Join county poverty dataframe to school district data
# Create a copy of the school district  dataframe and join the county poverty dataframe to to it
sd_county_pov <- stpov19 |> 
  left_join(county_pov_19, by = "CONUM") |> 
  select(id, district, COUNTY, CONUM, tpop, stpop, stpov,
         stpovrate, county_pop, county_pov_rate) |> 
  mutate(pov_diff_county = round(stpovrate - county_pov_rate, 3),
         pov_diff_state = round(stpovrate - state_stpovrate, 3))

write_csv(sd_county_pov, "data/processed/ny_sd_county_pov_data.csv")
# Calculate summary statistics of poverty rate

library(tidyverse)

# the two lines below create test dataframes
customer_purchase = data.frame(CustomerId = c(1:6), Product = c(rep("Toaster", 3), rep("Radio", 3)))
customer_state = data.frame(CustomerId = c(2, 4, 6), State = c(rep("Alabama", 2), rep("Ohio", 1)))

# Inner Join 
# keep only rows where CustomerId matches
inner_join_purchase <- customer_purchase %>% 
  inner_join(customer_state, by = "CustomerId")

# Full Join
# keep all rows from both dataframes
full_join_purchase <- customer_purchase %>% 
  full_join(customer_state, by = "CustomerId")

# Left Join 
#  keep all of the rows in the first dataframe and add the columns from the second
left_join_purchase <- customer_purchase %>% 
  left_join(customer_state, by = "CustomerId")

# Right Join 
#  keep the rows in the first dataframe that have the same value for CustomerId as the second
right_join_purchase <- customer_purchase %>% 
  right_join(customer_state, by = "CustomerId")

summary(sd_county_pov)