Summary: NEW VERSION is at https://esriurl.com/agoschoolcompStorymap metadata page: URL forthcoming Possible K-12 Next Generation Science standards addressed:Grade level(s) 6-8: Standard MS-LS4-4 - Biological Evolution: Unity and Diversity - Construct an explanation based on evidence that describes how genetic variations of traits in a population increase some individuals’ probability of surviving and reproducing in a specific environmenGrade level(s) 6-8: Standard MS-LS4-6 - Biological Evolution: Unity and Diversity - Use mathematical representations to support explanations of how natural selection may lead to increases and decreases of specific traits in populations over timeGrade level(s) 6-8: Standard MS-ESS1-2 - Earth’s Place in the Universe - Develop and use a model to describe the role of gravity in the motions within galaxies and the solar systemGrade level(s) 6-8: Standard MS-ESS2-4 - Earth’s Systems - Develop a model to describe the cycling of water through Earth’s systems driven by energy from the sun and the force of gravityGrade level(s) 9-12: Standard HS-PS1-2 - Matter and Its Interactions - Construct and revise an explanation for the outcome of a simple chemical reaction based on the outermost electron states of atoms, trends in the periodic table, and knowledge of the patterns of chemical propertiesGrade level(s) 9-12: Standard HS-LS2-1 - Ecosystems: Interactions, Energy, and Dynamics - Use mathematical and/or computational representations to support explanations of factors that affect carrying capacity of ecosystems at different scalesGrade level(s) 9-12: Standard HS-LS4-2 - Biological Evolution: Unity and Diversity - Construct an explanation based on evidence that the process of evolution primarily results from four factors: (1) the potential for a species to increase in number, (2) the heritable genetic variation of individuals in a species due to mutation and sexual reproduction, (3) competition for limited resources, and (4) the proliferation of those organisms that are better able to survive and reproduce in the environment.Most frequently used words:competitionesrihsstateApproximate Flesch-Kincaid reading grade level: 10.3. The FK reading grade level should be considered carefully against the grade level(s) in the NGSS content standards above.

Dataset, GDB, and Online Map created by Renee Haley, NMCDC, May 2023 DATA ACQUISITION PROCESS

Scope and purpose of project: New Mexico is struggling to maintain its healthcare workforce, particularly in Rural areas. This project was undertaken with the intent of looking at flows of healthcare workers into and out of New Mexico at the most granular geographic level possible. This dataset, in combination with others (such as housing cost and availability data) may help us understand where our healthcare workforce is relocating and why.

The most relevant and detailed data on workforce indicators in the United States is housed by the Census Bureau's Longitudinal Employer-Household Dynamics, LEHD, System. Information on this system is available here:

https://lehd.ces.census.gov/

The Job-to-Job flows explorer within this system was used to download the data. Information on the J2J explorer can ve found here:

https://j2jexplorer.ces.census.gov/explore.html#1432012

The dataset was built from data queried with the LED Extraction Tool, which allows for the query of more intersectional and detailed data than the explorer. This is a link to the LED extraction tool:

https://ledextract.ces.census.gov/

The geographies used are US Metro areas as determined by the Census, (N=389). The shapefile is named lehd_shp_gb.zip, and can be downloaded under this section of the following webpage: 5.5. Job-to-Job Flow Geographies, 5.5.1. Metropolitan (Complete). A link to the download site is available below:

https://lehd.ces.census.gov/data/schema/j2j_latest/lehd_shapefiles.html

DATA CLEANING PROCESS

This dataset was built from 8 non intersectional datasets downloaded from the LED Extraction Tool.

Separate datasets were downloaded in order to obtain detailed information on the race, ethnicity, and educational attainment levels of healthcare workers and where they are migrating.

Datasets included information for the four separate quarters of 2021. It was not possible to download annual data, only quarterly. Quarterly data was summed in a later step to derive annual totals for 2021.

4 datasets for healthcare workers moving OUT OF New Mexico, with details on race, ethnicity, and educational attainment, were downloaded. 1 contained information on educational attainment, 2 contained information on 7 racial categories identifying as non- Hispanic, 3 contained information on those same 7 categories also identifying as Hispanic, and 4 contained information for workers identifying as white and Hispanic.

4 datasets for healthcare worker moving INTO New Mexico, with details on race, ethnicity, and educational attainment, were downloaded with the same details outlined above.

Each dataset was cleaned according to Data Template which kept key attributes and discarded excess information. Within each dataset, the J2J Indicators reflecting 6 different types of job migration were totaled in order to simplify analysis, as this information was not needed in detail.

After cleaning, each set of 4 datasets for workers moving INTO New Mexico were joined. The process was repeated for workers moving OUT OF New Mexico. This resulted 2 main datasets.

These 2 main datasets still listed all of the variables by each quarter of 2021. Because of this the data was split in JMP, so that attributes of educational attainment, race and ethnicity, of workers migrating by quarter were moved from rows to columns. After this, summary columns for the year of 2021 were derived. This resulted in totals columns for workers identifying as: 6 separate races and all ethnicities, all races and Hispanic, white-Hispanic, and workers of 6 different education levels, reflecting how many workers of each indicator migrated to and from metro areas in New Mexico in 2021.

The data split transposed duplicate rows reflecting differing worker attributes within the same metro area, resulting in one row for each metro area and reflecting the attributes in columns, thus resulting in a mappable dataset.

The 2 datasets were joined (on Metro Area) resulting in one master file containing information on healthcare workers entering and leaving New Mexico.

Rows (N=389) reflect all of the metro areas across the US, and each state. Rows include the 5 metro areas within New Mexico, and New Mexico State.

Columns (N=99) contain information on worker race, ethnicity and educational attainment, specific to each metro area in New Mexico.

78 of these rows reflect workers of specific attributes moving OUT OF the 5 specific Metro Areas in New Mexico and totals for NM State. This level of detail is intended for analyzing who is leaving what area of New Mexico, where they are going to, and why.

13 Columns reflect each worker attribute for healthcare workers moving INTO New Mexico by race, ethnicity and education level. Because all 5 metro areas and New Mexico state are contained in the rows, this information for incoming workers is available by metro area and at the state level - there is less possability for mapping these attributes since it was not realistic or possible to create a dataset reflecting all of these variables for every healthcare worker from every metro area in the US also coming into New Mexico (that dataset would have over 1,000 columns and be unmappable). Therefore this dataset is easier to utilize in looking at why workers are leaving the state but also includes detailed information on who is coming in.

The remaining 8 columns contain geographic information.

GIS AND MAPPING PROCESS

The master file was opened in Arc GIS Pro and the Shapefile of US Metro Areas was also imported

The excel file was joined to the shapefile by Metro Area Name as they matched exactly

The resulting layer was exported as a GDB in order to retain null values which would turn to zeros if exported as a shapefile.

This GDB was uploaded to Arc GIS Online, Aliases were inserted as column header names, and the layer was visualized as desired.

SYSTEMS USED

MS Excel was used for data cleaning, summing NM state totals, and summing quarterly to annual data.

JMP was used to transpose, join, and split data.

ARC GIS Desktop was used to create the shapefile uploaded to NMCDC's online platform.

VARIABLE AND RECODING NOTES

Summary of variables selected for datasets downloaded focused on educational attainment:

J2J Flows by Educational Attainment

Summary of variables selected for datasets downloaded focused on race and ethnicity:

J2J Flows by Race and Ethnicity

Note: Variables in Datasets 1 through 4 downloaded twice, once for workers coming into New Mexico and once for those leaving NM. VARIABLE: LEHD VARIABLE DEFINITION LEHD VARIABLE NOTES DETAILS OR URL FOR RAW DATA DOWNLOAD

Geography Type - State Origin and Destination State

Data downloaded for worker migration into and out of all US States

Geography Type - Metropolitan Areas Origin and Dest Metro Area

Data downloaded for worker migration into and out of all US Metro Areas

NAICS sectors North American Industry Classification System Under Firm Characteristics Only downloaded for Healthcare and Social Assistance Sectors

Other Firm Characteristics No Firm Age / Size Detail Under Firm Characteristics Downloaded data on all firm ages, sizes, and other details.

Worker Characteristics Education, Race, Ethnicity

Non Intersectional data aside from Race / Ethnicity data.

Sex Gender

0 - All Sexes Selected

Age Age

A00 All Ages (14-99)

Education Education Level E0, E1, E2, E3, 34, E5 E0 - All Education Categories, E1 - Less than high school, E2 - High school or equivalent, no college, E3 - Some college or Associate’s degree, E4 - Bachelor's degree or advanced degree, E5 - Educational attainment not available (workers aged 24 or younger)

Dataset 1 All Education Levels, E1, E2, E3, E4, and E5

RACE

A0, A1, A2, A3, A4, A5 OPTIONS: A0 All Races, A1 White Alone, A2 Black or African American Alone, A3 American Indian or Alaska Native Alone, A4 Asian Alone, A5 Native Hawaiian or Other Pacific Islander Alone, SDA7 Two or More Race Groups

ETHNICITY

A0, A1, A2 OPTIONS: A0 All Ethnicities, A1 Not Hispanic or Latino, A2 Hispanic or Latino

Dataset 2 All Races (A0) and All Ethnicities (A0)

Dataset 3 6 Races (A1 through A5) and All Ethnicities (A0)

Dataset 4 White (A1) and Hispanic or Latino (A1)

Quarter Quarter and Year

Data from all quarters of 2021 to sum into annual numbers; yearly data was not available

Employer type Sector: Private or Governmental

Query included all healthcare sector workflows from all employer types and firm sizes from every quarter of 2021

J2J indicator categories Detailed types of job migration

All options were selected for all datasets and totaled: AQHire, AQHireS, EE, EES, J2J, J2JS. Counts were selected vs. earnings, and data was not seasonally adjusted (unavailable).

NOTES AND RESOURCES

The following resources and documentation were used to navigate the LEHD and J2J Worker Flows system and to answer questions about variables:

https://lehd.ces.census.gov/data/schema/j2j_latest/lehd_public_use_schema.html

https://www.census.gov/history/www/programs/geography/metropolitan_areas.html

https://lehd.ces.census.gov/data/schema/j2j_latest/lehd_csv_naming.html

Statewide (New