Your location join gives some job ads extra salary votes

Your national salary average moved when you added regional rows. Now the market report needs an explanation for why a location transformation changed a pay statistic.

The suspicious line is jobs.explode("locations"). The job location structure is a locations[] array of objects, so expanding it repeats any parent salary across its location associations.

Exploding job locations changes the analytical unit from an advert to a job-location association. Calculate a national mean on those expanded rows and ads with more associations receive more weight.

Keep the search population attached

For Dataspryva's Willhaben Actor, mode selects the collection route. It's a string input; startUrls is an array used with byUrls, which accepts ordinary listing URLs and job URLs, including searches.

That matters because the collection route isn't the record type. A mixed URL collection needs separate job and listing logic.

This Example input collects a bounded job search with detail enrichment and explicitly disables incremental output.

import os
import math
import re
from numbers import Real

import pandas as pd
import duckdb
from apify_client import ApifyClient

run_input = {
    "mode": "searchJobs",
    "query": "",
    "maxItems": 100,
    "includeDetails": True,
    "incrementalMode": False,
}

client = ApifyClient(os.environ["APIFY_TOKEN"])
run = client.actor(
    "dataspryva/willhaben-marketplace-cars-and-real-estate"
).call(run_input=run_input)

if run["status"] != "SUCCEEDED":
    raise RuntimeError(f"Collection ended with {run['status']}")

items = client.dataset(run["defaultDatasetId"]).list_items().items

Keep run_input with your analysis. A capped collection is a collected search population, not an estimate of the entire labour market.

Incremental output answers a different question again: later runs can contain changes rather than the standing population of ads. A salary average over changes measures the ads that changed.

Which fields belong in the parent table?

Job records have recordType: "job"; ordinary listings have "listing". Select the record family before choosing keys or interpreting geography.

For this analysis, we choose jobId as the candidate parent key. The loader will reject missing or repeated values rather than silently substitute another field.

These selected fields determine the identity, geography and salary denominators. Retain them when present: this isn't a promise that every record contains every field.

Field	What you retain	Normalisation and analytical trap
recordType	The "job" or "listing" discriminator	Select explicitly before applying identity rules; a shared dataset doesn't imply a shared schema.
jobId, jobUrl	The candidate parent key and a public absolute job link for traceability	Use nullable strings. Missing or repeated candidate keys block this analysis until resolved.
jobTitle, description	Text for inspecting adverts; full descriptions depend on detail enrichment and availability	Use nullable strings. Restricting text analysis to full descriptions changes its eligible population.
companyId	A value to retain for potential employer grouping	Use a nullable string. An omitted value means employer grouping is unresolved.
salary	The original salary value	Preserve it before deriving a nullable number under an explicit parsing policy.
salaryTimeFrame	The original pay-period label	Use a nullable string. Keep labels separate rather than guessing annual equivalents.
locations[]	An array of location objects	Build a child table. Its length becomes an implicit weight after expansion.
name	The name inside a location object	Use a nullable string. A label isn't automatically a unique geographic identifier.
federalState, country	Geographic labels inside each location object	Use nullable strings. Group them together; unresolved geography needs its own coverage count.

Source attributes that aren't carried are omitted rather than emitted as blanks or null. Detail enrichment can fill full descriptions and other detail fields where published; it doesn't promise universal completeness.

This cell separates record families, preserves raw salary values and checks the chosen parent key within the collected snapshot.

record_type_counts = pd.Series(
    [item.get("recordType") for item in items], dtype="string"
).value_counts(dropna=False)

job_records = [item for item in items if item.get("recordType") == "job"]
listing_records = [
    item for item in items if item.get("recordType") == "listing"
]

job_columns = [
    "recordType", "jobId", "jobUrl", "jobTitle", "description",
    "companyId", "salary", "salaryTimeFrame",
]
jobs = pd.DataFrame.from_records(job_records, columns=job_columns)

for column in job_columns:
    if column != "salary":
        jobs[column] = jobs[column].astype("string")

jobs["salaryTimeFrame"] = (
    jobs["salaryTimeFrame"].str.strip().replace("", pd.NA)
)
jobs["locations"] = pd.Series(
    [item.get("locations", []) for item in job_records], dtype="object"
)

bad_ids = jobs["jobId"].isna() | jobs["jobId"].str.strip().eq("")
if bad_ids.any() or jobs["jobId"].duplicated().any():
    raise ValueError("Resolve missing or duplicate jobId values before analysis")

A repeated identifier needs investigation before deduplication. Overlapping collections and conflicting versions require different decisions; keeping whichever row happened to arrive first would conceal that distinction.

Parse salary without inventing its meaning

A parser can recognise a number without establishing what the number means.

Our policy below accepts finite, non-negative numeric values, plain decimal strings, and explicitly euro-labelled strings using a comma decimal separator and optional grouped thousands. It rejects ranges, qualifiers and other formats.

That's a deliberately narrow acceptance policy. It isn't a claim that the Actor always emits these formats.

This cell keeps rejected values available in salary while deriving salaryValue.

euro_number = r"(?:[0-9]{1,3}(?:\.[0-9]{3})+|[0-9]+)(?:,[0-9]{1,2})?"

def salary_number(value):
    if isinstance(value, Real) and not isinstance(value, bool):
        return float(value) if math.isfinite(value) and value >= 0 else None
    if not isinstance(value, str):
        return None

    token = value.strip()
    if re.fullmatch(r"[0-9]+(?:\.[0-9]{1,2})?", token):
        parsed = float(token)
    else:
        match = re.fullmatch(
            rf"(?:€\s*(?P<prefix>{euro_number})|"
            rf"(?P<suffix>{euro_number})\s*EUR)",
            token,
        )
        if match is None:
            return None
        amount = match.group("prefix") or match.group("suffix")
        parsed = float(amount.replace(".", "").replace(",", "."))

    return parsed if math.isfinite(parsed) else None

jobs["salaryValue"] = jobs["salary"].map(salary_number).astype("Float64")

Blindly removing punctuation can turn a range or a qualified offer into an apparently precise salary. The decimal point rarely files a complaint.

Even successful parsing doesn't establish currency for unlabelled values, gross versus net pay, a floor versus a fixed amount, or comparable working hours. The resulting means are arithmetic diagnostics until those meanings have been checked.

Missing locations stay on the parent table

An omitted array shouldn't remove the advert from the national parent population. It should prevent that advert from acquiring an invented regional association.

We treat omission as an empty child collection. Unexpected array or object shapes raise an error rather than quietly manufacturing geography.

This cell creates the child table and exposes how many salary votes expansion would assign to each advert.

geo_fields = ["name", "federalState", "country"]
location_rows = []

for job_id, places in zip(jobs["jobId"], jobs["locations"]):
    if not isinstance(places, list):
        raise TypeError("locations must be an array when present")
    for place in places:
        if not isinstance(place, dict):
            raise TypeError("Each location must be an object")
        location_rows.append({
            "jobId": job_id,
            **{field: place.get(field) for field in geo_fields},
        })

locations = pd.DataFrame(
    location_rows, columns=["jobId", *geo_fields]
)
for column in locations.columns:
    locations[column] = (
        locations[column].astype("string").str.strip().replace("", pd.NA)
        if column != "jobId"
        else locations[column].astype("string")
    )

jobs["associationCount"] = (
    jobs["jobId"].map(locations.groupby("jobId").size())
    .fillna(0).astype("Int64")
)

The table relationship makes the competing denominators visible.

jobs: advert identity and salary
  |
  +--> locations: job-location associations
         |
         +--> regional distinct-ad counts

national salary summaries stay with jobs

An association can still lack a usable state or country. Array presence and regional assignability are separate coverage measures.

Regional ad counts aren't additive

For regional tables, count distinct adverts associated with each geography. Counting location rows instead would also count repeated associations within the same region.

This DuckDB query retains the association count beside the distinct-ad count so you can inspect that difference.

regional = duckdb.sql("""
    SELECT
        country,
        federalState,
        count(*) AS location_associations,
        count(DISTINCT jobId) AS distinct_job_ads
    FROM locations
    WHERE country IS NOT NULL
      AND federalState IS NOT NULL
    GROUP BY country, federalState
""").df()

An advert associated with several states contributes to each relevant regional count. Summing those counts doesn't recover a national distinct-ad total.

The appropriate label is “distinct job ads associated with the region”. It doesn't establish where an eventual employee will work, or how many vacancies the advert represents.

Separate extra weight from lost coverage

Expansion can change a salary mean through separate mechanisms: repeating salaries for multi-location ads and excluding ads without location associations.

Compare the full parent table, parents with associations, and expanded associations. The middle population isolates the coverage change before you examine the weighting change.

The Actor behind this worked example is Willhaben Marketplace, Cars & Real Estate Listings Scraper.

This cell builds the comparison within observed time-frame labels and records the exclusions separately.

expanded = locations.merge(
    jobs[["jobId", "salaryValue", "salaryTimeFrame"]],
    on="jobId",
    how="left",
    validate="many_to_one",
)

def salary_summary(frame, prefix):
    return (
        frame.dropna(subset=["salaryTimeFrame"])
        .groupby("salaryTimeFrame")
        .agg(
            rows=("jobId", "size"),
            parsed_salary_rows=("salaryValue", "count"),
            mean_salary=("salaryValue", "mean"),
        )
        .add_prefix(prefix)
    )

located_jobs = jobs.loc[jobs["associationCount"].gt(0)]
salary_comparison = (
    salary_summary(jobs, "ads_")
    .join(salary_summary(located_jobs, "located_ads_"), how="outer")
    .join(salary_summary(expanded, "associations_"), how="outer")
)

region_job_ids = locations.loc[
    locations[["federalState", "country"]].notna().all(axis=1), "jobId"
]
coverage = pd.Series({
    "job_ads": len(jobs),
    "ads_without_location_associations": jobs["associationCount"].eq(0).sum(),
    "ads_without_assignable_region": (~jobs["jobId"].isin(region_job_ids)).sum(),
    "ads_without_salary": jobs["salary"].isna().sum(),
    "ads_with_unparsed_salary": (
        jobs["salary"].notna() & jobs["salaryValue"].isna()
    ).sum(),
    "ads_without_salary_time_frame": jobs["salaryTimeFrame"].isna().sum(),
})

Compare the located-parent mean with the expanded-association mean to examine the effect of location multiplicity on the same located parent population.

Compare the full-parent mean with the located-parent mean to examine the exclusion of parents without associations.

If the expanded mean happens to match the parent mean, that doesn't validate the counting unit. Different weights can produce the same average.

Missing salary changes the population again

A mean excludes missing numeric values. Here, that leaves the subset whose salary passed your parser, within an observed salaryTimeFrame group.

That's narrower than “ads publishing salary”: rejected formats remain published values. Keep ads_without_salary and ads_with_unparsed_salary separate, because source availability and parser coverage are different problems.

The exclusions can overlap. An advert can lack both a usable salary and a time-frame label, so adding those coverage counts would create another misleading total.

A defensible salary table carries the collected ad count, the parsed salary count and the interpretation of the pay basis. A regional table additionally carries the number of ads that couldn't be assigned geographically.

If you group by companyId, report how many ads have a usable value. Counting distinct values alone doesn't establish the number of hiring organisations without checking how those values relate to employers and intermediaries.

An advert is still not a vacancy

These records describe public advertisements and their attached attributes. They don't establish completed hires, realised pay, unique vacancies or the full population of available work.

Location associations describe the geography attached to an advert. They don't divide its recruitment demand among those places.

Your national salary average needs a population and a pay definition. Your regional counts need a membership rule.

A longer location array isn't a larger employer payroll.