Using AgReFed @ Dale data

This is a small Jupyter notebook to show how one might consume the data provided by AgReFed @ Dale. It provides some simple plotting examples using plotly and hvPlot.

It is just javascript suitable for cutting and pasting directly into a browser and a bit of Python.

You can download the actual notebook from: https://webapps.plantenergy.uwa.edu.au/agrefed_dale/static/plotly.ipynb (much more fun!)

Hopefully you can get an idea of how easy it is to incorporate AgReFed @ Dale data into your own investigations.

see https://plot.ly/javascript/reference/ for a reference to plotly.js

from javascript

fetch plotly...

The notebook uses requirejs so we can use that to download plotly...

The plotly javascript is at https://cdn.plot.ly/plotly-latest.min.js (all 6MB of it!)

In [1]:
%%javascript
// ask for plotly and give it a name "plotly"
requirejs.config({
    paths: {
        plotly: 'https://cdn.plot.ly/plotly-latest.min'
    }
});
// helper function
window.plotly = (elem, plots, layout) => {
    const div = document.createElement('DIV')
    elem.append(div)
    // ask requirejs to call us when plotly is loaded
    requirejs(['plotly'], Plotly => Plotly.newPlot(div, plots, layout))
}

set Target to AgReFed @ Dale

In [2]:
%%javascript
window.Dale = 'https://webapps.plantenergy.uwa.edu.au/agrefed_dale'

Some helper functions

In [3]:
%%javascript
// list of objects to an object of lists
window.unzip  = (arr, ...keys) => {
    var o = {};
    keys.forEach(k => o[k]= []);
    arr.forEach(a => {
        keys.forEach(k => o[k].push(a[k]))
    });
    return o;
}
// show failure
window.jqfail = (ele, xhr)  => {
    const div = $('<div/>');
    div.css({"background-color": "orange"})
    div.text(xhr.responseText || xhr.statusText || 'unknown error')
    ele.append(div);
}
window.fetch_fail = (ele, ...args) => {
    args = args.map(x => x.toString()).join(',')
    const div = document.createElement('DIV');
    div.textContent = `${args}`
    div.style.backgroundColor = 'orange'
    ele.append(div)
}

using fetch

fetch is an ajax method available in all(most) modern browsers...

Let's get some site weather data!

Note that in the %%javascript notebook context element is a jQuery object representing the output text area of the current cell. This way we can avoid creating random id for div elements for plotly to find.

In [4]:
%%javascript
const plot1 = element;

// we only want these columns....
const cols1 = ['datetime', 'soilt', 'airt', 'AT_60cm', 'AT_30cm']
// our query per_page:0 mean *all* data
var params = { per_page:0, datetime: "2018-02-17/P20D", select:cols1.join(',') } 

var url = new URL(Dale + '/WeatherSite')
url.search = new URLSearchParams(params)


fetch(url).then(resp => resp.json())
    .then(json => unzip(json.items, ...cols1))
    .then(({datetime, ...rest}) => {
        // turn data into plotly plots.
        const traces = Object.entries(rest).map(([name, y]) => {
            return {
                x: datetime,
                y: y,
                type: 'scatter',
                name: name
            }
        });

        plotly(plot1, traces, {width:900});
    }).catch((...args) => fetch_fail(plot1, ...args))

using jQuery ajax

We always have jQuery available in a notebook

In [5]:
%%javascript
const plot2 = element;
const cols2 = ['datetime', 'soilt', 'airt']
var params = {per_page:0, datetime: "2018-02-17/2018-04-17", select: cols2.join(',')}

$.get(Dale + '/WeatherSite', params).done(json => {

    const {datetime, ...rest} = unzip(json.items, ...cols2);
    const traces = Object.entries(rest).map(([name, y]) => {
        return {
            x: datetime,
            y: y,
            type: 'scatter',
            name: name
        }
    });

    plotly(plot2, traces, {width:900});
}).fail((xhr) => jqfail(plot2, xhr))

Usually you want to compare a column to a value e.g. airt > 38. But you can also compare other columns if you "quote" the value with backticks: e.g. airt > `soilt`.

Here we plot all places where the air temperature was greater than the soil temperature.

We have to target the /query/suba endpoint which understands SUBA query language. See here.

In [6]:
%%javascript
const plot3 = element;
const cols3 = ['datetime', 'soilt', 'airt']
var params = {per_page:0, query: "airt > `soilt`", entity_name: "WeatherSite", select: cols3.join(',')}

$.post(Dale + '/suba/query', params).done(json => {

    const {datetime, ...rest} = unzip(json.items, ...cols3);
    const traces = Object.entries(rest).map(([name, y]) => {
        return {
            x: datetime,
            y: y,
            type: 'scatter',
            mode: 'markers',
            name: name
        }
    });

    plotly(plot3, traces,  {width:900});
}).fail((xhr) => jqfail(plot3, xhr))

crop yields vs plot

Let's look at how crop yield varied across plots

In [7]:
%%javascript
const plot4 = element;
const cols4 = ['plot_barcode', 'latitude', 'longitude', 'crop_yield']
$.get(Dale  + '/Plot', {per_page: 0, select: cols4.join(',')}).done(page => {

    const {plot_barcode, latitude, longitude, crop_yield} = unzip(page.items, ...cols4);
    const traces = [
         {
            y: latitude,
            x: longitude,
            type: 'scatter',
            mode: 'markers',
            text: page.items.map(r => `${r.plot_barcode}: ${r.crop_yield}`),
             marker : {
                 size:crop_yield.map(y => 2*y),
                 color:crop_yield,
                 colorscale: 'Viridis',
                 colorbar: {
                    title: 'Crop Yield'
                }
             }
        }]
    const axis = {
         tickformat: ".3f",
         ticksuffix:  "°",
    }
    const layout = {
      height: 750,
      width: 900,
      title: 'plot yield vs location',
      xaxis : {
        title: "longitude", ...axis
      },
      yaxis : {
        title: "latitude", ...axis
      }
    }

    plotly(plot4, traces, layout);
}).fail((xhr) => jqfail(plot4, xhr))

... or maybe you just want the data

Let's get a nice json renderer

In [8]:
%%javascript
requirejs.config({
    paths: {
        renderjson: 'https://cdn.jsdelivr.net/npm/[email protected]/renderjson.min'
    }
});
window.renderjson = (elem, json, level=2) => {
    requirejs(['renderjson'], r => {
        r.renderjson.set_show_to_level(level);
        elem.append(r.renderjson(json))
    })
}

... and give it some colour

In [9]:
%%html
<style type="text/css">
  pre.renderjson .string {
    color: #007700;
  }

  pre.renderjson .key {
    color: #000077;
  }
</style>

Now we can just look over the data.

Let's get a page of data

Notice the weird next_url? Just "get" that endpoint and you'll have the next page of data from your query (nice hey!). You can step forward (and back!) through the pages easily.

In [10]:
%%javascript
const plot5 = element;
var cols5 = ['datetime', 'soilt', 'airt']
var params = {per_page:5, query: "airt > `soilt`", entity_name: "WeatherSite", select: cols5.join(',')}

$.post(Dale + '/suba/query', params).done(json => {
    renderjson(plot5, json, 3)
}).fail((xhr) => jqfail(plot5, xhr))

All the metadata about the data can be found from the OAS schema.

In particular components.schemas.{Table}.properties gives details on the available data names. You can also find all the information here.

In [11]:
%%javascript
const plot6 = element;
$.get(Dale + '/swagger.json').done(json => {
    renderjson(plot6, json, 2)
}).fail((xhr) => jqfail(plot6, xhr))

from Python

It's just as easy from Python

In [12]:
import requests
import pandas as pd
Dale = 'https://webapps.plantenergy.uwa.edu.au/agrefed_dale'
In [13]:
params = {
    'per_page': 20,
    'query': "airt > `soilt`",
    'entity_name': "WeatherSite",
    'ordering' : '<datetime' # order by datetime ascending
}

data = requests.post(Dale + '/suba/query', data=params).json()
df = pd.DataFrame(data['items'])
df.datetime = pd.to_datetime(df.datetime)  # datetimes are always a problem
df
Out[13]:
AT_30cm AT_60cm airt battery datetime dirn evap gust id max_at min_t rain rh sddirn soilt solar wind windrun
0 38.9 38.5 34.8 13.1 2018-02-15 18:00:00 NaN 0.41 5.2 8 35.9 34.7 0.0 13.9 0.0 34.0 995.76 2.7 9.72
1 22.4 23.0 27.6 12.8 2018-02-15 22:00:00 134.0 0.18 8.8 12 28.3 22.1 0.0 29.0 15.6 26.9 0.00 3.5 12.60
2 32.0 31.7 32.8 13.4 2018-02-16 10:00:00 56.0 0.88 8.2 24 32.9 26.7 0.0 23.7 25.7 32.6 2744.28 3.8 13.68
3 23.0 23.7 23.8 12.7 2018-03-06 01:00:00 92.0 0.08 2.9 447 23.8 22.8 0.0 47.7 12.3 23.6 0.00 1.7 6.12
4 24.5 24.6 24.5 12.7 2018-03-06 02:00:00 63.0 0.11 4.4 448 24.9 23.9 0.0 45.8 14.4 23.6 0.00 2.5 9.00
5 24.1 24.3 23.9 12.6 2018-03-06 03:00:00 59.0 0.09 4.0 449 24.7 23.6 0.0 48.5 19.1 23.1 0.00 2.2 7.92
6 23.5 23.7 22.9 12.6 2018-03-06 04:00:00 59.0 0.09 4.1 450 24.0 22.7 0.0 51.4 10.7 22.7 0.72 2.3 8.28
7 22.4 22.6 23.7 12.6 2018-03-14 06:00:00 198.0 0.10 3.7 644 23.7 21.0 0.0 39.3 44.1 23.3 0.36 2.0 7.20
8 30.3 30.5 28.9 13.0 2018-03-20 18:00:00 62.0 0.23 6.4 800 30.8 28.8 0.0 20.5 16.8 28.6 356.76 3.7 13.32
9 24.5 24.9 25.9 12.8 2018-03-20 21:00:00 113.0 0.14 5.3 803 25.9 23.6 0.0 28.7 10.2 24.7 0.36 2.4 8.64
10 26.4 26.5 25.0 12.8 2018-03-20 22:00:00 95.0 0.18 5.7 804 26.3 24.9 0.0 30.6 11.1 24.5 0.00 3.8 13.68
11 26.0 26.1 24.8 12.8 2018-03-20 23:00:00 91.0 0.16 5.3 805 25.2 24.6 0.0 31.1 10.0 24.1 0.72 3.3 11.88
12 25.7 25.8 24.8 12.7 2018-03-21 00:00:00 80.0 0.17 6.0 806 25.1 24.4 0.0 32.9 10.2 23.8 0.36 3.7 13.32
13 25.3 25.4 24.2 12.7 2018-03-21 01:00:00 69.0 0.16 6.0 807 24.9 24.0 0.0 34.7 11.8 23.4 0.72 3.6 12.96
14 24.6 24.7 23.7 12.7 2018-03-21 02:00:00 69.0 0.13 4.6 808 24.2 23.5 0.0 36.9 12.9 23.0 0.36 2.7 9.72
15 23.0 23.3 22.9 12.7 2018-03-21 03:00:00 67.0 0.11 4.4 809 23.8 22.4 0.0 39.1 13.1 22.3 0.72 2.4 8.64
16 34.3 34.7 32.0 13.2 2018-03-21 18:00:00 28.0 0.25 5.0 824 33.1 31.9 0.0 21.8 16.2 31.7 456.84 3.1 11.16
17 29.1 29.8 29.4 12.8 2018-03-21 19:00:00 64.0 0.13 3.1 825 32.0 29.3 0.0 26.6 11.2 29.1 31.32 1.9 6.84
18 25.1 25.6 26.1 12.7 2018-03-21 23:00:00 357.0 0.11 4.0 829 26.5 24.8 0.0 38.5 24.0 24.9 0.00 2.2 7.92
19 25.8 26.2 25.8 12.7 2018-03-22 00:00:00 359.0 0.12 4.0 830 26.3 25.4 0.0 39.1 17.0 24.7 0.00 2.3 8.28

keep re-running this cell to step through the pages

In [14]:
data = requests.get(data['next_url']).json()
df = pd.DataFrame(data['items'])
df.datetime = pd.to_datetime(df.datetime)
df
Out[14]:
AT_30cm AT_60cm airt battery datetime dirn evap gust id max_at min_t rain rh sddirn soilt solar wind windrun
0 26.4 26.6 25.6 12.7 2018-03-22 01:00:00 19 0.12 4.3 831 25.9 25.3 0.0 40.1 12.2 24.6 0.00 2.7 9.72
1 33.6 33.5 30.1 13.5 2018-03-22 11:00:00 278 0.91 7.3 841 30.7 29.1 0.0 31.0 17.6 29.0 2864.52 5.0 18.00
2 34.9 34.2 31.6 13.5 2018-03-22 12:00:00 272 0.98 7.5 842 32.2 30.0 0.0 30.4 24.9 31.1 3100.68 4.9 17.64
3 22.7 22.5 22.2 13.2 2018-03-26 09:00:00 342 0.07 4.4 935 22.2 19.9 0.0 68.8 28.0 21.3 364.68 2.1 7.56
4 25.0 24.7 24.4 13.5 2018-03-26 10:00:00 317 0.16 8.3 936 24.4 22.1 0.0 67.2 19.6 22.1 579.96 4.4 15.84
5 27.7 27.2 25.2 13.6 2018-03-26 11:00:00 309 0.38 11.2 937 25.5 24.3 0.0 64.5 11.6 23.5 1270.80 7.1 25.56
6 29.1 28.3 26.7 13.6 2018-03-26 12:00:00 308 0.54 11.7 938 26.9 25.0 0.0 58.2 12.0 24.7 1740.60 7.3 26.28
7 31.1 30.0 26.9 13.5 2018-03-26 13:00:00 299 0.80 11.6 939 29.0 26.1 0.0 56.5 11.5 26.6 2663.64 8.4 30.24
8 33.9 33.0 32.4 13.3 2018-03-30 14:00:00 36 0.85 4.9 1036 33.1 29.8 0.0 22.1 60.6 31.3 2844.00 2.3 8.28
9 37.7 36.8 32.9 13.2 2018-03-30 15:00:00 336 0.75 5.0 1037 33.4 31.9 0.0 21.4 58.5 32.1 2435.04 2.3 8.28
10 37.1 36.4 32.8 13.3 2018-03-30 16:00:00 247 0.57 4.6 1038 33.4 32.4 0.0 22.1 63.5 31.9 1788.48 2.1 7.56
11 35.0 34.8 31.9 13.4 2018-03-30 17:00:00 231 0.38 5.5 1039 33.4 31.8 0.0 24.2 57.4 31.0 1031.04 2.8 10.08
12 27.5 27.3 24.9 13.6 2018-04-03 10:00:00 12 0.55 5.3 1128 24.9 21.8 0.0 48.9 23.1 24.6 2069.64 3.1 11.16
13 35.0 34.5 32.4 13.3 2018-04-03 15:00:00 295 0.57 5.5 1133 32.6 31.1 0.0 29.6 68.1 31.9 1888.56 2.0 7.20
14 37.0 36.1 32.7 13.3 2018-04-03 16:00:00 256 0.57 4.6 1134 33.6 32.3 0.0 25.6 77.6 32.2 1839.96 2.1 7.56
15 34.6 34.5 32.6 13.4 2018-04-03 17:00:00 252 0.36 6.8 1135 33.7 31.8 0.0 26.1 50.2 31.3 997.56 2.5 9.00
16 32.4 32.5 30.4 12.9 2018-04-03 18:00:00 245 0.18 5.8 1136 32.8 30.4 0.0 33.0 47.1 29.9 378.36 2.7 9.72
17 23.9 23.0 22.7 13.7 2018-04-16 12:00:00 93 0.50 4.9 1442 22.7 19.4 0.0 40.1 29.8 22.4 1930.32 2.7 9.72
18 26.4 26.0 24.8 13.6 2018-04-16 13:00:00 76 0.58 4.7 1443 25.0 22.4 0.0 31.9 31.6 24.1 2127.24 2.6 9.36
19 28.6 28.2 26.1 13.6 2018-04-16 14:00:00 82 0.37 3.6 1444 26.2 24.6 0.0 26.2 37.2 24.4 1263.96 1.9 6.84

Use headers= {"accept": "text/csv"} to get a CSV version of the data

In [15]:
from io import StringIO

cols = ['datetime', 'soilt', 'airt']
params = {
    'per_page': 0,
    'select': ','.join(cols),
    'datetime': "2018-02-17/P20D",
}

data = requests.get(Dale + '/WeatherSite',
                    params,
                    headers={'accept': 'text/csv'})
df = pd.read_csv(StringIO(data.text))

A good way to plot datafames is hvPlot.

It adds a hvPlot method to the dataframe object. (You might want to uncomment the line below before proceeding)

In [16]:
#!pip install hvplot
# *or* if that fails with permission errors
#!pip install --user hvplot
In [17]:
import hvplot.pandas