Your salary coverage can fall before employers change

Your salary-coverage chart drops after a routine refresh. Before you explain why employers became less transparent, check what happened around df.dropna(subset=["salaryMin"]).

Handshake omits empty fields, including salary fields when pay is missing. When an entire batch lacks a key, pandas can produce a frame without that column. A later column intersection, conditional load or row filter can quietly change the population behind your chart.

An omitted salary key is still a job in your denominator.

Capture the population before measuring pay

The Actor reads public campus job pages and employer profiles. For this analysis, a job record represents a retrieved public posting, identified by jobId, rather than an employer, an application or a confirmed vacancy filled.

Start with a job search that doesn't select for published pay.

import os
import json
import re

import pandas as pd
import duckdb
from apify_client import ApifyClient

run_input = {
    "mode": "searchJobs",
    "includeDescriptions": True,
    "onlyNewJobs": False,
    "maxItems": 50,
}

client = ApifyClient(os.environ["APIFY_TOKEN"])
run = client.actor(
    "dataspryva/handshake-campus-jobs-and-employer-showcase"
).call(run_input=run_input)

if run["status"] != "SUCCEEDED":
    raise RuntimeError(f"Collection did not succeed: {run['status']}")

items = client.dataset(run["defaultDatasetId"]).list_items().items
raw = pd.DataFrame.from_records(items)

Keep run_input and the dataset identifier with your reporting artefact. Discovery mode, filters and the return cap define which postings could enter the calculation.

minSalary retains jobs whose published maximum meets the threshold. salaryPeriod restricts jobs to a published pay period. Those inputs select on the information you're trying to measure.

Similarly, onlyNewJobs changes the population to previously unseen IDs. That supports monitoring new postings, but it doesn't reproduce a snapshot of previously collected jobs.

Your denominator needs a name before it needs a percentage: “retrieved public postings under these inputs” is defensible. “The campus jobs market” is considerably more ambitious.

How does an absent key change a table?

Pandas infers columns from keys present anywhere in the supplied records. A key missing from some rows becomes a missing value; a key missing from every row doesn't create a column.

Example: these batches differ in salary availability, so their inferred column lists differ too.

example_paid_batch = pd.DataFrame.from_records([
    {
        "jobId": "Example paid job",
        "salary": "$95,000 - $120,000 a year",
    }
])
example_unpaid_batch = pd.DataFrame.from_records([
    {"jobId": "Example job without published pay"}
])

example_column_sets = {
    "with_pay": example_paid_batch.columns.tolist(),
    "without_pay": example_unpaid_batch.columns.tolist(),
}

Inference alone hasn't corrupted anything. Combining frames with an outer column union can preserve the information.

The failure comes later: keeping only common columns, skipping batches that lack salaryMin, or counting only rows that survive numeric parsing.

Returned job
    |
    +-- salary key absent --> retain job, record absence
    |
    +-- salary key present --> retain job, validate value
                                  |
                                  +-- parse failure --> retain job, flag failure

A fixed schema preserves the column. A presence flag preserves why its value is missing. You need both if someone will ask what changed.

Read the pay fields as different evidence

These are the fields that matter most to this report. Empty attributes are omitted; the conditions below explain additional reasons for absence. The storage types are analytical choices, rather than guarantees about every incoming value.

Field	Contents and availability	Nullable storage	Analytical trap
salary	Pay as published, potentially formatted text; absent when pay is missing	pandas string; SQL VARCHAR	Text can establish that pay information was extracted without yielding a usable numeric range
salaryMin, salaryMax	Structured bounds in the listing's currency	pandas Float64; SQL DOUBLE	Requiring both bounds excludes postings with only partial pay information
salaryCurrency	Currency associated with the advertised amounts	pandas string; SQL VARCHAR	A symbol in free text isn't a safe substitute for this field
paySchedule	Whether the amount is per year, month or hour	pandas string; SQL VARCHAR	A pooled wage average can mix incompatible units
salaryType	An additional salary attribute without a supplied codebook	JSON text	A numeric value isn't permission to invent category labels
ownershipType	Employer-profile ownership: Public, Private, Nonprofit or Government; optional	pandas string; SQL VARCHAR	Missing ownership isn't a private employer, and job records aren't the ownership population
roleGroups	An array available through rich search cards; other discovered rows may lack it	JSON text, preserving the array	Restricting analysis to populated role groups selects on discovery coverage
descriptionHtml	Full job-description HTML when descriptions are enabled	pandas string; SQL VARCHAR	Missing content can reflect collection settings; populated HTML doesn't guarantee structured salary fields

Role groups appear on the 15 rich cards per search page. Their absence therefore has a collection mechanism beyond an employer choosing not to supply a category.

Don't use roleGroups.notna() as a convenient eligibility filter for the main salary chart. That creates a different population before you've compared any wages.

For an ownership comparison, collect employer profiles with includeProfiles enabled. Match them through employerId, validate the intended join cardinality and retain unmatched jobs.

Also distinguish the question being asked: a posting-weighted comparison gives employers with more retrieved postings more weight. An employer-weighted comparison requires an employer denominator.

Parse salary text without manufacturing a wage

Keep salary unchanged. Use a text parser as an additional check, rather than a fallback that silently overwrites structured values.

This deliberately narrow parser recognises a dollar-denominated range followed by an explicit period; unmatched strings remain unparsed.

amount_pattern = r"(?:\d{1,3}(?:,\d{3})+|\d+)(?:\.\d+)?"
range_pattern = re.compile(
    rf"\A\$(?P<low>{amount_pattern})\s*[-–]\s*"
    rf"\$(?P<high>{amount_pattern}) a "
    rf"(?P<period>year|month|hour)\Z"
)

salary_text = raw.reindex(columns=["salary"])["salary"].astype("string")
text_ranges = salary_text.str.extract(range_pattern)

for bound in ("low", "high"):
    text_ranges[bound] = pd.to_numeric(
        text_ranges[bound].str.replace(",", "", regex=False),
        errors="coerce",
    ).astype("Float64")

A failed match means “outside this parser's accepted format”, not “no published pay”.

The parser also doesn't identify currency from $. Nor should it annualise hourly pay: that requires assumptions about paid hours and working weeks that these fields don't supply.

For a wage comparison, retain currency and period as grouping dimensions. Treat a range midpoint as a chosen summary of an advertised interval, never as observed earnings.

Enforce types while retaining absence

The loader below preserves selected raw strings, serialises arrays without guessing their element schema, and gives structured bounds nullable numeric types.

It also records key presence before missing values become indistinguishable in a table.

sql_types = {
    "jobId": "VARCHAR",
    "employerId": "VARCHAR",
    "salary": "VARCHAR",
    "salaryMin": "DOUBLE",
    "salaryMax": "DOUBLE",
    "salaryCurrency": "VARCHAR",
    "paySchedule": "VARCHAR",
    "ownershipType": "VARCHAR",
    "roleGroups": "JSON",
    "salaryType": "JSON",
    "descriptionHtml": "VARCHAR",
    "postedDate": "TIMESTAMPTZ",
    "scrapedAt": "TIMESTAMPTZ",
}

typed = raw.reindex(columns=list(sql_types)).copy()
parse_failed = pd.Series(False, index=typed.index, dtype="boolean")

for field, sql_type in sql_types.items():
    original = typed[field]
    if sql_type == "DOUBLE":
        typed[field] = pd.to_numeric(
            original, errors="coerce"
        ).astype("Float64")
    elif sql_type == "TIMESTAMPTZ":
        typed[field] = pd.to_datetime(
            original, errors="coerce", utc=True
        )
    elif sql_type == "JSON":
        values = [record.get(field) for record in items]
        if field == "roleGroups":
            if any(v is not None and not isinstance(v, list) for v in values):
                raise TypeError("roleGroups must be an array when present")
        typed[field] = pd.Series(
            [pd.NA if v is None else json.dumps(v) for v in values],
            index=typed.index,
            dtype="string",
        )
    else:
        typed[field] = original.astype("string")

    if sql_type in {"DOUBLE", "TIMESTAMPTZ"}:
        parse_failed |= original.notna() & typed[field].isna()

typed["amount_key_present"] = pd.Series(
    [any(k in r for k in ("salary", "salaryMin", "salaryMax")) for r in items],
    index=typed.index,
    dtype="boolean",
)
typed["range_keys_present"] = pd.Series(
    ["salaryMin" in r and "salaryMax" in r for r in items],
    index=typed.index,
    dtype="boolean",
)
typed["range_values_usable"] = (
    typed["salaryMin"].notna()
    & typed["salaryMax"].notna()
    & typed["salaryMax"].ge(typed["salaryMin"])
).fillna(False)
typed["parse_failed"] = parse_failed

Keep items as the raw evidence alongside this projection. Coercion makes invalid numbers and dates queryable as missing values; the failure flag prevents that convenience from erasing a quality problem.

UTC is the loader's normalisation policy. Review timezone assumptions before treating a timezone-free timestamp as an exact instant.

ownershipType remains nullable here to support a shared projection. Its presence in the schema doesn't enrich job records with employer-profile information.

Which denominator belongs on the chart?

The worked source is our Handshake campus jobs Actor. Its public posting data supports several distinct measures, and each needs its own label.

“Any amount-bearing key present” measures extraction presence. “Both range keys present” measures structural completeness. “Usable ordered bounds” adds successful numeric conversion and a range-order check.

These aren't interchangeable definitions of transparency.

Create the table from the fixed types, insert the batch together and calculate each measure against all retrieved rows.

flag_fields = (
    "amount_key_present",
    "range_keys_present",
    "range_values_usable",
    "parse_failed",
)
sql_types.update({field: "BOOLEAN" for field in flag_fields})

connection = duckdb.connect()
column_definitions = ", ".join(
    f'"{field}" {sql_type}' for field, sql_type in sql_types.items()
)
connection.execute(f"CREATE TABLE jobs ({column_definitions})")
connection.register("incoming_jobs", typed)
connection.execute("INSERT INTO jobs SELECT * FROM incoming_jobs")

coverage = connection.execute("""
    SELECT
        count(*) AS retrieved_postings,
        avg(amount_key_present::INTEGER) AS amount_key_coverage,
        avg(range_keys_present::INTEGER) AS complete_range_key_coverage,
        avg(range_values_usable::INTEGER) AS usable_range_coverage,
        count(*) FILTER (WHERE parse_failed) AS parse_failure_rows
    FROM jobs
""").fetchdf()

connection.close()

An empty retrieval produces undefined coverage rather than a reassuring zero. Explicit nulls, if encountered, can have key presence without usable values; that discrepancy deserves inspection.

For repeated snapshots, check jobId uniqueness within each snapshot before aggregating. Across snapshots, the same job belongs to each relevant observation period, so deduplicating globally would erase its history.

Don't fill missing bounds with zero. That inserts invented wages into your distribution.

Dropping those rows has a different limitation: your median then describes postings with usable reported pay. It can't describe wages across all retrieved postings without assumptions about the missing population.

The refresh still can't explain employer intent

Public discovery isn't a census of every opportunity. The search index is cached, and newly posted jobs can take a few days to appear through keyword, role or city discovery; direct URLs return the current public record.

A changed coverage rate can therefore reflect changed posting membership, extraction presence, parsing success or collection settings. Compare retained job IDs across snapshots as well as the full retrieved populations, and report how many matched postings remain.

Missing structured pay also doesn't establish that no pay language appears in descriptionHtml. Any description-based measure needs a separate extraction definition and validation.

Public pay-field absence can't establish an employer's intent or a legal compliance finding. These records don't establish every fact needed for either conclusion.

Before you defend “employers became less transparent”, defend “these are the same kinds of postings, counted the same way”.