Cummulate and concatenate
[This article was first published on Numbers around us - Medium, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
Excel BI’s Excel Challenge #313 — solved in R
![](https://www.r-bloggers.com/wp-content/plugins/jetpack/modules/lazy-images/images/1x1.trans.gif)
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.
![](https://www.r-bloggers.com/wp-content/plugins/jetpack/modules/lazy-images/images/1x1.trans.gif)
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.
To leave a comment for the author, please follow the link and comment on their blog: Numbers around us - Medium.
R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.