rebeccajohnson88 / qss20_s21_proj Goto Github PK
View Code? Open in Web Editor NEWRepo for DOL Summer Data Challenge on equity in H-2A oversight
License: Creative Commons Zero v1.0 Universal
Repo for DOL Summer Data Challenge on equity in H-2A oversight
License: Creative Commons Zero v1.0 Universal
for above, be prepared to: (1) update imputation to correspond to new data and (2) update modeling by including a longer list of classifiers (can share other sample code on that)
Download tract shapefiles for 2014 (can just use that year)- https://www2.census.gov/geo/tiger/TIGER2014/TRACT/
Notes: write code to scrape the links and save the zip file in "raw"-- has two-digit state codes; can mass unzip using command line
Load and use sjoin
for spatial intersection with the lat/longs- can adapt this code from here (https://github.com/rebeccajohnson88/qss20_s21_proj/blob/main/code/qss20_groupcode/geocoding_acs/01_geocode_mergewithACS_plot_studentversion.ipynb) and might need to use crosswalk to connect two-digit state code with state names (eunice i think has code related to that)
al_shapes = gpd.read_file(PATH_TO_INT_OUTPUTS + "tl_2016_01_tract")
ky_shapes = gpd.read_file(PATH_TO_INT_OUTPUTS + "tl_2016_21_tract")
la_shapes = gpd.read_file(PATH_TO_INT_OUTPUTS + "tl_2016_22_tract")
ms_shapes = gpd.read_file(PATH_TO_INT_OUTPUTS + "tl_2016_28_tract")
tn_shapes = gpd.read_file(PATH_TO_INT_OUTPUTS + "tl_2016_47_tract")
tx_shapes = gpd.read_file(PATH_TO_INT_OUTPUTS + "tl_2016_48_tract")
shapefile_crs = tx_shapes.crs
## intersections while setting the CRS/projection of the
## points to match the shapefiles CRS; could be made more efficient
## by storing the points and shapes in a length-2 list and then
## putting that list in a dict with state as key
al_join = sjoin(AL_points.set_crs(shapefile_crs), al_shapes, how="right", op = "intersects")
ky_join = sjoin(KY_points.set_crs(shapefile_crs), ky_shapes, how="right", op = "intersects")
ms_join = sjoin(MS_points.set_crs(shapefile_crs), ms_shapes, how="right", op = "intersects")
la_join = sjoin(LA_points.set_crs(shapefile_crs), la_shapes, how="right", op = "intersects")
tx_join = sjoin(TX_points.set_crs(shapefile_crs), tx_shapes, how="right", op = "intersects")
tn_join = sjoin(TN_points.set_crs(shapefile_crs), tn_shapes, how="right", op = "intersects")
deleted from final script but here
indices_approved = approved_matches$matches$inds.a
indices_approved_b = approved_matches$matches$inds.b
sprintf("In the match object, there are %s unique indices for A; %s for B",
length(unique(indices_approved)),
length(unique(indices_approved_b)))
both_ind = intersect(indices_approved, indices_approved_b) # see that they're the same just in diff order
indices_dropped = setdiff(rownames(approved_only), indices_approved)
approved_only$row_id = rownames(approved_only)
View(approved_only %>% filter(row_id %in% indices_dropped) %>% select(dedupe_fields, EMPLOYER_NAME))
Writing Edits
Figure/Table Edits
Here is a link to the code: https://github.com/rebeccajohnson88/qss20_s21_proj/blob/main/code/01_explorequarterly.ipynb
Writing major revisions:- @lizard12995 @cassaydavis
Writing minor revisions:
Figure edits that can be made in overleaf (captions/alt text): - @camguage
Edits that require new code or figure revisions: @camguage and @rebeccajohnson88
508 Compliance Edits
## melt to long format
df_acs_long = pd.melt(df_acs, id_vars= ['county', 'state', 'tract']).sort_values(by =
['tract', 'county'])
## create prefix and suffix columns
df_acs_long['variable_prefix'], df_acs_long['variable_suffix'] = df_acs_long['variable'].str.split('_', 1).str
## manually indicate which prefix columns don't follow the pattern
prefixes_perc_notrelevant = ['B01002','B25031','B25107']
df_acs_long['perc_NA'] = np.where(df_acs_long.variable_prefix.isin(prefixes_perc_notrelevant),
1, 0)
prefixes_perc_extrahier = ['B25026', 'B25123']
df_acs_long['perc_extrahier'] = np.where(df_acs_long.variable_prefix.isin(prefixes_perc_extrahier),
1, 0)
## group by county, tract, and variable prefix to generate
## percentages (and later variable names)
df_acs_long_toiterate = df_acs_long[df_acs_long.perc_NA == 0].copy()
group_co_tract_varg = df_acs_long_toiterate.groupby(['county', 'tract', 'variable_prefix'])
################################## Generate percentages: auto-calc #################################
df_acs_long_percentage = []
#flag = 0
for group,data in group_co_tract_varg:
#if flag > 100: # flag to test code
# break
# print(data)
tract = data.tract.iloc[0]
county = data.county.iloc[0]
prefix = data.variable_prefix.iloc[0]
row_list_group = []
for i in range(1,data.shape[0]):
numerator = data.value.iloc[i]
denominator = data.value.iloc[0].astype(float)
percentage= numerator/denominator
row = [county,tract,prefix]
row = row + [data.variable_suffix.iloc[i],percentage]
row_list_group.append(row)
# print(row)
# print(row_list_group)
# print('___________________________')
# break
df_acs_long_percentage.append(pd.DataFrame(row_list_group))
#flag = flag + 1
percentages_all_groups = pd.concat(df_acs_long_percentage)
percentages_all_groups.columns = ['county', 'tract', 'variable_prefix',
'variable_suffix', 'percentage']
percentages_all_groups['variable_prefix_suffix'] = percentages_all_groups.variable_prefix + "_" + percentages_all_groups.variable_suffix
percentages_all_groups.drop(columns = ['variable_prefix', 'variable_suffix'], inplace= True)
####################################### Reshape percentages to wide ##################################
## subset to a few counties/tract (so even row numbers for wide reshape) (remove after testing)
### commented out testing code
## test_forwide = percentages_all_groups[(percentages_all_groups.county == 5) &
## (percentages_all_groups.tract.isin([100, 200, 400]))].copy()
percentages_all_groups['county_tract'] = percentages_all_groups.county.astype(str) + "_" + percentages_all_groups.tract.astype(str)
percentages_all_groups.drop(columns = ['county', 'tract'], inplace= True)
## try the pivot -- before pivoting, creating county-tract
percentages_wide_pivot = percentages_all_groups.pivot(index = 'county_tract',
columns = 'variable_prefix_suffix',
values = 'percentage')
percentages_wide_pivot_reset = percentages_wide_pivot.reset_index()
## other cols to drop
percentages_wide_pivot_reset.drop(columns = ['b01001_002e',
'b01001_026e'], inplace = True)
from lizzie:
Must do:
Maybe do:
Dispositions:
Closed = TRLA involvement with the case has concluded. A case with this disposition was never rejected. We took it on in some capacity (anywhere from just giving advice to taking on significant litigation) and then the case was closed.
Open = TRLA has accepted the case and it is in progress. The client and their case qualify for our services (re: income, immigration status, and area of law)
Pending = TRLA is evaluating the case before it is accepted. This stage could involve completing an intake application by asking/answering clarifying questions about the applicant and their case.
Rejected = TRLA declined to take the case. This often happens because of ineligibility. The client may be over-income, stop responding to messages from us after repeated tries, or outside of our geographic reach.
How to code adverse parties:
Case # is tied to an individual client, Lead Case # is one number to link those cases together (for a group case, which includes multiple individual cases).
Any case (or group case) could have multiple adverse parties tied to that case. A set of individual cases in a group could also all have the same adverse party.
Lead case AP would apply to all individual cases in the group.
If multiple are present you’d want to separate out.
So, if ind AP is missing, use Lead AP. You have to decide if a “count” is tied to an individual client or to a group case.
If Lead AP is missing, that is fine, use individual case AP.
If both are present, you could separate out into two cases for the first occurrence? Or for each depending on how you’ve decided to count.
Share case ids for illogical values like for some timestamps or states?
investigations_cleaned
and approved_only
) with idsapproved_only <- approved_only %>%
mutate(id = match(name_city_state,id_vector_1))
investigations_cleaned <- investigations_cleaned %>%
mutate(name_city_state = sprintf("%s, %s, %s", name, city, st_cd))
id_vector_2 <- unique(investigations_cleaned$name_city_state)
investigations_cleaned <- investigations_cleaned %>%
mutate(id = match(name_city_state,id_vector_2))
approved_only <- approved_only %>%
mutate(id = match(name_city_state,id_vector_1))
investigations_cleaned <- investigations_cleaned %>%
mutate(name_city_state = sprintf("%s, %s, %s", name, city, st_cd))
id_vector_2 <- unique(investigations_cleaned$name_city_state)
investigations_cleaned <- investigations_cleaned %>%
mutate(id = match(name_city_state,id_vector_2))
For this note, those were within the same dataset or across two datasets? seems like it could be a threshold issue in the earlier script or needing to do the within-dataset deduplication in that script
Also, issues like DEL'S GRASS FARMS, LTD, SAN ANTONIO, TX, one without apostrophe, one with period after LTD., one without the LTD showing up as unique entries
dedupe_fields = c("derived_businessname",
"derived_businessownername",
"derived_street")
er_matches <- fastLink(dfA = er_todedupe,
dfB = er_todedupe,
varnames = dedupe_fields,
stringdist.match = dedupe_fields,
dedupe.matches = FALSE)
er_deduped = getMatches(dfA = er_todedupe,
dfB = er_todedupe,
fl.out = er_matches)
## add multiple flag
er_wnewid = er_deduped %>%
group_by(dedupe.ids) %>%
mutate(is_multiple_rows = ifelse(n() > 1, TRUE, FALSE)) %>%
ungroup() %>%
rename(derived_biz_id_distinct = dedupe.ids)
Geolocation data:
Main challenge: getting tract identifiers onto the violations data if they don't already exist
Ways to make scope more manageable: possibly focusing on states within TRLA catchment area ('TX', 'MS', 'LA', 'KY', 'AL', 'TN') to have fewer things to geocode and fewer sets of tracts to pull; could also increase geographic unit of analysis to zip code or county, but less interesting; if time, could add housing locations data (in this repo)
DOL staffing:
Main challenge: getting the OPM workforce data into a usable format/investigating granularity of staffing data
Text as data:
Main challenge: data is already acquired so data acquisition straightforward; main goal is using the problem set two text as data content to find interesting patterns/expanding beyond that if relevant and also merging of jobs/violations data
ML/stats:
Main challenge: since not explicitly covered in course content, ML code in sklearn or other packages
Some cleanup of rowbinding code @rebeccajohnson88
## relative path name -- should work regardless of user if we have same directory structure (can sync at the meeting)
OUTPUT_DIR = "../../qss20_finalproj_rawdata/summerwork/raw/"
## absolute pathname has my username in it, eg
/Users/rebeccajohnson/Dropbox/qss20_s21_proj/code
Cleaning of job addresses/geocoding:- maybe @helenma0223234 ?
h2a_combined_2014-2021
and clean as neededCheck ACS codebook and pull additional ACS characteristics for all census tracts: - maybe @Grant-98 and @eunice30718
Fuzzy matching between job orders data and violations data- maybe @camguage
Pulling and rowbind DOL quarterly H-2A disclosures data from 2014 onwards (just csv or excel files and not the addendums)
General link: https://www.dol.gov/agencies/eta/foreign-labor/performance
2014-2020 are in the "H2-A program" section
2021 is up top
qss20_finalproj_rawdata/summerwork/raw
; have argument in the script to either pull from source or not@eunice30718 and @camguage
Geocoding locations of rowbound data (after rowbinding above):
Fuzzy matching between violations and quarterly disclosure data (after rowbinding above):
Script here: https://github.com/rebeccajohnson88/qss20_s21_proj/blob/main/code/11_cleanTRLA_intake.Rmd
Category: adverse party/opponent:
I did the hierarchical coding discussed on slack in this chunk of code, so "missing all" means that a given row had none of the following fields: adverse party organization, lead case ap organization, adverse party name, lead case ap name
About 47% are missing all four fields:
trla_orig = trla_orig %>%
mutate(derived_opponent_consolidated = case_when(!is.na(adverse_party_organization) ~ adverse_party_organization,
is.na(adverse_party_organization) & !is.na(lead_case_ap_organization) ~ lead_case_ap_organization,
is.na(adverse_party_organization) & is.na(lead_case_ap_organization) &
!is.na(adverse_party_name) ~ adverse_party_name,
is.na(adverse_party_organization) & is.na(lead_case_ap_organization) &
is.na(adverse_party_name) & !is.na(lead_case_ap_name) ~ lead_case_ap_name,
TRUE ~ NA_character_),
derived_opponent_source = case_when(!is.na(adverse_party_organization) ~ "AP org",
is.na(adverse_party_organization) & !is.na(lead_case_ap_organization) ~ "Lead AP org",
is.na(adverse_party_organization) & is.na(lead_case_ap_organization) &
!is.na(adverse_party_name) ~ "AP name",
is.na(adverse_party_organization) & is.na(lead_case_ap_organization) &
is.na(adverse_party_name) & !is.na(lead_case_ap_name) ~ "Lead AP name",
TRUE ~ "Missing all"),
derived_is_notemp = case_when(grepl("Social Security Administration|Department of Labor|Workforce", derived_opponent_consolidated) ~ TRUE,
TRUE ~ FALSE)
Intake date: was the pull all cases or pre-2014? there are a non-negligible number in the 1990s and possibly one data entry error that should be recoded to maybe 1991?
Commit with changes here: 26b30b9
Main things I changed were:
Changed fuzzy matching code so that jobs are the "base" data that we merge matches onto--- key thing is we want to retain all jobs and use the presence or NA for matches to code outcomes; previous code used investigations as the base data so lost the majority of jobs that didn't match to an investigation
also noticed that the names function was producing below bug so updated that and reran all the fuzzy matching/deduplication
I stopped around line 330; i think hopefully straightforward to finish setting all flags to FALSE so things aren't rerun; script 7 can then be restricted to the date-based outcome variable coding
outcomes = ['outcome_XX', 'outcome_YY']
df_y = pre_df.select_dtypes(bool)
print("Outcome variables to predict are:" + str(df_y.columns.values))
y1 = list(df_y.iloc[:, 0])
y2 = list(df_y.iloc[:, 1])
# remove the them from the preMatrix ... because that would be too easy!
pre_df = pre_df.select_dtypes(exclude=['bool'])
outcome_compare_TRLA_WHD
and use np.where to define four outcomes: investigated by either, investigated by TRLA only, investigated by WHD only, investigated by bothThe Researcher(s) will produce a final paper and will submit it via email to the DOL Summer Data Challenge address ([email protected]) that will:
present the results of the analysis;
discuss the questions that the research was not able to answer;
identify other gaps in knowledge;
recommend new data collection efforts that should be considered to address these gaps;
propose what the DOL should fund in the future.
The researchers will deliver a draft paper by September 2021. They will deliver a briefing to DOL on the paper in October 2021 on a date yet to be determined.
Currently, the code seems to contain a step to create a modified id if an investigations_group_id spans multiple states. The main issue is:
investigations_deduped_clean
creation on line 255, do the new id creation to create new ids for same name entities that span multiple states- this needs to occur here because otherwise the sampling is with the wrong ids. Then, use that new id to do the sampling of 1 within an idapproved_deduped_clean
around line 208, do the new id creation and sampling within that idQuestions when reading in fuzzy matching outputs to construct outcomes variables:
jobs_group_id
is the final identifier for a unique employer (and safe to drop jobs_group_id_part1 and jobs_group_id_part2 from the df if those were intermediate created for the cross-state stuff)?acs_pred = fread("intermediate/job_combined_acs_premerging.csv")
Next steps:
derived_casedispo = case_when(case_disposition_orig == "Closed" |
case_disposition_more == "CLO" ~ "Closed",
case_disposition_orig == "Pending" |
case_disposition_more == "PEN" ~ "Pending",
case_disposition_orig == "Open" |
case_disposition_more == "ACC" ~ "Open/accepted",
TRUE ~ "Unknown dispo"))
copy a version of this script and name it something like 13_fuzzymatching_TRLA.R
- https://github.com/rebeccajohnson88/qss20_s21_proj/blob/main/code/03_fuzzy_matching.R
work on edits to that script where, in places where that script uses the investigations data, substitute in the TRLA data- we can either touch base to go over general script logic before you dig into it or once you've started making modifications; the basic logic is: (1) dedupe both datasets (for jobs, you can just read in the ones with the job_group_ids added; for TRLA, maybe skip this step), (2) fuzzy match between the two, (3) add back the rows removed during the deduplication
selected_cat_large
(the threshold should make the final unique values in the col around 100)outcome
variables[1]# use the find status function and put into a new column
h2a <- h2a %>%
mutate(status = find_status(CASE_STATUS)) # RStudio is taking forever to view so hard for me to tell if this worked
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.