Data Processing Archives - Visokio

How to build a live data transaction report

Antonio Poggi — Tue, 07 Dec 2021 16:43:49 +0000

Data is cheap, insight is priceless, as they say…
Connecting a modern BI app to an open data firehose to build automatically updated visuals can make that transition happen.

The first step in the process is thinking about meaningful questions, so a report can be developed to deliver relevant answers.

With the world’s attention span getting shorter, the analyst will also want to deliver these with a minimal number of charts. This is where the data artisan skills become useful…

Open data sources are everywhere and, in many cases, they come in a format that is not accessible to all potential users (xml, JSON, not just Excel), which means that only patient/ data literate users can interrogate the information and extract meaningful insights.

Curious? This tutorial will show you how to build the interactive dashboard presented below. Read more…

Feel free to play with the charts and filters to see the effects!

The post How to build a live data transaction report appeared first on Visokio.

How to Visualise Geographic Time Data – Analysis of the Water Quality in Europe

Paola Tomei — Mon, 07 Jun 2021 16:01:39 +0000

The Findings

Are you only as good as your last performance? Analysis of the water quality in Europe in the year 2020 looks impressive – an assessment of coastline, lakes and transitional waters rated the majority of locations as “excellent”. Star country Cyprus achieved a 100% rating, while only two countries recorded less than impressive results.

Focusing on Poland’s performance we see that only 22% of the sampling locations are rated “excellent”, also that 401 data points out of 602 never submitted a measurement. With these records filtered out, the situation has changed: 66% of the submitted results are rated “excellent”, which is in line with the country’s results in the last 10 years. It is worth noting that Poland recorded dramatic improvement in 2011, jumping from just 24% to 67%. So – Poland’s result can be attributed to the data recording method, rather than an actual poor performance.

(Link to the interactive report)

On the other hand – the UK has shown notably poor records against the European neighbours in the last 10 years and the country has managed to record a performance over 80% only in 2013.

According to the Guardian “All English rivers have failed to meet quality tests for pollution amid concerns over the scale of sewage discharges and agricultural and industrial chemicals entering the water system.” Also that “water companies had poured raw sewage into rivers on more than 20,000 occasions in 2019, and dumped thousands of tonnes of raw sewage on beaches.”

Similarly to Poland, only a subset of the 640 registered measuring locations reported results in 2020 (183). When only those results are taken into account, the rating is 60%, which still leaves the UK at the bottom of the league.

This leads us to another question – how come that Great Britain, an island country with a coastline which is 12,429km long, has only 640 registered water quality measuring stations? Greece’s coast is 13,676km long an

d Italy’s coastline is about 7,500km long. Italians are measuring the quality of water in over 5.5K locations, France over 3.2K, Germany 2.2K and Greece in 1,634.

The most encouraging finding is that all countries (apart from the UK) have shown significant improvements over time, especially countries who joined the EU in the later wave, like Romania (where the proportion of ‘excellent’ ratings was increased from 6% to 70% in 13 years). EU membership candidate Albania has also improved from 51% in 2013 to 76% in 2020.

The UK has chosen to opt out of the EEA membership following Brexit, so this dataset will be the last one to include the UK and compare it to other European countries on a like-for-like basis.

How we made it: The data visualisation challenges

Every data visualisation has two phases: first the analyst needs to explore the data – to sort, compare, isolate, and identify relationships or trends. The second task is to communicate the findings effectively to an audience. Charts and tools used in the first ‘probing’ and exploratory phase are not necessarily the same visualisations to use for the story-telling presentation.

A challenge in this report was to create a comparison of results in the year 2020, on a geographical level, then provide perspective about the developments over time and explain how the countries got there. Is a good / bad result an outlier or is in line with a long-term trend?

From the macro level we can zoom into each individual performance and drill all the way down into the results for each measuring location. In two tabs we are moving from the continent/country aggregation down to your local beach/ river/ lake. We are going on a journey in space and time.

On the first tab the map with shapefiles aggregates the countries’ latest results and instantly identifies the two ‘offenders’ – a view that is complemented by the two bar charts: the first one ordering the % scores and another one adding context with regards to the number of measuring locations per country.

In a dataset with 30 countries and 30 years of data, it was useful to create a benchmarking effect and highlight one country of interest, while keeping the others in the frame grey – see the layered line view, which is driven by the Country Choice variable (configured in the Report Data Sources).

On the Country tab the viewer can follow the progress of one country at a time (filter choice limited to 1) and explore the spatial distribution of measuring places, as well as the performance over time on both the aggregated level and individual location level.

Data titles on both tabs are dynamic,

created by using Content view with integrated formulas, responsive to filtering; interactive heatmap is a Pivot chart with the cell values removed.

In this report not a single data point is wasted – curious viewers will be able to check the water quality before they dip their toes into the river, lake or the sea of their choice.

Londoners will have to satisfy themselves with the results for the Serpentine lake (rated ‘poor’ in 2020) and hope that Thames water will be rated soon!

Data transformation and preparation challenges

The dataset comes from the European Environment Agency: each record represents a measuring location, while each year of results was added as a new column. This is a straight-forward de-pivoting exercise, where we go from 43 fields x 22,276 records to 14 fields x 690K records! https://www.eea.europa.eu/themes/water/europes-seas-and-coasts/assessments/state-of-bathing-water/state-of-bathing-waters-in-2020

Another challenge was to handle the 3 location Management status fields (Management2018, Management2019, Management2020), which were applicable and populated only in the last 3 years. De-pivoting them with the rest of the dataset was not an option. These fields were therefore isolated, de-pivoted on their own, then merged with the main dataset, so they joined only the relevant records (merging on both location ID and Year fields).

Data transformation depends on the visualisation requirements – once the basics are done (field formatting, cleaning, classification, validation) requirements will come from the visualisation. Is the data orientation suitable for the charts, what is the data granularity required for the analysis? Are all the fields in the dataset relevant? The cool thing about Omniscope is that the ETL (transformation) and visualisation components go hand-in-hand. The visualisation can come first and be used for the data diagnostic purposes, to decide on the course of action, then again at the end, for the data presentation. The analyst can seamlessly go back and forth, flip between the two modes, and make changes to the underlying data, even while in the middle of the visualisation job, to quickly shorten the label, change a data field format or add a new calculation.

The post How to Visualise Geographic Time Data – Analysis of the Water Quality in Europe appeared first on Visokio.

How to set parameters and trigger workflow execution from a Report

Antonio Poggi — Wed, 26 Aug 2020 20:18:29 +0000

There are sooo many use cases possible in Omniscope Evo by leveraging the fully integrated data processing + visualisation app capabilities together with the Workflow API and Custom Views.

As an example, in this particular video, we show you how you can set “project parameters” used in workflow configurations and trigger a data execution from a Report.
A way to allow your end users to refresh dashboards and complex workflows simply by tweaking some preconfigured params.

Enjoy
Feedback is always welcomed

The post How to set parameters and trigger workflow execution from a Report appeared first on Visokio.

Rocking after Rock – new Omniscope features

Antonio Poggi — Mon, 27 Jul 2020 11:03:38 +0000

Difficult times can help spark creativity, so we’ve tried to use this tricky period to inspire some clever solutions.

Take a look at these new features – they should bring a smile to your face.

Data editing

Data Table block in the workflow, linked to the improved Table View in the Report, empowers project editors and report viewers to snapshot, query and edit data, to amend mistakes or add comments and push the edited data ‘upstream’ into the workflow. Read more

Data validation

Validate data block can automate multiple data quality scans, such as data schema and values. Create alerts should something fail the criteria or add an output with diagnostics report, telling you about everything that failed, so you can quickly identify the problems. More..

Data alerts

Email block makes communication an integral part of the data management process: notify the report viewer that their report is ready, or let the analyst know they need to fix something, by sending them those validation diagnostics.

Before you ask – email output is integrated with the Batch output block, so you can use it in the multi-user publishing scenarios. More…

Block bookmarks

Bookmark frequently used workflow blocks to avoid having to configure them repeatedly – works for the multi-tab Reports and coding blocks too!
Find more here

Salesforce integration

The Salesforce connector is now added to Evo, making it easy to integrate SF API with data coming from other sources to create a complete overview of your business, edit the CRM data, even publish it back to Salesforce, should you wish to. Read more

Finally – we’ve added some features to make the report formatting easier.

As usual, make sure to check our changelog to see features making their way daily.

Stay tuned, stay safe and let us know if you have any feedback

#TeamVisokio

The post Rocking after Rock – new Omniscope features appeared first on Visokio.

How to Edit data and Execute Workflow from Report

Antonio Poggi — Wed, 06 May 2020 15:27:26 +0000

We’ve recently developed the Data Table block and added editing capabilities in the Table View.
Please follow KB article here https://help.visokio.com/support/solutions/articles/42000077480-data-table-block-edit-the-data-in-workflow-or-report

Editing + Workflow Execution

Attached here you’ll find a IOZ file to import the project locally to play with 2 interesting features:

1. Edit data from a Report using the “Editable Table” view. When connected to a Text Input or Data Table block the view allows you to perform data edits as if it was a simple spreadsheet app.
2. Execute specific blocks of a Workflow through a Custom view in the Report app which uses the Workflow REST API

A simple custom view to control Workflow Execution from the Report has been added to our GitHub repo, you’ll find it in your Omniscope Evo by clicking on the Add View menu (alt+A) -> and selecting or typing “Workflow Execution”

A video which describes the project is available here. Enjoy.

The post How to Edit data and Execute Workflow from Report appeared first on Visokio.

How to build a Twitter bot posting data alerts

Antonio Poggi — Fri, 25 Oct 2019 15:24:01 +0000

In this post I am going to show you how easy is to build a data workflow capable of sending alerts on Twitter, all integrated in one solution.

This is something we have done to help measuring air pollution in Puglia (Italy), but you can apply this concept to anything. For instance you might want to Tweet financial news when data meets a certain threshold, or tweet an update on your company page when a new feature is released.

Step by step, here is described how we built and power this Twitter Omniscope data alerts bot – @OmniscopeBot.

An example tweet:

Omniscope data alerts bot on Twitter

Air pollution alert – https://t.co/5TcJZ0Vhv8 – daily average over limit: * Brindisi – Via dei Mille, PM10: 82.47 µg/m³;

1. Setup your Twitter developer account and create your app

To build your bot the first thing to do is to apply for a Twitter developer account on developer.twitter.com .

You can build your profile for personal use or for your organisation, either ways Twitter needs to know what’s the reason for the developer account, so you have to specify the details to get approved, making sure to be thourough in the explaination.

Once your account is verified you can create your Twitter app, which it will be in practice used by your bot to tweet.

The main things to obtain once you have created your Twitter app are: the API keys and access tokens. They allow a script (e.g. a Python script) to communicate with your Twitter app.
On the Twitter app page, go to the App details to obtain the 4 keys and tokens. N.B. anyone with these keys will be able to access your account, keep them safe!

2. Setup the data workflow

In our case the workflow is obtaining data from a remote service, so we are pulling data in Omniscope, cleaning, transforming, and then we have a path to validate the data: any measurement above limit will be gathered, ready to be used to compose the text to tweet.

This is the resulted workflow:

Arpa Puglia Monitor

No Description

The bottom right part (highlighted in yellow) is the one which aggregates the data to evaluate a sum, to then compare the measurements against the limits.
If some rows are produced by the record filter block (the data over the limits, signifying anomalies) the information is sorted and passed to the Python – Twitter block, where the tweeting happens.

3. Setup the Python to post updates / alerts

To tweet we use a very common Python library https://pypi.org/project/python-twitter/ , which allows you to authenticate to your account through the API keys and tokens, post updates to your account and more.

This is the content of the Custom Python block in Omniscope which gets the input data, builds a message and tweets on the bot account.

import twitter, sys

if len(input_data) <= 0:
sys.exit(2)

#Omniscope Bot
api = twitter.Api(consumer_key="xxx",
consumer_secret="xxx",
access_token_key="xxx-xxx",
access_token_secret="xxxx")

message="Air pollution alert - https://bit.ly/ArpaPuglia24h - daily average over limit: "

for index, row in input_data.iterrows():
message+= " \n• "+ row['Centralina'] + ", " + row['Inquinante'] + ": " + str(row['Media giornaliera']) + " "+ row['Unita Misura']+";"

try:
status = api.PostUpdates(message)
except UnicodeDecodeError:
print("Your message could not be encoded. Perhaps it contains non-ASCII characters? ")
print("Try explicitly specifying the encoding with the --encoding flag")
sys.exit(2)

output_data = pandas.DataFrame([message])

Note that the consumer_key, consumer_secret, access_token_key, access_token_secret have been obscured, and those are the only bits you have to customise to allow the script communicating with your Twitter app.

The PostUpdates method is the one which posts a tweet on your page. In this case we are concatenating rows from the input data to build a message showing all measurements which are above a certain limit.

4. Automate the workflow

The last step obviously is to automate the workflow, to run at certain time / interval. This is done by setting up a Omniscope Scheduler task which re-execute the whole workflow to get new data, validate the information and tweet in case of anomalies.

Enjoy!

The post How to build a Twitter bot posting data alerts appeared first on Visokio.

How to compare R and Python algorithms – Clustering example

Antonio Poggi — Fri, 06 Sep 2019 13:47:15 +0000

Clustering analysis using Omniscope Evo, comparing R Gaussian Mixture Models and Python DBSCAN implementations.

The post How to compare R and Python algorithms – Clustering example appeared first on Visokio.