# ETL
library(tidyverse)10 Practice aggregations
In this second exercise, we’re going to practice to :
filter data.
perform some aggregations.
The examples in this chapter might look a bit repetitive, the purpose is to be comfortable to aggregate data with different dimensions, and to illustrate a common ETL flow : Get data => Filter => Transform .
Let’s start by uploading the needed libraries. Here, the usual tidyverse.
Upload raw data
The data frame is located on the GitHub repository.
We’re uploading a large data frame with :
43 columns.
10.000 rows.
There are 2 dimensions, Country and Product.Description, and some sales quantities per period.
11 Create a tidy data frame
We’re going to transform the initial data frame into a “tidy format”, which means that each variable is either a dimension or a measure. We follow the same steps that we did in the first practice.
#-------------------
# Upload raw data
#-------------------
# Define the URL of the raw CSV file
url <- "https://raw.githubusercontent.com/nguyennico/etl_practice/main/ETL_practice2_data.csv"
# Read the CSV file from the URL
df1 <- read.csv(url)
#-------------------
# Create a tidy dataframe
#-------------------
# pivot
df1 <- df1 |> gather(key = "period",
value = "sales_qty",
3:length(df1))
# replace missing values (NA) by zero
df1$sales_qty <- df1$sales_qty |> replace_na(0)
# remove the "X" in front of the period
df1$period <- gsub("X", "",df1$period)
# Format Date
df1$period <- as.Date(df1$period, format = '%m.%d.%Y')
# add Calendar Year
df1$calendar_year <- year(df1$period)
# keep results
ETL_practice2_data <- df1
glimpse(df1)Rows: 410,000
Columns: 5
$ Country <chr> "France", "France", "France", "France", "France", …
$ Product.Description <chr> "Product 1", "Product 2", "Product 3", "Product 4"…
$ period <date> 2016-10-01, 2016-10-01, 2016-10-01, 2016-10-01, 2…
$ sales_qty <int> 1370, 461, 765, 0, 4496, 1623, 950, 230, 143, 720,…
$ calendar_year <dbl> 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016, 20…We obtain a data frame with 5 variables and 410.000 rows.
The 5 variables are :
Country : 4 values, “France”, “Germany”, “Spain”, “Italy”.
Product.Description : 2.500 products.
period : a date, with months from October 2016 until February 2020.
sales_qty : amount of sales, in units.
calendar_year : the calendar year related to the period.
12 Monthly Sales by Country
In this example, we want to create a summary, i.e an aggregation, of the monthly sales per country.
The operation is similar to a pivot table in excel. Here, we are going to use the 2 functions group_by() and summarise() to perform this aggregation.
We will aggregate using 2 dimensions : Country and period. Those 2 dimensions are written within the group_by() function.
Then we will use the function spread() to spread the variable “period” into distinct columns.
# set a working df
df1 <- ETL_practice2_data
# Aggregate by Country
df1 <- df1 |> group_by(Country, period) |>
summarise(sales_qty = sum(sales_qty))
# spread data
df1 <- df1 |> spread(period, sales_qty)
# display
df1# A tibble: 4 × 42
# Groups: Country [4]
Country `2016-10-01` `2016-11-01` `2016-12-01` `2017-01-01` `2017-02-01`
<chr> <int> <int> <int> <int> <int>
1 France 1250319 1286042 1266616 1254652 1237272
2 Germany 61656 61719 62753 63054 62434
3 Italy 25199 24717 25209 25353 24844
4 Spain 1246446 1234329 1273446 1241711 1252289
# ℹ 36 more variables: `2017-03-01` <int>, `2017-04-01` <int>,
# `2017-05-01` <int>, `2017-06-01` <int>, `2017-07-01` <int>,
# `2017-08-01` <int>, `2017-09-01` <int>, `2017-10-01` <int>,
# `2017-11-01` <int>, `2017-12-01` <int>, `2018-01-01` <int>,
# `2018-02-01` <int>, `2018-03-01` <int>, `2018-04-01` <int>,
# `2018-05-01` <int>, `2018-06-01` <int>, `2018-07-01` <int>,
# `2018-08-01` <int>, `2018-09-01` <int>, `2018-10-01` <int>, …13 Countries and Calendar Year
In this example, we want to create another aggregation, this time of the yearly sales per country.
We’re going to aggregate using 2 dimensions : Country and calendar_year.
Then we will spread as we did previously.
# set a working df
df1 <- ETL_practice2_data
# Aggregate by Country
df1<- df1 |> group_by(Country, calendar_year) |>
summarise(sales_qty = sum(sales_qty)
)
# spread data
df1 <- df1 |> spread(calendar_year, sales_qty)
# display
df1# A tibble: 4 × 6
# Groups: Country [4]
Country `2016` `2017` `2018` `2019` `2020`
<chr> <int> <int> <int> <int> <int>
1 France 3802977 15069505 15108326 15171819 2515933
2 Germany 186128 753298 747328 751043 125266
3 Italy 75125 299119 301329 300171 49078
4 Spain 3754221 15039455 15085333 15011769 247263214 Filter on one selected Product
Now we’re going to create the same aggregated table, with the sales by Countries and Calendar Year.
However, instead of considering the whole products portfolio, we will look only at the “Product 1”.
We will then filter the data, before the aggregation, using the function filter().
Note : we actually can filter before or after. The interest to filter before is to have a smaller (already) filtered data frame to aggregate (making then the operation faster).
# set a working df
df1 <- ETL_practice2_data
# Filter on one Product
df1 <- df1 |> filter(Product.Description %in% c("Product 1"))
# Aggregate by Country
df1 <- df1 |> group_by(Country, calendar_year) |>
summarise(sales_qty = sum(sales_qty)
)
# spread data
df1 <- df1 |> spread(calendar_year, sales_qty)
# display
df1# A tibble: 4 × 6
# Groups: Country [4]
Country `2016` `2017` `2018` `2019` `2020`
<chr> <int> <int> <int> <int> <int>
1 France 3999 14037 12842 10414 1371
2 Germany 65 277 337 284 55
3 Italy 31 138 104 128 35
4 Spain 1666 5079 4849 6236 148515 Aggregation on 3 dimensions
Now, we are going to filter on a few selected Products, and add one more dimension, the Product.Description, to the aggregation.
# set a working df
df1 <- ETL_practice2_data
# Filter on Products
df1 <- df1 |> filter(Product.Description %in% c("Product 1", "Product 10", "Product 20"))
# Aggregate
df1 <- df1 |> group_by(Country, Product.Description, calendar_year) |>
summarise(sales_qty = sum(sales_qty))
# spread data
df1 <- df1 |> spread(calendar_year, sales_qty)
# display
df1# A tibble: 12 × 7
# Groups: Country, Product.Description [12]
Country Product.Description `2016` `2017` `2018` `2019` `2020`
<chr> <chr> <int> <int> <int> <int> <int>
1 France Product 1 3999 14037 12842 10414 1371
2 France Product 10 2048 5552 6229 4872 248
3 France Product 20 1553 6754 5995 5477 738
4 Germany Product 1 65 277 337 284 55
5 Germany Product 10 65 245 266 274 57
6 Germany Product 20 115 263 308 314 58
7 Italy Product 1 31 138 104 128 35
8 Italy Product 10 25 149 110 159 31
9 Italy Product 20 20 164 126 84 36
10 Spain Product 1 1666 5079 4849 6236 1485
11 Spain Product 10 413 6885 6746 5797 1159
12 Spain Product 20 2483 7378 6768 5671 51616 2 filters & aggregation
In this last part, we’re going to :
filter the initial data frame based on the variables Product.Description and Country.
then aggregate and spread the results.
Note : as we mentioned earlier, the 2 filters could also be placed after the aggregation and spread of the data
# set a working df
df1 <- ETL_practice2_data
# Filter on Products
df1 <- df1 |> filter(Product.Description %in% c("Product 1", "Product 10", "Product 20"))
# Filter on Countries
df1 <- df1 |> filter(Country %in% c("France", "Spain"))
# Aggregate
df1 <- df1 |> group_by(Country, Product.Description, calendar_year) |>
summarise(sales_qty = sum(sales_qty))
# spread data
df1 <- df1 |> spread(calendar_year, sales_qty)
# display
df1# A tibble: 6 × 7
# Groups: Country, Product.Description [6]
Country Product.Description `2016` `2017` `2018` `2019` `2020`
<chr> <chr> <int> <int> <int> <int> <int>
1 France Product 1 3999 14037 12842 10414 1371
2 France Product 10 2048 5552 6229 4872 248
3 France Product 20 1553 6754 5995 5477 738
4 Spain Product 1 1666 5079 4849 6236 1485
5 Spain Product 10 413 6885 6746 5797 1159
6 Spain Product 20 2483 7378 6768 5671 516Well done! We now master the aggregations in R!