# ETL
library(tidyverse)11 Calculate YTD vs LYTD
We’re now going to create a very classic type of table : a comparison of YTD (Year-to-Date) vs LYTD (Last-Year-to-Date) sales per products.
It’s an useful way to compare the evolution of the sales over a similar horizon of time between 2 years.
Let’s start by uploading the needed libraries. Here, the usual tidyverse.
Next, we will upload the raw data and transform them into a tidy format.
12 Get Tidy dataset
12.1 Upload Raw Data
The data frame is located on the GitHub repository.
It’s the same data frame that we used 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_example_raw_data.csv"
# Read the CSV file from the URL
df1 <- read.csv(url)
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 223012.2 Tidy
We follow the same approach that we did in the first practice.
# 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("Jan","Feb","Mar","Apr","May","Jun",
"Jul","Aug","Sep", "Oct", "Nov", "Dec"))
# Get Results
Tidy_data <- df1
glimpse(Tidy_data)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, …13 Calculate YTD vs LYTD
We will use our previous data frame Tidy_data, and calculate the YTD Feb20 vs LYTD Feb19 for all the products.
We will go through the following steps :
Get Data.
Filter those data : using the function
filter()on 2 dimensions.calendar_year : to keep only the 2 years we want to compare.
calendar_month : to keep only the YTD horizon we want to compare.
Transform those data.
perform an aggregation around the calendar_year, then calculate the difference (delta) between YTD vs LYTD, in value and in percentage (vs last year) per product.
then sort the data by descending order using the function
arrange().name some columns, using the function
colnames(), based on their position.
and finally give a name to the final data frame : YTD_vs_LYTD_data .
#---------------------------------
# Get Data
#---------------------------------
# set a working df
df1 <- Tidy_data
#---------------------------------
# Filters
#---------------------------------
# Select Years
df1 <- df1 |> filter(calendar_year %in% c("2020", "2019"))
# Select Months
df1 <- df1 |> filter(calendar_month <= 2)
#---------------------------------
# Transform
#---------------------------------
# aggregate
df1 <- df1 |> group_by(Product.Description, calendar_year) |>
summarise(sales_qty = sum(sales_qty))
# spread
df1 <- df1 |> spread(calendar_year, sales_qty)
# Calculate differences
df1$delta <- df1$'2020' - df1$'2019'
df1$delta.pc <- df1$delta / df1$'2019'
# Calculate YTD.pc
df1$YTD.pc <- df1$'2020' / sum(df1$'2020')
# rename
df1 <- df1 |> rename(YTD2020 = '2020',
LYTD2019 = '2019')
# sort
df1 <- df1 |> arrange(desc(YTD2020))
# display results
df1# A tibble: 6 × 6
# Groups: Product.Description [6]
Product.Description LYTD2019 YTD2020 delta delta.pc YTD.pc
<chr> <int> <int> <int> <dbl> <dbl>
1 Product E 12960 12545 -415 -0.0320 0.556
2 Product F 3438 3994 556 0.162 0.177
3 Product B 2257 3000 743 0.329 0.133
4 Product C 1379 1387 8 0.00580 0.0615
5 Product A 1795 1371 -424 -0.236 0.0607
6 Product D 170 273 103 0.606 0.0121The function colnames() can be used to rename a column based on its position.
We also could use the function rename() that we saw previously.
The purpose is to introduce here another quite useful function.
# rename column
colnames(df1)[2] <- "LYTD (units)"
colnames(df1)[3] <- "YTD (units)"
colnames(df1)[4] <- "YTD vs LYTD (units)"
colnames(df1)[5] <- "YTD vs LYTD Volume (%)"
colnames(df1)[6] <- "YTD share Volume (%)"
# keep results
YTD_vs_LYTD_data <- df1
# display results
YTD_vs_LYTD_data# A tibble: 6 × 6
# Groups: Product.Description [6]
Product.Description `LYTD (units)` `YTD (units)` `YTD vs LYTD (units)`
<chr> <int> <int> <int>
1 Product E 12960 12545 -415
2 Product F 3438 3994 556
3 Product B 2257 3000 743
4 Product C 1379 1387 8
5 Product A 1795 1371 -424
6 Product D 170 273 103
# ℹ 2 more variables: `YTD vs LYTD Volume (%)` <dbl>,
# `YTD share Volume (%)` <dbl>