2020CENSUS.GOV

Improving Efficiency in NonData-Collection Activities with 

Data Science Tools

October 26, 2022

The Case of Census’s Count Question Resolution Operation 

Nelson Chung

Survey Statistician

Decennial Census Management Division

Count Question Resolution Branch

nelson.chung@census.gov
2020CENSUS.GOV

Overview

• 2020 Census Count Question Resolution (CQR) Introduction

• Leveraging Pre-existing Data

• Customer Service

• Internal Assessment

• Tracking Workflows

• Challenges

2
2020CENSUS.GOV

2020 Census CQR

• The 2020 Census Count Question Resolution (CQR) program provides a mechanism for governmental units to request a review 

of their official 2020 Census results and to help ensure that housing and population counts are correctly allocated to 2020 

census tabulation blocks in the 50 states, District of Columbia, and Puerto Rico.

• CQR may not collect any new data.

• The goal is to improve the quality of the base used for post-2020 Census population estimates.

• Count corrections are issued, when necessary, to the tribal chairperson or highest elected official of affected governmental 

units.

• CQR will not revise the apportionment data sent to the office of the President or any redistricting data sent to the states.

• CQR Revisions may affect local funding.

3
2020CENSUS.GOV

Leverage Pre-existing 

Data

Produce the Following “Crosswalk” 

• Created for internal use.

• Block count lists are forms Governmental Units (GUs) use to submit the corrections they request

• Useful for assisting GUs in identifying census blocks within their jurisdiction to create block count 

lists. Scrape and unzip public Census block assignment data. Merge with internal spreadsheet for 

Census Regional Office (RO).

4
2020CENSUS.GOV

CQR Block Count List

Complete Block Count List

Help GUs identify blocks in their jurisdiction to populate their CQR Block Count List with one click.

5

BlockID from Crosswalk

AABBBCCCCCCDDDD

State

County

Tract

Block

CENSUS ENTITYID 

(unique for each GU)
2020CENSUS.GOV

Customer Service

Weekly Case Status Updates on 2020 CQR Website

6

• Enable GU to see status of their case.

• Status based on presence or absence of dates in 

production control system (PCS).

• PCS data tables accessible by Oracle TOAD software. 

• Steps 

(1) loading list of cases from TOAD

(2) writing data to pre-formatted spreadsheet

(3) converting it to PDF

(4) uploading to website

• All four steps reduced to one-click.

• Script enables Python communication with TOAD and 

Excel.
2020CENSUS.GOV

Internal Analytics

7

• Management-driven Queries (Ad hoc)

• General curiosity

• Public Information Office

• Operational Statute

• News/Media Inquiries

• Requires integrating publicly-available data with 

internal data

• Assessment Tables (Regular)

• Post-operational assessment to tell story of operation
2020CENSUS.GOV

Assessment Tables

8

Region GU Type Boundary Housing Count

New York

Tribe

State

County

MCD

Place

Total

• Info from production 

system and master file 

queried TOAD. Field names 

may not be consistent 

from different databases.

• Row and Column totals for 

multiple tables; functions 

handy.
2020CENSUS.GOV

Tracking Workflow Script

• No customized software for tracking preprocessing workflow.

• Common to other certain geographic operations 

that have pre-processing.

• Working directory folder structure. Numbered 

folders for processing steps, personal folders to 

“check out” case.
2020CENSUS.GOV

Tracking Workflow Script

• Cases are recorded on .txt files and sent from one folder to another via cut-and-paste.

• One analyst works on case, leaves notes, moves it to numbered folder.
2020CENSUS.GOV

Locating Cases Files

• File names: Case ID + extension.

• Extract Case IDs from Oracle.

• Use Python to determine whether file exist under a certain directory.

• Scan for duplicates.

• Avoid need to look through every folder and subfolder.
2020CENSUS.GOV

Challenges

1. IT limitations.

2. Economies of Scale not always reached.
2020CENSUS.GOV

Questions? 

Thank you!
14 2020CENSUS.GOV

Backup slides
2020CENSUS.GOV

Key Python Packages

• Communicating with Oracle TOAD: cx_Oracle

• Manipulating Excel: openpyxl, xlsxwriter

• Scraping and uploading data to website: selenium

• Scan for file: pathlib