Variance Replicate Tables for the 2015 ACS


In [8]:
import metatab as mt
import pandasreporter as pr
import pandas as pd
import numpy as np
import geoid

doc = mt.open_package('../_packages/census.gov-varrep_tables_support-2011e2015-1/')
doc


Out[8]:

Variance Replicate Estimates Support Data 2011-2015

census.gov-varrep_tables_support-2011e2015-1

Variance replicate estimate tables include estimates, margins of error, and 80 variance replicates for selected American Community Survey 5-year Detailed Tables. The tables are intended for advanced users who are adding ACS data within a table or between geographies. This package contains the state average weights and k values for use in some calculations.

Documentation

Variance Replicate Tables Documentation Main documentation page, with links to support data

Variance Replicate Tables Links to all data tables.

Contacts

Wrangler: Eric Busboom Civic Knowledge

Resources

  1. ave_weights - data/ave_weights.csv Table APP1: Average Weight by State for 2011-2015 ACS 5-Year Data

  2. k_values - data/k_values.csv Table APP2: Assigning k-Value Based on an Area’s Total Population


In [128]:
B01003 = pr.get_varrep_dataframe(2015,  'B01003', '140' , state='06', cache=True)
B01003 = B01003[pd.notnull(B01003['GEOID'])]
B01003.head()


TBLID GEOID NAME ORDER TITLE estimate moe CME SE Var_Rep1 ... Var_Rep71 Var_Rep72 Var_Rep73 Var_Rep74 Var_Rep75 Var_Rep76 Var_Rep77 Var_Rep78 Var_Rep79 Var_Rep80
2 B01003 14000US06001400100 Census Tract 4001, Alameda County, California 1.0 Total 2952.0 186.0 +/-186 113.0 3006.0 ... 3054.0 2824.0 2974.0 2987.0 2916.0 2888.0 2880.0 2977.0 2925.0 2872.0
3 B01003 14000US06001400200 Census Tract 4002, Alameda County, California 1.0 Total 1984.0 99.0 +/-99 60.0 1949.0 ... 1998.0 1957.0 1993.0 1995.0 1993.0 2001.0 2029.0 1989.0 1978.0 1971.0
4 B01003 14000US06001400300 Census Tract 4003, Alameda County, California 1.0 Total 5377.0 524.0 +/-524 319.0 5442.0 ... 5429.0 5411.0 5449.0 5632.0 5282.0 5281.0 5686.0 5495.0 5187.0 5170.0
5 B01003 14000US06001400400 Census Tract 4004, Alameda County, California 1.0 Total 4105.0 305.0 +/-305 185.0 4223.0 ... 4181.0 4119.0 4172.0 3983.0 4017.0 4120.0 4156.0 4027.0 4183.0 4169.0
6 B01003 14000US06001400500 Census Tract 4005, Alameda County, California 1.0 Total 3651.0 299.0 +/-299 182.0 3650.0 ... 3650.0 3634.0 3634.0 3657.0 3672.0 3707.0 3594.0 3765.0 3609.0 3657.0

5 rows × 89 columns


In [129]:
# Break out the geoid by county
def parse_geoid(tv):
    from geoid.core import parse_to_gvid
    tract = parse_to_gvid(tv)
    
    return pd.Series({'state_fips':tract.state, 
                      'county_fips':tract.county, 
                      'tract_fips':tract.tract})

B01003 = B01003.merge(B01003.GEOID.apply(parse_geoid), left_index=True, right_index=True) 
B01003.head()


TBLID GEOID NAME ORDER TITLE estimate moe CME SE Var_Rep1 ... Var_Rep74 Var_Rep75 Var_Rep76 Var_Rep77 Var_Rep78 Var_Rep79 Var_Rep80 county_fips state_fips tract_fips
2 B01003 14000US06001400100 Census Tract 4001, Alameda County, California 1.0 Total 2952.0 186.0 +/-186 113.0 3006.0 ... 2987.0 2916.0 2888.0 2880.0 2977.0 2925.0 2872.0 1 6 400100
3 B01003 14000US06001400200 Census Tract 4002, Alameda County, California 1.0 Total 1984.0 99.0 +/-99 60.0 1949.0 ... 1995.0 1993.0 2001.0 2029.0 1989.0 1978.0 1971.0 1 6 400200
4 B01003 14000US06001400300 Census Tract 4003, Alameda County, California 1.0 Total 5377.0 524.0 +/-524 319.0 5442.0 ... 5632.0 5282.0 5281.0 5686.0 5495.0 5187.0 5170.0 1 6 400300
5 B01003 14000US06001400400 Census Tract 4004, Alameda County, California 1.0 Total 4105.0 305.0 +/-305 185.0 4223.0 ... 3983.0 4017.0 4120.0 4156.0 4027.0 4183.0 4169.0 1 6 400400
6 B01003 14000US06001400500 Census Tract 4005, Alameda County, California 1.0 Total 3651.0 299.0 +/-299 182.0 3650.0 ... 3657.0 3672.0 3707.0 3594.0 3765.0 3609.0 3657.0 1 6 400500

5 rows × 92 columns


In [122]:
def varrep_se(x):
    """Compute the standard error from a set of variance replicates"""
    return int(round(np.sqrt((4/80) * sum((x.loc['estimate']-x.loc[varcols])**2)),0))

df2['est_calc'] = np.round(np.mean(df2[varcols],axis=1)).astype(int)
df2['se_calc'] = df2.apply(f, axis=1)

In [132]:
B01003.groupby('county_fips').sum().sort_values('estimate', ascending = False)


ORDER estimate moe SE Var_Rep1 Var_Rep2 Var_Rep3 Var_Rep4 Var_Rep5 Var_Rep6 ... Var_Rep73 Var_Rep74 Var_Rep75 Var_Rep76 Var_Rep77 Var_Rep78 Var_Rep79 Var_Rep80 state_fips tract_fips
county_fips
37 2346.0 10038388.0 886694.0 538994.0 10038388.0 10038388.0 10038388.0 10038388.0 10038388.0 10038388.0 ... 10038388.0 10038388.0 10038388.0 10038388.0 10038388.0 10038388.0 10038388.0 10038388.0 14076 957321337
73 628.0 3223096.0 279982.0 170218.0 3223096.0 3223096.0 3223096.0 3223096.0 3223096.0 3223096.0 ... 3223096.0 3223096.0 3223096.0 3223096.0 3223096.0 3223096.0 3223096.0 3223096.0 3768 8863592
59 583.0 3116069.0 239330.0 145499.0 3116069.0 3116069.0 3116069.0 3116069.0 3116069.0 3116069.0 ... 3116069.0 3116069.0 3116069.0 3116069.0 3116069.0 3116069.0 3116069.0 3116069.0 3498 39380550
65 453.0 2298032.0 213637.0 129869.0 2298032.0 2298032.0 2298032.0 2298032.0 2298032.0 2298032.0 ... 2298032.0 2298032.0 2298032.0 2298032.0 2298032.0 2298032.0 2298032.0 2298032.0 2718 32910046
71 369.0 2094769.0 197725.0 120199.0 2094769.0 2094769.0 2094769.0 2094769.0 2094769.0 2094769.0 ... 2094769.0 2094769.0 2094769.0 2094769.0 2094769.0 2094769.0 2094769.0 2094769.0 2214 4937688
85 372.0 1868149.0 140660.0 85517.0 1868149.0 1868149.0 1868149.0 1868149.0 1868149.0 1868149.0 ... 1868149.0 1868149.0 1868149.0 1868149.0 1868149.0 1868149.0 1868149.0 1868149.0 2232 188482024
1 361.0 1584983.0 126026.0 76612.0 1584983.0 1584983.0 1584983.0 1584983.0 1584983.0 1584983.0 ... 1584983.0 1584983.0 1584983.0 1584983.0 1584983.0 1584983.0 1584983.0 1584983.0 2166 156424046
67 317.0 1465832.0 126726.0 77035.0 1465832.0 1465832.0 1465832.0 1465832.0 1465832.0 1465832.0 ... 1465832.0 1465832.0 1465832.0 1465832.0 1465832.0 1465832.0 1465832.0 1465832.0 1902 3163344
13 208.0 1096068.0 82825.0 50345.0 1096068.0 1096068.0 1096068.0 1096068.0 1096068.0 1096068.0 ... 1096068.0 1096068.0 1096068.0 1096068.0 1096068.0 1096068.0 1096068.0 1096068.0 1248 71711231
19 199.0 956749.0 85204.0 51793.0 956749.0 956749.0 956749.0 956749.0 956749.0 956749.0 ... 956749.0 956749.0 956749.0 956749.0 956749.0 956749.0 956749.0 956749.0 1194 899960
29 151.0 865736.0 78684.0 47835.0 865736.0 865736.0 865736.0 865736.0 865736.0 865736.0 ... 865736.0 865736.0 865736.0 865736.0 865736.0 865736.0 865736.0 865736.0 906 510803
111 174.0 840833.0 66564.0 40468.0 840833.0 840833.0 840833.0 840833.0 840833.0 840833.0 ... 840833.0 840833.0 840833.0 840833.0 840833.0 840833.0 840833.0 840833.0 1044 2809419
75 197.0 840763.0 76570.0 46537.0 840763.0 840763.0 840763.0 840763.0 840763.0 840763.0 ... 840763.0 840763.0 840763.0 840763.0 840763.0 840763.0 840763.0 840763.0 1182 11636055
81 158.0 748731.0 56462.0 34326.0 748731.0 748731.0 748731.0 748731.0 748731.0 748731.0 ... 748731.0 748731.0 748731.0 748731.0 748731.0 748731.0 748731.0 748731.0 948 96658307
77 139.0 708554.0 61329.0 37283.0 708554.0 708554.0 708554.0 708554.0 708554.0 708554.0 ... 708554.0 708554.0 708554.0 708554.0 708554.0 708554.0 708554.0 708554.0 834 506861
99 94.0 527367.0 42957.0 26116.0 527367.0 527367.0 527367.0 527367.0 527367.0 527367.0 ... 527367.0 527367.0 527367.0 527367.0 527367.0 527367.0 527367.0 527367.0 564 183195
97 100.0 495078.0 40711.0 24745.0 495078.0 495078.0 495078.0 495078.0 495078.0 495078.0 ... 495078.0 495078.0 495078.0 495078.0 495078.0 495078.0 495078.0 495078.0 600 16059509
107 78.0 454033.0 38403.0 23342.0 454033.0 454033.0 454033.0 454033.0 454033.0 454033.0 ... 454033.0 454033.0 454033.0 454033.0 454033.0 454033.0 454033.0 454033.0 468 169739
83 90.0 435850.0 37373.0 22719.0 435850.0 435850.0 435850.0 435850.0 435850.0 435850.0 ... 435850.0 435850.0 435850.0 435850.0 435850.0 435850.0 435850.0 435850.0 540 3121947
53 94.0 428441.0 47984.0 29175.0 428441.0 428441.0 428441.0 428441.0 428441.0 428441.0 ... 428441.0 428441.0 428441.0 428441.0 428441.0 428441.0 428441.0 428441.0 564 2837055
95 96.0 425753.0 36581.0 22234.0 425753.0 425753.0 425753.0 425753.0 425753.0 425753.0 ... 425753.0 425753.0 425753.0 425753.0 425753.0 425753.0 425753.0 425753.0 576 24934949
61 85.0 366280.0 30233.0 18384.0 366280.0 366280.0 366280.0 366280.0 366280.0 366280.0 ... 366280.0 366280.0 366280.0 366280.0 366280.0 366280.0 366280.0 366280.0 510 2791853
79 54.0 276517.0 23979.0 14577.0 276517.0 276517.0 276517.0 276517.0 276517.0 276517.0 ... 276517.0 276517.0 276517.0 276517.0 276517.0 276517.0 276517.0 276517.0 324 1592518
87 53.0 269278.0 23563.0 14319.0 269278.0 269278.0 269278.0 269278.0 269278.0 269278.0 ... 269278.0 269278.0 269278.0 269278.0 269278.0 269278.0 269278.0 269278.0 318 6969624
47 49.0 263885.0 22201.0 13495.0 263885.0 263885.0 263885.0 263885.0 263885.0 263885.0 ... 263885.0 263885.0 263885.0 263885.0 263885.0 263885.0 263885.0 263885.0 294 60187
41 56.0 258349.0 19492.0 11849.0 258349.0 258349.0 258349.0 258349.0 258349.0 258349.0 ... 258349.0 258349.0 258349.0 258349.0 258349.0 258349.0 258349.0 258349.0 336 7346923
7 51.0 222564.0 21513.0 13081.0 222564.0 222564.0 222564.0 222564.0 222564.0 222564.0 ... 222564.0 222564.0 222564.0 222564.0 222564.0 222564.0 222564.0 222564.0 306 87152
113 41.0 207320.0 16463.0 10008.0 207320.0 207320.0 207320.0 207320.0 207320.0 207320.0 ... 207320.0 207320.0 207320.0 207320.0 207320.0 207320.0 207320.0 207320.0 246 438673
17 43.0 182093.0 18034.0 10966.0 182093.0 182093.0 182093.0 182093.0 182093.0 182093.0 ... 182093.0 182093.0 182093.0 182093.0 182093.0 182093.0 182093.0 182093.0 258 2290927
89 48.0 178942.0 17505.0 10643.0 178942.0 178942.0 178942.0 178942.0 178942.0 178942.0 ... 178942.0 178942.0 178942.0 178942.0 178942.0 178942.0 178942.0 178942.0 288 549090
25 31.0 178206.0 16402.0 9970.0 178206.0 178206.0 178206.0 178206.0 178206.0 178206.0 ... 178206.0 178206.0 178206.0 178206.0 178206.0 178206.0 178206.0 178206.0 186 1279218
39 23.0 153187.0 14015.0 8519.0 153187.0 153187.0 153187.0 153187.0 153187.0 153187.0 ... 153187.0 153187.0 153187.0 153187.0 153187.0 153187.0 153187.0 153187.0 138 9979
31 27.0 150998.0 12307.0 7482.0 150998.0 150998.0 150998.0 150998.0 150998.0 150998.0 ... 150998.0 150998.0 150998.0 150998.0 150998.0 150998.0 150998.0 150998.0 162 1004633
55 40.0 140295.0 12178.0 7404.0 140295.0 140295.0 140295.0 140295.0 140295.0 140295.0 ... 140295.0 140295.0 140295.0 140295.0 140295.0 140295.0 140295.0 140295.0 240 8037696
23 31.0 135034.0 12819.0 7795.0 135034.0 135034.0 135034.0 135034.0 135034.0 135034.0 ... 135034.0 135034.0 135034.0 135034.0 135034.0 135034.0 135034.0 135034.0 186 2111509
57 20.0 98570.0 8696.0 5288.0 98570.0 98570.0 98570.0 98570.0 98570.0 98570.0 ... 98570.0 98570.0 98570.0 98570.0 98570.0 98570.0 98570.0 98570.0 120 12044
101 21.0 95247.0 7627.0 4636.0 95247.0 95247.0 95247.0 95247.0 95247.0 95247.0 ... 95247.0 95247.0 95247.0 95247.0 95247.0 95247.0 95247.0 95247.0 126 1060934
45 21.0 87544.0 9022.0 5480.0 87544.0 87544.0 87544.0 87544.0 87544.0 87544.0 ... 87544.0 87544.0 87544.0 87544.0 87544.0 87544.0 87544.0 87544.0 126 1209008
115 14.0 73437.0 5852.0 3558.0 73437.0 73437.0 73437.0 73437.0 73437.0 73437.0 ... 73437.0 73437.0 73437.0 73437.0 73437.0 73437.0 73437.0 73437.0 84 568109
33 15.0 64158.0 7036.0 4277.0 64158.0 64158.0 64158.0 64158.0 64158.0 64158.0 ... 64158.0 64158.0 64158.0 64158.0 64158.0 64158.0 64158.0 64158.0 90 10909
103 11.0 63152.0 5524.0 3358.0 63152.0 63152.0 63152.0 63152.0 63152.0 63152.0 ... 63152.0 63152.0 63152.0 63152.0 63152.0 63152.0 63152.0 63152.0 66 6600
69 11.0 57557.0 4862.0 2955.0 57557.0 57557.0 57557.0 57557.0 57557.0 57557.0 ... 57557.0 57557.0 57557.0 57557.0 57557.0 57557.0 57557.0 57557.0 66 5609
109 11.0 54079.0 4929.0 2996.0 54079.0 54079.0 54079.0 54079.0 54079.0 54079.0 ... 54079.0 54079.0 54079.0 54079.0 54079.0 54079.0 54079.0 54079.0 66 1016703
9 10.0 44767.0 4689.0 2850.0 44767.0 44767.0 44767.0 44767.0 44767.0 44767.0 ... 44767.0 44767.0 44767.0 44767.0 44767.0 44767.0 44767.0 44767.0 60 3001
93 14.0 43895.0 3415.0 2077.0 43895.0 43895.0 43895.0 43895.0 43895.0 43895.0 ... 43895.0 43895.0 43895.0 43895.0 43895.0 43895.0 43895.0 43895.0 84 9206
5 9.0 36995.0 3600.0 2189.0 36995.0 36995.0 36995.0 36995.0 36995.0 36995.0 ... 36995.0 36995.0 36995.0 36995.0 36995.0 36995.0 36995.0 36995.0 54 2614
35 9.0 32645.0 3393.0 2064.0 32645.0 32645.0 32645.0 32645.0 32645.0 32645.0 ... 32645.0 32645.0 32645.0 32645.0 32645.0 32645.0 32645.0 32645.0 54 363014
21 6.0 28029.0 1829.0 1112.0 28029.0 28029.0 28029.0 28029.0 28029.0 28029.0 ... 28029.0 28029.0 28029.0 28029.0 28029.0 28029.0 28029.0 28029.0 36 62003
15 8.0 27788.0 3058.0 1860.0 27788.0 27788.0 27788.0 27788.0 27788.0 27788.0 ... 27788.0 27788.0 27788.0 27788.0 27788.0 27788.0 27788.0 27788.0 48 991018
11 5.0 21396.0 1339.0 814.0 21396.0 21396.0 21396.0 21396.0 21396.0 21396.0 ... 21396.0 21396.0 21396.0 21396.0 21396.0 21396.0 21396.0 21396.0 30 1500
63 7.0 18966.0 2181.0 1325.0 18966.0 18966.0 18966.0 18966.0 18966.0 18966.0 ... 18966.0 18966.0 18966.0 18966.0 18966.0 18966.0 18966.0 18966.0 42 2206
27 6.0 18373.0 1312.0 799.0 18373.0 18373.0 18373.0 18373.0 18373.0 18373.0 ... 18373.0 18373.0 18373.0 18373.0 18373.0 18373.0 18373.0 18373.0 36 2300
43 6.0 17789.0 2239.0 1360.0 17789.0 17789.0 17789.0 17789.0 17789.0 17789.0 ... 17789.0 17789.0 17789.0 17789.0 17789.0 17789.0 17789.0 17789.0 36 1406
51 3.0 14146.0 801.0 486.0 14146.0 14146.0 14146.0 14146.0 14146.0 14146.0 ... 14146.0 14146.0 14146.0 14146.0 14146.0 14146.0 14146.0 14146.0 18 403
105 5.0 13373.0 1671.0 1016.0 13373.0 13373.0 13373.0 13373.0 13373.0 13373.0 ... 13373.0 13373.0 13373.0 13373.0 13373.0 13373.0 13373.0 13373.0 30 1103
49 4.0 9184.0 1059.0 644.0 9184.0 9184.0 9184.0 9184.0 9184.0 9184.0 ... 9184.0 9184.0 9184.0 9184.0 9184.0 9184.0 9184.0 9184.0 24 1000
91 1.0 3021.0 167.0 102.0 2952.0 2971.0 3005.0 2959.0 3019.0 3091.0 ... 3022.0 3088.0 3035.0 2946.0 3113.0 3043.0 3006.0 3065.0 6 10000
3 1.0 1131.0 167.0 102.0 1200.0 1181.0 1147.0 1193.0 1133.0 1061.0 ... 1130.0 1064.0 1117.0 1206.0 1039.0 1109.0 1146.0 1087.0 6 10000

58 rows × 86 columns


In [134]:
import pandasreporter as pr

api = pr.CensusApi()
ds = api.get_dataset('ACSSF5Y2015')
df = ds.fetch_dataframe( 'GEOID', 'NAME', 'B01003_001E', 'B01003_001M', geo_in='state:06', geo_for='county:*')
df.head()


GEOID NAME B01003_001E B01003_001M state county
0 05000US06001 Alameda County, California 1584983 0 06 001
1 05000US06003 Alpine County, California 1131 167 06 003
2 05000US06005 Amador County, California 36995 0 06 005
3 05000US06007 Butte County, California 222564 0 06 007
4 05000US06009 Calaveras County, California 44767 0 06 009

In [113]:
df2 = df.drop(['TBLID','NAME','CME', 'GEOID','ORDER','TITLE'],1)
varcols = [ 'Var_Rep'+str(i) for i in range(1,81)]

In [114]:
df2.head()


estimate moe SE Var_Rep1 Var_Rep2 Var_Rep3 Var_Rep4 Var_Rep5 Var_Rep6 Var_Rep7 ... Var_Rep71 Var_Rep72 Var_Rep73 Var_Rep74 Var_Rep75 Var_Rep76 Var_Rep77 Var_Rep78 Var_Rep79 Var_Rep80
0 5209 403 245 5311 4897 5089 5285 5313 5179 5141 ... 5218 5099 5292 5185 5156 5176 5295 5214 5171 5117
1 2388 319 194 2423 2311 2510 2343 2476 2524 2382 ... 2439 2347 2409 2282 2373 2399 2378 2355 2451 2269
2 207 101 61 155 174 182 237 234 206 146 ... 256 220 158 217 242 175 222 191 229 205
3 59 56 34 45 72 49 59 38 36 43 ... 64 70 40 67 80 58 67 68 39 71
4 86 65 40 83 71 99 84 90 74 75 ... 68 64 120 64 106 78 82 100 59 93

5 rows × 83 columns

Example calculations

The first way to test SE calculations using variance replicates is to reproduce the estimate and SE for each row.


In [115]:
c = df2[['estimate','est_calc','SE','se_calc']].copy()

In [117]:
[['GEOID', 'state_fips', 'county_fips', 'tract_fips']]


GEOID state_fips county_fips tract_fips
0 14000US11001000100 11 1 100
1 14000US11001000100 11 1 100
2 14000US11001000100 11 1 100
3 14000US11001000100 11 1 100
4 14000US11001000100 11 1 100
5 14000US11001000100 11 1 100
6 14000US11001000100 11 1 100
7 14000US11001000100 11 1 100
8 14000US11001000100 11 1 100
9 14000US11001000100 11 1 100
10 14000US11001000100 11 1 100
11 14000US11001000100 11 1 100
12 14000US11001000100 11 1 100
13 14000US11001000100 11 1 100
14 14000US11001000100 11 1 100
15 14000US11001000100 11 1 100
16 14000US11001000100 11 1 100
17 14000US11001000100 11 1 100
18 14000US11001000100 11 1 100
19 14000US11001000100 11 1 100
20 14000US11001000100 11 1 100
21 14000US11001000100 11 1 100
22 14000US11001000100 11 1 100
23 14000US11001000100 11 1 100
24 14000US11001000100 11 1 100
25 14000US11001000100 11 1 100
26 14000US11001000100 11 1 100
27 14000US11001000100 11 1 100
28 14000US11001000100 11 1 100
29 14000US11001000100 11 1 100
... ... ... ... ...
8741 14000US11001011100 11 1 11100
8742 14000US11001011100 11 1 11100
8743 14000US11001011100 11 1 11100
8744 14000US11001011100 11 1 11100
8745 14000US11001011100 11 1 11100
8746 14000US11001011100 11 1 11100
8747 14000US11001011100 11 1 11100
8748 14000US11001011100 11 1 11100
8749 14000US11001011100 11 1 11100
8750 14000US11001011100 11 1 11100
8751 14000US11001011100 11 1 11100
8752 14000US11001011100 11 1 11100
8753 14000US11001011100 11 1 11100
8754 14000US11001011100 11 1 11100
8755 14000US11001011100 11 1 11100
8756 14000US11001011100 11 1 11100
8757 14000US11001011100 11 1 11100
8758 14000US11001011100 11 1 11100
8759 14000US11001011100 11 1 11100
8760 14000US11001011100 11 1 11100
8761 14000US11001011100 11 1 11100
8762 14000US11001011100 11 1 11100
8763 14000US11001011100 11 1 11100
8764 14000US11001011100 11 1 11100
8765 14000US11001011100 11 1 11100
8766 14000US11001011100 11 1 11100
8767 14000US11001011100 11 1 11100
8768 14000US11001011100 11 1 11100
8769 14000US11001011100 11 1 11100
8770 14000US11001011100 11 1 11100

8771 rows × 4 columns


In [88]:
c['est_diff'] = (((c['est_calc'] - c['estimate']) / c['estimate']) * 100).fillna(0) 
c['est_eq'] = np.abs(c['est_diff']) < 2
c[c.estimate != 0].head()


estimate est_calc SE se_calc est_diff est_eq
0 5209 5200 245 245 -0.172778 True
1 2388 2383 194 194 -0.209380 True
2 207 206 61 61 -0.483092 True
3 59 58 34 34 -1.694915 True
4 86 87 40 40 1.162791 True

In [89]:
c['se_eq'] = c.SE == c.se_calc
c[c.estimate == 0].head()


estimate est_calc SE se_calc est_diff est_eq se_eq
6 0 0 10 0 0.0 True False
31 0 0 10 0 0.0 True False
51 0 0 7 0 0.0 True False
52 0 0 7 0 0.0 True False
53 0 0 7 0 0.0 True False

In [82]:
df[df.estimate == 0].head()


TBLID GEOID NAME ORDER TITLE estimate moe CME SE Var_Rep1 ... Var_Rep71 Var_Rep72 Var_Rep73 Var_Rep74 Var_Rep75 Var_Rep76 Var_Rep77 Var_Rep78 Var_Rep79 Var_Rep80
6 B01001 14000US11001000100 Census Tract 1, District of Columbia, District... 7 18 and 19 years 0 17 +/-17 10 0 ... 0 0 0 0 0 0 0 0 0 0
31 B01001 14000US11001000100 Census Tract 1, District of Columbia, District... 32 20 years 0 17 +/-17 10 0 ... 0 0 0 0 0 0 0 0 0 0
51 B01001 14000US11001000201 Census Tract 2.01, District of Columbia, Distr... 3 Under 5 years 0 12 +/-12 7 0 ... 0 0 0 0 0 0 0 0 0 0
52 B01001 14000US11001000201 Census Tract 2.01, District of Columbia, Distr... 4 5 to 9 years 0 12 +/-12 7 0 ... 0 0 0 0 0 0 0 0 0 0
53 B01001 14000US11001000201 Census Tract 2.01, District of Columbia, Distr... 5 10 to 14 years 0 12 +/-12 7 0 ... 0 0 0 0 0 0 0 0 0 0

5 rows × 89 columns


In [ ]: