# ETL
library(tidyverse)9 Pivot | Merge | Factor
We will start practicing the ETL through 2 parts :
first by creating a code to transform a small data frame.
and then using the same code and apply it to a larger data frame.
Outside practicing, the idea is to show that R is “scalable & reproducible”. It means that the same script can process 10 lines or 10.000 lines .
We’re going to practice to :
pivot data.
replace some missing values by zero.
work on some dates attributes : year, month.
create a factor.
merge 2 data frames.
and finally we will keep the result, which is a data frame, affecting a name to it.
Let’s start by uploading the needed libraries. Here, the usual tidyverse.
10 ETL small data frame
10.1 Upload raw data
The data frame is located on this GitHub repository.
We notice that there is a “X” in front of the period of time.
We will remove it later on, when we format the date.
#-------------------
# Upload raw data
#-------------------
# Define the URL of the raw CSV file
url <- "https://raw.githubusercontent.com/nguyennico/etl_practice/main/ETL_example_raw_data.csv"
# Read the CSV file from the URL
df1 <- read.csv(url)
# let's have a look at the dataset
head(df1) Product.Description X10.1.2016 X11.1.2016 X12.1.2016 X1.1.2017 X2.1.2017
1 Product A 1370 1528 1101 738 1229
2 Product B 461 812 796 1010 904
3 Product C 765 927 1412 717 800
4 Product D NA NA NA NA NA
5 Product E 4496 5681 3838 5385 5150
6 Product F 1623 1522 2306 1416 2214
X3.1.2017 X4.1.2017 X5.1.2017 X6.1.2017 X7.1.2017 X8.1.2017 X9.1.2017
1 1451 879 1505 1375 1146 1325 1156
2 721 665 537 1275 589 538 704
3 906 596 713 642 695 577 741
4 96 55 106 47 35 15 48
5 6203 4827 5695 5758 3147 3929 4781
6 2538 1841 1982 2749 1929 626 1280
X10.1.2017 X11.1.2017 X12.1.2017 X1.1.2018 X2.1.2018 X3.1.2018 X4.1.2018
1 1081 1258 894 700 1289 1207 926
2 379 1302 1741 1098 939 764 1413
3 677 783 1400 620 693 713 618
4 19 46 19 39 53 51 52
5 5675 5816 5097 5100 5126 6456 6129
6 1215 2693 1564 1936 1949 1923 1810
X5.1.2018 X6.1.2018 X7.1.2018 X8.1.2018 X9.1.2018 X10.1.2018 X11.1.2018
1 1476 1254 1111 1175 881 916 1225
2 781 950 1007 534 570 567 1353
3 536 610 596 544 1281 882 3304
4 59 51 56 50 97 79 97
5 5303 6083 4861 2370 5649 6551 5800
6 2187 2296 1671 1049 1229 1880 2041
X12.1.2018 X1.1.2019 X2.1.2019 X3.1.2019 X4.1.2019 X5.1.2019 X6.1.2019
1 682 739 1056 1117 727 1233 839
2 1212 1122 1135 1261 1046 931 695
3 738 722 657 841 629 683 543
4 65 97 73 108 106 96 93
5 4791 6390 6570 6569 5980 6135 5581
6 1935 1617 1821 1940 1905 1699 2255
X7.1.2019 X8.1.2019 X9.1.2019 X10.1.2019 X11.1.2019 X12.1.2019 X1.1.2020
1 862 862 702 740 934 603 468
2 849 644 635 1753 1506 1826 1618
3 680 538 777 633 750 578 683
4 122 88 124 130 187 136 141
5 5405 2380 8031 6108 6079 4702 6828
6 1853 522 1682 2261 1523 3210 1764
X2.1.2020
1 903
2 1382
3 704
4 132
5 5717
6 2230It’s a data frame in a wide format, with several columns related to a same dimension of period of time :
the first variable is called Product.Description, and contains 6 products (Product A to F).
the next 41 variables are monthly periods of time.
We also notice that there are some missing values, we will replace them by zero later on.
10.2 Pivot & Transform
To transform the data we’re going to :
pivot them into a long format, using the function
gather().replace the missing values by zero, using the function
replace_na().transform the variable “period”, removing the “X” at the beginning, using the function
gsub().add some dates features, such as :
calendar year, using the function
year().calendar month, using the function
month().calendar month abbreviation : Jan, Feb,…; using the function
month.abb().
and finally create a factor for the calendar month abbreviation, using the function
factor().- starting the calendar month abbreviation from the month of October (otherwise, in the alphabetical order, it would be April by default)
We will call the data frame that we obtain at the end sales_data.
# Pivot
df1 <- df1 |> gather(key = "period",
value = "sales_qty",
2:length(df1))
# replace missing 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')
#---------------------------------
# Extract Date features
#---------------------------------
# Calendar Year
df1$calendar_year <- year(df1$period)
# Calendar Month
df1$calendar_month <- month(df1$period)
# transform the Month number into a month abbreviation
# note that month.abb comes with [ and not (
df1$calendar_month_abb <- month.abb[df1$calendar_month]
# Create Factor
df1$calendar_month_abb <- factor(df1$calendar_month_abb,
levels = c("Oct", "Nov", "Dec","Jan","Feb","Mar",
"Apr","May","Jun","Jul","Aug","Sep"))
# keep results
sales_data <- df1
glimpse(df1)Rows: 246
Columns: 6
$ Product.Description <chr> "Product A", "Product B", "Product C", "Product D"…
$ period <date> 2016-10-01, 2016-10-01, 2016-10-01, 2016-10-01, 2…
$ sales_qty <int> 1370, 461, 765, 0, 4496, 1623, 1528, 812, 927, 0, …
$ calendar_year <dbl> 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016, 20…
$ calendar_month <dbl> 10, 10, 10, 10, 10, 10, 11, 11, 11, 11, 11, 11, 12…
$ calendar_month_abb <fct> Oct, Oct, Oct, Oct, Oct, Oct, Nov, Nov, Nov, Nov, …10.3 Create a Fiscal Calendar
Now, we want to add a new variable called fiscal_month to our data frame sales_data.
The purpose is to affect a number to each value “calendar_month_abb”, to ensure that “Oct” is the first fiscal month, and “Sep” the last one.
To do this, we can:
create a Fiscal Calendar, starting in October.
merge it with the previous data frame sales_data.
# Create a Fiscal Calendar
df1 <- data.frame(
calendar_month_abb = c("Oct", "Nov", "Dec", "Jan", "Feb", "Mar",
"Apr", "May", "Jun", "Jul", "Aug", "Sep"),
fiscal_month = c(1,2,3,4,5,6,7,8,9,10,11,12)
)
# display
df1 calendar_month_abb fiscal_month
1 Oct 1
2 Nov 2
3 Dec 3
4 Jan 4
5 Feb 5
6 Mar 6
7 Apr 7
8 May 8
9 Jun 9
10 Jul 10
11 Aug 11
12 Sep 12We can notice that :
the calendar_month_abb is a character.
the related fiscal_month is a double.
When we look at the object df1 in a dedicated window and click on the label of the variable calendar_month_abb to sort it, we notice that :
the first month is Apr, and the last one is Sep.
it’s because, as a character, the variable calendar_month_abb is sorted by alphabetical order.
We will need to transform the variable calendar_month_abb into a factor to indicate that the first value is October (the second one is November and so on), and also to merge it with the previous data frame sales_data (for which the variable calendar_month_abb is a factor).
10.4 Create a factor
Here, we’re going to transform the variable calendar_month_abb into a factor, and indicate that the first value if “Oct”.
We will save the results as fiscal_calendar_data.
# create a Factor
df1$calendar_month_abb <- factor(df1$calendar_month_abb,
levels = c("Oct", "Nov", "Dec", "Jan","Feb","Mar",
"Apr","May","Jun","Jul","Aug","Sep"))
# keep results
fiscal_calendar_data <- df1
# display
fiscal_calendar_data calendar_month_abb fiscal_month
1 Oct 1
2 Nov 2
3 Dec 3
4 Jan 4
5 Feb 5
6 Mar 6
7 Apr 7
8 May 8
9 Jun 9
10 Jul 10
11 Aug 11
12 Sep 1210.5 Merge
Now, let’s merge the 2 data frames sales_data and fiscal_calendar_data. They have one variable in common : calendar_month_abb (for which the class is a factor)
The merger will the be done automatically according to this common variable.
# Merge data
df1 <- left_join(sales_data, fiscal_calendar_data)
glimpse(df1)Rows: 246
Columns: 7
$ Product.Description <chr> "Product A", "Product B", "Product C", "Product D"…
$ period <date> 2016-10-01, 2016-10-01, 2016-10-01, 2016-10-01, 2…
$ sales_qty <int> 1370, 461, 765, 0, 4496, 1623, 1528, 812, 927, 0, …
$ calendar_year <dbl> 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016, 20…
$ calendar_month <dbl> 10, 10, 10, 10, 10, 10, 11, 11, 11, 11, 11, 11, 12…
$ calendar_month_abb <fct> Oct, Oct, Oct, Oct, Oct, Oct, Nov, Nov, Nov, Nov, …
$ fiscal_month <dbl> 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3,…10.6 Keep results
Now, let’s affect an object called “set_up_sales_data” to this final result, so we can keep it.
# Keep Results
set_up_sales_data <- df1
glimpse(df1)Rows: 246
Columns: 7
$ Product.Description <chr> "Product A", "Product B", "Product C", "Product D"…
$ period <date> 2016-10-01, 2016-10-01, 2016-10-01, 2016-10-01, 2…
$ sales_qty <int> 1370, 461, 765, 0, 4496, 1623, 1528, 812, 927, 0, …
$ calendar_year <dbl> 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016, 20…
$ calendar_month <dbl> 10, 10, 10, 10, 10, 10, 11, 11, 11, 11, 11, 11, 12…
$ calendar_month_abb <fct> Oct, Oct, Oct, Oct, Oct, Oct, Nov, Nov, Nov, Nov, …
$ fiscal_month <dbl> 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3,…The result is a data frame of 7 columns and 246 rows.
R is scalable and reproducible
At this moment, we’re ready to reuse our code to process another data frame.
What works with 10 observations rows also works with 100.000 observations. You always can reuse your code, it is scalable .
Let’s illustrate it in the next example, processing a larger data frame.
11 ETL large data frame
We’re going to use the same code of the Part 1. The only difference : we change the data frame that we upload to ETL_example_large_raw_data
11.1 Upload raw data
The data frame is also located on this GitHub repository.
#-------------------
# Upload raw data
#-------------------
# Define the URL of the raw CSV file
url <- "https://raw.githubusercontent.com/nguyennico/etl_practice/main/ETL_example_large_raw_data.csv"
# Read the CSV file from the URL
df1 <- read.csv(url)
# let's have a look at the dataset
head(df1) Product.Description X10.1.2016 X11.1.2016 X12.1.2016 X1.1.2017 X2.1.2017
1 Product 1 1370 1528 1101 738 1229
2 Product 2 461 812 796 1010 904
3 Product 3 765 927 1412 717 800
4 Product 4 NA NA NA NA NA
5 Product 5 4496 5681 3838 5385 5150
6 Product 6 1623 1522 2306 1416 2214
X3.1.2017 X4.1.2017 X5.1.2017 X6.1.2017 X7.1.2017 X8.1.2017 X9.1.2017
1 1451 879 1505 1375 1146 1325 1156
2 721 665 537 1275 589 538 704
3 906 596 713 642 695 577 741
4 96 55 106 47 35 15 48
5 6203 4827 5695 5758 3147 3929 4781
6 2538 1841 1982 2749 1929 626 1280
X10.1.2017 X11.1.2017 X12.1.2017 X1.1.2018 X2.1.2018 X3.1.2018 X4.1.2018
1 1081 1258 894 700 1289 1207 926
2 379 1302 1741 1098 939 764 1413
3 677 783 1400 620 693 713 618
4 19 46 19 39 53 51 52
5 5675 5816 5097 5100 5126 6456 6129
6 1215 2693 1564 1936 1949 1923 1810
X5.1.2018 X6.1.2018 X7.1.2018 X8.1.2018 X9.1.2018 X10.1.2018 X11.1.2018
1 1476 1254 1111 1175 881 916 1225
2 781 950 1007 534 570 567 1353
3 536 610 596 544 1281 882 3304
4 59 51 56 50 97 79 97
5 5303 6083 4861 2370 5649 6551 5800
6 2187 2296 1671 1049 1229 1880 2041
X12.1.2018 X1.1.2019 X2.1.2019 X3.1.2019 X4.1.2019 X5.1.2019 X6.1.2019
1 682 739 1056 1117 727 1233 839
2 1212 1122 1135 1261 1046 931 695
3 738 722 657 841 629 683 543
4 65 97 73 108 106 96 93
5 4791 6390 6570 6569 5980 6135 5581
6 1935 1617 1821 1940 1905 1699 2255
X7.1.2019 X8.1.2019 X9.1.2019 X10.1.2019 X11.1.2019 X12.1.2019 X1.1.2020
1 862 862 702 740 934 603 468
2 849 644 635 1753 1506 1826 1618
3 680 538 777 633 750 578 683
4 122 88 124 130 187 136 141
5 5405 2380 8031 6108 6079 4702 6828
6 1853 522 1682 2261 1523 3210 1764
X2.1.2020
1 903
2 1382
3 704
4 132
5 5717
6 2230It’s a data frame in a wide format, with several columns related to a same dimension of period of time :
the first variable is called Product.Description, and it contains this time 10.000 products.
the next 41 variables are monthly periods of time.
We also notice that there are some missing values, we will replace them by zero later on.
We’re going to perform the same steps as we did previously, reusing the same codes.
11.2 Pivot & Transform
# Pivot
df1 <- df1 |> gather(key = "period",
value = "sales_qty",
2:length(df1))
# replace missing 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')
#---------------------------------
# Extract Date features
#---------------------------------
# Calendar Year
df1$calendar_year <- year(df1$period)
# Calendar Month
df1$calendar_month <- month(df1$period)
# transform the Month number into a month abbreviation
# note that month.abb comes with [ and not (
df1$calendar_month_abb <- month.abb[df1$calendar_month]
# Create Factor
df1$calendar_month_abb <- factor(df1$calendar_month_abb,
levels = c("Oct", "Nov", "Dec","Jan","Feb","Mar",
"Apr","May","Jun","Jul","Aug","Sep"))
# keep results
sales_data <- df1
glimpse(df1)Rows: 410,000
Columns: 6
$ 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…
$ calendar_month <dbl> 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10…
$ calendar_month_abb <fct> Oct, Oct, Oct, Oct, Oct, Oct, Oct, Oct, Oct, Oct, …Note that this time, after pivot the data, the data frame is much longer. There are now 410.000 rows.
11.3 Create a Fiscal Calendar
We’re going to create a Fiscal Calendar, starting in October.
# Create a Fiscal Calendar
df1 <- data.frame(
calendar_month_abb = c("Oct", "Nov", "Dec", "Jan", "Feb", "Mar",
"Apr", "May", "Jun", "Jul", "Aug", "Sep"),
fiscal_month = c(1,2,3,4,5,6,7,8,9,10,11,12)
)
# display
df1 calendar_month_abb fiscal_month
1 Oct 1
2 Nov 2
3 Dec 3
4 Jan 4
5 Feb 5
6 Mar 6
7 Apr 7
8 May 8
9 Jun 9
10 Jul 10
11 Aug 11
12 Sep 1211.4 Create a factor
# create a Factor
df1$calendar_month_abb <- factor(df1$calendar_month_abb,
levels = c("Oct", "Nov", "Dec", "Jan","Feb","Mar",
"Apr","May","Jun","Jul","Aug","Sep"))
# keep results
fiscal_calendar_data <- df1
# display
fiscal_calendar_data calendar_month_abb fiscal_month
1 Oct 1
2 Nov 2
3 Dec 3
4 Jan 4
5 Feb 5
6 Mar 6
7 Apr 7
8 May 8
9 Jun 9
10 Jul 10
11 Aug 11
12 Sep 1211.5 Merge
# Merge data
df1 <- left_join(sales_data, fiscal_calendar_data)
glimpse(df1)Rows: 410,000
Columns: 7
$ 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…
$ calendar_month <dbl> 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10…
$ calendar_month_abb <fct> Oct, Oct, Oct, Oct, Oct, Oct, Oct, Oct, Oct, Oct, …
$ fiscal_month <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,…11.6 Keep results
# Keep Results
set_up_sales_data <- df1
glimpse(df1)Rows: 410,000
Columns: 7
$ 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…
$ calendar_month <dbl> 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10…
$ calendar_month_abb <fct> Oct, Oct, Oct, Oct, Oct, Oct, Oct, Oct, Oct, Oct, …
$ fiscal_month <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,…The result is a data frame of 7 columns and 410.000 rows.
We created it exactly the same way we did in the first example, using the same code. R is scalable and reproducible.