4

I need to calculate population density at the level of U.S. Census tracts for across a number of states.

I used the instructions here Where to get 2010 Census Block data? to download DP-1 for the states CT, ME, MA, NH, NY, RI, and VT, but some of the values in the column for Total Population show up like this:

2114(r39570)

This evidently means that the value has been revised. I went back to American FactFinder to view the data, and by clicking on the value "(r39570)", I'm able to confirm that these indicate that the value has been revised:

screen shot of explanation from American FactFinder

As far as I can tell, the correct value, which is evidently 1120 in this example, does not appear anywhere in the .csv that I imported.

How can I get the correct values onto my computer and appropriately imported into QGIS so that I can perform the correct spatial calculations?

  • There are a number of them in the 11,000+ row table. It's a little hard to count them given the size of the table. – user1521655 Dec 29 '16 at 17:48
  • 1
    I came across this document which provides notes to revisions, your example of New London is provided, however the figures appear different. – whyzar Dec 29 '16 at 17:49
3

This is not a very satisfying answer, so I'd love to hear from somebody more familiar with U.S. Census data (or for that matter, somebody more experienced with bash), but the following does seem to work.

The annotations for references are included in a .txt file included with the data download. The file I downloaded was called DEC_10_DP_DPDP1.txt, and contains references in the following format:

(r39570)        This count has been revised.
Revised count: 1,120 Revision date: 02-09-2012

For more information, see  2010 Census Count Question Resolution.

To extract values from this file and inject them into the correct slots in an edited version of the .csv file DEC_10_DP_DPDP1_with_ann.csv, I used the following bash commands:

cp DEC_10_DP_DPDP1_with_ann.csv DEC_10_DP_DPDP1_with_ann_EDIT.csv
#make an array of strings containing reference and values
ARR=$(grep -h -A 1 "(r[0-9]*)" DEC_10_DP_DPDP1.txt |\
 sed "s/Revision date: ..-..-....$//g" |\
 sed "s/This count has been revised.//g" |\
 sed "s/Revised count: //g" | tr -d ",\n" |\
 sed "s/--/\n/g" |\
 tr -d "[:blank:]");
#iterate through array, search for references in .csv, and replace with value
for X in $ARR; do
  REF=$(echo `expr "$X" : '\((r[0-9]*)\)'`);
  VAL=$(echo $X | sed "s/(r[0-9]*)//g");
  sed -i -e "s/[0-9]*$REF/$VAL/g" DEC_10_DP_DPDP1_with_ann_EDIT.csv;
done

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.