Demographic indicators

[SashaWeinstein]

Goal: output 9 tables containing all data points for the 4 indicators that fall into the demographics category. Note that data comes from Census (all of Race and Hispanic origin comes from Census) or it comes from ACS PUMS data.

Final output will include 9 tables; 1 for each geography level and 1 for each timeframe. Each table should have the same number of columns.

• PUMA: 1 row for each PUMA
  • demographics_2000_puma.csv
  • demographics_0812_puma.csv
  • demographics_1519_puma.csv
• Borough: 1 row per borough
  • demographics_2000_borough.csv
  • demographics_0812_borough.csv
  • demographics_1519_borough.csv
• City: 1 row for citywide data
  • demographics_2000_citywide.csv
  • demographics_0812_citywide.csv
  • demographics_1519_citywide.csv

Create new folder in edm-publishing db-eddt>demographics>demographics_2000_puma.csv to place output files.

Each datapoint will have the following:

• Count
• CV
• MOE
• Percent
• Percent MOE

Consult data dictionary and data matrix field specifications tab with detailed information on exactly what to pull for each indicator.

[AmandaDoyle]

AD feedback on output

• [x] 1) Update file names and folder structure to match what was scoped out in the epic so that structure is consistent across outputs.
• [x] 2) Where is the 2000 data?
• [x] 3) Something is wrong with the outputs because there are only 6 rows in the demographic 2019_by_puma.csv
• [x] 4) Remove total_pop-pct-denom field.
• [ ] 5) I think the the denominator logic is different for the race and hispanic origin category. Instead of having the denominator be the total population of that race, it's the total population (i.e. pop anh pct would be 4677 / 110196 = 4.24%)
• [x] 6) Remove the total_pop-anh-pct-denom and other race denominator fields for total pop. Let's discuss how to best record denominators.
• [ ] 7) Double check to make sure that CV calculation matches was was done in Pop Fact Finder. See here
• [ ] 8) This document walks you through of how to calculate median age
• [x] 9) I checked the counts, MOEs, and percent values for 2019 data for PUMA 3701 using the census summary tables and the values look correct to me.
• [ ] 10) We'll need to rename the fields, but lets draft out the naming convention and agree on it so were on the same page before implementing anything

[SashaWeinstein]

Hey sorry about the 6 columns in the by puma, I thought I ran the export with all data but I only ran with test data. I will re-run now and then we will have the full columns. Will work through other feedback while that is running

[SashaWeinstein]

2000 data is from Erica, we are holding off on that

[SashaWeinstein]

Putting Te's comment here

Naming Convention

I am still unsure about the naming convention. In this work race, moe, cv suffix is appended with - and in the wiki it seems that all should be appended with _.

This was a good comment he made on the export PUMS pull request. The reason I'm not addressing it yet is because of our plan changing math first and worry about column labels second. it makes sense to merge in this export PUMS pull request first as to not create merge conflicts in the aggregation code. We will address this once math is correct

[SashaWeinstein]

Ok so race crosstabs denominators seem to be correct for indicators but not correct for the total_pop columns. I think we can just take out the total pop crosstabbed by race? We only use that information to calculate percentages and it should only be reported in the relevant denominator columns. We will get correct numbers on race from the decennial census

[AmandaDoyle]

March 10 review

• File names in folder is correct

Review of 1519_borough file

• [x] There shouldn't be a cv for a percent (just have pct_moe and drop pct_cv)
• [x] Remove _count from field names (i.e. lep should just be lep and not lep_count same with fb. fb and not fb_count)
• [x] Age field names do not match convention we agreed on yesterday (i.e. age_popu16, age_p16t64, age_p65pl, age_median, age_p5pl)
• [ ] The Race/ethnicity abbreviation and stats suffix should always follow the root field name (i.e. age_median and not age_hsp_median)
• [x] Update field names to be all lower case
• [ ] Missing age_p5pl

[SashaWeinstein]

I thought we said no age_p5pl, because denominator columns stay as is

[AmandaDoyle]

I thought if something had a specific variable we'd include the field. If that last piece is a heavy life then leave all denominators as is.

[SashaWeinstein]

sorry I guess I remembered wrong. I'm not sure if I understand "The Race/ethnicity abbreviation and stats suffix should always follow the root field name (i.e. age_median and not age_hsp_median)" What's the issue with age_hsp_median? It's the same as lep_wnh_count or pop_hsp_pct as far as I can see?

[AmandaDoyle]

Yeah they all need to be updated to follow the convention that we've laid out and have estaibaled with other field names.

[SashaWeinstein]

Ok so all race crosstab column labels for all processes we have are in the wrong order. Is this something we've talked about before? I don't remember talking about it in standup, and it's not in the long comment. I don't think I can make those changes very quickly

[AmandaDoyle]

Let's discuss what this would entail after I'm off the phone with Lynn

[SashaWeinstein]

ok