Cummulate and concatenate

Excel BI’s Excel Challenge #313 — solved in R

Defining the Puzzle:

Today we need to transform table into its cummulative (someway) version. Every column have to include content of column lagged by 2.

Generate the result table.
Here, Tn = T(n-2) & Tn where & is concatenation operator.

Loading Data from Excel:

Lets start loading data and libraries:

library(tidyverse)
library(readxl)
library(data.table)

input = read_excel(“Scan3.xlsx”, range = “A2:G5”, col_names = c(“X1”, “X2”, “X3”, “X4”, “X5”, “X6”, “X7”))
test = read_excel(“Scan3.xlsx”, range = “I2:O5”, col_names = c(“X1”, “X2”, “X3”, “X4”, “X5”, “X6”, “X7”))

Approach 1: Tidyverse with purrr

process_columns <- function(df) {
 num_cols <- ncol(df)
 if (num_cols < 3) {
 return(df)
 }
 for (i in 3:num_cols) {
 df <- df %>% 
 mutate(across(all_of(names(df)[i]), ~ paste0(df[[i — 2]], .)))
 }
 
 return(df)
}

result = process_columns(input)

Approach 2: Base R

process_columns_base_R <- function(df) {
 num_cols <- ncol(df)
 if (num_cols < 3) {
 return(df)
 }
 
 for (i in 3:num_cols) {
 df[[i]] <- paste0(df[[i — 2]], df[[i]])
 }
 
 return(df)
}

result_base_R = process_columns_base_R(input)

Approach 3: Data.table

process_columns_data_table <- function(df) {
 setDT(df) 
 num_cols <- ncol(df)
 if (num_cols < 3) {
 return(df)
 }
 
 cols_to_modify <- names(df)[3:num_cols]
 for (i in cols_to_modify) {
 df[, (i) := paste0(df[[which(names(df) == i) — 2]], df[[i]])]
 }
 
 return(as_tibble(as.data.frame(df)))
}

result_dt = process_columns_data_table(input)

Validating Our Solutions:

identical(result, test)
# [1] TRUE

identical(result_base_R, test)
# [1] TRUE

identical(result_dt, test)
# [1] TRUE

If you like my publications or have your own ways to solve those puzzles in R, Python or whatever tool you choose, let me know.

Cummulate and concatenate was originally published in Numbers around us on Medium, where people are continuing the conversation by highlighting and responding to this story.