Search code examples
jsonapivega-lite

Vega-Lite chart from OECD API

I have an api which I would like to use directly within Vegalite, in order for the chart to update automatically. https://stats.oecd.org/SDMX-JSON/data/REVGBR/TOTALTAX.NES/all?startTime=1965&endTime=2021&dimensionAtObservation=allDimensions

However the api doesn't seem to be in a pleasant format.

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.2.json",
  "data": {
    "url": "https://stats.oecd.org/SDMX-JSON/data/REV/NES.TOTALTAX.TAXGDP.OAVG?contentType=json",
    "format": {
      "type": "json",
      "property": "dataSets[0].series['0:0:0:0'].observations"
    },
    "transform": [
      {
        "type": "formula",
        "as": "year",
        "expr": "parseInt(datum.key)"
      },
      {
        "type": "formula",
        "as": "Tax_revenue",
        "expr": "datum.value[0]"
      }
    ]
  },
  "mark": "line",
  "encoding": {
    "x": {"field": "year", "type": "temporal"},
    "y": {"field": "Tax_revenue", "type": "quantitative"}
  }
}

When looking at the data viewer, all that is shown is the values, without the years. The values however are not clean, e.g. [24.855,null]

Is it even possible to get this api to work within vegalite?

Solution

  • It is possible but you need to make assumptions. As you know the query to the API starts with 1965, you can use something like this assuming the years are continuous.

    enter image description here

    {
  "$schema": "https://vega.github.io/schema/vega-lite/v5.2.json",
  "data": {
    "name": "api",
    "url": "https://stats.oecd.org/SDMX-JSON/data/REV/NES.TOTALTAX.TAXGDP.OAVG?contentType=json",
    "format": {
      "type": "json",
      "property": "dataSets[0].series['0:0:0:0'].observations"
    }
  },
  "transform": [
    {"fold": ["0", "1", "2", "3","4","5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53", "54", "55"]},
    {"calculate": "parseInt( datum.key) + 1965", "as": "year"},
    {"calculate": "datum.value[0]", "as": "Tax_revenue"}
  ],
  "mark": "line",
  "encoding": {
    "x": {"field": "year", "type": "nominal"},
    "y": {"field": "Tax_revenue", "type": "quantitative"}
  }
}