Vega Lite (v4) - What transform do I use for this?

Question

I am trying to create a graph that displays the count of appearances of different keywords per month (to find out which keywords are in 'popular season').

Currently, I am attempting to display a single keyword as an area, with a dotted line of "total" records overlaid so we can see the "relative" volume of keyword appearances.

Picture of my graph

My issue is I've accomplished this one way, but I'd like to figure out transforms to do it better as I keep creating this view.

{
  "$schema": "https://vega.github.io/schema/vega-lite/v4.json",
  "title": "Kingstree Plumbing Calls by Keyword",
  "width": "container",
  "height": "container",
  "transform": [ 
      {"filter" : "datum['Call type'] !== 'Non-Client'"},
      {
        
        "calculate" : "indexof(lower(datum['Call Reason'] === null ? '' : datum['Call Reason']), 'sump') !== -1 ? 'Sump' : 'Total'", "as": "filtered"

      },
      {
        "calculate" : "month(datum['Time Created'])", "as": "rawMonth"
      },
      {
        "aggregate": [{"op": "count", "as": "calls"}],
        "groupby": ["rawMonth", "filtered"]
      },
      {
        "impute": "calls",
        "key": "rawMonth",
        "groupby" : ["filtered"], 
        "method" : "value",
        "value": 0,
        "keyvals": {"start": 0, "stop": 12}
    },
    
    {"calculate" : "datetime(2022, datum.rawMonth)", "as": "newMonth"}
    
    ],
  

"encoding" : {
  
   "x": {
        "timeUnit" :"month",
        "field" : "newMonth",
        "type": "temporal",
        "title": ""
    },
    "y": {
      "field" : "calls",
      "type": "quantitative",
        "axis": {
            "tickMinStep": 1,
            "tickCount" : 10
          },
      "title" : "Call Volume"
    }
},
 "layer" : [{
  "transform"  : [{"filter" : "datum.filtered === 'Sump'"}],
  "layer" : [
    {
      "mark" : {
        "type" : "area",
        "interpolate" : "monotone",
        "line" : true
      }
    },
    {
      "mark" : {
        "type" : "circle"
      }
    }
  ]},
  {
    "transform"  : [{"filter" : "datum.filtered === 'Total'"}],
    "mark" : {
      "type": "line",
      "interpolate" :"monotone",
      "strokeDash": [
          10,
          15
        ],
        "color" : "#aaaaaa"
    }
  }]
}

In the code above, I've created a table of records that contain the volume "calls" of keyword appearances for 1 source, flagging each record with its source. I then use a filter per layer to display one flag each. For example my data should have been transformed to look like this:

Month	calls	keyword
april	12	total
april	4	keyword1
may	25	total
may	3	keyword1

But what I would instead want is a table that looks like this

Month	total	keyword1
april	12	4
may	25	3

From what I am reading, this could be accomplished by perhaps a stack or fold transform but I am having trouble understanding how I might use them correctly.

As a note, the impute and rawMonth stuff is just my solution to the aggregation creating null values instead of zero, it's turning the date into a number and using that to impute the missing one, and then turning it BACK into a date (as I do not know how to impute date fields)

(also I am using v4 because airtable)

Thanks!

Answer 1

To convert your example table 1 to table 2, try pivot transform.

Vega-lite:

{
  "pivot": "keyword",
  "groupby": ["Month"],
  "value": "calls"
}

Vega:

{
  "type": "pivot",
  "groupby": ["Month"],
  "field": "keyword",
  "value": "calls"
}