NYC Squirrel Census (JSON API) → Flat CSV

View on GitHub

What

Flatten the 2018 NYC Central Park Squirrel Census — published as a JSON array by a REST/Socrata API — into a tidy CSV, taking the union of keys across heterogeneous records, coercing native JSON booleans to text, skipping a nested geo object, and parsing a separator-less date, all in one template.

Why interesting

A huge share of public data now ships as a JSON array of objects from an API, not as a CSV — and turning that into a spreadsheet-ready CSV is the daily jq/pandas chore bxp removes. This famous dataset packs four real JSON-shaped problems into one file: (1) heterogeneous records — most sightings omit primary_fur_color and location, so the objects don't share a key set and a naive keys() on the first record loses columns; (2) native JSON booleans ("running": false) that a CSV needs as text, not as a language-specific literal; (3) a nested object geocoded_column: { type, coordinates } that has no flat CSV representation; (4) a date stored as the separator-less digit blob "10142018". bxp reads the array directly: it scans once for the union of keys (first-seen order), materialises one record at a time, and collapses absent keys to empty cells.

Edge cases sourced from.

• Socrata / SODA JSON API returns a top-level array of objects with per-record optional fields and nested geocoded_column location objects — the standard shape of NYC/US open-data APIs.
• The census date field is MMDDYYYY with no separators ("10142018").

Data source. NYC Open Data — 2018 Central Park Squirrel Census (vfnx-vebw). Public domain. (This slice: 8 real sightings hand-picked for their key sets — two omit primary_fur_color and location, one omits only location, and the rest are complete, so the union-of-keys behaviour is visible in eight lines. The records carry 26 to 33 keys each.)

The trick

(see sample.json):

• Declare JSON input with file_type_in: "json". bxp scans the whole array once, takes the union of keys as the column set, and streams one record at a time — keys absent from a record become "".
• Skip the nested object. geocoded_column flattens to "" (bxp doesn't descend into nested {}/[]), so the template reads the flat fields the API also provides: the lon/lat (x/y) and the hectare grid cell. The coordinates pass through with every significant digit intact — bxp copies numeric-looking strings verbatim, it doesn't round them to a fixed precision.
• Parse the blob date with DATE_CONVERT([date], 'MMDDYYYY', 'YYYY-MM-DD').
• Coerce booleans: [running] etc. arrive as JSON false/true and land as the text false/true.

At full scale

bash fetch-full.sh          # downloads all ~3,023 sightings as JSON into ./full/
bxp-cli --config full.json  # flattens every sighting → CSV

Final result

The first two sightings have no primary_fur_color or location keys at all; the third does. bxp lines them up under one schema:

squirrel_id,date,shift,lat,lon,hectare,fur_color,location,running,eating,foraging
37F-PM-1014-03,2018-10-14,PM,40.7940823884086,-73.9561344937861,37F,,,false,false,false
21B-AM-1019-04,2018-10-19,AM,40.7837825208444,-73.9688574691102,21B,,,false,false,false
11B-PM-1014-08,2018-10-14,PM,40.775533619083,-73.9742811484852,11B,Gray,Above Ground,false,false,false

— missing fields are genuinely empty (not a misaligned shift), the digit-blob date is ISO, and the booleans are plain text. That CSV opens straight in a spreadsheet, with no jq and no per-record key bookkeeping.

Sample data

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

sample.json (config)sample.in.jsonsample.csvx (result)

{
  // NYC Central Park Squirrel Census — published as a JSON API (Socrata), not a
  // CSV. It is the textbook "JSON array → flat CSV" job, and the records carry
  // four real JSON-shaped headaches: (1) OPTIONAL keys — most sightings omit
  // `primary_fur_color`/`location`, so the objects are heterogeneous; (2) native
  // JSON BOOLEANS (`running: false`) rather than text; (3) a NESTED object
  // `geocoded_column: { type, coordinates }`; (4) a date stored as the
  // separator-less digit blob `"10142018"`. Getting this into a spreadsheet
  // normally means a jq/pandas script; bxp reads the array straight to CSV.
  conversion_templates: {
    squirrel_census_to_csv: {
      data_dir:         ".",
      // TRICK 0 — declare JSON input. bxp scans the whole array once, takes the
      // UNION of keys (first-seen order) as the column set, and materialises one
      // record at a time. Keys absent from a record collapse to "".
      file_type_in:     "json",
      file_pattern_in:  ".in.json",
      file_type_out:    "csv",
      file_pattern_out: ".csvx",

      input_schema: {
        $id:    "[unique_squirrel_id]",
        // TRICK 1 — date is `MMDDYYYY` with NO separators ("10142018"). One
        // DATE_CONVERT turns it into ISO-8601.
        $date:  "DATE_CONVERT([date], 'MMDDYYYY', 'YYYY-MM-DD')",
        $shift: "[shift]",

        // TRICK 2 — the nested `geocoded_column` object flattens to "" (bxp does
        // not descend into nested {}/[]), so read the flat top-level fields the
        // API also provides: the high-precision lon/lat (`x`/`y`) and the
        // `hectare` grid cell. The coordinates pass through with every
        // significant digit intact (no float round-trip).
        $lat:   "[y]",
        $lon:   "[x]",
        $grid:  "[hectare]",

        // TRICK 3 — OPTIONAL keys. Most sightings omit these; absent → "".
        $fur:   "[primary_fur_color]",
        $where: "[location]",

        // TRICK 4 — native JSON booleans (`false`/`true`) are coerced to the
        // text "false"/"true" — no quoting surprises downstream.
        $running:  "[running]",
        $eating:   "[eating]",
        $foraging: "[foraging]"
      },

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

      output_schema: {
        squirrel_id: "$id",
        date:        "$date",
        shift:       "$shift",
        lat:         "$lat",
        lon:         "$lon",
        hectare:     "$grid",
        fur_color:   "$fur",
        location:    "$where",
        running:     "$running",
        eating:      "$eating",
        foraging:    "$foraging"
      }
    }
  }
}

[{"x":"-73.9561344937861","y":"40.7940823884086","unique_squirrel_id":"37F-PM-1014-03","hectare":"37F","shift":"PM","date":"10142018","hectare_squirrel_number":"3","combination_of_primary_and":"+","running":false,"chasing":false,"climbing":false,"eating":false,"foraging":false,"kuks":false,"quaas":false,"moans":false,"tail_flags":false,"tail_twitches":false,"approaches":false,"indifferent":false,"runs_from":false,"geocoded_column":{"type":"Point","coordinates":[-73.9561344937861,40.7940823884086]},":@computed_region_f5dn_yrer":"19",":@computed_region_yeji_bk3q":"4",":@computed_region_92fq_4b7q":"19",":@computed_region_sbqj_enih":"13"}
,{"x":"-73.9688574691102","y":"40.7837825208444","unique_squirrel_id":"21B-AM-1019-04","hectare":"21B","shift":"AM","date":"10192018","hectare_squirrel_number":"4","combination_of_primary_and":"+","running":false,"chasing":false,"climbing":false,"eating":false,"foraging":false,"kuks":false,"quaas":false,"moans":false,"tail_flags":false,"tail_twitches":false,"approaches":false,"indifferent":false,"runs_from":false,"geocoded_column":{"type":"Point","coordinates":[-73.9688574691102,40.7837825208444]},":@computed_region_f5dn_yrer":"19",":@computed_region_yeji_bk3q":"4",":@computed_region_92fq_4b7q":"19",":@computed_region_sbqj_enih":"13"}
,{"x":"-73.9742811484852","y":"40.775533619083","unique_squirrel_id":"11B-PM-1014-08","hectare":"11B","shift":"PM","date":"10142018","hectare_squirrel_number":"8","primary_fur_color":"Gray","combination_of_primary_and":"Gray+","location":"Above Ground","above_ground_sighter":"10","running":false,"chasing":true,"climbing":false,"eating":false,"foraging":false,"kuks":false,"quaas":false,"moans":false,"tail_flags":false,"tail_twitches":false,"approaches":false,"indifferent":false,"runs_from":false,"geocoded_column":{"type":"Point","coordinates":[-73.97428114848522,40.775533619083]},":@computed_region_f5dn_yrer":"19",":@computed_region_yeji_bk3q":"4",":@computed_region_92fq_4b7q":"19",":@computed_region_sbqj_enih":"13"}
,{"x":"-73.9596413903948","y":"40.7903128889029","unique_squirrel_id":"32E-PM-1017-14","hectare":"32E","shift":"PM","date":"10172018","hectare_squirrel_number":"14","age":"Adult","primary_fur_color":"Gray","combination_of_primary_and":"Gray+","color_notes":"Nothing selected as Primary. Gray selected as Highlights. Made executive adjustments.","running":false,"chasing":false,"climbing":false,"eating":true,"foraging":true,"kuks":false,"quaas":false,"moans":false,"tail_flags":false,"tail_twitches":false,"approaches":false,"indifferent":false,"runs_from":true,"geocoded_column":{"type":"Point","coordinates":[-73.9596413903948,40.7903128889029]},":@computed_region_f5dn_yrer":"19",":@computed_region_yeji_bk3q":"4",":@computed_region_92fq_4b7q":"19",":@computed_region_sbqj_enih":"13"}
,{"x":"-73.9702676472613","y":"40.7762126854894","unique_squirrel_id":"13E-AM-1017-05","hectare":"13E","shift":"AM","date":"10172018","hectare_squirrel_number":"5","age":"Adult","primary_fur_color":"Gray","highlight_fur_color":"Cinnamon","combination_of_primary_and":"Gray+Cinnamon","location":"Above Ground","specific_location":"on tree stump","running":false,"chasing":false,"climbing":false,"eating":false,"foraging":true,"kuks":false,"quaas":false,"moans":false,"tail_flags":false,"tail_twitches":false,"approaches":false,"indifferent":false,"runs_from":false,"geocoded_column":{"type":"Point","coordinates":[-73.9702676472613,40.7762126854894]},":@computed_region_f5dn_yrer":"19",":@computed_region_yeji_bk3q":"4",":@computed_region_92fq_4b7q":"19",":@computed_region_sbqj_enih":"13"}
,{"x":"-73.9722500196844","y":"40.7742879599026","unique_squirrel_id":"11D-AM-1010-03","hectare":"11D","shift":"AM","date":"10102018","hectare_squirrel_number":"3","age":"Adult","primary_fur_color":"Gray","highlight_fur_color":"Cinnamon","combination_of_primary_and":"Gray+Cinnamon","location":"Above Ground","above_ground_sighter":"30","running":false,"chasing":false,"climbing":true,"eating":false,"foraging":false,"other_activities":"grooming","kuks":false,"quaas":false,"moans":false,"tail_flags":false,"tail_twitches":false,"approaches":false,"indifferent":true,"runs_from":false,"geocoded_column":{"type":"Point","coordinates":[-73.9722500196844,40.7742879599026]},":@computed_region_f5dn_yrer":"19",":@computed_region_yeji_bk3q":"4",":@computed_region_92fq_4b7q":"19",":@computed_region_sbqj_enih":"13"}
,{"x":"-73.9670628558161","y":"40.7734991420941","unique_squirrel_id":"12I-AM-1013-01","hectare":"12I","shift":"AM","date":"10132018","hectare_squirrel_number":"1","age":"Adult","primary_fur_color":"Cinnamon","highlight_fur_color":"White","combination_of_primary_and":"Cinnamon+White","location":"Ground Plane","above_ground_sighter":"FALSE","specific_location":"on tree roots","running":false,"chasing":false,"climbing":false,"eating":false,"foraging":false,"other_activities":"sitting","kuks":false,"quaas":false,"moans":false,"tail_flags":false,"tail_twitches":true,"approaches":false,"indifferent":false,"runs_from":true,"geocoded_column":{"type":"Point","coordinates":[-73.9670628558161,40.77349914209411]},":@computed_region_f5dn_yrer":"19",":@computed_region_yeji_bk3q":"4",":@computed_region_92fq_4b7q":"19",":@computed_region_sbqj_enih":"13"}
,{"x":"-73.9756976076707","y":"40.7753897331918","unique_squirrel_id":"10A-PM-1014-01","hectare":"10A","shift":"PM","date":"10142018","hectare_squirrel_number":"1","age":"Adult","primary_fur_color":"Gray","highlight_fur_color":"Black, Cinnamon, White","combination_of_primary_and":"Gray+Black, Cinnamon, White","color_notes":"Gray & White selected as Primary. Black & Cinnamon selected as Highlights. Made executive adjustments.","location":"Ground Plane","above_ground_sighter":"FALSE","specific_location":"in b/w trees","running":false,"chasing":false,"climbing":false,"eating":false,"foraging":true,"kuks":false,"quaas":false,"moans":false,"tail_flags":false,"tail_twitches":true,"approaches":false,"indifferent":true,"runs_from":false,"geocoded_column":{"type":"Point","coordinates":[-73.9756976076707,40.7753897331918]},":@computed_region_f5dn_yrer":"19",":@computed_region_yeji_bk3q":"4",":@computed_region_92fq_4b7q":"19",":@computed_region_sbqj_enih":"13"}]

squirrel_id,date,shift,lat,lon,hectare,fur_color,location,running,eating,foraging
37F-PM-1014-03,2018-10-14,PM,40.7940823884086,-73.9561344937861,37F,,,false,false,false
21B-AM-1019-04,2018-10-19,AM,40.7837825208444,-73.9688574691102,21B,,,false,false,false
11B-PM-1014-08,2018-10-14,PM,40.775533619083,-73.9742811484852,11B,Gray,Above Ground,false,false,false
32E-PM-1017-14,2018-10-17,PM,40.7903128889029,-73.9596413903948,32E,Gray,,false,true,true
13E-AM-1017-05,2018-10-17,AM,40.7762126854894,-73.9702676472613,13E,Gray,Above Ground,false,false,true
11D-AM-1010-03,2018-10-10,AM,40.7742879599026,-73.9722500196844,11D,Gray,Above Ground,false,false,false
12I-AM-1013-01,2018-10-13,AM,40.7734991420941,-73.9670628558161,12I,Cinnamon,Ground Plane,false,false,false
10A-PM-1014-01,2018-10-14,PM,40.7753897331918,-73.9756976076707,10A,Gray,Ground Plane,false,false,true

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