Computing KPIs from CSV data
A KPI or a Key Performance Indicator is a quantity that indicates the performance of an activity. Examples include:
- number of countries that have harnessed wind energy for electric power generation.
- percentage of children enrolled in secondary education.
KPIs represent a summarization of detailed data which provides a good indicator of a measurable quantity. They can be computed from CSV data as we will show below.
Importing CSV data into the grid editor
- To begin with, let us import a CSV file. The file we are importing contains data about annual electricity production for each country using wind energy. The file contains data from about 1990 to 2018. The file looks like the picture above after we import the file into the grid editor.
- After the file is imported, click the Analytics tab shown.
Counting unique values in a text field
The Analytics section allows you to explore various aspects of your CSV file including:
- Unique values in text fields.
- Statistical information about numeric fields
- Grouping (or aggregates) fields and computing different types of metrics on those groups.
Let us now see how we can view the unique values in a text field.
- In the Analytics tab, ensure that the Metrics pill is active (1).
- Choose the column for which you want to display the unique values (2).
- The type of the column is shown (3). The type is automatically identified from the first few rows when opening the CSV.
- The unique values along with the occurence counts are displayed in the table below (4).
Viewing statistics for a numeric field
- When you have a number field in your CSV, you can view the statistics for that field quite easily. To begin with, ensure that the Metrics tab is selected (1).
- Choose a numeric column from the columns dropdown (2).
- The type of the column is indicated as shown (3).
- The statistics for the column is shown (4).
Computing a key performance indicator (KPI)
- Now that we know how to obtain statistics from CSV data, let us compute a key performance indicator (KPI) we need. First, we need to find the total power generated in the world by wind energy during 2017.
- First switch to the Grid editor tab (1).
- Click the search icon to perform a search (2). The search panel appears above the grid.
with Year equal to 2017
row => row.Year == 2017
- Click the search icon inside the panel. The search is executed and the grid is updated (5).
- The number of rows matching the query is also shown (6).
- After this is done, we click on the Analytics tab to
get back to the analytics panel.
In the analytics panel, ensure that the Metrics pill is selected and choose the Quantity column from the dropdown. Note down the value of the field sum in the displayed statistics (currently 1113817.36).
This is the total amount of electricity produced by wind energy in 2017.
Repeat this process with another CSV file containing data about total electricity production from all sources for each country.
From this, we determine that the total electricity generated in the world in 2017 was 25599352.07.
- Computing key performance indicators may involve cleaning up the data and manipulating it to get it to the right form. This can be performed using the grid editor.
- Once the data has been cleaned up, you can use the analytics section of the software to summarize information in the CSV file.