Methods 1, Week 3

• Create dataframe of poverty rate by county
• Create dataframe of student poverty rate by school district
• Calculate the statewide student poverty rate
• Join the school district and county poverty dataframes to compare the poverty rates
• Measure the difference in poverty rates of each school district and it’s county and the state
• Use summary statistics to explore and gain understanding
• Use visualizations to explore and gain understanding

• Script 1. Process data
  • Create dataframe of poverty rate by county
• Script 2. Process data
  • Create dataframe of student poverty rate by school district
  • Calculate statewide student poverty rate
• Script 3. Create analysis dataframe
  • 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
• Script 4. Visualize and explore your analysis dataframe

Create a new script named 1.process_ny_county_poverty_data.R

• save it to methods1/ny_poverty_analysis

# 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/nhgis0042_ds244_20195_2019_county.csv") 

## 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_rate = (ALWVE002 + ALWVE003)/county_pop) %>% 
  select(STATE, COUNTY, GEOID, county_pop, county_pov_rate) 

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

Create a new script named 2.process_ny_sd_poverty_data.R

• save it to methods1/ny_poverty_analysis

# Create dataframe of school district poverty rate

library(tidyverse)
# import raw student poverty dataset
raw_stpov19 <- read_csv("data/raw/ny_student_poverty_2019.csv")

# create new dataframe from raw_stpov19 to process the data
stpov19 <- raw_stpov19 %>%
  rename(Estimated_Pop_5_17 = `Estimated Population 5-17`) %>% 
  mutate(stud_pov_rate = Estimated_relevant_5_17_in_poverty/Estimated_Pop_5_17,
         year = "2019") %>% 
  select(Postal, County, CONUM, district_id, Name, Estimated_Total_Pop, Estimated_Pop_5_17,
         Estimated_relevant_5_17_in_poverty, stud_pov_rate, year) %>%
  # filter(Estimated_Pop_5_17 >= 100) %>% # remove filter
  rename(district = Name,
         tpop = Estimated_Total_Pop,
         stpop = Estimated_Pop_5_17,
         stpov = Estimated_relevant_5_17_in_poverty,
         stpovrate = stud_pov_rate)

We’re keeping 2 columns that weren’t in the homework file:

• County and CONUM (county id)

• To connect two dataframes, you need at least one id variable that is in both dataframes - called a key.

County dataframe

School district dataframe

• These two datasets do have a common key - though the column name is different.

Create a new script named 3.analyze_ny_poverty.R

• save it to methods1/ny_poverty_analysis
• list the analysis steps (with a hash before every line so that it doesn’t run)

# Create analysis dataframe

library(tidyverse)
# Import processed dataframes
# 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

Import your 2 datasets

# Create analysis dataframe
library(tidyverse)

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

# 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

Join the county data to the school district poverty data

# Create analysis dataframe
library(tidyverse)

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

# Join county poverty dataframe to school district data
sd_county_pov <- sd_pov %>% 
  left_join(county_pov, by = c("CONUM"="GEOID"))

# 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

When you join two dataframes together, you need to think about which one is your primary table.

In the school district - county example:

• Each school district has 1 county
• Each county has many school districts

We want to keep all of the school district rows and add a column with the county poverty rate. So we:

• create a new dataframe from the school districts
• use a left_join() to add the county data to the school district data if they have the same county id.

sd_county_pov <- sd_pov %>% 
  left_join(county_pov, by = c("CONUM"="GEOID"))

We will discuss this more later! For now, just know that there are other types of joins.

selected columns from the analysis dataframe:

Work through this analysis on your own up to this point for ~ 30 minutes.

I’ll come around to help!

We’ll come back together and answer these questions together:

• What district has the highest student poverty rate?
• What county is that district in?
• What county has the highest poverty rate?
• What school district has the lowest student poverty rate in that county?

If you finish, try working through the joins_simple_examples.R script in today’s folder.

A histogram is a chart that shows the distribution of your data.

• The height of each bar indicates how many district’s poverty difference is within that range.

( pov_diff_county = stpovrate - county_pov_rate )

A scatterplot is a chart that is used to look at the relationship between two variables.

• Each dot is a school district.
• The pattern of dots helps you to determine whether a relationship exists between two variables.
• Here, you can say there is a positive relationship between County Poverty Rate and School District Poverty Rate
  • meaning as County Poverty Rate increases, the School District Poverty Rate tends to be higher also

• We can use the summarise() function to create our own statistics to help answer our questions.
  • we’ll create a new dataframe that is a single row summarizing our dataframe
  • useful arguments within summarize:
    • mean(), median(), min(), max(), sum(), n()
    • n() returns the number of rows

• We can also calculate the same statistics for each county by using:
  • group_by() and summarise() together
  • group_by() indicates that you want to summarize based on another variable rather than the whole dataset

Work through the in-class exercise and answer the following questions:

• What district has the highest student poverty rate?
• What county is that district in?
• What county has the highest poverty rate? (you should use the county poverty dataset created in script 1 to answer this question)
• What school district has the lowest student poverty rate in Montgomery County?
• What county has the largest range in student poverty rate?
• What county has the smallest range in student poverty rate?

On Canvas, submit a text document with the answer to these questions and your 3 scripts.