Pivot table¶
A pivot table is a good visualisation to see the relation of discrete values in two different columns of a dataset. Using the positive_cases_covid_d
dataset, let’s see the total number of cases related to the values in the column estado
that represent the severity of the COVID-19 case and the column ubicacion
that represents where the covid-19 case was treated.
Below is the meaning of the values that will appear in the visualisation:
Casa
: It means that the individual was treated at home;Fallecido
: It means that the individual died from covid-19;Leve
: It means that the individual had mild covid-19 symptoms;Moderado
: It means that the individual had moderate covid-19 symptoms;Grave
: It means that the individual had severe covid-19 symptoms.
To do this, open the positive_cases_covid_d
dataset and click on the Table
value to change the VISUALISATION TYPE:
{width=300px}
In the window that will open, type Pivot table
:
{width=750px}
Click on the Pivot Table v2
and on the SELECT
button.
We need to select a column to be used as columns of the pivot table and another to be used as rows of the pivot table. In this example, let’s use ubicacion
in the field COLUMNS and estado
in the field ROWS.
To see the number of cases grouped by the values in the columns and rows of the pivot table let’s use as metrics COUNT(*)
, to count all the records returned in this aggregation.
So, the final query is:
{width=300px}
To avoid the columns with values not informed, let’s create 2 different filters. The first is to ignore the null values and the second is to ignore the values equal to “N/A” that represent the individuals that died but not by covid.
{width=300px}
The <>
represents the not equals
operator.
Then click on the RUN
button, and the result will be:
{width=500px}
In the Options
section selected below you have the following possibilities:
{width=300px}
If you select the first two items (SHOW ROWS TOTAL
, and SHOW COLUMNS TOTAL
), that is the result:
{width=500px}
It is now time to :
Specify a title for the chart, for instance
estado vs ubicacion
,Save it, by clicking on
+SAVE
button in the middle panel.
Furthermore, it’s possible to change the aggregation function used to compute the total values in the rows in the columns by changing the value in the select box. The default value is Sum
.
It’s also possible to transpose the table, by selecting the TRANSPOSE PIVOT
and put different metrics side by side (if you add more than one metric in the METRICS
field) by selecting the COMBINE METRICS
.