# ETL
library(tidyverse)8 Other useful functions
In the previous chapter we saw the main functions to manipulate data and transform a data frame.
Now we’re going to introduce a few more useful functions, with a particular focus on the stringr package.
As usual, let’s start by uploading the needed libraries.
Create a demo data frame
Let’s create a demo data frame with 2 variables :
a period of time.
an associated sales value.
# create vectors
period <- c("10/4/2025", "10/12/2025", "10/17/2025", "10/24/2025", "11/4/2025", "12/5/2025", "12/13/2025", "12/19/2025", "12/26/2025", "1/5/2026", "1/14/2026")
sales <- c(10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110)
# combine
df1 <- data.frame(period,
sales)
# format the period as a date
df1$period <- as.Date(df1$period, format = "%m/%d/%Y")
# keep results
demo_data <- df1
glimpse(df1)Rows: 11
Columns: 2
$ period <date> 2025-10-04, 2025-10-12, 2025-10-17, 2025-10-24, 2025-11-04, 20…
$ sales <dbl> 10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 1109 lag() and lead()
Let’s introduce the functions lag() and lead() from the package dplyr.
They are very useful to “navigate” within a data frame and are opposite functions :
lag(): will consider the previous rows’ values.lead(): will consider the next rows’ values.
9.1 lag()
In the example below, we will use the function lag() to put on the current row the previous value of the variable “sales”, which is 1, 2 or 3 rows before.
# set a working df
df1 <- demo_data
# create a new variable with lagged sales, which is by default one row
df1 <- df1 |>
mutate(lagged_sales_1_period = lag(sales))
# we also can lag 2 or 3 rows, for example :
df1 <- df1 |>
mutate(
lagged_sales_2_periods = lag(sales, n = 2),
lagged_sales_3_periods = lag(sales, n = 3)
)
# display data frame
df1 period sales lagged_sales_1_period lagged_sales_2_periods
1 2025-10-04 10 NA NA
2 2025-10-12 20 10 NA
3 2025-10-17 30 20 10
4 2025-10-24 40 30 20
5 2025-11-04 50 40 30
6 2025-12-05 60 50 40
7 2025-12-13 70 60 50
8 2025-12-19 80 70 60
9 2025-12-26 90 80 70
10 2026-01-05 100 90 80
11 2026-01-14 110 100 90
lagged_sales_3_periods
1 NA
2 NA
3 NA
4 10
5 20
6 30
7 40
8 50
9 60
10 70
11 80We can see that the variables :
“lagged_sales_1_period” contains the value of the variable “sales” located 1 row before.
“lagged_sales_2_periods” contains the value of the variable “sales” located 2 rows before.
“lagged_sales_3_periods” contains the value of the variable “sales” located 3 rows before.
9.2 lead()
In the following example, we will use the function lead() to put on the current row the next value of the variable “sales”, which is 1, 2 or 3 rows later.
# set a working df
df1 <- demo_data
# create a new variable with lagged sales, which is by default one row
df1 <- df1 |>
mutate(lead_sales_1_period = lead(sales))
# we also can lead 2 or 3 rows, for example :
df1 <- df1 |>
mutate(
lead_sales_2_periods = lead(sales, n = 2),
lead_sales_3_periods = lead(sales, n = 3)
)
# display data frame
df1 period sales lead_sales_1_period lead_sales_2_periods
1 2025-10-04 10 20 30
2 2025-10-12 20 30 40
3 2025-10-17 30 40 50
4 2025-10-24 40 50 60
5 2025-11-04 50 60 70
6 2025-12-05 60 70 80
7 2025-12-13 70 80 90
8 2025-12-19 80 90 100
9 2025-12-26 90 100 110
10 2026-01-05 100 110 NA
11 2026-01-14 110 NA NA
lead_sales_3_periods
1 40
2 50
3 60
4 70
5 80
6 90
7 100
8 110
9 NA
10 NA
11 NAWe can see that the variables :
“lead_sales_1_period” contains the value of the variable “sales” located 1 row later.
“lead_sales_2_periods” contains the value of the variable “sales” located 2 rows later.
“lead_sales_3_periods” contains the value of the variable “sales” located 3 rows later.
10 floor_date()
The function floor_date() from the package lubridate allows to quickly transform a date into the beginning of a period it is related to.
For example, the beginning of the week or the beginning of the month.
Let’s illustrate it using the data frame “demo_data” :
# set a working df
df1 <- demo_data
# let's create a week_period variable
# which is the beginning of the week the period is related to
df1$week_period <- floor_date(df1$period, unit = "week")
# let's create a month_year_period variable
# which is the beginning of the month the period is related to
df1$month_year_period <- floor_date(df1$period, unit = "month")
# display data frame
df1 period sales week_period month_year_period
1 2025-10-04 10 2025-09-28 2025-10-01
2 2025-10-12 20 2025-10-12 2025-10-01
3 2025-10-17 30 2025-10-12 2025-10-01
4 2025-10-24 40 2025-10-19 2025-10-01
5 2025-11-04 50 2025-11-02 2025-11-01
6 2025-12-05 60 2025-11-30 2025-12-01
7 2025-12-13 70 2025-12-07 2025-12-01
8 2025-12-19 80 2025-12-14 2025-12-01
9 2025-12-26 90 2025-12-21 2025-12-01
10 2026-01-05 100 2026-01-04 2026-01-01
11 2026-01-14 110 2026-01-11 2026-01-01We got 2 new variables :
week_period : which is the beginning of the week which contains the value of the variable period.
- note : by default, the beginning of a week starts on Sunday.
month_year_period : which is the beginning of the month which contains the value of the variable period.
11 concatenate
Now, we’re going to look at a way to concatenate values. R offers several options to proceed, and a simple one is to use the function paste().
Let’s first create a new demo data frame with 3 variables :
product : 5 items.
country : where those products are sold.
region : where those countries are located.
# create vectors
product <- c("item1", "item1", "item1", "item2", "item2", "item3", "item3", "item3", "item4", "item4", "item5")
country <- c("Singapore", "Japan", "Taiwan", "Singapore", "Malaysia", "Australia", "Taiwan", "South Korea", "Vietnam", "Thailand", "Philippines")
region <- c("South East Asia", "North Asia", "North Asia", "South East Asia", "South East Asia", "Oceania", "North Asia", "North Asia", "South East Asia", "South East Asia", "South East Asia")
# combine
df1 <- data.frame(product,
country,
region)
# keep results
demo_data <- df1
# display data frame
demo_data product country region
1 item1 Singapore South East Asia
2 item1 Japan North Asia
3 item1 Taiwan North Asia
4 item2 Singapore South East Asia
5 item2 Malaysia South East Asia
6 item3 Australia Oceania
7 item3 Taiwan North Asia
8 item3 South Korea North Asia
9 item4 Vietnam South East Asia
10 item4 Thailand South East Asia
11 item5 Philippines South East AsiaThe paste() function is used to concatenate strings.
We can specify a separator using the “sep” argument.
Let’s create 2 variables :
“DFU” : the concatenation of [product] and [country]. We will separate using the sign “
|”.“comment” : a chain of strings with some additional words, to form a short sentence.
# set a working df
df1 <- demo_data
# Concatenate using paste()
df1$DFU <- paste(df1$product, df1$country, sep = "|")
# we also can add some words, for example :
df1$comment <- paste("The", df1$product, "is sold in", df1$country, sep = " ")
# display results
df1 product country region DFU
1 item1 Singapore South East Asia item1|Singapore
2 item1 Japan North Asia item1|Japan
3 item1 Taiwan North Asia item1|Taiwan
4 item2 Singapore South East Asia item2|Singapore
5 item2 Malaysia South East Asia item2|Malaysia
6 item3 Australia Oceania item3|Australia
7 item3 Taiwan North Asia item3|Taiwan
8 item3 South Korea North Asia item3|South Korea
9 item4 Vietnam South East Asia item4|Vietnam
10 item4 Thailand South East Asia item4|Thailand
11 item5 Philippines South East Asia item5|Philippines
comment
1 The item1 is sold in Singapore
2 The item1 is sold in Japan
3 The item1 is sold in Taiwan
4 The item2 is sold in Singapore
5 The item2 is sold in Malaysia
6 The item3 is sold in Australia
7 The item3 is sold in Taiwan
8 The item3 is sold in South Korea
9 The item4 is sold in Vietnam
10 The item4 is sold in Thailand
11 The item5 is sold in Philippines12 separate
We also can split a concatenated variable, using the function separate() .
Let’s keep only 2 variables from the previous example :
df1 <- df1 |> select(region, DFU)Now let’s separate the variable DFU, using the sign “|” as separator.
# separate variable
df_separated <- df1 |>
separate(DFU, into = c("product", "country"), sep = "\\|")
# display
df_separated region product country
1 South East Asia item1 Singapore
2 North Asia item1 Japan
3 North Asia item1 Taiwan
4 South East Asia item2 Singapore
5 South East Asia item2 Malaysia
6 Oceania item3 Australia
7 North Asia item3 Taiwan
8 North Asia item3 South Korea
9 South East Asia item4 Vietnam
10 South East Asia item4 Thailand
11 South East Asia item5 PhilippinesIn this particular example, we need to write a double backlash “\\” before the sign “|”.
It’s because in R, the pipe character (|) is interpreted as a special character in regular expressions. Regular expressions are used for pattern matching and text manipulation, and certain characters have special meanings within them.
13 round
There are a few ways to round numbers.
Here are the most common ways : round up, round down, and at a specific number of digits after the comma.
13.1 round up
We use the function ceiling().
# Round up the value 3.728
rounded_value <- ceiling(3.728)
# display value
rounded_value[1] 413.2 round down
We use the function floor() .
# Round down the value 3.728
rounded_value <- floor(3.728)
# display value
rounded_value[1] 313.3 at 1 digit
We use the function round(), and specify after the comma the number of digits we want .
# Round the value 3.728 to one decimal place
rounded_value <- round(3.728, 1)
rounded_value[1] 3.714 more about stringr
14.1 Pad a string
This is useful when you want to add a white space to a string to make sure it has a certain length.
Using the function str_pad(), it’s possible to add a white space on the left side, right side, or on both sides. Let’s look at the example below :
# set a working df
df1 <- demo_data
# add pad
df1 <- df1 |>
mutate(
country_both = str_pad(country, width = 20, side = "both"),
country_left = str_pad(country, width = 20, side = "left"),
country_right = str_pad(country, width = 20, side = "right")
)
# display data frame
df1 product country region country_both
1 item1 Singapore South East Asia Singapore
2 item1 Japan North Asia Japan
3 item1 Taiwan North Asia Taiwan
4 item2 Singapore South East Asia Singapore
5 item2 Malaysia South East Asia Malaysia
6 item3 Australia Oceania Australia
7 item3 Taiwan North Asia Taiwan
8 item3 South Korea North Asia South Korea
9 item4 Vietnam South East Asia Vietnam
10 item4 Thailand South East Asia Thailand
11 item5 Philippines South East Asia Philippines
country_left country_right
1 Singapore Singapore
2 Japan Japan
3 Taiwan Taiwan
4 Singapore Singapore
5 Malaysia Malaysia
6 Australia Australia
7 Taiwan Taiwan
8 South Korea South Korea
9 Vietnam Vietnam
10 Thailand Thailand
11 Philippines Philippines 14.2 Collapse a vector of strings
You want to collapse a vector of strings into a single string.
A classic use case is when you want to write your R object as a csv or an excel file, as the list format needs to be converted into a simple character string.
First, let’s create a list of countries where our products are sold.
# set a working df
df1 <- demo_data
# create a list
df1 <- df1 |> group_by(product) |>
summarise(country = list(country))
# display data frame
df1# A tibble: 5 × 2
product country
<chr> <list>
1 item1 <chr [3]>
2 item2 <chr [2]>
3 item3 <chr [3]>
4 item4 <chr [2]>
5 item5 <chr [1]>Now, let’s collapse this list, using the function below, through sapply().
Note : we will see in the next chapters how to create a function.
The variable “country” is now a character, capturing all the values which were inside the vector, and separating them with a “;” symbol.
# Convert list column to a character string
df1 <- df1 |> mutate(country = sapply(country,
function(x) paste(x, collapse = "; "))
)
df1# A tibble: 5 × 2
product country
<chr> <chr>
1 item1 Singapore; Japan; Taiwan
2 item2 Singapore; Malaysia
3 item3 Australia; Taiwan; South Korea
4 item4 Vietnam; Thailand
5 item5 Philippines 14.3 Detect matches in a string
14.3.1 inside a character
You want to check if a regular expression matches in a string. We can use the function str_detect().
In the example below, we want to identify the rows for which the variable “country” has strings which contain the pattern “an”.
We’re going to create a new variable called “contains_an” to identify those rows, based on the function str_detect() .
# set a working df
df1 <- demo_data
# create a new variable 'contains_an' using str_detect
df1$contains_an <- str_detect(df1$country, "an")
# display data frame
df1 product country region contains_an
1 item1 Singapore South East Asia FALSE
2 item1 Japan North Asia TRUE
3 item1 Taiwan North Asia TRUE
4 item2 Singapore South East Asia FALSE
5 item2 Malaysia South East Asia FALSE
6 item3 Australia Oceania FALSE
7 item3 Taiwan North Asia TRUE
8 item3 South Korea North Asia FALSE
9 item4 Vietnam South East Asia FALSE
10 item4 Thailand South East Asia TRUE
11 item5 Philippines South East Asia FALSE14.3.2 inside a vector
It also works when we apply it to a list of values.
Let’s create a new variable called “list_of_country” which displays, as a list (i.e. here a vector) the list of the countries where the product X is sold.
This list of country will be presented as c("Singapore", "Japan", "Taiwan") for example.
# set a working df
df1 <- demo_data
# create a list
df1 <- df1 |> group_by(product) |>
summarise(list_of_country = list(country))
# Create a new variable 'contains_Singapore' using str_detect
df1$contains_Singapore <- str_detect(df1$list_of_country, "Singapore")Warning in stri_detect_regex(string, pattern, negate = negate, opts_regex =
opts(pattern)): argument is not an atomic vector; coercing# display data frame
df1# A tibble: 5 × 3
product list_of_country contains_Singapore
<chr> <list> <lgl>
1 item1 <chr [3]> TRUE
2 item2 <chr [2]> TRUE
3 item3 <chr [3]> FALSE
4 item4 <chr [2]> FALSE
5 item5 <chr [1]> FALSE Looking at the variable “contains_Singapore” in this table, we can quickly identify the rows, items, which are concerned.
14.4 Match the start or end of a string
When you want to write a regular expression that matches strings that start or end with a particular pattern.
We can use the functions str_starts() and str_ends().
Note : they are “case sensitive”, which means that they will consider the capital or small characters.
# set a working df
df1 <- demo_data
# ending with "an"
df1$ends_with_an <- str_ends(df1$country, "an")
# starting with "Vi"
df1$starts_with_vi <- str_starts(df1$country, "Vi")
# display data frame
df1 product country region ends_with_an starts_with_vi
1 item1 Singapore South East Asia FALSE FALSE
2 item1 Japan North Asia TRUE FALSE
3 item1 Taiwan North Asia TRUE FALSE
4 item2 Singapore South East Asia FALSE FALSE
5 item2 Malaysia South East Asia FALSE FALSE
6 item3 Australia Oceania FALSE FALSE
7 item3 Taiwan North Asia TRUE FALSE
8 item3 South Korea North Asia FALSE FALSE
9 item4 Vietnam South East Asia FALSE TRUE
10 item4 Thailand South East Asia FALSE FALSE
11 item5 Philippines South East Asia FALSE FALSE14.5 Filter based on a string
We previously see that we can identify the strings which contain, starts or ends with a particular pattern.
Now, we’re going to see how we can filter the rows related to those patterns. For this, we just need to combine 2 functions : the function filter() together with str_starts() or str_ends() or str_detect() .
14.5.1 starts with
Let’s filter the rows of the data frame demo_data, for which the variable country starts with the pattern “Ph”.
# set a working df
df1 <- demo_data
# filter
df1 <- df1 |> filter(str_starts(country, "Ph"))
# display data frame
df1 product country region
1 item5 Philippines South East Asia14.5.2 ends with
Let’s filter the rows of the data frame demo_data, for which the variable country ends with the pattern “an”.
# set a working df
df1 <- demo_data
# filter
df1 <- df1 |> filter(str_ends(country, "an"))
# display data frame
df1 product country region
1 item1 Japan North Asia
2 item1 Taiwan North Asia
3 item3 Taiwan North Asia14.5.3 contains
Let’s filter the rows of the data frame demo_data, for which the variable country contents the pattern “ai”.
# set a working df
df1 <- demo_data
# filter
df1 <- df1 |> filter(str_detect(country, "ai"))
# display data frame
df1 product country region
1 item1 Taiwan North Asia
2 item3 Taiwan North Asia
3 item4 Thailand South East Asia14.6 Count the number of matches of a pattern in a string
Let’s continue on our dataset demo_data.
Let’s say that we want to know in how many countries of the region “North Asia” each product is sold.
We can :
create a list with all the regions where a product is sold, based on the location of the country.
then use the function
str_count()to “screen” the list, and count the occurrences of the pattern we’re looking for.
# set a working df
df1 <- demo_data
# create a list
df1 <- df1 |> group_by(product) |>
summarise(region = list(region))
# count the number of occurrences of the value "North Asia" within the variable "region"
df1 <- df1 |>
mutate(
counts_nb_occurences = str_count(region, pattern = "North Asia")
)
# display data frame
df1# A tibble: 5 × 3
product region counts_nb_occurences
<chr> <list> <int>
1 item1 <chr [3]> 2
2 item2 <chr [2]> 0
3 item3 <chr [3]> 2
4 item4 <chr [2]> 0
5 item5 <chr [1]> 0