Repo for DOL Summer Data Challenge

Repo for DOL Summer Equity Project.

Current scripts (code)

• 00_scraping_DOLh2a.ipynb

  • Takes in: none
  • What it does: scrapes DOL h2a employee data from 2014 to 2021
  • Output: excel data (one file per year)

• 01_disclosure_diff_fields.py

  • Takes in: DOL h2a job data 2014-2021
  • What it does: looks at common and different column names based on year
  • Output: none

• 02_RenameCol_Rowbind.py

  • Takes in: DOL h2a job data 2014-2021
  • What it does: rename and reconcile the column names and row bind the data
  • Output:
    • 1) combined 2014-21 h2a data based on shared columns in csv;
    • 2) csv jobs data just 2020 and 2021;
    • 3) jobs data all years/all columns

• 03_fuzzy_matching.R

  • Takes in: combined DOL h2a data and WHD investigation data
  • What it does: fuzzy matches between the two data based on name and city
  • Output: fuzzy matched data in RDS

• 04_acs_demographics.py 04_acs_pulls.sh

  • Takes in: csv acs predictors data
  • What it does: loads ACS variables at tract level and pull census data for a certain year;
  • A bash script that runs 04_acs_demographics.py multiple times by iterating over years is also included
  • Output:

• 04_acs_demographics_percentage.py

  • Takes in: csv predictors, pickle acs tract demographics data, pickle H2A jobs tract data
  • What it does: identify which predictors to calculate percentage, calculate percentage, and clean and combine percentage and nonpercentage variables' data
  • Output: .pkl of calculated tract-level percentage data and remaining predictors data

• 04_acs_percentage_pulls.sh

  • A bash script that pulls percentage (runs the script 04_acs_demographics_percentage.py on data) from year 2014 to 2019

• 05_geocode_jobs.py

  • Takes in: pkl combined DOL h2a data
  • What it does: using api, geocode each data entry's location with latitude and longitude
  • Output: .pkl and csv of the geocoded data

• 06_h2a_tract_intersections.ipynb

  • Takes in: tract shapefiles url and geocoded dol h2a data
  • What it does: joins dol data with geoid (as a unique identifier) and tract shape; plots case number on US map
  • Output: .pkl dol h2a data merged with geoId and corresponding tract shape; graph of case number on US map

• 07_merging_acs_geocodedjobs.py

  • Takes in: acs data with percentage calculation from year 2014-2019, h2a job data with tract information
  • What it does: merge all the datasets
  • Output: one .csv data file of 2014-2019 acs data merged with geocode/tract information (referred as h2a combined data form now on)

• 08_clean_TRLA_intake.Rmd

  • Takes in: TRLA report data in .xlsx/.xls
  • What it does: cleans the data, consolidate opponent columns, deals with and flags missing values
  • Output: one .csv and .RDS for cleaned TRLA data

• 09_fuzzy_matching_TRLA.R

  • Takes in: h2a combined data, cleaned TRLA data
  • What it does: fuzzy matches between H2A applications data and the TRLA data
  • Output: .RDS and .csv of matched data of trla and h2a job

• 10_construct_outcomes.R

  • Takes in: matched data (between h2a application and WHD data) with de-duped datasets, TRLA cleaned data, ACS data
  • What it does: dedupes the data based on unique identifier; constructs outcome variables (based on findings start/end date and job star/end date) on the matched data, then merge ACS onto datasets
  • Output: WHD data 1) with outcome variable columns and merged with ACS, 2) of TRLA states with outcome variables and merged with ACS

• 10a_construct_jobs_address.py

  • Takes in: h2a and WHD matched data
  • What it does: creates unique job address column on dataset
  • Output: h2a and WHD matched data with employer full address column

• 12_mlmodeling_preprocessing.py

  • Takes in: TRLA state's whd data, general whd data, h2a 2014-2021 combined data (for feature columns extracction)
  • What it does: preprocesses the TRLA and full whd data for later modeling; preprocess include: extract feature columns, generate dummy values for trivial row values, impute missing values, train and test split based on uniqie identifier jobs_group_id
  • Output: .pkl files of split train and test for TRLA and full whd data.

• 13_descriptives_and_visualizations.R

  • Takes in: TRLA state's whd data, general whd data
  • What it does: creates descriptives and visualizations based on merged WHD/TRLA data

• 14_mlmodeling_eval.py

  • Takes in: train and test split of WHD data
  • What it does: run several machine learning models and evaluate the performance of trained models on prediction, including confusion matrices
  • Output: .csv of evalutation results, confusion matrices, predictions on test data of different models

• 15_viz_modelingoutputs.R

  • Takes in: all .csv from script 14_mlmodeling_eval.py
  • What it does: creates visualizations of outputs from the predicative models on WHD data

• 16_mlmodeling_featureimportances.py

  • Takes in: train and test split of WHD data
  • What it does: run specifically two models, shallow GradientBossting() and logistic regression and evaluate the performance of trained models on prediction, including confusion matrices
  • Output: none

• 17_mlmodeling_eval_trla.py

  • Takes in: train and test split of TRLA-WHD data
  • What it does: run machine learning models, focussing on whether the outcome variable is intersect of TRLA/WHD, or one of them, or none; and evaluate the performance of trained models on prediction, including confusion matrices
  • Output: .csv of evalutation results, confusion matrices, predictions on test data of the models

• 18_viz_modelingoutputs_trla.R

  • Takes in: all .csv from script 17_mlmodeling_eval_trla.py
  • What it does: visualizes outputs from models and predictions on TRLA on WHD vilation data

• 19_visualize_investigations.ipynb

  • Takes in: trac shape files and investigation data from TRLA on WHD violations
  • What it does: visualizes shaded maps with investigation locations

• 20_textanalysis_addendums.Rmd

  • Takes in: job disclosure data and investigation data of TRLA on WHD violations
  • What it does: text analysis and topic matching on the disclosure data
  • Output: html_document of analysis result

Directory structure

• background_reading: contains background readings on (1) the H-2 program, (2) legal rights visa holders have, (3) evidence of violations of those rights

Links to external data sources

• DOL Wages and Hour Division (WHD) compliance data: https://enforcedata.dol.gov/views/data_summary.php

  • Data download link: https://enfxfr.dol.gov/data_catalog/WHD/whd_whisard_20210127.csv.zip
  • Rough summary: contains ~309681 compliance-related actions (not limited to H2 program) beginning in FY2005
  • Documentation in data/documentation/whd_data_dictionary.csv

• OPM staffing data (potentially useful for WHD staffing patterns)- https://www.fedscope.opm.gov/

Details on data

TRLA's Merged Dataset: Quarterly Disclosure & Scraped Data

Relevant links:

• Static data can be found in this folder.
• Data dictionary for DOL-only fields is here.
• Data dictionary for scraper-only fields is here.

Background:

• job_central parts 1 and 2 contain data for every clearance order submitted to DOL, for both the H-2A and H-2B visa programs. Each clearance order is for a primary job, but can contain multiple worksites and multiple housing locations (see below).

  • Disclosure data: Data on job postings released every quarter of a fiscal year.
  • Scraped data: In the interim between these data releases, we scrape the DOL site to see who is requesting H-2A or H-2B certification in real time.

• additional_housing contains all the data for the additional housing locations associated with a single clearance order.

• additioanl_worksite contains all data for additional work locations associated with a single clearance order.

• both ^ can be linked together with the Case Number.

DOL WHD data