Project US census

Location: C:\Documents\Examples\US census 2012\US census.morph

Overview

The project requires 4 sources of data:

  1. Text file fips.csv
  2. Text file empclass.csv
  3. Text file cbp12st.txt
  4. Text file lfo codes.csv

It doesn't export any data.

The project creates 6 tables that are later transformed using 24 transformations. Below is step-by-step description of the transformation process:

Table: "FIPS"

Step 1: Import 2 columns (listed below) from text file fips.csv. Other parameters: separator is comma, encoding ANSI (codepage ).

Table: "Emp. class"

Step 1: Import all columns from text file empclass.csv. Other parameters: separator is comma, encoding ASCII.

Step 2: Modify these column(s) using following expressions:

Column NameExpression
Classupper([Class])

Table: "LFO Codes"

Step 1: Import all columns from text file lfo codes.csv. Other parameters: separator is comma, encoding ASCII.

Table: "Census data"

Step 1: Import 11 columns (listed below) from text file cbp12st.txt. Other parameters: separator is comma, encoding ASCII.

Step 2: Modify these column(s) using following expressions:

Column NameExpression
fipstateN([fipstate])

Step 3: Map table "FIPS". Look up values in column [fipstate] in table "FIPS" column [Numeric code] and if found, replace them with values from table "FIPS" column [Name]. Otherwise keep original values.

Step 4: Map table "Emp. class". Look up values in column [empflag] in table "Emp. class" column [Class] and if found, replace them with values from table "Emp. class" column [Description]. Otherwise keep original values.

Step 5: Map table "LFO Codes". Look up values in column [lfo] in table "LFO Codes" column [LFO Code] and if found, replace them with values from table "LFO Codes" column [LFO Description]. Otherwise keep original values.

Step 6: Modify these column(s) using following expressions:

Column NameExpression
empflagifempty([empflag],'Other')

Step 7: Rename 5 columns

Old NameNew Name
apPayroll, MM
empEmployees
estEstablishments
fipstateState
lfoLFO Description

Step 8: Remove 6 columns

Step 9: Remove rows with the following values in column [LFO Description]:

Table: "Payroll by State"

Step 1: Take table "Census data"

Step 2: Aggregate data. Calculate:

grouped by

Step 3: Calculate new column(s) using following expressions:

Column NameExpression
Avg. Payroll[Sum of Payroll, MM] / [Sum of Employees] * 1000

Step 4: Select 2 columns

Step 5: Sort table

ColumnOrder
Avg. PayrollDescending

Table: "Top-10 States By Establishments"

Step 1: Take table "Census data"

Step 2: Aggregate data. Calculate:

grouped by

Step 3: Rename 1 column

Old NameNew Name
Sum of EstablishmentsEstablishments

Step 4: Sort table

ColumnOrder
EstablishmentsDescending

Step 5: Enumerate rows. Create column [RowNo] with row number for every row in the table.

Step 6: Trim table. Keep only top 10 rows