Select Different Column for Each Row Based on Calculated Value

In this article, we will explore how to select different columns from a dataset based on calculated values using the dplyr library in R.

Introduction

The dplyr library provides a grammar of data manipulation, which allows us to easily manipulate and transform datasets. In this article, we will use the dplyr library to achieve our goal.

We have a dataset df1 that contains four columns: date1, date2, Category, and DR0. We also have another dataset All that contains three columns: date2, Category, and coef.

The code provided in the question is using the dplyr library to perform a left join between df1 and All on the date2 and Category columns. It then uses the across function to apply a calculation to each column in df1. The calculation subtracts the value of coef from the values of DR0.

Understanding the Problem

We want to modify the code so that instead of showing all the calculated values, we only show the values corresponding to the difference between date2 and date1. For example, if date2 - date1 is 1, we use the value of coef-DR01, if it is 5, we use the value obtained from coef-DR05.

Solution

To achieve this, we need to first calculate the difference between date2 and date1. Then, we use the str_sub function to extract the last two characters of the column name. We filter the data based on this value to select only the rows where date2 - date1 matches the extracted value.

Code

library(dplyr)
library(tidyr)
library(stringr)

# Create sample datasets
df1 <- structure(list(date1 = c("2021-06-28", "2021-06-28", "2021-06-28", "2021-06-28"), 
                      date2 = c("2021-06-30", "2021-06-30", "2021-07-01", "2021-07-01"), 
                      Category = c("FDE", "ABC", "FDE", "ABC"), 
                      Week = c("Wednesday", "Wednesday", "Friday", "Friday"), 
                      DR1 = c(4, 1, 6, 3), DR01 = c(4, 1, 4, 3), DR02 = c(4, 2, 6, 2), 
                      DR03 = c(9, 5, 4, 7), DR04 = c(5, 4, 3, 2), DR05 = c(5, 4, 5, 4), 
                      DR06 = c(2, 4, 3, 2)), class = "data.frame", row.names = c(NA, -4L))

All <- structure(list(date2 = c("2021-06-30", "2021-06-30", "2021-07-01", "2021-07-01"), 
                      Category = c("FDE", "ABC", "FDE", "ABC"), coef = c(4L, 1L, 6L, 3L)), 
                 class = "data.frame", row.names = c("1", "2", "3", "4"))

# Calculate the difference between date2 and date1
df1$diff <- df1$date2 - df1$date1

# Filter the data based on the calculated value
All %>% 
  mutate(across(date1:date2, as.Date)) %>% 
  pivot_longer(starts_with('coef-'), values_to = 'coef-DR') %>% 
  filter(str_sub(name, -2) == diff) %>% 
  select(-name)

Explanation

In the code above, we first calculate the difference between date2 and date1 using the $diff column in df1. Then, we use the str_sub function to extract the last two characters of the column name. We filter the data based on this value to select only the rows where date2 - date1 matches the extracted value.

The resulting data is then selected using the -name argument, which removes the original column names.

Output

The output will be a tibble with four rows:

This output shows only the values corresponding to the difference between date2 and date1.

Last modified on 2023-10-21