Technical Post: Polar charts in Grafana using ae3e-plotly-panel plugin; formatting tips for energy visualizations using dates on the angular axis

Update in 2024: Everyone should be using the nLine Plotly plugin for Grafana. The ae3e one seems to be abandoned, or at least is updated far less often. Other than the plugin name, instructions below are mostly the same. 

I've been banging away on a mini-project and want to publish some of the technical details before they're forgotten, because some were not intuitive to me (as a non-programmer) to figure out. 

If you're browsing my energy post series, you may want to skip this one if you're not interested in programming! 

The problem: 

Economist Edgardo Sepulveda published some fascinating graphs that show differences between electricity supplied by Ontario's nuclear power plants and grid-connected wind and solar installations. Here is an example:

I wanted to replicate these (to the best of my ability!) for my Saskatchewan Electricity Mix project (Twitter, website). I managed to get it working (see near the end of this post), but it was tricky so I wanted to leave a trail of breadcrumbs for "future me" and others who might want to replicate this visualization with other datasets. 

Technology:

• Grafana as a visualization engine (I'm running v9.2.4 as of this post) 
• influxdb2 as a data repository and source to Grafana. Note: nothing in this post is specific to influxdb. 

Obstacles to overcome:

I'll expand on each of these in more detail:

1. Grafana does not support polar charts
2. The ae3e-plotly-panel Grafana plugin supports polar charts, but the config instructions for Grafana are virtually non-existent (for a non-programmer this is a big roadblock!) 
3. Plotly does not currently support using dates on the angular axis

Adding polar chart support to Grafana via Plotly 

Here's the easiest step of the whole process: installing Plotly. 

Assuming your Grafana instance is connected to the internet, navigate to Configuration > Plugins. Search "Plotly" and install the ae3e version:

The natel version of the plugin is limited to XY plots and others, but the ae3e version supports any chart in Plotly, which is huge. 

(Update 2023-01-07: It looks like there's a more current fork of the ae3e plugin by nLine, I suggest trying that one instead! https://grafana.com/grafana/plugins/nline-plotlyjs-panel/)

Note: Plotly has been developed for a few different languages, including (most popularly?) Python and Javascript. The one Grafana/ae3e uses is Javascript, so be sure to add "Javascript" to your Plotly Google searches later on. 

Using Plotly in Grafana

When you spin up a new Plotly visualization in Grafana, you have none of the GUI elements you're accustomed to configuring. You have a bunch of text fields where you write Javascript code. 

The ae3e Github page gives the most bare-bones configuration example but doesn't really break it down for dummies like me. From Github:

let x  = data.series[0].fields[0].values.buffer
let y  = data.series[0].fields[1].values.buffer

let serie = {
    x : x,
    y : y,
    name : variables.project //where project is the name of a Grafana's variable 
}

return {
    data : [serie],
    config : {
        displayModeBar: false
    }
}

What I would have loved to have spelled out for me is that data.series[0].fields[0].values.buffer is the first column of data in the first Grafana query, and data.series[0].fields[1].values.buffer is the second column of data in the first query. 

If you have multiple queries (as I do in the previous screenshot) those datasets and fields can be accessed sequentially: data.series[1].fields[0].values.buffer would the first column of data in the second query, data.series[2].fields[1].values.buffer would be the second column of data in the third query, etc. 

The simplest configuration for a Plotly graph, then, is to create a Grafana query that returns two variables from your data source, literally take the text from the code above and plop it in the "Script" box in the Grafana Plotly plugin. 

Another good resource with examples on Plotly and Grafana is this stackoverflow post.

Creating polar charts with dates on the angular axis 

The main challenge to recreating Edgardo's great visualization is Grafana currently does not support dates on the polar angular axis, despite some clues in the documentation that it does.

The first step, then, is converting the timestamp in your source data to degrees (i.e. between 0 and 360). 

My source data is aggregated daily, so here's how I'm doing it in Grafana: taking the timestamp, extracting the day of year (0 to 365), and multiplying by 360 degrees per 365 days to get a "day of year" in degrees. 

  |> map(fn: (r) => ({ r with day_of_year: float(v: date.yearDay(t: r._time)) * 360.0 / 365.0}))  

Yes, I know: there's a leap year edge case I'm not accounting for - but I have until 2024 to solve that. 

The data stream looks like this (I strip out :

The next step is configuring the scatterpolar chart layout. Here's the code I've got in the "Layout" pane of the Grafana Plotly interface, with some notes following:

{

  "legend": {

    "font": {

      "color": "#aaa"

    }

  },

  "paper_bgcolor": "rgba(0,0,0,0)",

  "polar": {

    "angularaxis": {

      "color": "#aaa",

      "rotation": 0,

      "tickmode": "array",

      "ticktext": [

        "Jan",

        "Feb",

        "Mar",

        "Apr",

        "May",

        "Jun",

        "Jul",

        "Aug",

        "Sep",

        "Oct",

        "Nov",

        "Dec",

        ""

      ],

      "tickvals": [

        0,

        30.57534247,

        58.1917808219178,

        88.76712329,

        118.3561644,

        148.9315068,

        178.5205479,

        209.0958904,

        239.6712329,

        269.260274,

        299.8356164,

        329.4246575,

        360

      ],

      "type": "linear"

    },

    "bgcolor": "rgba(0, 0, 0, 0)",

    "radialaxis": {

      "angle": 0,

      "autorange": true,

      "color": "#aaa",

      "range": [

        0,

        39.312

      ],

      "type": "linear",

      "visible": true

    }

  },

  "showlegend": true,

  "title": {

    "font": {

      "color": "#aaa"

    },

    "text": "SK Electricity Radar Chart (Daily GWh)<br><span style=\"font-size: 14px;\">Radial axis: daily GWh of production; common scale</span>"

  }

}

Notes on the above:

• This is a variant on a solution from this stackoverflow post, essentially just describing an array of strings and their positions on the circle. tickmode, ticktext, and tickvals are the key parameters.
• Depending on how you want the datetime labels positioned (e.g. if you wanted "Jan" at the centre of the time window and an empty tick denoting the start and end, like Edgardo's version, you could expand those arrays as needed. Here's how I arrived at these numbers. 
  
  
• Be sure to scale the radialaxis appropriately for your data, or as shown above, use autorange=true. 

Here's how the Script portion of the Grafana Plotly panel is configured: 

console.log(data)

var trace = {

  name: 'Coal',

  line: {

      color: "#825631",

      width: 1.5

    },

  r: data.series[0].fields[2].values.buffer,

  theta: data.series[0].fields[1].values.buffer,

  type: 'scatterpolar',

  fill: 'toself',

  fillcolor: 'rgba(130,86,49,0.2)'

};

//trace2 through 6 config omitted

data = [trace,trace2,trace3,trace4,trace5,trace6];

layout = {

  title:'Title<br><span style="font-size: 14px;">Subtitle</span>'

}

return {data,layout};

And that's it! Putting it all together, we get a visualization like this: 

I built a second query to visualize all energy sources on the same unitless scale, where 1 is the scaled maximum of each individual energy source in the time span:

One of the nice features of the Grafana Plotly plugin is you can disable traces easily, as I've done on this chart to just compare natural gas and wind "peakiness". 

Bonus: influxdb2 query to ensure fill:toself works

If you're using influxdb2 to pull data, here's an example query from the scalar/unitless polar chart - it's a bit more complex.

Table  t1 is a manually-built array consisting of two elements: a zero at 0 degrees, and a zero at 360 degrees. These will ensure that the fill:toself function works on the line markers for a transparent fill. 

maxValue is a pre-query to get the largest value in the time range for scaling, 

import "date"

import "experimental/array"

t1 = array.from(rows: [{_field: "coal_MW_by_type", day_of_year: 0.0, GWh: 0.0},{_field: "coal_MW_by_type", day_of_year: 360.0, GWh: 0.0}])

  |> group()

// Define a helper function to extract field values

getFieldValue = (tables=<-, field) => {

    extract = tables

        |> findColumn(fn: (key) => key._field == field, column: "_value")

    return extract[0]

}

maxValue = from(bucket: "saskpower")

  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)

  |> filter(fn: (r) => r["_measurement"] == "saskpower_daily")

  |> filter(fn: (r) => r["_field"] == "coal_MW_by_type" )

  |> max()

  |> getFieldValue(field: "coal_MW_by_type")

t2 = from(bucket: "saskpower")

  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)

  |> filter(fn: (r) => r["_measurement"] == "saskpower_daily")

  |> filter(fn: (r) => r["_field"] == "coal_MW_by_type" )

  |> hourSelection(start:6,stop:23)    

  |> truncateTimeColumn(unit: 1d)

  |> timeShift(duration: -18h)

  |> unique(column:"_time")

  |> map(fn: (r) => ({r with _value: r._value / maxValue }))

  |> map(fn: (r) => ({ r with day_of_year: float(v: date.yearDay(t: r._time)) * 360.0 / 365.0}))  

  |> drop(columns: ["_measurement", "_start","_stop","_time"])  

  |> rename(columns: {_value: "GWh"})

  |> group()

union(tables: [t1, t2])

  |> sort(columns: ["day_of_year"], desc: false)  

Downsides of my approach 

• I'm probably under-utilizing smart Javascript programming to do some smarter things. 
• For example, hard-coding the date/time scaling instead of figuring out a clever way to generate it dynamically from the Grafana time range parameters. Please leave a comment if you figure this out! 
• When a user mouses over the chart, they see decimal "days", e.g. "308.7123". So it's not a great way to zero in on individual data points, but that's not really the intent. 
• There is probably a better way to include larger datasets than the six manual queries in my example - in fact, I see the solution now (just having more columns and naming different the data.series[0].fields[n].values.buffer fields in this string - perhaps harder to keep track of, though).

Hopefully these clues help save someone else the hours of trial-and-error config I spent trying to get a polar chart with dates on the angular axis working! 

These charts are now live.

My next energy post should be back on-topic to energy, nuclear, and Saskatchewan! 

Updated 2022-12-22 for dark theme, fill, and including influxdb2 query.