Aggregate in YouPrep™¶
When you are building your story, you might not have the values at the visualization level you need: if you have your data aggregated at a city level, you might want to visualize them at a country level. You will then need to aggregate them. Thanks to the aggregate widget, you can apply the following operations to your dataset:
- Group By
- Hierarchical roll-up
- Get unique Groups/value
Before you start!
My dataset is containing my turnover per Quarter for each year.
I want to display the evolution of my turnover per year. I need to group all my values per year using the Aggregate step : Group By operation:
- Group by allows you to perform different types of operation on your KPIs : you can sum them up or get their average for example !
Your YouPrep™ step should look like this:
Here you go ;)
My data might only contains data at the lowest level. The hierarchical rollup operation will allow you to aggregate data at each hierarchical level and stack the aggregated data of every level, based on hierarchical columns (e.g. city < subcountry < counrtry ). This step is really helpful to give your dataset a hierarchical structure that Toucan could interpret properly for its charts (ex leaderboard drill, waterfall drill… or even hierarchical view selector).
📝 Perfect operation to set a hierarchy with only the children value.
Before you start! You will often have to display various levels of aggregations on your screens. For ewample, my dataset contains the main cities worldwide. We can find different level: names of the city, subcountry and country. Thanks to the hierarchical rollup operation, we are going to create a hierarchy between these three columns.
![keep column](image14.png) I would like to have the number of main cities by subcountries and countries. By adding a hierarchical rollup step, the output data structure stacks the data of every level of the hierarchy, specifying for every row the label, level and parent in dedicated columns. Your YouPrep™ step should look like this: ![rollup operation](image10.png) Now I can add a filter to be able to have the ranking of the main city at the three levels: - Ranking of the countries containing the most “main cities” - Ranking of the sub countries containing the most “main cities” - Ranking of the main cities ![rollup operation2](image11.png)
```directive_rst .. collapsable:: Ex3: Get unique
📝 You can use this step to get the unique values from a column or unique groups of values from a combination of several columns.
Get unique groups/values in columns: you can select one or several columns that will be combined to constitute unique groups of values.
Tadaaaa 🎉 a# Reshape in YouPrep
Sometimes, the data table you have to use does not have the right shape to “fit” what is expected by the chart you want to use.
Before YouPrep, it was really painful to apply a Pivot or UnPivot function to your dataset. Now, in two clicks you are able to realize a Pivot or Unpivot function to reshape your data for a particular chart. Let’s see some of the transformations we can do on our data tables !
You can apply the following operations to your dataset:
📝 Pivot operation looks like:
You will need to add the column you want to keep, the column on which you want to apply the Pivot, the value on which you’re applying the pivot and the aggregation you’re applying on your value.
📝 Unpivot operation looks like:
You just need to declare the Columns you want to keep and the columns on which you’re applying the unpivot function.
Before you start!
My dataset contains the following columns: “Time”, “Country Name”, “GDP” and “Population”. I want to display my values for the GDP and Population in one column.
📝 In fact, I should want to have a filter on my story where I could select “GDP” or “Population” and then display my values into a leaderboard. Thanks to this unpivot step, I will have in the same column all my values (both GP and population values) and in a new column the variable names “GDP” or “Population”.
By keeping the “Time” and “Country name” and unpivot “GDP” and “Population”, I’m now displaying my value in a unique column. Your YouPrep step should look like this :
Here you go!
Before you start!
My dataset contains the following columns: “Time”, “Country Name”, “Variable” and “Value”. I would like to Pivot the Variable column to display only one row per “Country_Name” and “Time” and two news columns “GDP” and “Population”.
I need to keep my columns “Time” and “Country Name” and Pivot the column “Variable” and use the “Value” column with a sum aggregation to display my result with one row for each Time and Country
📝 Perfect tool to switch your values from rows to columns.
Your YouPrep step should look like this :
Your output dataset format is the following one:
Do not hesitate to read the weaverbird documentation if you need any further informations on YouPrep!
``` Do not hesitate to read the weaverbird documentation if you need any further informations on YouPrep!