I have a source dataset that looks like the following:
cat1 <- c('Text1','Text1', 'Text2','Text1', 'Text1','Text4', 'Text1','Text3', 'Text1','Text1', 'Text2', 'Text1')
cat2 <- c('AA','AA','BB','BB','AA','BB','AA','BB','AA','BB','AA','AA')
col3 <- c('A' ,'B' ,'C' ,'D' ,'E' ,'F' ,'G' ,'H' ,'I' ,'J' ,'K' ,'L' )
grp1 <- c('Block 1','Block 2','Block 1','Block 2','Block 3','Block 3', 'Block 4', 'Block 1', 'Block 4', 'Block 3', 'Block 2', 'Block 1')
nums <- c(100, 200, 300, 400, 100, 200, 300, 400, 100, 200, 300, 400)
orig_src_df <- cbind(cat1, cat2, col3, grp1, nums) |>
as.data.frame() |>
mutate(nums = as.numeric(nums))
I have a 2nd dataset that looks like this:
grp1 <- c('Block 1', 'Block 1', 'Block 2', 'Block 3', 'Block 4', 'Block 4')
grp2 <- c('Block 1', 'Block 5', 'Block 5', 'Block 6', 'Block 6', 'Block 5')
ratio <-c(0.15, 0.85, 1, 1, 0.25, 0.75)
conversion_df <- cbind(grp1, grp2, ratio) |>
as.data.frame() |>
mutate(
cat1 = 'Text1',
cat2 = 'AA',
ratio = as.numeric(ratio)
)
Anytime I have values where cat1 == “Text1” and cat2 == “AA”, I want to split the values of nums by the ratio in the 2nd dataset and add the grp2 column as it will supersede grp1.
What is a clean way in which I can do this?
0
Maybe like this?
You mentioned “add the grp2 column as it will supersede grp1”, so here I have altered grp1 to be the new grp2 only when a match was found in the 2nd table. coalesce will output the first non-missing (non-NA) value, so we can use that there, and also to make nums_new use the ratio when available, or 1 if not.
orig_src_df |>
left_join(conversion_df) |>
transmute(cat1, cat2, col3,
grp1 = coalesce(grp2, grp1),
nums_orig = nums,
nums_new = coalesce(ratio, 1) * nums)
Result
Joining with `by = join_by(cat1, cat2, grp1)`
cat1 cat2 col3 grp1 nums_orig nums_new
1 Text1 AA A Block 1 100 15
2 Text1 AA A Block 5 100 85
3 Text1 AA B Block 5 200 200
4 Text2 BB C Block 1 300 300
5 Text1 BB D Block 2 400 400
6 Text1 AA E Block 6 100 100
7 Text4 BB F Block 3 200 200
8 Text1 AA G Block 6 300 75
9 Text1 AA G Block 5 300 225
10 Text3 BB H Block 1 400 400
11 Text1 AA I Block 6 100 25
12 Text1 AA I Block 5 100 75
13 Text1 BB J Block 3 200 200
14 Text2 AA K Block 2 300 300
15 Text1 AA L Block 1 400 60
16 Text1 AA L Block 5 400 340
Not sure if that‘s waht you want:
library(tidyverse)
orig_src_df |>
left_join(conversion_df, by = c('cat1', 'cat2', 'grp1')) |>
mutate(nums = if_else(!is.na(ratio), nums * ratio, nums))
cat1 cat2 col3 grp1 nums grp2 ratio
1 Text1 AA A Block 1 15 Block 1 0.15
2 Text1 AA A Block 1 85 Block 5 0.85
3 Text1 AA B Block 2 200 Block 5 1.00
4 Text2 BB C Block 1 300 <NA> NA
5 Text1 BB D Block 2 400 <NA> NA
6 Text1 AA E Block 3 100 Block 6 1.00
7 Text4 BB F Block 3 200 <NA> NA
8 Text1 AA G Block 4 75 Block 6 0.25
9 Text1 AA G Block 4 225 Block 5 0.75
10 Text3 BB H Block 1 400 <NA> NA
11 Text1 AA I Block 4 25 Block 6 0.25
12 Text1 AA I Block 4 75 Block 5 0.75
13 Text1 BB J Block 3 200 <NA> NA
14 Text2 AA K Block 2 300 <NA> NA
15 Text1 AA L Block 1 60 Block 1 0.15
16 Text1 AA L Block 1 340 Block 5 0.85