Skip to content

Inside Airbnb NYC Listings → Analytics Schema

View on GitHub

What

Reshape Inside Airbnb's public NYC scrape into an analytics CSV with short room-type codes, a regulatory-status column, and a visible sentinel for the redacted price field.

Why interesting

Inside Airbnb is the de-facto open dataset for short-term-rental regulation research, and it ships with three real-world pain points: (1) the visualisations/listings.csv endpoint redacts the price column entirely yet ships it as an empty field rather than removing the column, (2) listing names contain raw commas inside double quotes ("Perfect for Your Parents, With Garden") so a naive split-on-comma parser silently shifts every column rightward, (3) NYC's Local Law 18 (2023) introduced short-term-rental registration and the license column is one of three states ("", "Exempt", or "OSE-STRREG-NNNNNNN") — most listings are still unregistered.

Edge cases sourced from.

Data source. Inside Airbnb — New York City, 2026-02-13 scrape (this slice: 12 real listings hand-picked so that each room type meets each of the three regulatory states, plus the two comma-inside-quotes names and one never-reviewed listing).

At full scale

The committed sample.csv is a 12-row teaching slice; the real scrape is the full current NYC listing set. Pull it and run the same template against the whole thing:

bash fetch-full.sh          # downloads ./full/listings.csv (~6 MB)
bxp-cli --config full.json  # processes every NYC listing

Measured on the reference machine (ReleaseFast, 8 cores):

metric value
input 36,445 listings (RFC-4180 records), no column shift
output 36,616 rows — 154 listings split, see below
wall time ~0.07 s
peak RSS ~17 MB
unlicensed 31,645 rows (86%)
exempt 2,686 rows
registered 2,285 rows
price redacted 36,471 rows (every real listing — the endpoint strips every price; the other 145 rows are the split fragments below, whose columns are shifted)

Why the two row counts differ

Both numbers are right, they just count different things. 36,445 is what a strict RFC-4180 parser sees: it treats a newline inside a quoted field as part of the value and keeps reading. 36,616 is what bxp emits, because a newline always ends a record here — lazy-quote semantics, a deliberate design decision, not a parsing bug (see Not planned in the roadmap). 154 listings on this scrape carry a newline inside their quoted description — most span two lines, a few up to six — so they arrive as 171 extra rows, and the run says so: 308 row(s) had an unbalanced quote — treated as literal text. Their columns are shifted, so 145 of them show a stray value in price_usd and 144 fall into the unlicensed bucket by default — a 0.5% skew that does not move the headline rate. If you need those descriptions rejoined, strip the newlines before the conversion.

86% of the full 36k listings are unlicensed — Local Law 18's enforcement gap, straight out of the reg_status column the template derives. The quoted-comma names (e.g. Perfect for Your Parents, With Garden & Patio) stay intact in a single field, exactly as TRICK 0 promises — commas are handled by the quoting rules; only newlines break a record.

The tricks

See inline comments in sample.json:

  1. CSV double-quote escaping — csv_text_quote_in: "double" so names like "Maison des Sirenes1,bohemian, luminous apartment" don't shift every following column.
  2. room_type enum — Entire home/apt / Private room / Hotel room / Shared room → short codes via REMAP() + a named map.
  3. Redacted price sentinel — COALESCE([price], '<price-redacted>') so the gap is visible in every row instead of silently empty.
  4. last_review empty == "never reviewed" — kept as empty on purpose; not every absent value is an error.
  5. Three-state regulatory column — a CASE map over [license] derives unlicensed / exempt / registered.

Final result

Three raw shapes of the license column become one column you can filter on, and the redacted price stops looking like a value that happens to be missing:

raw license                →  reg_status    price
""                         →  unlicensed    <price-redacted>
"Exempt"                   →  exempt        <price-redacted>
"OSE-STRREG-0006194"       →  registered    <price-redacted>

"Perfect for Your Parents, With Garden & Patio" also survives as one field — the comma inside the quotes does not shift every column to its right.

A downstream tool with no per-field validation sees only "license is a string" and never flags the missing-value pattern; here WHERE reg_status = 'unlicensed' is the whole compliance query.

Trace it in the GUI

Open sample.csvx and sort by reg_status, then click a price_usd cell: the trace pane shows COALESCE falling through to the sentinel, which is what distinguishes "the endpoint redacts this" from "this listing is free".

Sample data

Run it with bxp-cli --config ./sample.json --template airbnb_listings_to_analytics:

{
  // Inside Airbnb's "visualisations/listings.csv" endpoint redacts price
  // for the public scrape (rate-limiting risk). The /data/ endpoint has it
  // but requires registration. We treat empty price as a real-world signal,
  // not an error: COALESCE downstream gives `<price-redacted>` so the gap is
  // visible at a glance instead of silently zero.
  maps: {
    room_type_short: {
      "Entire home/apt": "ENTIRE",
      "Private room":    "PRIVATE",
      "Hotel room":      "HOTEL",
      "Shared room":     "SHARED"
    }
  },
  conversion_templates: {
    airbnb_listings_to_analytics: {
      data_dir:           ".",
      file_pattern_in:    ".csv",
      file_pattern_out:   ".csvx",
      // TRICK 0 — Names like `"Perfect for Your Parents, With Garden"`
      // embed commas, so the CSV uses double-quote escaping. Without this
      // every quoted name shifts every downstream column by one field.
      csv_text_quote_in:  "double",
      csv_text_quote_out: "double",

      input_schema: {
        $listing_id: "[id]",
        $name:       "[name]",
        $host:       "[host_name]",
        $district:   "[neighbourhood_group]",
        $area:       "[neighbourhood]",
        $lat:        "[latitude]",
        $lon:        "[longitude]",

        // TRICK 1 — room_type free-form text → 4-value enum via REMAP + named map.
        $room_type:  "REMAP([room_type], 'room_type_short')",

        // TRICK 2 — empty price gets a visible sentinel. Salesforce/Airtable
        // would silently drop empty numerics; this preserves the gap.
        $price_usd:  "COALESCE([price], '<price-redacted>')",

        $min_nights: "[minimum_nights]",
        $reviews:    "[number_of_reviews]",

        // TRICK 3 — last_review can be empty (never-reviewed listing). Keep
        // empty as empty (don't fall through to "<missing>" — empty here
        // is a meaningful "no reviews yet").
        $last_review:"[last_review]",

        $reviews_pm: "[reviews_per_month]",
        $avail_365:  "[availability_365]",

        // TRICK 4 — NYC requires short-term rentals to register since 2023.
        // license is one of: empty (unlicensed), "Exempt" (statutory carve-out),
        // or "OSE-STRREG-NNNNNNN" (registered). Normalize into a 3-state
        // status field — "unlicensed" rows are the regulatory risk. CASE maps
        // the two sentinel values to labels with "registered" as the default.
        $reg_status: "CASE([license], '', 'unlicensed', 'Exempt', 'exempt', 'registered')"
      },

      row_rules: [ { when: "1 = 1", rows: [ {} ] } ],

      output_schema: {
        listing_id:    "$listing_id",
        name:          "$name",
        host:          "$host",
        district:      "$district",
        area:          "$area",
        lat:           "$lat",
        lon:           "$lon",
        room_type:     "$room_type",
        price_usd:     "$price_usd",
        min_nights:    "$min_nights",
        reviews:       "$reviews",
        last_review:   "$last_review",
        reviews_pm:    "$reviews_pm",
        avail_365:     "$avail_365",
        reg_status:    "$reg_status"
      }
    }
  }
}

id,name,host_id,host_profile_id,host_name,neighbourhood_group,neighbourhood,latitude,longitude,room_type,price,minimum_nights,number_of_reviews,last_review,reviews_per_month,calculated_host_listings_count,availability_365,number_of_reviews_ltm,license
2595,Skylit Studio Oasis | Midtown Manhattan Sanctuary,2845,1462506326262395414,Jennifer,Manhattan,Midtown,40.75356,-73.98559,Entire home/apt,,30,47,2022-06-21,0.24,3,256,0,
6872,Uptown Sanctuary w/ Private Bath (Month to Month),16104,1462506784874222530,Kae,Manhattan,East Harlem,40.80107,-73.94255,Private room,,30,2,2025-10-07,0.04,2,83,1,
7097,"Perfect for Your Parents, With Garden & Patio",17571,1462506848265336113,Jane,Brooklyn,Fort Greene,40.69194,-73.97389,Private room,,2,423,2025-09-23,2.16,2,0,26,OSE-STRREG-0000008
8490,"Maison des Sirenes1,bohemian, luminous apartment",25183,1462507010207005813,Nathalie,Brooklyn,Bedford-Stuyvesant,40.684555843449075,-73.93963415175676,Entire home/apt,,30,189,2023-10-16,0.94,2,220,0,
11943,Country space in the city,45445,1462507517388692132,Harriet,Brooklyn,Flatbush,40.63702,-73.96327,Private room,,30,0,,,1,0,0,
12937,"1 Stop to Midtown! Private Bedroom, Landmark House",50124,1462507638639660367,Orestes,Queens,Long Island City,40.74757,-73.94571,Private room,,1,470,2026-01-14,2.46,1,140,37,OSE-STRREG-0000923
15385,"Very, very cozy place",60252,1462507969318892407,Cristina,Brooklyn,Williamsburg,40.71211,-73.96397,Private room,,31,63,2025-07-25,0.33,1,97,3,
77765,Superior @ Box House,417504,1462517884839893571,The Box House Hotel,Brooklyn,Greenpoint,40.73777,-73.95366,Hotel room,,1,72,2025-10-11,0.40,29,0,4,Exempt
80700,Loft w/ Terrace @ Box House Hotel,417504,1462517884839893571,The Box House Hotel,Brooklyn,Greenpoint,40.73777,-73.95366,Private room,,1,10,2022-10-22,0.06,29,0,0,Exempt
94783,"Beautiful, Bright’s, Warm & Spacious 1.5BR Apt",473113,1462519279507587472,Keishera,Brooklyn,Crown Heights,40.6736,-73.9551,Entire home/apt,,3,178,2026-01-03,0.99,1,96,17,Exempt
151199,Astoria/LIC  Private Home located near train,722320,1462513776231025449,Gigi,Queens,Astoria,40.7573,-73.91488,Entire home/apt,,1,568,2026-02-07,3.23,1,234,38,OSE-STRREG-0002372
248865,Loft Suite,417504,1462517884839893571,The Box House Hotel,Brooklyn,Greenpoint,40.73756,-73.9535,Entire home/apt,,1,36,2024-07-13,0.21,29,0,0,Exempt

listing_id,name,host,district,area,lat,lon,room_type,price_usd,min_nights,reviews,last_review,reviews_pm,avail_365,reg_status
2595,Skylit Studio Oasis | Midtown Manhattan Sanctuary,Jennifer,Manhattan,Midtown,40.75356,-73.98559,ENTIRE,<price-redacted>,30,47,2022-06-21,0.24,256,unlicensed
6872,Uptown Sanctuary w/ Private Bath (Month to Month),Kae,Manhattan,East Harlem,40.80107,-73.94255,PRIVATE,<price-redacted>,30,2,2025-10-07,0.04,83,unlicensed
7097,"Perfect for Your Parents, With Garden & Patio",Jane,Brooklyn,Fort Greene,40.69194,-73.97389,PRIVATE,<price-redacted>,2,423,2025-09-23,2.16,0,registered
8490,"Maison des Sirenes1,bohemian, luminous apartment",Nathalie,Brooklyn,Bedford-Stuyvesant,40.684555843449075,-73.93963415175676,ENTIRE,<price-redacted>,30,189,2023-10-16,0.94,220,unlicensed
11943,Country space in the city,Harriet,Brooklyn,Flatbush,40.63702,-73.96327,PRIVATE,<price-redacted>,30,0,,,0,unlicensed
12937,"1 Stop to Midtown! Private Bedroom, Landmark House",Orestes,Queens,Long Island City,40.74757,-73.94571,PRIVATE,<price-redacted>,1,470,2026-01-14,2.46,140,registered
15385,"Very, very cozy place",Cristina,Brooklyn,Williamsburg,40.71211,-73.96397,PRIVATE,<price-redacted>,31,63,2025-07-25,0.33,97,unlicensed
77765,Superior @ Box House,The Box House Hotel,Brooklyn,Greenpoint,40.73777,-73.95366,HOTEL,<price-redacted>,1,72,2025-10-11,0.4,0,exempt
80700,Loft w/ Terrace @ Box House Hotel,The Box House Hotel,Brooklyn,Greenpoint,40.73777,-73.95366,PRIVATE,<price-redacted>,1,10,2022-10-22,0.06,0,exempt
94783,"Beautiful, Bright’s, Warm & Spacious 1.5BR Apt",Keishera,Brooklyn,Crown Heights,40.6736,-73.9551,ENTIRE,<price-redacted>,3,178,2026-01-03,0.99,96,exempt
151199,Astoria/LIC  Private Home located near train,Gigi,Queens,Astoria,40.7573,-73.91488,ENTIRE,<price-redacted>,1,568,2026-02-07,3.23,234,registered
248865,Loft Suite,The Box House Hotel,Brooklyn,Greenpoint,40.73756,-73.9535,ENTIRE,<price-redacted>,1,36,2024-07-13,0.21,0,exempt

Full-scale & binary files (run it on the complete dataset): fetch-full.sh · full.json.