In [ ]:
#2018-09-15 12:39:58.775939
%load_ext metapack.jupyter.magic
In [ ]:
CACHE_DIR='/Users/eric/Library/Application Support/metapack/'
RESOURCE_NAME='census_race'
RESOLVED_URL='ipynb://notebooks/RaceEthnicity.ipynb#out_df'
WORKING_DIR='/Users/eric/proj/virt-proj/data-project/metatab-packages/census.gov/census.gov-race-20155-san_diego-tract'
METATAB_DOC='metapack+file:///Users/eric/proj/virt-proj/data-project/metatab-packages/census.gov/census.gov-race-20155-san_diego-tract/metadata.csv'
METATAB_WORKING_DIR='/Users/eric/proj/virt-proj/data-project/metatab-packages/census.gov/census.gov-race-20155-san_diego-tract'
METATAB_PACKAGE='metapack+file:///Users/eric/proj/virt-proj/data-project/metatab-packages/census.gov/census.gov-race-20155-san_diego-tract/'
name='census_race'
description='Counts of population by race for tracts in San Diego county'
url='ipynb:notebooks/RaceEthnicity.ipynb#out_df'
In [ ]:
METAPACK_BUILDING=True
In [1]:
import pandas as pd
import numpy as np
import pandasreporter as pr
import metatab as mt
census_race_codes = {
'A': 'white',
'B': 'black',
'C': 'aian',
'D': 'asian',
'E': 'nhopi',
'F': 'other',
'G': 'multiple',
'H': 'nonhisp',
'I': 'hisp'
}
table_t = 'B01001{}'
In [2]:
B01001A = pr.get_cr_dataframe('B01001A', '140', '05000US06073', cache=True)
[ c for c in B01001A.ct_columns.columns if 'Margins' not in c and any( str(a) in c for a in [55,65,75,85])]
Out[2]:
['B01001A013 Total Male 55 to 64 years',
'B01001A014 Total Male 65 to 74 years',
'B01001A015 Total Male 75 to 84 years',
'B01001A016 Total Male 85 years and over',
'B01001A028 Total Female 55 to 64 years',
'B01001A029 Total Female 65 to 74 years',
'B01001A030 Total Female 75 to 84 years',
'B01001A031 Total Female 85 years and over']
In [3]:
B01001A.release
Out[3]:
{'id': 'acs2015_5yr', 'name': 'ACS 2015 5-year', 'years': '2011-2015'}
In [4]:
col_names = {
'55_64': ['013', '028'],
'65_74': ['014', '029'],
'75_84': ['015', '030'],
'85': ['016', '031']
}
def mk_index():
for cn in col_names.keys():
for x in census_race_codes.values():
for v in ('est', 'est_m90'):
yield(cn, x, v)
In [5]:
mi = pd.MultiIndex.from_tuples(list(mk_index()))
re_df = pd.DataFrame(index=B01001A.set_index('geoid').index, columns=mi)
for re_code, re_name in census_race_codes.items():
tbl = table_t.format(re_code)
df = pr.get_cr_dataframe(tbl, '140', '05000US06073', cache=True).set_index('geoid', drop=False)
for age_group, input_cols in col_names.items():
try:
re_df[(age_group,re_name, 'est')], re_df[(age_group,re_name, 'est_m90')] = df.sum_m(*[ tbl+c for c in input_cols])
except Exception as e:
print(e)
re_df
Out[5]:
55_64
...
85
white
black
aian
asian
nhopi
...
nhopi
other
multiple
nonhisp
hisp
est
est_m90
est
est_m90
est
est_m90
est
est_m90
est
est_m90
...
est
est_m90
est
est_m90
est
est_m90
est
est_m90
est
est_m90
geoid
14000US06073000100
525.0
101.178061
0.0
16.970563
0.0
16.970563
5.0
15.000000
10.0
19.209373
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
177.0
78.294317
0.0
16.970563
14000US06073000201
352.0
101.019800
0.0
16.970563
0.0
16.970563
0.0
16.970563
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
68.0
52.172790
0.0
16.970563
14000US06073000202
383.0
121.165177
0.0
16.970563
0.0
16.970563
50.0
75.953933
0.0
16.970563
...
0.0
16.970563
11.0
27.730849
0.0
16.970563
45.0
43.908997
11.0
27.730849
14000US06073000300
523.0
158.927656
50.0
81.884064
0.0
16.970563
23.0
38.897301
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
180.0
115.870618
0.0
16.970563
14000US06073000400
300.0
125.219807
0.0
16.970563
0.0
16.970563
0.0
16.970563
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
21.0
22.825424
0.0
16.970563
14000US06073000500
292.0
90.553851
13.0
24.186773
0.0
16.970563
5.0
16.278821
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
5.0
14.422205
46.0
32.202484
0.0
16.970563
14000US06073000600
241.0
76.419893
19.0
31.384710
0.0
16.970563
15.0
20.000000
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
39.0
27.802878
8.0
16.970563
14000US06073000700
461.0
176.400113
11.0
22.472205
0.0
16.970563
0.0
16.970563
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
35.0
49.477268
50.0
60.207973
14000US06073000800
323.0
123.239604
56.0
71.470274
0.0
16.970563
31.0
36.069378
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
51.0
37.536649
0.0
16.970563
14000US06073000900
486.0
154.207652
0.0
24.041631
15.0
29.410882
19.0
28.284271
16.0
29.410882
...
0.0
24.041631
0.0
24.041631
0.0
24.041631
66.0
51.894123
0.0
24.041631
14000US06073001000
544.0
322.046580
0.0
24.041631
0.0
24.041631
21.0
29.410882
0.0
24.041631
...
0.0
24.041631
0.0
24.041631
0.0
24.041631
0.0
24.041631
38.0
54.708317
14000US06073001100
306.0
106.775465
57.0
48.020829
0.0
16.970563
0.0
16.970563
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
29.0
30.463092
0.0
16.970563
14000US06073001200
392.0
118.444080
0.0
24.041631
0.0
24.041631
0.0
24.041631
0.0
24.041631
...
0.0
24.041631
0.0
24.041631
0.0
24.041631
10.0
25.495098
21.0
28.460499
14000US06073001300
317.0
150.416090
135.0
84.154620
9.0
23.345235
117.0
82.152298
0.0
24.041631
...
0.0
24.041631
0.0
24.041631
0.0
24.041631
44.0
38.275318
0.0
24.041631
14000US06073001400
300.0
78.006410
6.0
15.000000
0.0
16.970563
48.0
37.202150
31.0
39.849718
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
27.0
25.059928
0.0
16.970563
14000US06073001500
280.0
81.344945
74.0
44.418465
0.0
16.970563
29.0
23.706539
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
33.0
22.803509
9.0
17.691806
14000US06073001600
294.0
140.064271
141.0
98.478424
0.0
24.041631
28.0
46.238512
0.0
24.041631
...
0.0
24.041631
0.0
24.041631
0.0
24.041631
15.0
28.600699
13.0
27.018512
14000US06073001700
331.0
101.118742
76.0
55.072679
0.0
16.970563
0.0
16.970563
0.0
16.970563
...
0.0
16.970563
14.0
25.059928
0.0
16.970563
0.0
16.970563
14.0
25.059928
14000US06073001800
308.0
143.387587
18.0
33.615473
17.0
32.756679
9.0
22.022716
0.0
24.041631
...
0.0
24.041631
0.0
24.041631
0.0
24.041631
53.0
44.407207
0.0
24.041631
14000US06073001900
550.0
116.777566
10.0
20.000000
14.0
24.186773
12.0
22.472205
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
59.0
58.249464
9.0
20.000000
14000US06073002001
603.0
115.836091
0.0
16.970563
0.0
16.970563
14.0
19.209373
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
136.0
49.497475
8.0
17.691806
14000US06073002002
348.0
81.688432
8.0
16.278821
0.0
16.970563
8.0
18.439089
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
23.0
27.730849
0.0
16.970563
14000US06073002100
275.0
101.965681
128.0
94.260278
0.0
16.970563
59.0
52.392748
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
14.0
26.832816
0.0
16.970563
14000US06073002201
171.0
74.027022
138.0
63.600314
0.0
16.970563
91.0
61.983869
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
0.0
16.970563
0.0
16.970563
14000US06073002202
185.0
85.211502
37.0
41.436699
0.0
24.041631
82.0
41.773197
0.0
24.041631
...
0.0
24.041631
0.0
24.041631
0.0
24.041631
0.0
24.041631
18.0
31.906112
14000US06073002301
160.0
76.059187
105.0
65.115282
0.0
16.970563
98.0
60.415230
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
0.0
16.970563
0.0
16.970563
14000US06073002302
400.0
177.259696
31.0
37.013511
0.0
24.041631
278.0
107.447662
0.0
24.041631
...
0.0
24.041631
0.0
24.041631
0.0
24.041631
0.0
24.041631
0.0
24.041631
14000US06073002401
201.0
101.178061
114.0
77.252832
14.0
25.942244
57.0
61.188234
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
0.0
16.970563
0.0
16.970563
14000US06073002402
170.0
83.600239
48.0
39.357337
0.0
24.041631
129.0
60.959003
0.0
24.041631
...
0.0
24.041631
0.0
24.041631
0.0
24.041631
0.0
24.041631
0.0
24.041631
14000US06073002501
316.0
99.488693
54.0
41.182521
0.0
24.041631
102.0
44.553339
0.0
24.041631
...
0.0
24.041631
0.0
24.041631
0.0
24.041631
9.0
27.018512
13.0
27.018512
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
14000US06073020708
479.0
121.827747
46.0
38.897301
9.0
19.209373
9.0
18.439089
10.0
20.808652
...
0.0
16.970563
0.0
16.970563
19.0
25.942244
59.0
42.190046
0.0
16.970563
14000US06073020709
1032.0
180.424499
88.0
85.615419
15.0
28.600699
113.0
90.138782
0.0
24.041631
...
0.0
24.041631
0.0
24.041631
0.0
24.041631
94.0
93.477270
18.0
32.756679
14000US06073020710
302.0
58.258047
17.0
15.000000
0.0
16.970563
16.0
20.000000
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
47.0
31.112698
0.0
16.970563
14000US06073020801
1199.0
164.757397
0.0
24.041631
0.0
24.041631
10.0
25.495098
0.0
24.041631
...
0.0
24.041631
0.0
24.041631
0.0
24.041631
115.0
55.865911
12.0
27.802878
14000US06073020805
545.0
144.170038
0.0
16.970563
0.0
16.970563
8.0
17.691806
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
52.0
41.880783
0.0
16.970563
14000US06073020806
563.0
140.035710
0.0
24.041631
27.0
44.384682
0.0
24.041631
0.0
24.041631
...
0.0
24.041631
0.0
24.041631
0.0
24.041631
53.0
37.947332
0.0
24.041631
14000US06073020807
463.0
90.553851
10.0
10.816654
0.0
16.970563
0.0
16.970563
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
39.0
28.600699
0.0
16.970563
14000US06073020809
1339.0
307.800260
0.0
24.041631
10.0
24.758837
5.0
19.723083
0.0
24.041631
...
0.0
24.041631
0.0
24.041631
0.0
24.041631
108.0
72.034714
11.0
24.758837
14000US06073020810
988.0
156.310588
0.0
24.041631
10.0
23.345235
0.0
24.041631
0.0
24.041631
...
0.0
24.041631
0.0
24.041631
0.0
24.041631
28.0
33.615473
0.0
24.041631
14000US06073020811
716.0
132.548104
43.0
40.000000
0.0
24.041631
0.0
24.041631
0.0
24.041631
...
0.0
24.041631
0.0
24.041631
0.0
24.041631
72.0
39.812058
0.0
24.041631
14000US06073020902
496.0
105.118980
0.0
16.970563
0.0
16.970563
0.0
16.970563
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
2.0
13.000000
6.0
14.422205
14000US06073020903
459.0
91.967386
0.0
16.970563
40.0
14.866069
18.0
14.317821
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
68.0
37.802116
9.0
15.620499
14000US06073020904
347.0
120.880106
0.0
16.970563
0.0
16.970563
18.0
22.472205
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
44.0
26.627054
0.0
16.970563
14000US06073021000
476.0
188.302947
0.0
16.970563
0.0
16.970563
32.0
53.366656
0.0
16.970563
...
0.0
16.970563
18.0
31.384710
0.0
16.970563
145.0
114.629839
18.0
31.384710
14000US06073021100
1146.0
351.980113
60.0
65.122961
13.0
10.295630
0.0
24.041631
0.0
24.041631
...
0.0
24.041631
30.0
45.541190
0.0
24.041631
252.0
177.597860
30.0
45.541190
14000US06073021202
588.0
147.434731
0.0
16.970563
26.0
12.727922
0.0
16.970563
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
0.0
16.970563
0.0
16.970563
14000US06073021204
1007.0
173.277235
20.0
31.906112
0.0
24.041631
5.0
19.723083
0.0
24.041631
...
0.0
24.041631
0.0
24.041631
0.0
24.041631
169.0
85.088190
52.0
68.154237
14000US06073021205
838.0
216.815590
18.0
33.615473
0.0
24.041631
20.0
37.121422
0.0
24.041631
...
0.0
24.041631
0.0
24.041631
0.0
24.041631
154.0
122.188379
0.0
24.041631
14000US06073021206
312.0
92.763139
16.0
28.635642
0.0
16.970563
0.0
16.970563
0.0
16.970563
...
24.0
38.897301
0.0
16.970563
0.0
16.970563
11.0
20.000000
0.0
16.970563
14000US06073021302
685.0
148.519359
73.0
46.238512
0.0
24.041631
2.0
17.464249
0.0
24.041631
...
0.0
24.041631
11.0
24.758837
0.0
24.041631
8.0
20.808652
11.0
24.758837
14000US06073021303
889.0
190.213038
16.0
31.064449
0.0
24.041631
204.0
91.934759
0.0
24.041631
...
0.0
24.041631
0.0
24.041631
0.0
24.041631
77.0
66.030296
17.0
32.756679
14000US06073021304
521.0
86.371292
6.0
15.000000
22.0
34.176015
0.0
16.970563
5.0
14.422205
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
34.0
32.310989
0.0
16.970563
14000US06073021400
471.0
105.645634
17.0
29.410882
22.0
38.013156
103.0
55.172457
0.0
24.041631
...
0.0
24.041631
0.0
24.041631
0.0
24.041631
171.0
104.004808
0.0
24.041631
14000US06073021500
826.0
189.525724
17.0
31.906112
0.0
24.041631
316.0
146.013698
0.0
24.041631
...
0.0
24.041631
0.0
24.041631
0.0
24.041631
118.0
80.808415
0.0
24.041631
14000US06073021600
205.0
57.982756
0.0
16.970563
0.0
16.970563
17.0
29.546573
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
5.0
14.422205
173.0
63.513778
0.0
16.970563
14000US06073021800
386.0
75.822160
11.0
19.209373
0.0
16.970563
5.0
14.422205
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
135.0
49.929951
0.0
16.970563
14000US06073021900
100.0
50.249378
0.0
24.041631
0.0
24.041631
18.0
19.798990
0.0
24.041631
...
0.0
24.041631
0.0
24.041631
0.0
24.041631
0.0
24.041631
36.0
32.756679
14000US06073022000
103.0
58.249464
28.0
27.856777
0.0
16.970563
217.0
77.620873
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
41.0
34.655447
0.0
16.970563
14000US06073022100
1156.0
225.417391
0.0
24.041631
0.0
24.041631
48.0
42.638011
0.0
24.041631
...
0.0
24.041631
0.0
24.041631
0.0
24.041631
85.0
71.168813
0.0
24.041631
14000US06073990100
0.0
16.970563
0.0
16.970563
0.0
16.970563
0.0
16.970563
0.0
16.970563
...
0.0
16.970563
0.0
16.970563
0.0
16.970563
0.0
16.970563
0.0
16.970563
628 rows × 72 columns
In [6]:
re_df.stack([0,1])
Out[6]:
est
est_m90
geoid
14000US06073000100
55_64
aian
0.0
16.970563
asian
5.0
15.000000
black
0.0
16.970563
hisp
103.0
72.180330
multiple
0.0
16.970563
nhopi
10.0
19.209373
nonhisp
432.0
87.114867
other
0.0
16.970563
white
525.0
101.178061
65_74
aian
0.0
16.970563
asian
0.0
16.970563
black
0.0
16.970563
hisp
31.0
27.459060
multiple
4.0
13.416408
nhopi
0.0
16.970563
nonhisp
362.0
84.219950
other
12.0
21.633308
white
381.0
86.371292
75_84
aian
0.0
16.970563
asian
0.0
16.970563
black
0.0
16.970563
hisp
19.0
22.472205
multiple
0.0
16.970563
nhopi
0.0
16.970563
nonhisp
257.0
80.919713
other
0.0
16.970563
white
276.0
80.919713
85
aian
0.0
16.970563
asian
7.0
16.970563
black
0.0
16.970563
...
...
...
...
...
14000US06073990100
55_64
nonhisp
0.0
16.970563
other
0.0
16.970563
white
0.0
16.970563
65_74
aian
0.0
16.970563
asian
0.0
16.970563
black
0.0
16.970563
hisp
0.0
16.970563
multiple
0.0
16.970563
nhopi
0.0
16.970563
nonhisp
0.0
16.970563
other
0.0
16.970563
white
0.0
16.970563
75_84
aian
0.0
16.970563
asian
0.0
16.970563
black
0.0
16.970563
hisp
0.0
16.970563
multiple
0.0
16.970563
nhopi
0.0
16.970563
nonhisp
0.0
16.970563
other
0.0
16.970563
white
0.0
16.970563
85
aian
0.0
16.970563
asian
0.0
16.970563
black
0.0
16.970563
hisp
0.0
16.970563
multiple
0.0
16.970563
nhopi
0.0
16.970563
nonhisp
0.0
16.970563
other
0.0
16.970563
white
0.0
16.970563
22608 rows × 2 columns
In [16]:
out_df = re_df.stack([0,1]) #.reset_index().set_index('geoid')
out_df.index.names = ['geoid','age_group','race']
#out_df.columns = 'age_group race pop pop_m90'.split()
In [ ]:
%mt_materialize out_df '/Users/eric/Library/Application Support/metapack/_materialized_data/census.gov-senior_race-20155-san_diego-tract-1'
In [ ]:
%mt_materialize_all '/Users/eric/Library/Application Support/metapack/_materialized_data/census.gov-senior_race-20155-san_diego-tract-1'
In [ ]:
%mt_show_metatab
In [ ]:
%mt_show_libdirs
Content source: CivicKnowledge/metatab-packages
Similar notebooks: