Step 01 - Standardize the data frame • titleclassifier

standardize_df() turns a raw Part VII compensation table into the form the rest of the pipeline expects: upper-case titles without periods, numeric hours and pay, and 0/1 checkboxes.

Before and after

Rows of the demo data, as filed and after step 01:

form	title, filed	title, step 01	hours, filed	TOT.HOURS	pay, org	other pay + benefits	TOT.COMP	officer box, filed	officer box, step 01
990EZ	Secretary, Dir	SECRETARY, DIR	3	3	0	0	0		NA
990EZ	VP, Treasurer & Secretary	VP, TREASURER & SECRETARY	2	2	0	0	0		NA
990	Executive Dir.	EXECUTIVE DIR	40	40	70098	0	70098		0
990EZ	Secretary, Dir.	SECRETARY, DIR	1	1	0	0	0		NA
990	dir rec & parks	DIR REC & PARKS	40	40	125135	12028	137163		0
990EZ	Founder/Board Member	FOUNDER/BOARD MEMBER	3	3	0	0	0		NA
990EZ	President & Chief Operations Office	PRESIDENT & CHIEF OPERATIONS OFFICE	40	40	0	0	0		NA
990	Secretary/Treas	SECRETARY/TREAS	1	1	0	0	0	X	1

What it does

standardize_df()
|
+- check_names()   add any missing compensation or checkbox column (as NA)
+- pre_clean()     upper-case the title and strip periods
+- to_numeric()    hours and the pay components as numbers, blanks as 0
+- to_boole()      checkboxes: "X" -> 1, blank -> 0; NA on a 990-EZ, which has no boxes

Keeps the filed title as TITLE_RAW and gives each row a PERSONID.
Sums hours at the organization and at related organizations into TOT.HOURS.
Sums pay from the organization and from related organizations, other compensation and benefits into TOT.COMP.
Converts the trustee, officer, key-employee, highly-compensated and former checkboxes to 1 or 0. A 990-EZ has no checkboxes, so on a 990-EZ they are NA.

Assets

Step 01 uses no crosswalk or word list. It needs the Part VII column names of the NCCS efile schema (F9_07_COMP_DTK_*). See the Workflow article for fetching the tables. tinypartvii is a demo slice in that schema:

data(tinypartvii)
dim(tinypartvii)
#> [1] 81309    33
grep("^F9_07", names(tinypartvii), value = TRUE)
#>  [1] "F9_07_COMP_DTK_EMPL_BEN"          "F9_07_COMP_DTK_NAME_ORG_L1"      
#>  [3] "F9_07_COMP_DTK_NAME_ORG_L2"       "F9_07_COMP_DTK_NAME_PERS"        
#>  [5] "F9_07_COMP_DTK_TITLE"             "F9_07_COMP_DTK_AVE_HOUR_WEEK"    
#>  [7] "F9_07_COMP_DTK_AVE_HOUR_WEEK_RL"  "F9_07_COMP_DTK_POS_INDIV_TRUST_X"
#>  [9] "F9_07_COMP_DTK_POS_INST_TRUST_X"  "F9_07_COMP_DTK_POS_OFF_X"        
#> [11] "F9_07_COMP_DTK_POS_KEY_EMPL_X"    "F9_07_COMP_DTK_POS_HIGH_COMP_X"  
#> [13] "F9_07_COMP_DTK_POS_FORMER_X"      "F9_07_COMP_DTK_COMP_ORG"         
#> [15] "F9_07_COMP_DTK_COMP_RLTD"         "F9_07_COMP_DTK_COMP_OTH"

Try it

pre_clean(c("Vice President.", "sec/treas.", "Exec. Dir."))
#> [1] "VICE PRESIDENT" "SEC/TREAS"      "EXEC DIR"

d  <- dplyr::sample_n(tinypartvii, 50)
df <- standardize_df(d)
#> [OK] standardize df step complete
df |>
  dplyr::select(TITLE_RAW, F9_07_COMP_DTK_TITLE, TOT.HOURS, TOT.COMP) |>
  head(6)
#>                    TITLE_RAW      F9_07_COMP_DTK_TITLE TOT.HOURS TOT.COMP
#> 1                   DIRECTOR                  DIRECTOR         1        0
#> 2 CHIEF MARKETING & COMM. OF CHIEF MARKETING & COMM OF        40   187972
#> 3                  PRESIDENT                 PRESIDENT         1        0
#> 4                     MEMBER                    MEMBER        20        0
#> 5                  PRESIDENT                 PRESIDENT         0        0
#> 6                         VP                        VP        10        0

Input: a raw Part VII data frame (NCCS efile schema). Output: the same rows with cleaned title text, numeric hours and pay, 0/1 checkboxes and a PERSONID. Continue with step 02.