Excel examples for your work, sports and more.: 2021

26 Dec 2021

Power BI Animated Bar Chart Race for Formula 1 season 2021

#78: Power BI Animated Bar Chart Race for Formula 1 season 2021

This post is about a Power BI report I made after Max Verstappen's victory of the Formula 1 championship 2021, a historic victory for the Netherlands as he is the first Dutchman to win a Formula 1 world championship. It was a battle between Lewis Hamilton and Max Verstappen, which was decided in the last race, and even in the last lap:
https://www.youtube.com/watch?v=MTe12fH2xtQ

Report

On this page: https://www.formula1points.com/season/season-progression/2021
I saw a line-chart which has a line-chart that shows how close Verstappen and Hamilton were until the last race. I added this chart to my dashboard (see FIG.1).

FIG.1: Line-chart timeseries cumulative points per driver

But to 're-live' the F1 2021 season, I also added 2 'racing bar charts', so an animation with the total points per driver over time:

*racing bar chart 1:

for this I used custom visual 'Power BI Animated Bar Chart Race', see e.g:
How to create Animated Bar chart race in Power BI

https://inovista.com/animatorFiles/demoProjects/BarRacePBI.html

NB: this visual has a limit of max 20 bars (while in F1 2021 season there were 21 drivers..)

FIG.2: Animated Bar Chart Race

NB: for a video-recording I made of this chart, see:

https://youtu.be/Fh4mRA_s8mo

*racing bar chart 2:

for this I used custom visual 'Play Axis', see video:
Guy in a Cube: Can we have ANIMATED Power BI visuals

FIG.3: Play Axis

Chart-1 is IMO the most fun one, as bar-charts (one bar per F1-driver) are moving up and down based on the driver's cumulative points after each race (date).
The date in this chart I had to format as a number (YYYYMMD), else the date-counter (bottom-right of chart) stayed 0. For more details, see:
https://community.powerbi.com/t5/Desktop/Period-Shown-in-Animated-Bar-Chart-Race/m-p/822985

NB: On the charts, I added this photo as a background:
https://commons.wikimedia.org/wiki/File:2015_Malaysian_GP_opening_lap.jpg
from https://commons.wikimedia.org/wiki/User:Morio
This file is licensed under the Creative Commons Attribution-Share Alike 4.0 International license

Data

The main data source that I have used is:
https://www.formula1points.com/season/season-progression/2021

Unfortunately, this page did not have the race-date, just the race/round-nr, and the date-field is a must-have for a racing bar chart. To get the race-dates, I found this F1-API:
http://ergast.com/mrd/

As a best practice, I also created a table for the Date-dimension, for how to do this, see e.g.:
https://docs.microsoft.com/en-us/power-bi/guidance/model-date-tables

My data source has the points per round, but for the charts, I needed the cumulative points.
To calculate this, first thing I did was to add a calculated Date-column to the Points-table, and as I created relations between this Points-table and the Rounds/Date table, I could do this with this DAX-formula:

Date = RELATED(Rounds_Dates[Date])

With this, I could use the DAX-pattern for the 'running total per group' (where in this case, the group is the driver, so for each driver, I needed to accumulate the points for the in total 22 races):

Points running total in Driver = 
CALCULATE(
SUM ( Points[Points] ),
FILTER (
    ALL ( Points ),
    Points[Date] <= MAX ( Points[Date])
        && Points[Driver] = MAX ( Points[Driver] )
) 
)

For more details about this DAX-formula, see e.g:

https://stackoverflow.com/questions/30072341/dax-running-total-multiple-critiera-grouping

https://www.daxpatterns.com/cumulative-total/

There were some other issues with the data source (e.g. race-points sometimes had a red star (*),

or for round 8, the country-name was not correct. If someone knows a better site with Formula-1 data, please share, for if I would make for the new season a new report (only if Verstappen wins ;)

Interesting reads

https://community.powerbi.com/t5/Data-Stories-Gallery/Formula-1-analysis-1950-2021/m-p/2052947

https://towardsdatascience.com/formula-one-extracting-and-analysing-historical-results-19c950cda1d1

https://www.linkedin.com/pulse/data-analysis-decision-making-formula-1-part-1-abhishek-kumar/

Downloads

Power BI file

Embedded report

6 Jun 2021

#77: Power BI report with ArcGIS map: "COVID-19 in USA"

This post is about a new Power BI report I made: "COVID-19 in USA", see FIG.1.

FIG.1: Power BI report COVID-19 in USA with (animated) ArcGIS map

In this report I used the ESRI ArcGIS map. I used this map also in my previous post, but this time I wanted to experiment with a nice feature of the ArcGIS map: animation of time-series. The dataset I used for this is open data from the New York Times:

https://raw.githubusercontent.com/nytimes/covid-19-data/master/us-states.csv

NB: for more details about this NYT COVID-19 data, see e.g.:
https://developer.nytimes.com/covid
https://www.nytimes.com/article/coronavirus-county-data-us.html

The dateset's oldest data is from Feb.2020 and it is updated daily.
I have limited the number of data-points by filtering only the first day of the month. See FIG.2 for the Power Query I used for this:

FIG.2: NYT Dataset and applied transformations (in Power Query)

As a reference-layer for the map I used demographic data, e.g. US Median Age. And I also added an infographic with this data (median age), and also for the total population.

I made this video with the animated map and how to interact with it in Power BI, e.g by filtering by dimensions location (state) and time (year/month):

https://youtu.be/se5pLNCdo0M

NB: I used this nice free tool https://www.screencastify.com/ to make this screencast.

My Power BI report also has a time-slicer, so you could check e.g. : 'what was the total number of COVID-19 cases in 2020 (at 1/1/2021)', see FIG.3 (which also includes a screenshot of this site: https://datausa.io/coronavirus, which I used to check the numbers in my report)

FIG.3: total COVID-19 cases in USA in 2020

To know more about how ESRI ArcGIS maps are used in this pandemic, I can really recommend this video of ESRI:

COVID-19 & Crisis Management - A GIS Approach

And for a another nice video of ESRI in which they show how AI (Machine Learning) can be used to do COVID-19 predictions, see:

Modeling COVID-19 in ArcGIS

In this video, they say:

It has been amazing to watch how the GIS-community has done an amazing work in the COVID-19 response, from data collection, modelling and data-visualization..

I've also seen great initiatives in the Netherlands about which I wrote in earlier blog-posts, e.g.:

- data collection:

CoronaWatchNL - open COVID-19 data of Netherland, by Jonathan de Bruin of Utrecht University

https://dataverse.harvard.edu/dataverse/covid-19-eu
by Vyacheslav Tykhonov #4tikhonov

- dashboard:

ESRI Nederland COVID-19 dashboard with ArcGIS map

References

*COVID-19 in USA

https://www.nytimes.com/interactive/2020/nyregion/new-york-city-coronavirus-cases.html

https://datausa.io/coronavirus

https://covid.cdc.gov/covid-data-tracker/#datatracker-home

https://www.worldometers.info/coronavirus/country/us/#graph-cases-daily

https://news.google.com/covid19/map?hl=en-US&mid=%2Fm%2F09c7w0&gl=US&ceid=US%3Aen&state=1

*Power BI and ArcGIS

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualizations-arcgis

ArcGIS Maps for Power BI: Demographic Data

https://doc.arcgis.com/en/power-bi/design/map-time-aware-data.htm

* GIS and Location Intelligence

COVID-19 & Crisis Management - A GIS Approach

Modeling COVID-19 in ArcGIS

Using spatial analysis to understand key factors in the COVID-19 epidemic

Analyze and Visualize the Impact of COVID-19 Pandemic

Downloads

Power BI file

31 May 2021

Power BI report with ArcGIS map: COVID-19 in the Netherlands

#76: Power BI report with ArcGIS map: "COVID-19 in the Netherlands"

Power BI has several types of maps, and one of them is the ArcGIS Map, which I used in a new report "COVID-19 in the Netherlands", see FIG.1.

Update 6/6/2021: I made a new Power BI report for COVID-19 in USA, which also uses a ArcGIS-map, and includes also a video of the report, see:

https://worktimesheet2014.blogspot.com/2021/06/77-power-bi-report-with-arcgis-map.html

https://youtu.be/se5pLNCdo0M

FIG.1: Power BI report with ArcGIS Map, combining COVID-19 data and (demo) employee-address data

ArgGIS is GIS-software of company ESRI. The ArcGIS-map in Power BI is both available for ArcGIS-users as for people without a ArcGIS-license (as is my case), although the latter group has can't use all map-features.
NB:
-I read here that ESRI offers a free (trial) of ArcGIS-Online to companies that use their maps to fight COVID-19.
-The famous COVID-19 dashboard of the Johns Hopkins University is also made with ArcGIS

ESRI Netherlands offers maps of the Netherlands with (actual) COVID-19 data, e.g. the map "#Diagnosed per 100K inhabitants in the last 7 days per municipality", see:
https://www.arcgis.com/home/item.html?id=aafffb2776194e0db063b7b7e751997d
This map I used as a reference layer (see here how to do this) for my Power BI report, see FIG.2.

FIG.2: Power BI report with ArcGIS Map which includes a reference-layer with COVID-19 data

If you click on a municipality, you can see the related ArcGIS-data (e.g. for municipality Zwijndrecht: it has 44K inhabitants, and in the last 7 days, 93 (new) COVID-19 cases (Diagnosed):

FIG.3: Power BI report with ArcGIS Map with card showing Municipality-data and COVID-19 data that is included in the map.

On top of this COVID-19 map layer (municipalities filled with color brown) is a layer with the 'actual' data: the number of employees (of a fictitious company) who live in a certain municipality (the green 'bubbles').
NB: in my previous post I had a similar map-report, made with Report Builder (see FIG.4 of that post), but creating that one, was much more work as I had to collect and transform the COVID-19 data myself, and also configure the shape-map, while with the ArcGIS-map, this comes 'out of the box'.

And as is always the case in a Power BI report, the map and other widgets in the report are interconnected, so if you filter e.g. in the bar-chart on Utrecht (municipality where 18 employees live), automatically the map zooms in to this region:

FIG.4: Filtering chart > zooming map

I also made a mobile version for my report (which does not have the bar-chart of the desktop-version, to adapt to the (smaller) screen-size of a mobile):

FIG.5: Power BI report on mobile

Note that my map (the brown reference-layer with COVID-19 data) is the same as that in the COVID-19 dashboard of ESRI NL:

FIG.6: ESRI NL COVID-19 dashboard

https://experience.arcgis.com/experience/ea064047519040469acb8da05c0f100d

References

*Power BI:

ArcGIS Maps for Power BI

Microsoft Power BI: Deep dive into the ESRI Map Visual in Power BI

At Home with Power BI - ArcGIS maps

Mapbox v ArcGIS v Power BI Maps v Azure Maps. Which one should I use in Power BI?

https://powerbi.microsoft.com/en-us/blog/how-public-agencies-keep-communities-informed-on-covid-19-with-power-bi/

https://docs.microsoft.com/en-us/power-bi/create-reports/sample-covid-19-us

*ESRI ArcGIS

https://coronavirus-resources.esri.com/

https://www.esri.com/about/newsroom/arcuser/johns-hopkins-covid-19-dashboard/

https://www.arcgis.com/apps/dashboards/85320e2ea5424dfaaa75ae62e5c06e61

*ESRI NL

https://nlcovid-19-esrinl-content.hub.arcgis.com/

*GIS

Geographic Information Systems (GIS): Dan Scollon at TEDx Redding

Downloads

Power BI file

16 May 2021

Power BI dashboard "COVID-19 in the Netherlands" with CoronaWatchNL-data

#75: Power BI dashboard "COVID-19 in the Netherlands" with CoronaWatchNL-data

This post is about my third (and last) dashboard "COVID-19 in the Netherlands", with data from CoronaWatchNL. I made this dashboard with Microsoft Power BI. The 2 previous dashboards in this series, which I made with Tableau and Google Data Studio, you can find here and here. In March 2020, I also made a Power BI COVID-dashboard (see here), but the data in that dashboard was not refreshed automatically, while this happens in this new (2021) Power BI dashboard. And the new Power BI report has some other improvements as:
- it has a has a responsive design, so it fits well on each type of device (see FIG.1)
- it has a (new) (shape-)map
- it has a improved dataset, which made it possible to combine the 3 COVID-19 metrics (# Diagnosed, Hospitalized and Deaths) in one chart

FIG.1: My COVID-19 dashboard in Power BI, on tablet, laptop and mobile

Shape Map

The report has the Shape Map custom-visualization Drilldown Choropleth (which you can download here), with 2 layers: provinces and municipalities in the Netherlands, which data is in these 2 TopoJson files:

https://raw.githubusercontent.com/cartomap/nl/gh-pages/wgs84/provincie_2020.topojson

https://raw.githubusercontent.com/cartomap/nl/gh-pages/wgs84/gemeente_2020.topojson

And I saw on the website of these files, so:

https://github.com/cartomap/nl

https://cartomap.github.io/nl/

that they also have other TopoJson-files, e.g. for GGD (Dutch Municipal Health Services), which could be interesting if you would like to make a GGD-map, so one that shows the COVID-19 numbers in the 25 GGD-regions.

FIG.2: Shape Map, showing layer-1 of map: Provinces

FIG.3: Shape Map, showing layer-2 of map: Municipalities

NB: The (score-)Card 'Total Diagnosed' shows a 'normal' number, so not abbreviated as 331K, and how to do this, I found on:
https://community.powerbi.com/t5/Desktop/Number-Formatting-on-Card-Visualization/m-p/229483

In the beginning of the COVID-19 outbreak in the Netherlands, in March 2020, I made a similar Shape-map for my work, with Microsoft Reportbuilder. This is a free 'classical' reporting- tool (download-link) to make paginated reports that can be printed, while more modern reporting-tools as Power BI are meant for visual exploration ('slicing-and-dicing') of the data.

My COVID-19 Report Builder report (see FIG.4) shows 3 kinds of information:

*A: the Dutch municipalities where your employees live, ranging from 0 (white) to 100 (dark grey)

*B: # COVID-19 cases, the green/yellow/red circles, ranging from 0 (green) to 100 (red)

*C: # employees that live in a municipalities with COVID-19 cases, ranging from 0 (small circle) to 100 (big circle)

The goal of this report was to see if a company has employees which live in municipalities with a high number of COVID-19 cases and if so, propose them to work from home. FIG.4 shows the situation (for a fictitious (test-)company) in March 2020, when the (known) COVID-19 cases were concentrated insmall amount of municipalities. The first weeks, we updated this COVID-19-report frequently, to adapt the max. values for # COVID-19 cases (B in Legend of map), but as soon COVID-19 was spread in all the Netherlands, we stopped with it, and the report was not needed anymore as everyone started working from home (and more than 1 year later, most of us still are...).

FIG.4: Microsoft Report Builder report that combines COVID-19 data (in beginning of outbreak) and employee-location data.

Update 31/5/2021: for a new version of this report, now made in Power BI with a ArcGIS-map, see the next post.

Dataset

The 3 COVID-19 metrics that I use in my report (# Diagnosed, Hospitalized, Deceased) are stored in the CoronaWatch-files in such a way that it would not be possible to draw more than 1 metric in a chart (each metric is in a separate row, instead that they are all in 1 row). Power BI has the Power Query language 'M' to do all kind of data-transformations, and in this case I used the Pivot-function to get all the 3 metrics from 3 rows in 1 row with 3 'metric-columns' (see FIG.5-7).
NB: For more info about this function, see e.g.:
https://radacad.com/pivot-and-unpivot-with-power-bi

The CoronaWatch-files has both the metric day-values ('delta') as the total (cumulative) values, and to be able to do the pivot, I had to split these 2 metric-values in 2 separate datasets, e.g.: 'RIVM_NL_municipal_latest_NewCases' and 'RIVM_NL_municipal_latest_TotalCases', and then join them together (resulting in dataset 'RIVM_NL_municipal_latest'), what can be done in M using the Expand function (see FIG.5-7). To be able do this join, I created a datamodel in which I linked the 3 datasets (1:1 relations) (see FIG.8).
NB: For more info about this function, see e.g.:
https://radacad.com/how-to-change-joining-types-in-power-bi-and-power-query

For dataset 'RIVM_NL_municipal_latest' , I used 2 other M-functions:

- Sort, so that table is sorted by date, province and municipaltiy.
NB: For more info about this function, see e.g.:
https://support.microsoft.com/en-us/office/sort-a-table-power-query-baf200d5-1097-49cf-80b3-f1ce07b5f114

-Replace, to get rid of NULL values.
NB: For more info about this function, see e.g.:
https://www.youtube.com/watch?v=EoRjNx1G2g4

FIG.5: datasource of report

FIG.6: datasource of report and transformation of data with M (Power Query)

FIG.7a: datasource of report and transformation of data with M (Power Query)

FIG.7b: datasource of report and transformation of data with M (Power Query)

FIG.8: tables of datasourcein Power BI Datamodel

In the upper chart in tab-2 of the report you can an example of a chart that has now more than 1 metric (e.g. Hospitalized and Deceased).

Mobile report

I made a mobile layout for the report, so it automatically displayed in the best format for the device (PC, mobile or tablet) on which the report runs (as you can see in FIG.1).

For a nice video that explains all the features of the Power BI Mobile app (so Power BI for mobile devices as smartphones and tablets), e.g. natural language query and query by voice-message, see:
https://youtu.be/pF_ST8B_XU0

FIG.9: mobile report design in Power BI Desktop

Power BI Service (cloud) features: Dashboards,
Machine Learning generated reports, natural language query, sharing and colaboration

After publishing a Power BI report to the cloud (Power BI Service), I normally do a quick check with the 'Quick Insights' feature to see if the build-in AI (Machine Learning) detects some interesting patterns which I didn't see, so also this time. It detected e.g. there is a correlation between Diagnosed and Hospitalized, or that in April 2020, there the Hospitalized-number had some outliers.

FIG.10: Quick insights on datasets

FIG.11: Quick insights Reports

FIG.12: Quick insights Report: correlation between Diagnosed and Hospitalized numbers

For more details about Quick Insights / AI in Power BI, see:
https://docs.microsoft.com/en-us/power-bi/consumer/end-user-insight-types

In the Power BI Service, it is possible to pin elements of a report to a dashboard, to show for example just some high-level numbers that fit on 1 screen, which can be usefull for a CEO, who might not be interested in more detailed break-down charts with which you can interact, which might be more a task for a business analyst. I made a dashboard (for PC and mobile) that shows the 3 main COVID-19 metrics (total Diagnosed, Hospitalized, Deceased), and by clicking on them, you go to the corresponding report of these charts. And the dashsboard has also 3 charts coming from 'Quick Insights' (so charts which were not in a report I made).

FIG.13: Power BI dashboard (desktop version)

FIG.14: Power BI dashboard (mobile version)

Power BI also has colaboration feaureIt is possible to comment on / annotate dashboards and reports that are in the Power BI Service. Here you can see some examples of this (from mobile):

FIG.15: comment in (mobile) report

FIG.16: anotate in (mobile) report

In Power BI Service, it is now also possible to create reports (before, it was only posssible todo this with Power BI Desktop). So if you share your workspace with the datasets that you created (in Power BI Desktop) with someone else, he can create his own report for this, as you can see here:

FIG.17:Power BI report made in the cloud (Power BI Service)

In Power BI Service, you can set the schedule for the refresh of the data of your datasource, see. e.g.: https://docs.microsoft.com/en-us/power-bi/report-server/configure-scheduled-refresh
For My COVID-19 report I set the refresh-schedule to Daily, as this was the frequency with which my datasource (CoronaWatchNL ) was updated, but... :

End of CoronaWatchNL and consequence for my dashboard

CoronaWatchNL stopped at 17/4/2021, so my Power BI dashboard (and also the 2 other ones in Google Data Studio and Tableau) are not COVID-19 live-trackers anymore. CoronaWatchNL recommends to use the data of the RIVM-website, which is now of good quality, making CoronaWatchNL's work not necessary anymore as they say.

Although CoronaWatchNL is history now (and hopefull COVID-19 will soon be as wel..), we should remember that in the period of over 1 year of CoronaWatchNL's existence, this was the best (Dutch) COVID-19 datasource for reporting, especially in the beginning of 2020, when using the RIVM-data for reporting was not really an option. For the details why, read this article of Follow the Money, for which CoronaWatchNL-founder Jonathan de Bruijn was also interviewed, and made this nice statement about the RIVM initial problems with sharing COVID-19-data:

We hebben [aan RIVM] toen aangeboden om te helpen die [COVID-19 data op RIVM-website) te structureren, maar daar hebben ze geen gebruik van gemaakt.’ Met een lach voegt hij toe: ‘Waarschijnlijk hadden ze het te druk om te zien dat wij ze hadden kunnen ontlasten.’

So CoronaWatchNL, Jonathan and Roos and all other people who helped in this Open Data project, thanks for all the effort!

I'm not sure yet if I will still do this datasource-replacement (so CoronaWatchNL -> RIVM) for my COVID-19 in NL dashboards, but I've seen a Tableau Public dashboard that did do this, see:
https://public.tableau.com/profile/jolanda.luime#!/vizhome/CoronaMonitorNoord-Brabant/OntwikkelingNederland

which has this datasource-link:
https://www.rivm.nl/en/novel-coronavirus-covid-19/current-information

Microsoft Power BI Desktop vs Tableau Public vs Google Data Studio

So I used for my COVID-19 Dashboard these 3 free tools to create them:

- Microsoft Power BI Desktop

- Google Data Studio

- Tableau Public

Which one would I recommend to someone who is looking for a free BI-tool?
Tableau Public has the big limitation that you can only store your work on their website: https://public.tableau.com. For me, this was not a problem, as my report was for a (public) COVID-19 dataset (CoronaWatchNL), but I guess for most reports you don't want to make it public, but just share it with your co-workers. Another big disadvange of Tableau Public is that the number of possible datasources you can connect to is limited.

So that leaves Microsoft Power BI Desktop and Google Data Studio. If you don't have a paid Power BI license, and so you don't have an account for Power BI Service (cloud), your sharing-options are more limited. You can share your report by email or embedding your report in your website, as I did here (see the end of this blog post). With Google Data Studio, sharing is just as easy as for any other Google Drive file (e.g. a Google Sheets doc). And if your dataset is already in a good shape for visualization (so no data-transformations needed), I think Google Data Studio is the best option. But if you have to prepare your data before you can make your dataviz (as I had to pivot the data of CoronaWatchNL) or want more advanced datav-options (as the Shape Map for Dutch municipalities that I used), then I would recommend Power BI.

I also googled a bit to see what others say in a comparision of these BI tools, and I found in a post about Google Data Studio here this statement:

Not everyone needs an airplane to get to work in the morning

And this post, from 2016, when Google Data Studio was just made general available, also says:

The market is now starting to make visualization solutions accessible to small organizations without the means to pay a team of elite analysts and developers to take care of visualizing their data.

So what they mean is that Google Data Studio (100% free) would be a better option for a lot of (smaller) companies than Power BI or Tableau (which are not free if you want to use all it's features). For a more recent (2021) review of GDS, see e.g.:
https://towardsdatascience.com/google-data-studio-for-beginners-99355e9df809

If the choice for a BI-tool wouldn't be constrained by budget, then at this moment (2021), according to the Magic Quadrant for Analytics and BI tools of analyst house Gartner, Power BI would be the best choice, see e.g. :

https://powerbi.microsoft.com/en-us/blog/microsoft-named-a-leader-in-2021-gartner-magic-quadrant-for-analytics-and-bi-platforms/

AI - COVID-19 hackathon University of Groningen

University of Groningen (RUG) will organize a AI - COVID-19 hackathon #aiHackCovid, and they asked me if I could maybe tell something about my COVID-19 dashboards. If you want to be a speaker, participant of the hackathon, or a member of the jury, let them know.

https://aihackcovid.aimedgroningen.com/

Update 27/6/2021 (after my presentation):

Here a post on Linkedin, which includes the powerpoint I used for the presentatio

And now for something completely different:

The Johan Cruyff Foundation, who work to make sport accesible for all children, including e.g. disabled children, organized a 14K virtual run to raise money, and the runners can help with that by asking people to sponsor them. I participated on 25/4/2021 (although the race is open until 19/5), see my Strava-activity, and raised 102 euro so far. If you also want to be my sponsor and donate something, you can do so here:

https://acties14k.cruyff-foundation.org/actie/maarten-van-reek?utm_source=kentaa&utm_medium=email-share&utm_campaign=cruyff-legacy-14k&locale=en

Here a video of the good work the foundation did on Greek islands with refugee-camps:
https://www.cruyff-foundation.org/en/news/first-cruyff-court-in-refugee-camp/

https://pt-br.facebook.com/cruyfffoundation/videos/192881359314861/?__so__=permalink&__rv__=related_videos

References

*Power BI:

https://www.datacamp.com/community/tutorials/data-visualisation-powerbi

https://blogs.msn.com/tools-to-sharpen-your-data-journalism-skills/

https://news.microsoft.com/microsoft365forjournalists/

https://news.microsoft.com/microsoft365forjournalists/learning-tools/visualizing-data-with-power-bi/

https://news.microsoft.com/wp-content/uploads/prod/sites/557/2020/11/AnatomyPoster-12.01.20.pdf

https://docs.microsoft.com/en-us/power-bi/consumer/end-user-insight-types

https://carldesouza.com/using-quick-insights-in-power-bi/

https://community.powerbi.com/t5/Data-Stories-Gallery/Covid-19-Report-Global-and-Netherlands/m-p/1047015#M3826

https://community.powerbi.com/t5/COVID-19-Data-Stories-Gallery/bd-p/pbi_covid19_datastories

*DataVisualization best practices

https://material.io/design/communication/data-visualization.html#behavior

https://eazybi.com/blog/data-visualization-and-chart-types

https://www.youtube.com/watch?v=-tdkUYrzrio - Power BI visualization best practices
by Marco Russo

https://help.tableau.com/current/blueprint/en-us/bp_why_visual_analytics.htm

https://www.datacamp.com/community/tutorials/data-visualisation-powerbi - From Information Visualization to Visual Analytics by Jack van Wijk (Eindhoven University of Tech)

https://coolinfographics.com/blog/2016/10/10/5-great-ted-talks-about-dataviz.html

*COVID-19 info (in Dutch) that I used to check the numbers in my dashboard:

https://coronadashboard.rijksoverheid.nl/landelijk

https://allecijfers.nl/nieuws/corona-virus-covid19-bevestigde-besmettingen-per-dag-nederland-provincie-gemeente/

https://allecijfers.nl/nieuws/statistieken-over-het-corona-virus-en-covid19/#Corona_opgenomen_personen

https://www.rtlnieuws.nl/nieuws/nederland/artikel/5212577/miljoen-coronagevallen-maar-hoeveel-mensen-hebben-het-virus-gehad

https://data.rivm.nl/geonetwork/srv/dut/catalog.search#/search?any_OR__title=covid-19&isChild='false'&fast=index

Downloads:

Power BI file on G-drive:

https://drive.google.com/file/d/1ySo2KGmsgHCpWFf3DkAqWgYfXQj6OZPU/view?usp=sharing

Embedded Power BI report

---

2 Feb 2021

AEMET Open Data for weather in Spain, analysis with Python

#74: AEMET Open Data for weather in Spain, analysis with Python

2021 started in Spain with extreme weather caused by storm Filomena, with heavy snowfall, and extreme low temperatures, and with a record of -34 C, see:

https://www.lavanguardia.com/natural/20210108/6173987/record-frio-34o-c-sera-reconocido-agencia-meteorologia.html

When I looked on the site of AEMET ("The State Meteorological Agency", an agency of the Government of Spain), to find more information about Filomena, I saw they offer open data via a REST-API, see: https://opendata.aemet.es/centrodedescargas/AEMETApi?

For how to use this API, see this video by AEMET (starting at min. 25): https://www.youtube.com/watch?v=l-YF_dQ983Y&feature=emb_logo

I tested it with this URI for extreme temperatures, see:
https://opendata.aemet.es/dist/index.html?#/valores-climatologicos

GET /api/valores/climatologicos/valoresextremos/parametro/{parametro}/estacion/{idema}

and for the parameters I chose:

- value: temperature (in decimal degrees Celcius, so e.g. -1.0 C = -10 dC

- weather station (IDEMA-code): 2462 - PUERTO DE NAVACERRADA (ski-resort of Madrid, see: http://puertonavacerrada.com/).

Jupyter Notebook

I made a Jupyter notebook in Python to visualize the result of this API-call, and run it in Google Colab (Colaboratory), see FIG.1

FIG.1: Jupyter notebook with min.temperatures in Navacerrada

(with min.temp. of -203 'decigrado Celcius = -20.3 C in 1962).

NB:

- For 1962 there are duplicate lines, so it looks like AEMET forgot to clean this data (de-duplicate)

- 1956 was one of the coldest winters in Spain and the temp. of -18.6 in Navacerrada was the 4th coldest temp. that winter, see:

https://aemetblog.es/2019/02/11/efemeridesmeteo-ola-de-frio-febrero-de-1956/

You can run this notebook for another weather station by replacing in the GET-statement the IDEMA-code for the station in Navacerrada (2462). To get the IDEMA-code of a station, see e.g. :

https://www.tiempo.com/ram/8059/cmo-consultar-los-datos-de-las-emas-de-aemet/

or:

https://datosclima.es/Aemethistorico/Estaciones.php

I run it, so Google Colab, from my Android-phone, for the province of Teruel (IDEMA-code: 8368U), which had one of the lowest temperatures during Filomena, with this result (note that Jan. 2021-1, with -21C, was the coldest month in the history of Teruel).

FIG.1b: Jupyter notebook run on Android-phone for province Teruel

I used besides Google Colab, also Binder and a JupyterLab-demo project to run the notebook, see FIG.2b and:

https://mybinder.org/v2/gh/mvanreek/AEMET_Weather_Spain/HEAD

FIG.2b: Jupyter Notebook in Binder

And I also used Microsoft Visual Code (open source IDE, multi-platform, multi-language) and Anaconda (tool for data science with Python and R) to run the notebook, see FIG.2c.

FIG.2c: Jupyter Notebook in VS Code

API

The API-result consists of a JSON-file that has 2 URLs: one for the actual data and one for the meta-data, and these are also in JSON-format, see FIG.3-5

FIG.3 : result API-call

FIG.4: actual data (JSON-format)

FIG.5: meta data (JSON-format)

Chart

For the chart, I selected (in the notebook) these columns:

- name of weather station ('nombre')

- min.temperature, in 'deci-grados' Celcius ('temMin')

- date of measurement ('anioMin' (year), 'mesMin' (month), 'diaMin' (day))

To store the JSON-data returned by the API, I used a dataframe of the Pandas-library and for the data viz (line-chart) I used the Seaborn-library.

To conclude this post, here a link with some photos of how my neighborhood in Madrid looked like when it was covered in the Filomena-snow:

https://photos.app.goo.gl/uDy19LKprArnWKG69

If you liked this post, you can leave a comment below (non-anonymous, to prevent spam).

And if you'd like to donate something, check the Donate (via PayPal) button on the top-right.

References:

http://www.aemet.es/es/datos_abiertos/AEMET_OpenData

https://www.youtube.com/watch?v=mnW61tjHsqI&feature=emb_logo

Python Tutorial | How to use a Jupyter notebook online with Binder and Google Colab

https://towardsdatascience.com/jupyterlab-you-should-try-this-data-science-ui-for-jupyter-right-now-a799f8914bb3

https://www.aemet.es/es/conocermas/borrascas/2020-2021/estudios_e_impactos/filomena

https://www.youtube.com/watch?v=ByGxNCovueo&feature=youtu.be

Webinar AEMET Open Data

https://youtu.be/agj3AxNPDWU

Google Colab turorial

https://youtu.be/tse_8LLWtfY

Data Visualization using Python on Jupyter Notebook" en YouTube

https://youtu.be/HP3RkzwATH4

https://youtu.be/g8qH-Rp8mKk

Filomena snowstorm

VS Code :

https://www.zdnet.com/google-amp/article/visual-studio-code-how-microsofts-any-os-any-programming-language-any-software-plan-is-paying-off/

https://code.visualstudio.com/docs/python/jupyter-support

https://medium.com/analytics-vidhya/working-on-jupyter-notebooks-in-vs-code-from-virtual-conda-environment-f415726e329d

Using VS Code with Python for Data Science / Data Analysis

https://www.youtube.com/watch?v=UuwlySU7Hjg&feature=youtu.be

Working with GitHub in VS Code

Increase the power of Visual Studio Code through Extensions

Anaconda:

https://www.anaconda.com/products/individual

Downloads:

Jupyter Notebook:

#Mirror 1 - Github:
https://github.com/mvanreek/AEMET_Weather_Spain/blob/main/Aemet_Wether_Spain_v2.ipynb

#Mirror 2 - Google Colab:

https://colab.research.google.com/github/mvanreek/AEMET_Weather_Spain/blob/main/Aemet_Wether_Spain_v2.ipynb

5 Jan 2021

Messi Goal Tracker dashboard in Google Data Studio

#73: Messi Goal Tracker dashboard in Google Data Studio

Update 11/9/2023

Begin Update

Because of Messi's transfer to Inter Miami I had to update my Messi-goal-tracker GoogleDataStudio-report to include the goals that he made for his new club.
And I included another page to see easily the #goals he made per club and competition.

End Update 11/9/2023

Update 3/10/2021:

Begin Update

Now Messi moved to Paris Saint-Germain (PSG), I had to change my Messi-goal-tracker GoogleDataStudio-report a bit, to reflect e.g. if a goal he scored in the Champions League was a goal for FC Barcelona or for PSG.

I below screenshot you can see how the new dashboard looks like:

I leave it to you to find the differences/improvements with the previous version of the dashboard (see FIG.1 below) ;) But screenshot below, which has a filter on opponent Manchester City, against which Messi made hist 1st goal for PSG this week, could help you.

End Update

When Messi made on 22/12/2020 his 644-th goal for FC Barcelona, he broke the 46 year old record '# goals made for one club' of Pele (who made 643 goals for Santos). See e.g.:

https://www.espn.in/football/barcelona/story/4215713/barcelonas-messi-overtakes-peles-scoring-record-with-644-goals-for-the-club

or (in Spanish, with a chart with a breakdown of the 644 goals by competiton):

https://www.mundodeportivo.com/futbol/fc-barcelona/20201223/491248765482/barca-valladolid.html

This new record of Messi inspired me to make a new Messi-dashboard.

The previous Messi-dashboards I made, for his record '# goals made in one year'

(91 goals in 2012), see posts:

https://worktimesheet2014.blogspot.com/2015/08/dashboard-for-messis-worldrecord-of-91.html

https://worktimesheet2014.blogspot.com/2016/12/powerbi-dashboard-for-messis.html

https://worktimesheet2014.blogspot.com/2020/01/google-data-studio-dashboard-for-messi.html

were 'static snapshots'. This time I wanted to make a 'live-dashboard', so one that is automatically updated after each match of Messi in which he scored. So that when Messi retires in Barcelona (or leaves to another club, which might happen at the end of this season (2020-2021)..), this dashboard shows his final score (so which will probably be more than the current 644 goals).

In FIG.1 you can see my dashboard, and for the dashboard in Google Data Studio, see: https://datastudio.google.com/s/lZaGsxWsomc

And at the bottom of this post there is also an embedded version of the dashboard.

FIG.1: Messi (live) dashboard, showing his record '644-goals for one club' (Barca).

Datasource

I found this Power BI Messi-dashboard of Microsoft Data Platform MVP Tristan Malherbe:

https://community.powerbi.com/t5/Data-Stories-Gallery/Incredible-Lionel-Messi/td-p/82225 that uses this (live) datasource: https://messi.starplayerstats.com/en ,

which is made by Dutch sports journalist Ramon Min.

So I decided to use this datasource as well.

I used Google Sheets to import this data, using this function:

=IMPORTHTML("https://messi.starplayerstats.com/en/goals/0/0/all/0/0/0/t/all/all/0/0/1","table",1)