Make sense of your hackAIR sensor data

Once you’ve set up your hackAIR sensors, the question is: what can you do with the data? Of course, you can check it day by day on the map, but wouldn’t it be cool to do more? Let’s see what’s already possible.

This blog post has been written for hackAIR users who are familiar with Excel and curious about data visualisation. Please check out what hackAIR communications lead Wiebke Herding has learned about visualising data! We’d also love to hear from you what you have been experimenting with.

From the sensor profile

Each sensor has its own profile page that you can find by going to Profile > Sensors. After a moment’s wait, you’ll get a list of the latest measurements translated as air quality ratings.

Using the ‘Export Measurements’ button, you can then download the exact measurements from your sensor in CSV format. This way, you can export 5000 measurements at a time – if you need more, just run multiple exports. Note: the timestamp in the export is in GMT – depending on your own time zone, you might need to adjust this (e.g. add an hour if you are based in Berlin).

Air pollution over time with Excel

After downloading the files, you can open them in Excel. I then added three columns to be able to access the values I was interested in:

• Date CET: =<Date>+”01:00″
  
• PM10: =IF(<Pollutant_Q_Name>=”PM10_AirPollutantValue”,<Pollutant_Q_Value>,””)
  
• PM2.5: =IF(<Pollutant_Q_Name>=”PM2.5_AirPollutantValue”,<Pollutant_Q_Value>,””)
  

Note: these formulas assume that you use English localisation settings. If you use Excel in a different language, you might need to adjust quotation marks and commas.

Using Insert >PivotChart, I created a chart with Date CET as the axis and PM10 and PM2.5 as the values. I set both values to show the average PM measurement and changed the chart type to a line graph. The result was a time series of daily averages:

Using the report filter buttons at the bottom right, I could then zoom into the hourly averages and finally into the individual measurements.

As I am currently running two sensors (120 is a hackAIR home v1 at the front of my house, 255 is a hackAIR home v2 in a more protected space at the back), I can also compare the two:

 

On average, the sensor at the front of my house picked up 53% more PM10 particles, and 23% more PM2.5 particles in the testing period.

Building graphs that update themselves

As downloading the files can get tedious over time, we could also use the hackAIR API to access our data. We can use a service like data.world for that. After setting up an an account and creating a new project, you can add your own sensor data using Add data > Add from URL.

Paste the following link: https://api.hackair.eu/hackair_data?access_key=1234 (replace 1234 with your own sensor’s access key). Add the extension .json to the file name, and you’re good. 

To enable automatic updates, go to project settings, and enable the Automatic Sync Options. You can then explore your data and build graphs like the one below.

Data.world alone will not give you graphs that you can permanently link to, but it’s provides good access to the API data. One option for building graphs is Google Data Studio. Log in and add a data source, adding data.world as a community connector. You’ll need the URL of your data.world project. To import all data from your sensor, add “SELECT * FROM <table_name>” as your SQL query. To be able to use the data, you’ll need to make a few adjustments:

• date_str: set the type to Date Hour (YYYMMDDHH)
  
• pollutant_q_value: set the aggregation to None
  

Now add two new fields and set both of them to aggregation = Average.

• PM10 with the formula “CASE WHEN pollutant_q_name=”PM10_AirPollutantValue” THEN pollutant_q_value ELSE 0 END”
  
• PM2.5 with the formula “CASE WHEN pollutant_q_name=”PM2.5_AirPollutantValue” THEN pollutant_q_value ELSE 0 END”
  

Now you can set up your report, for example using the time series chart or the data table. Play around – and when you’re done, you can share the link to your sensor data. Here’s the view of my recent measurements: https://datastudio.google.com/open/11iG_TgonCmPy0nFmUS0ObVWLudstQk4M

How about measurements from a specific geographic area?

You can download the latest measurements from a rectangular geographic area as follows:

1. Determine the coordinates of the top left corner of your area, e.g. by locating it on OpenStreetMap and selecting show address. This will give you a pair of two numbers, the latitude (e.g.  52.6315 for Berlin) and the longitude (e.g. 13.1259 for Berlin). We’ll call them lat1 and lon1.
   
2. Determine the coordinates of the bottom right corner of your area, e.g.  52.3153, 13.7569 for Berlin. Again, we’ll call them lat2 and lon2.
   
3. Go to the following location: https://api.hackair.eu/hackair_data?location=lon1,lat1|lon2,lat2 (replacing the lon and lat variables with your actual values)
   

Now you can either add this link to data.world (as explained above) or transform it to a csv file using a JSON to CSV service (like Konklone.io/json). After you import or connect this data to Google Data Studio, you’ll again need to make a few adjustments:

• date_str: set the type to Date Hour (YYYMMDDHH)
  
• pollutant_q_value: set the aggregation to None
  

Now add two new fields:

• with the formula “CASE WHEN pollutant_q_name=”PM10_AirPollutantValue” THEN pollutant_q_value ELSE 0 END”. Set the aggregation to average.
  
• coordinate with the formula “CONCAT(loc_coordinates_1,”,”,loc_coordinates_0)”
  

You can now add a map to your report. Here are, for example, some of the latest measurements in the Netherlands:

Over to you!

• What other ideas do you have to visualise and use the data you collect through hackAIR? Any cool tools we’ve missed?