2014-2018 new issues

[SPTKL]

so to have it all in the same place of the errors i've found so far in my checks:

• [x] overall problem of data not appearing where i have data-- database shows 0, api pull shows as null
• [x] social: abroad, DfHsDfCnt, DfHsSmCnt, DfHsUS .. the percent and percent MOE columns should be calculated off of DfHs2 and not pulled directly from the census bureau's data (which i think is the problem)
• [x] economic: seems that there may be some issues with MdEWrk, MdEFFTWrk, MdEFFTWrk-- missing MOEs and CVs for some, different top-coded values (census bureau has 250,000 as top-coded for their geographies, api database is showing 250,001 (example: 1007900, 1011401, 1006900, etc.))
• [x] housing: the issue with the rooms section-- MOE, CV, Percent, Percent MOE appearing as null when they should have values

select * from (
select * from (
select *,
(case when ac is null then 1 else 0 end)+(case when bc is null then 1 else 0 end) as c_,  
(case when ae is null then 1 else 0 end)+(case when be is null then 1 else 0 end) as e_, 
(case when am is null then 1 else 0 end)+(case when bm is null then 1 else 0 end) as m_,
(case when ap is null then 1 else 0 end)+(case when bp is null then 1 else 0 end) as p_,
(case when az is null then 1 else 0 end)+(case when bz is null then 1 else 0 end) as z_
from(
select a.geoid, a.variable, 
a.c as ac, 
a.e as ae,
a.m as am,
a.p as ap,
a.z as az,
b.c as bc, 
b.e as be,
b.m as bm,
b.p as bp,
b.z as bz from 
pff_housing."Y2014-2018" a
JOIN pff_housing."Y2014-2018-erica" b
ON a.variable = b.variable AND a.geoid = b.geoid) c) d
where c_=1 or e_ = 1 or m_=1 or p_=1 or z_=1) e
WHERE bm is not null and am is null;

SELECT * FROM (
SELECT a.geoid,a.variable, 
round(a.c-b.c) as c_diff, 
round(a.e-b.e) as e_diff, 
round(a.m-b.m) as m_diff, 
round(a.p-b.p) as p_diff, 
round(a.z-b.z) as z_diff, 
'' as gap0,
a.c, b.c AS c_,
'' as gap1,
a.e, b.e AS e_,
'' as gap2,
a.m, b.m AS m_,
'' as gap3,
a.p, b.p AS p_,
'' as gap4,
a.z, b.z AS z_
FROM pf."Y2014-2018-erica" a
RIGHT JOIN pff_economic."Y2014-2018" b 
ON a.geoid = b.geoid AND a.variable = b.variable
WHERE a.c != b.c
OR a.c != b.c
OR a.e != b.e
OR a.m != b.m
OR a.p != b.p
OR a.z != b.z) dif
WHERE c_diff != 0
    or e_diff != 0
    or m_diff != 0
    or p_diff != 0
    or z_diff != 0;

[SPTKL]

ECON:

• [x] PU18BwPv, P65plBwPv,PBwPv , have different z but same everything else CvLFUEm2 have different p and z but same everything else
• [x] NTAs missing Median calculation for c, m, 15 records

update: CvLFUEm2: if we recalculated p and z, mismatch resolves

status:

• [x] mismatch PU18BwPv, P65plBwPv,PBwPv
  • the only thing remaining: top coding for medians +1
• [x] null NTAs missing Median calculation for c, m, 15 records

Good morning, I looked into the Median Moes, they all seem to have the same issue. Following the instruction, sometimes P_upper falls in the last bin interval, and we can't find A2 because there's no next higher category, that's why the MOE is left as null because the calculation cannot proceed

e.g. for MN21, mdefftwrk

here's the cumulative distribution

[0.20990764063811923, 0.20990764063811923, 0.20990764063811923, 0.20990764063811923, 0.6856982927511894, 0.8396305625524769, 1.035544360481388, 1.56731038343129, 1.9031626084522808, 2.127064091799608, 2.65883011474951, 3.456479149174363, 3.8902882731598094, 7.514693534844668, 9.599776098516653, 15.938986845787854, 25.98656591099916, 34.1589700531766, 52.54687937307585, 100.0]

p_lower = 46.55054141297496
p_upper = 53.44945858702504

p_upper falls in the last bin available, therefore cannot define A2 in this case

[SPTKL]

SOCIAL:

• [x] country variables missing z for geoid 0, 1, 2, 3, 4, 5

reason: for these country variables, base variable Pop_5 has -555555555 as m value from census API, which gets changed to null

status:

• [x] mismatch
• [x] null

[SPTKL]

HOUSING:

• [x] top coding + 1

same reason see below

status:

• [x] median mismatch 223 records, top coding +1
• [x] null : missing m=0 for --- > confirm with matt

  HOVacU
  OOcHU4
  PopOOcHU
  PopRtOcHU
  RntVacU
  ROcHU2
  VacRnt
  VacRntNO
  VacSale
  VacSoldNO 

  and QN99 missing c and m

[SPTKL]

DEMO:

• [x] for some variables, missing z value, e.g. Fpop5t9, only for geoid 01,2,3,4,5

base variables have null MOE pulled from census e.g. for

"Pop": [
        "B01001_001"
    ]

status:

• [x] mismatch
• [x] null need to fill null as 0

[EricaMaurer]

• [X] geoid 0,1,2,3,4,5 is missing the C value

Also seeing top coding + 1 issue on economic profile and medians missing m & c data

RE:

• medians are only calculated for NTA levels, so top coding +1 is pulled directly from census api. e.g. for variable

  "MdEMFTWrk": [
      "DP03_0093"
  ]

  

data pulled directly from census:

('DP03_0093E', '250001')
('DP03_0093M', '-333333333')
('DP03_0093PE', '-888888888')
('DP03_0093PM', '-888888888')
('DP03_0093EA', '250,000+')
('DP03_0093MA', '***')
('DP03_0093PEA', '(X)')
('DP03_0093PMA', '(X)')

[EricaMaurer]

• [X] Housnig: HU1, HU2, HU3, HU4 missing c for geoid 0 & 4 it seems that the code is turning the zeros to null for cv calculations if the estimate is not zero, then a cv should be calculated

RE: resolved

[EricaMaurer]

• [x] PBwPv -- percent: S1701_C03_001E -- percent MOE: S1701_C03_001M
• [x] PU18BwPv -- percent: S1701_C03_002E -- percent MOE: S1701_C03_002M
• [x] P65plBwPv -- percent: S1701_C03_010E -- percent MOE: S1701_C03_010M

[SPTKL]

everything seems to be resolved, only remaining issue is and QN99 missing c and m for mdrms still need some digging to resolve that one record, otherwise everything else is ready to go row counts are consistent across all 3 years for each table

[SPTKL]

LAST FEW ISSUES: