Alternate views of segregation

alternate_segregation.ipynb

chicago_outcomes_and_lags.csv

lodes_db.sh

#!/bin/bash

# createdb lodes -O jsaxon

psql lodes << EOD

  CREATE TABLE od (wgeoid BIGINT, hgeoid BIGINT, e0 INT, e1 SMALLINT, e2 SMALLINT, e3 SMALLINT);
  CREATE TABLE wac (wgeoid BIGINT, e1 INTEGER, e2 INTEGER, e3 INTEGER, r1 INTEGER, r2 INTEGER, r3 INTEGER, r4 INTEGER, r5 INTEGER, r7 INTEGER, t1 INTEGER, t2 INTEGER);


  \\copy od  FROM '/media/jsaxon/brobdingnag/data/lodes/raw/od.cook.2013.tmp'  DELIMITER ',' CSV
  \\copy wac FROM '/media/jsaxon/brobdingnag/data/lodes/raw/wac.2013.tmp' DELIMITER ',' CSV

  CREATE TABLE wac_frac AS select wgeoid, (e1 > 0)::int e1, (e2 > 0)::int e2, (e3 > 0)::int e3, r1::float/(e1+e2+e3) r1, r2::float/(e1+e2+e3) r2, r3::float/(e1+e2+e3) r3, r4::float/(e1+e2+e3) r4, r5::float/(e1+e2+e3) r5, r7::float/(e1+e2+e3) r7, t1::float/(e1+e2+e3) t1, t2::float/(e1+e2+e3) t2 from wac;

  CREATE TABLE homes AS SELECT hgeoid, SUM(e0) e0, SUM(e1) e1, SUM(e2) e2, SUM(e3) e3 FROM od GROUP BY hgeoid ORDER BY hgeoid;

  ALTER TABLE wac_frac ADD PRIMARY KEY (wgeoid, e1, e2, e3);
  ALTER TABLE homes    ADD PRIMARY KEY (hgeoid);

EOD

psql lodes -f workplace_segregation.sql -t -A -F"," -o workplace_segregation.csv

lodes_download.sh

#!/bin/bash

states="al ak az ar ca co ct de dc fl ga hi id il in ia ks ky la me md ma mi mn ms mo mt ne nv nh nj nm ny nc nd oh ok or pa ri sc sd tn tx ut vt va wa wv wi wy"

for y in 2013; do 
  for s in $states; do 

    # wget https://lehd.ces.census.gov/data/lodes/LODES7/${s}/od/${s}_od_aux_JT00_${y}.csv.gz
    # wget https://lehd.ces.census.gov/data/lodes/LODES7/${s}/od/${s}_od_main_JT00_${y}.csv.gz

    for i in 1 2 3; do 
      wget https://lehd.ces.census.gov/data/lodes/LODES7/${s}/wac/${s}_wac_SE0${i}_JT00_${y}.csv.gz
    done 

done; done

# mkdir -p raw tract
mv *gz raw/

cd raw
zcat *_wac_*_JT00_2013.csv.gz | cut -d, -f1,6-8,29-36 | grep -v w_geocode > wac.2013.tmp
zcat *_od_*_JT00_2013.csv.gz | grep "^[0-9]*,17031" | cut -d, -f1-3,7-9 | grep -v w_geocode > od.cook.2013.tmp
cd -

workplace_segregation.sql

SELECT 
  h.hgeoid / 10000 htr_geoid,
  SUM(black) / SUM(h.e0) black
FROM 
  homes h 
JOIN (
  SELECT 
    hgeoid,
    SUM(od.e1 * wf.e1 * wf.r2 + od.e2 * wf.e2 * wf.r2 + od.e3 * wf.e3 * wf.r2) black
  FROM 
    od
  JOIN wac_frac wf ON
    od.wgeoid = wf.wgeoid
  GROUP BY
    hgeoid
) fr ON 
  h.hgeoid = fr.hgeoid
GROUP BY 
  htr_geoid
ORDER BY
  htr_geoid
;