Methods 1, Week 3

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

library(tidyverse)

# import the county poverty rate dataset
raw_county_pov22 <- read_csv("data/raw/county_child_poverty_2022.csv") 

## process the data to create New York poverty rate dataset
county_pov_22 <- raw_county_pov22 |> 
  rename(county_child_poverty_count = `Poverty Estimate, Age 5-17 in Families`) |> 
  mutate(conum = paste0("36", `County FIPS Code`),
           county_child_poverty_rate = county_child_poverty_rate/100,
           county_child_count = county_child_poverty_count/county_child_poverty_rate) |> 
  select(Name, conum, county_child_count, county_child_poverty_count, county_child_poverty_rate) 

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

# Create analysis dataframe

library(tidyverse)
# Import processed dataframes
# Calculate state-wide child 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_22 <- read_csv("data/processed/county_pov_rate_2022.csv")
stpov22 <- read_csv("data/processed/ny_school_district_student_poverty_rate_2022.csv")

# Calculate the state-wide child 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_22 <- read_csv("data/processed/county_pov_rate_2022.csv")
stpov22 <- read_csv("data/processed/ny_school_district_student_poverty_rate_2022.csv")

# Calculate the state-wide child poverty rate
state_stpovrate = sum(county_pov_22$county_child_poverty_count)/sum(county_pov_22$county_child_count)

# Create a copy of the school district  dataframe and join the county poverty dataframe to to it
sd_county_pov <- stpov22 |> 
  left_join(county_pov_22, 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_22 <- read_csv("data/processed/county_pov_rate_2022.csv")
stpov22 <- read_csv("data/processed/ny_school_district_student_poverty_rate_2022.csv")

# Calculate the state-wide child poverty rate
state_stpovrate = sum(county_pov_22$county_child_poverty_count)/sum(county_pov_22$county_child_count)

# 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 <- stpov22 |> 
  left_join(county_pov_22, by = "conum") |> 
  select(id, district, County, conum, tpop, stpop, 
         stpovrate, county_child_count, county_child_poverty_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_22 <- read_csv("data/processed/county_pov_rate_2022.csv")
stpov22 <- read_csv("data/processed/ny_school_district_student_poverty_rate_2022.csv")

# Calculate the state-wide child poverty rate
state_stpovrate = sum(county_pov_22$county_child_poverty_count)/sum(county_pov_22$county_child_count)

# 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 <- stpov22 |> 
  left_join(county_pov_22, by = "conum") |> 
  select(id, district, County, conum, tpop, stpop, stpov,
         stpovrate, county_child_count, county_child_poverty_rate)  |> 
  mutate(pov_diff_county = round(stpovrate - county_child_poverty_rate, 3),
         pov_diff_state = round(stpovrate - state_stpovrate, 3))

write_csv(sd_county_pov, "data/processed/ny_school_district_AND_county_child_poverty.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")