Data Preparation
Purpose
Every figure on the analysis pages comes from one table. This page documents how that table was built: what was pulled, what was kept, what was removed, and what was repaired. Anyone reading the conclusions should be able to judge how much weight the evidence can carry.
Data Source
The panel is built from the WRDS jobs_2026 dataset supplied for Module 3: 504,208 postings across 134 columns, distributed as twenty-one Parquet parts with a manifest, per-column coverage report and checksums.
Modules 1 and 2 used the MET Employability Career Match API. The analysis moved to the supplied dataset in Module 3 and measured the two sources against each other on the exact scope before moving forward:
| Field | MET API panel | Module 3, as supplied | Module 3, after cleaning |
|---|---|---|---|
| Postings in scope | 6,199 | 7,074 | 7,074 |
| Skills listed | 93% | 100% | 100% |
| US state recorded | 28% | 92% | 73% |
| Minimum education level | 59%, parsed from text | 69% | 69%, a coded field |
| Work arrangement recorded | 48% | 65% | 44% |
| Annual salary usable | 19% | 25% | 25% |
| Years of experience | 66%, parsed from text | 56% | 24%, a numeric field |
| Occupation code (SOC) | 98% | 33% | 33%, and unreliable |
The third and fourth columns differ because several supplied fields encode missing data as a value rather than as a blank. Those cases are set out under Field Normalization below.
The switch is not an improvement on every axis. Occupational coding is substantially sparser and, as found upon inspection, unreliable where it is present. Experience is recorded on fewer postings once placeholder values are removed, though what remains is a number rather than a figure that is extracted with a regular expression. Geography was the single largest limitation of the earlier panel and it improves by a factor of two and a half even after cleaning, the skill fields arrive split into named tools, specialisations, transferable skills and certifications, and education and experience stop being the team’s estimates and become the provider’s.
Industry and Career Scope
| Industry code | NAICS 2022 — 523, Securities, Commodity Contracts, and Other Financial Investments and Related Activities |
| Career pathway | Business Intelligence / Data Analyst |
| Date window | Postings published on or after 1 September 2025 |
| Build date | 4 October 2026 |
The date window keeps the analysis current: the source spans postings back to 2014, and hiring requirements from a decade ago describe a market the target user is not applying into. In practice the window costs little — only 192 in-scope postings predate it.
Why this industry, and why a three-digit code
Module 1 anchored the project to NAICS 5221, Depository Credit Intermediation. That code returned 59 postings in total, too few to support compensation or skill-gap analysis, and lead to the analysis being re-anchored to NAICS 523 after measuring the neighbouring codes. NAICS 523 accounts for roughly two-thirds of all analytics postings within Finance and Insurance, so it is where the market actually is.
The assignment asks for four- or six-digit codes where possible. The earlier source carried no four-digit granularity beneath 523; this dataset does expose NAICS4 through NAICS6, and those columns are retained in the panel for finer analysis, but 523 remains the scope boundary so that the project stays comparable across modules.
Role Definition
The assignment allows title keywords, SOC or O*NET mappings, skills logic, or a combination, provided the choice is explained. For this analysis, the team uses a combination, and this dataset makes the case stronger than before: occupation codes are present on only a third of postings, so an occupation-only filter would discard two postings in three unseen.
A posting is kept when either condition holds:
- its SOC occupation matches one of: data scientist, database architect, statistician, mathematician, operations research, market research analyst, financial and investment analyst, credit analyst, management analyst
- or its title contains one of: analyst, analytics, data, business intelligence, BI, reporting, SQL, data science, machine learning
Information-security roles are then removed. They match the word “analyst” but belong to a different career pathway, and including them would distort the skill profile the product reports.
Filter Funnel
| Stage | Postings |
|---|---|
| Source dataset | 504,208 |
| In NAICS 523 | 24,914 |
| Matched the role definition | 8,747 |
| Removed: information-security roles | −76 |
| Removed: no publication date | −1,405 |
| Removed: published before 1 September 2025 | −192 |
| Removed: duplicate identifiers | −0 |
| Removed: missing an essential field | −0 |
| Analytical panel | 7,074 |
Postings without a publication date are the largest single exclusion. They cannot be placed inside the date window, and keeping them would mean reporting a twelve-month market using rows of unknown age.
Cleaning Decisions
Cleaning stays conservative. Where a value is implausible a blank is inserted into that field rather than an assumed a replacement, and where a whole posting is questionable it is recorded as a gap. Deleting rows narrows the sample; blanking one field costs only that field.
The rules below were written for Module 2 by Katie Bowles and carried over unchanged; only the column names differ, and the salary handling is extended because this dataset quotes pay in several periods.
Duplicates. Deduplicated on the posting identifier. None remained.
Rows missing essentials. A posting is dropped if it has no identifier, title, employer or publication date. After the date filter, none failed this test.
Dates. Parsed to a single format. Where an expiry date preceded the publication date the expiry would be blanked, since one of the two must be wrong and it cannot be determined which is incorrect; no postings were affected.
Salary. The provider’s SALARY column turned out to be a free-text summary whose value is an empty placeholder on most rows, so it is kept for reference but never used as a figure. The usable numbers are the structured range, and that range is quoted in different periods — 1,751 annual, 315 hourly, 18 monthly. Each is validated against bounds appropriate to its own period rather than a single annual rule, which had been silently deleting hourly rates as “implausible”. Reversed ranges are swapped, zeros treated as missing, and 20 rows were cleared as out of range across all periods.
Hourly and monthly rates are kept in their own columns and are not converted to annual figures, because the conversion requires an assumption about hours worked that the postings do not supply. ANNUAL_SALARY_MID — the midpoint of the annual range — is the field used for salary analysis, and it is populated on 1,741 postings.
Experience and education. Taken directly from the provider’s coded fields. Unlike the earlier panel these are not team estimates, so no parsing decisions were needed.
Skills. Arrive as arrays and are stored as JSON-style list strings — for example ["Microsoft Excel", "SQL (Programming Language)"] — across four fields: all skills, named software, specialised skills and transferable skills, plus certifications. Names are not yet normalised, so casing and synonyms remain distinct; that normalisation belongs to the skill-gap work, where it has an analytical purpose. Code reading these fields must parse the list rather than splitting on a delimiter.
Remote status. Never inferred, and category labels are normalised because the source spells the same category two ways. A posting with no remote label is an absence of information, not evidence of an office requirement.
Location. Text fields trimmed of stray whitespace. This dataset carries no coordinates, so the latitude and longitude check from the earlier pipeline does not apply; geography is carried by state, city and metropolitan area.
Types. Every column is cast explicitly — dates, booleans, numbers and categories — so later analysis cannot silently treat a number as text.
Field Normalization
Integration testing in Module 3 exposed four defects that coverage statistics alone do not reveal. Each repair is written to a separate column, so the provider’s original value stays auditable.
The job title is overwritten on part of the panel. On 2,165 postings — 31 percent — TITLE_NAME holds the occupation name instead of the advertised title, so a vacancy posted as “Sr Financial Analyst, AWS GTM Finance” reads simply as “Management Analysts”. Counting titles from that field produced an apparent leading title of “management analysts” with 1,991 postings, which is an artifact of the coding and not a fact about the market. TITLE_CLEAN is rebuilt from the raw title text instead, raising the number of distinct titles from 2,396 to 4,000.
Occupational coding is wrong on identifiable rows, not merely sparse. Several postings titled Senior Financial Analyst are coded as Models, Demonstrators, and Product Promoters. The field is kept and the defect reported rather than deleted, because it is the direct justification for defining roles by title as well as by code.
The state field is not only a state field. It mixes full names with two-letter codes, so New York appeared twice — as “New York” (514) and as “NY” (212) — and it carries 1,202 postings labelled “United States” and 178 labeled “Remote”, a country and a work arrangement. STATE_CLEAN expands the codes and discards the non-geographic values, which lowers real coverage from an apparent 92 percent to an honest 73 percent.
Two fields encode “missing” as a value. REMOTE_TYPE_NAME carries a literal “Unknown” on 1,442 postings alongside 2,506 true nulls, and spells the office category both “Onsite” and “On-site”; REMOTE_CLEAN merges the spellings and treats “Unknown” as missing, leaving 44 percent labelled. MIN_YEARS_EXPERIENCE reports zero years on 2,288 postings, but on 2,143 of them the stated maximum is exactly one — a fixed “0 to 1” band attached to postings titled Senior Financial Analyst, Finance Manager and Accounting Manager. That is a provider default, and left in place it produced a median requirement of zero years across the panel. MIN_YEARS_EXP_CLEAN removes the band, leaving 1,702 postings with a stated requirement and a median of three years.
Several headline numbers fall as a result. That is the intended outcome: the higher figures counted placeholders as data.
A fifth defect surfaced in Module 5, in the employer field. On 397 postings it holds an applicant-tracking hostname rather than a company name, on 229 it names a staffing agency or job aggregator rather than the hiring firm, and one employer appeared under three spellings. The provider’s own COMPANY_IS_STAFFING flag cannot be used to find these: it is set on 93 of 7,074 rows and contradicts itself, marking TD as staffing while giving JPMorgan Chase both values. EMPLOYER_CLEAN, EMPLOYER_UNIDENTIFIED and IS_AGENCY record the repair without dropping any rows, so employer concentration is measured on the 6,448 postings where a hiring firm can actually be identified.
Data Quality and Limitations
Coverage in the panel of 7,074 postings:
| Field | Populated | Share |
|---|---|---|
| Skills | 7,074 | 100% |
| Named software skills | 7,074 | 100% |
| US state, after normalization | 5,135 | 73% |
| Minimum education level | 4,913 | 69% |
| Work arrangement, after normalization | 3,126 | 44% |
| Occupation code (SOC 2021) | 2,357 | 33% |
| Annual salary usable | 1,741 | 25% |
| Years of experience, after normalization | 1,702 | 24% |
Four limitations shape how the analysis pages should be read.
Salary is disclosed on one posting in four. Employers who publish pay ranges are not a random sample — pay-transparency law applies in some states and not others — so the disclosed group likely skews toward those jurisdictions and toward larger employers. Every salary figure in this project describes that subset, not the market.
Occupation codes are sparse and partly wrong. Only a third of postings carry a SOC occupation, and within that third the classification misassigns senior finance roles badly enough to be caught by eye. Any breakdown by occupation describes the coded third, and comparisons between occupations are ratios within that subset rather than counts of the whole market.
Titles are dependable, classifications are not. Raw title text is present on every posting and is what is counted. Provider-normalised titles and occupation codes are used only where the analysis is explicitly about data quality.
Experience survives on one posting in four. The provider supplies a value more often than that, but most of the additional rows carry a placeholder band rather than a requirement. It is a reliable number where present, but absence is common and should not be read as “no experience required”.
Against that, geography and skills are close to complete, which means the location analysis and the skill-gap work — the parts the product most depends on — rest on much firmer ground than the compensation analysis does.
Output
| Analytical panel | data/processed/career_market_panel.csv — 7,074 rows, 57 columns |
| Data dictionary | outputs/data_dictionary.md and outputs/data_dictionary.csv |
| Build log | outputs/m3_panel_build_log.md |
| Normalization log | outputs/m3_normalization_log.md |
| Employer cleanup log | outputs/m5_employer_cleanup_log.md |
| Baseline numbers | outputs/m3_baseline_numbers.md |
The source Parquet files are kept locally and excluded from version control; at roughly a gigabyte they are not needed to reproduce the analysis from the panel.
Reproducing This Step
- Download the Module 3 dataset into
data/raw/m3_dataset/ pip install -r requirements.txtpython notebooks/19_build_panel_wrds.py— scope filter, role filter, cleaningpython notebooks/21_fix_salary.py— period-aware salary validationpython notebooks/23_normalize_panel.py— field normalizationpython notebooks/24_clean_employers.py— employer cleanuppython notebooks/22_data_dictionary_m3.py— data dictionarypython notebooks/15_market_baseline_figures.py— baseline figures
The scripts must run in that order: normalization depends on the salary step, and the dictionary and figures both read the normalized panel.
All work runs on the course AWS EC2 environment.