Create histogram with group by count (binned data)

[Pond]

Hi,

I have devices users are using:

{"userID":"1", "deviceID": "11"}
{"userID":"1", "deviceID": "12"}
{"userID":"1", "deviceID": "13"}
{"userID":"2", "deviceID": "21"}
{"userID":"2", "deviceID": "22"}
{"userID":"3", "deviceID": "31"}
{"userID":"3", "deviceID": "32"}
{"userID":"4", "deviceID": "41"}

As the result, I want to receive how many users are using 1-2 devices, who use 3-4 devices etc.:
1-2 devices — 3 users
3-4 devices — 1 user

I found this topic, which is very similar to what I need. But this solution didn't work for me:

I also tried to create a histogram with binned data based on this one. But I'm not sure how to use data I have, cause I need first count the number of devices each user use and then group users based on that aggregated data. Are there any options to make it? Thanks!

[wylie (Wylie Conlon)]

I can help debug your Vega spec and ES queries. The first thing I notice is that you have index: "/events-*" instead of index: event-*. Secondly, it might help if you showed the rest of the Vega spec in your encoding section.

[Pond]

Sure, I attached all the code below. Thank you!

``` 
{
      "$schema": "https://vega.github.io/schema/vega-lite/v2.json"
      "data": {
        "url": {
          "%timefield%": "@timestamp"
          "%context%": "true"
          "index": "/events-*"
          "body": {
        "size": 0, 
      "aggs": {
              "users": {
                "terms": {
                  "field": "userID", 
                  "size": 10000,
                  "order" : {"eventCount": "desc"}
                }, 
      "aggs": {
                  "eventCount": {
                    "cardinality": {
                      "field": "deviceID"}
                  }
                }
              }
            }
          }
        }
      "format": {"property": "aggregations.users.buckets"}
      }
      "mark": "bar"
      "transform": [
        {
          "aggregate": [
            {"op": "count", "field": "key", "as": "usercount"}
          ]
          "groupby": ["eventCount.value"]
        }
      ]
      "encoding": {
        "x": { "field": "eventCount\\.value", "type": "ordinal", "sort": "descending" }
        "y": { "field": "usercount", "type": "quantitative" }
      }

[wylie (Wylie Conlon)]

It looks fine except for the issue I already pointed out, your index name should likely be events-* without a forward slash

[Pond]

I changed the index name, and the error disappeared, but no results are showing:

What could be the problem?

[wylie (Wylie Conlon)]

When it's empty like that, Vega is matching no results with the field names you're using. Maybe the issue is that you've written eventCount\\.value instead of eventCount.value for the X axis?

[Pond]

Thanks for you help!

I tried but no result (

[Pond]

Is there any other way to create a histogram with binned data?

[wylie (Wylie Conlon)]

Let's make sure that you are getting results from your query first. In your Vega visualization, can you open the browser dev tools and type VEGA_DEBUG.vegalite_spec? This will show the results of your Elasticsearch query as the data, and this lets you copy it into the online vega editor.

If you're willing to share the spec with realistic-looking data, then I can test it out and help you find the problem.