Argmax in case of ties - Vegalite chart

Elastic Stack Kibana
1

Hi All,

I have Elasticsearch index whose structure is as shown below.

My requirement is I want to create chart with highest duration (ms) corresponding to highest frequency for each SNI# by using argmax function in Vegalite script . As my data contains ties for highest frequency for each SNI#, not getting highest duration (ms) for highest frequency in each SNI#.

Ex: for SNI# (0x03 ESC (FR-1B)) , highest frequency is 6 ; maximum duration with h
highest frequency is 96 but getting 93.

Your suggestions please to achieve my objective.

Script:

{
  "$schema": "https://vega.github.io/schema/vega-lite/v4.json",
  "autosize": {"type": "fit", "contains": "padding"},
  "data": {
    "url": {
      "index": "index01",
      "%context%": true,
      "body": {"size": 1000}
    },
    "format": {"property": "hits.hits"}
  }, 
 "transform": [{
    "sort": [
      {"field": "ECU_SNI", "order": "descending"},
      {"field": "Frequency", "order": "descending"},
      {"field": "ms", "order": "descending"}
    ],
    "window": [{
      "op": "rank",
      "as": "rank"
    }],
    "groupby": ["ECU_SNI"]
  },
  {"filter": "datum.rank == 1"}],
   "encoding": {
    "x": {
      "field": "_source.ECU_SNI","title": "ECU_SNI"
      "scale": {"padding": 0},
      "axis": {"labelAngle": -25, "labelLimit": 1000, "title": "ECU-SNI"}
    }
  },
  "layer": [
    {
      "mark": {
        "color": "#85A9C5",
        "type": "bar",
        "tooltip": true,
        "point": false
      },
      "encoding": {
        "y": {
          "aggregate": {"argmax": "_source.Frequency"},
          "field": "_source.ms",
          "type": "quantitative",
          "title": "[ms]",
          "axis": {"titleColor": "#85C5A6"}
        },
        "y2": {"aggregate": "max", "field": "_source.Frequency","title": "Frequency"}
      }
    },
    {
      "mark": {
        "opacity": 10,
        "type": "line",
        "width": 4,
        "color": "#85C5A6",
        "tooltip": true
      },
      "encoding": {
        "y": {
          "aggregate": "max",
          "field": "_source.Frequency",
          "title": "Frequency",
          "axis": {"titleColor": "#85A9C5"}
        }
      }
    }
  ],
  "resolve": {"scale": {"y": "independent"}},
  "config": {}
}

Index data:

{"index": {}}
{"SNI#": "0x03 ESC (FR-1B)", "ms": 93, "Frequency": 2, "avgFreq": 95, "vehicle": "TE1484421", "filedate": "2022-10-18", "communication_type": "FR1B", "threshold": 200, "ECU_SNI": "FR1B - 0x03 ESC (FR-1B)", "chart3_flag": 0, "chart2_flag": 0.0}
{"index": {}}
{"SNI#": "0x03 ESC (FR-1B)", "ms": 95, "Frequency": 1, "avgFreq": 0, "vehicle": "TE1484421", "filedate": "2022-10-18", "communication_type": "FR1B", "threshold": 200, "ECU_SNI": "FR1B - 0x03 ESC (FR-1B)", "chart3_flag": 0, "chart2_flag": 0.0}
{"index": {}}
{"SNI#": "0x03 ESC (FR-1B)", "ms": 96, "Frequency": 2, "avgFreq": 0, "vehicle": "TE1484421", "filedate": "2022-10-18", "communication_type": "FR1B", "threshold": 200, "ECU_SNI": "FR1B - 0x03 ESC (FR-1B)", "chart3_flag": 1, "chart2_flag": 1.0}
{"index": {}}
{"SNI#": "0x15 Airbag_6D (FR-1B)", "ms": 0, "Frequency": 6, "avgFreq": 0, "vehicle": "TE1484421", "filedate": "2022-10-18", "communication_type": "FR1B", "threshold": 200, "ECU_SNI": "FR1B - 0x15 Airbag_6D (FR-1B)", "chart3_flag": 0, "chart2_flag": 1.0}
{"index": {}}
{"SNI#": "0x15 Airbag_6D (FR-1B)", "ms": 1, "Frequency": 6, "avgFreq": 0, "vehicle": "TE1484421", "filedate": "2022-10-18", "communication_type": "FR1B", "threshold": 200, "ECU_SNI": "FR1B - 0x15 Airbag_6D (FR-1B)", "chart3_flag": 0, "chart2_flag": 0.0}
{"index": {}}
{"SNI#": "0x15 Airbag_6D (FR-1B)", "ms": 2, "Frequency": 2, "avgFreq": 0, "vehicle": "TE1484421", "filedate": "2022-10-18", "communication_type": "FR1B", "threshold": 200, "ECU_SNI": "FR1B - 0x15 Airbag_6D (FR-1B)", "chart3_flag": 0, "chart2_flag": 0.0}

Thanks & Regards,
Siva Kumar

2

Hi @Siva_Kumar_Menta

I've tried your Vega spec with the given data and to me it looks correct: (note I've just used static data, so _source. has been omitted):

{
  "$schema": "https://vega.github.io/schema/vega-lite/v4.json",
  "autosize": {"type": "fit", "contains": "padding"},
  "data": {
    "values": [
{"SNI#": "0x03 ESC (FR-1B)", "ms": 93, "Frequency": 2, "avgFreq": 95, "vehicle": "TE1484421", "filedate": "2022-10-18", "communication_type": "FR1B", "threshold": 200, "ECU_SNI": "FR1B - 0x03 ESC (FR-1B)", "chart3_flag": 0, "chart2_flag": 0.0}
{"SNI#": "0x03 ESC (FR-1B)", "ms": 95, "Frequency": 1, "avgFreq": 0, "vehicle": "TE1484421", "filedate": "2022-10-18", "communication_type": "FR1B", "threshold": 200, "ECU_SNI": "FR1B - 0x03 ESC (FR-1B)", "chart3_flag": 0, "chart2_flag": 0.0}
{"SNI#": "0x03 ESC (FR-1B)", "ms": 96, "Frequency": 2, "avgFreq": 0, "vehicle": "TE1484421", "filedate": "2022-10-18", "communication_type": "FR1B", "threshold": 200, "ECU_SNI": "FR1B - 0x03 ESC (FR-1B)", "chart3_flag": 1, "chart2_flag": 1.0}
{"SNI#": "0x15 Airbag_6D (FR-1B)", "ms": 0, "Frequency": 6, "avgFreq": 0, "vehicle": "TE1484421", "filedate": "2022-10-18", "communication_type": "FR1B", "threshold": 200, "ECU_SNI": "FR1B - 0x15 Airbag_6D (FR-1B)", "chart3_flag": 0, "chart2_flag": 1.0}
{"SNI#": "0x15 Airbag_6D (FR-1B)", "ms": 1, "Frequency": 6, "avgFreq": 0, "vehicle": "TE1484421", "filedate": "2022-10-18", "communication_type": "FR1B", "threshold": 200, "ECU_SNI": "FR1B - 0x15 Airbag_6D (FR-1B)", "chart3_flag": 0, "chart2_flag": 0.0}
{"SNI#": "0x15 Airbag_6D (FR-1B)", "ms": 2, "Frequency": 2, "avgFreq": 0, "vehicle": "TE1484421", "filedate": "2022-10-18", "communication_type": "FR1B", "threshold": 200, "ECU_SNI": "FR1B - 0x15 Airbag_6D (FR-1B)", "chart3_flag": 0, "chart2_flag": 0.0}
    ]
  }, 
 "transform": [{
    "sort": [
      {"field": "ECU_SNI", "order": "descending"},
      {"field": "Frequency", "order": "descending"},
      {"field": "ms", "order": "descending"}
    ],
    "window": [{
      "op": "rank",
      "as": "rank"
    }],
    "groupby": ["ECU_SNI"]
  },
  {"filter": "datum.rank == 1"}
  ],
   "encoding": {
    "x": {
      "field": "ECU_SNI","title": "ECU_SNI"
      "scale": {"padding": 0},
      "axis": {"labelAngle": -25, "labelLimit": 1000, "title": "ECU-SNI"}
    }
  },
  "layer": [
    {
      "mark": {
        "color": "#85A9C5",
        "type": "bar",
        "tooltip": true,
        "point": false
      },
      "encoding": {
        "y": {
          "aggregate": {"argmax": "Frequency"},
          "field": "ms",
          "type": "quantitative",
          "title": "[ms]",
          "axis": {"titleColor": "#85C5A6"}
        },
        "y2": {"aggregate": "max", "field": "Frequency","title": "Frequency"}
      }
    },
    {
      "mark": {
        "opacity": 10,
        "type": "line",
        "width": 4,
        "color": "#85C5A6",
        "tooltip": true
      },
      "encoding": {
        "y": {
          "aggregate": "max",
          "field": "Frequency",
          "title": "Frequency",
          "axis": {"titleColor": "#85A9C5"}
        }
      }
    }
  ],
  "resolve": {"scale": {"y": "independent"}},
  "config": {}
}

For the "0x03 ESC (FR-1B)" a high duration of 96 is associated with the max Frequency of 2.
For the "0x15 Airbag_6D (FR-1B)" a high duration of 2 is associated with the max Frequency of 6.

Screenshot 2022-12-19 at 10.12.01
Screenshot 2022-12-19 at 10.12.012000×1180 160 KB

Did I understand correctly what you are looking for?

3

your understanding is correct.
But for SNI "0x15 Airbag_6D (FR-1B)", result is not coming as expected (Maximum Frequency 6 and corresponding highest 'ms' supposed to be 1 but coming as 0].
Any suggestions please.

4

It returns 1 in the chart above:

Screenshot 2022-12-19 at 11.30.37
Screenshot 2022-12-19 at 11.30.37916×356 16.5 KB

5

Now, I'm also getting same result. Will validate with more data and let you know any issues.

Thanks,
Siva Kumar