How to create a new column in R where arithmetic is applied on opposite values given a group

Question

Suppose the following data frame:

group	value1	value2
1	16	2
1	20	4
2	12	2
2	8	6
3	10	7
3	14	5

I want to create a table that divides value2 over value1. However, I want it to be the only other unique value in the same group. I've attached an image to demonstrate the process.

here is an image of what I'm trying to achieve

When that is done, the output should look something like this:

group	value1	value2	perc
1	16	2	2/20 10
1	20	4	4/16 25
2	12	2	2/8 25
2	8	6	6/12 50
3	10	7	7/14 50
3	14	5	5/10 50

(I've added the fractions in the perc column so it follows my image, I'd just like the value at the end of each row)

At the moment, I'm having a hard time with this problem, I realise it may have something to do with setdiff and selecting the only other unique value in that group (there's only two rows per group) but I'm not sure how. Any help is much appreciated. Thank you!

Answer 1

We can reverse the order of value1 then calculate the perc column.

library(dplyr)

df %>% 
  group_by(group) %>% 
  mutate(value3 = rev(value1),
         perc = (value2/value3)*100) %>% 
  select(-value3)

# A tibble: 6 × 4
# Groups:   group [3]
  group value1 value2  perc
  <int>  <int>  <int> <dbl>
1     1     16      2    10
2     1     20      4    25
3     2     12      2    25
4     2      8      6    50
5     3     10      7    50
6     3     14      5    50

data

df <- read.table(header = T, text = "
group   value1  value2
1   16  2
1   20  4
2   12  2
2   8   6
3   10  7
3   14  5")

Answer 2

You can use lead and lag to get the cell above or below the current row. The two results can be joined together:

library(tidyverse)

data <- tribble(
  ~group, ~value1, ~value2,
  1L, 16L, 2L,
  1L, 20L, 4L,
  2L, 12L, 2L,
  2L, 8L, 6L,
  3L, 10L, 7L,
  3L, 14L, 5L
)

full_join(
  data %>%
    group_by(group) %>%
    mutate(
      frac = value2 / lead(value1),
      perc_text = str_glue("{value2}/{lead(value1)} {frac * 100}")
    ) %>%
    filter(!is.na(frac)),
  data %>%
    group_by(group) %>%
    mutate(
      frac = value2 / lag(value1),
      perc_text = str_glue("{value2}/{lag(value1)} {frac * 100}")
    ) %>%
    filter(!is.na(frac))
) %>%
  arrange(group)
#> Joining, by = c("group", "value1", "value2", "frac", "perc_text")
#> # A tibble: 6 × 5
#> # Groups:   group [3]
#>   group value1 value2  frac perc_text
#>   <int>  <int>  <int> <dbl> <glue>   
#> 1     1     16      2  0.1  2/20 10  
#> 2     1     20      4  0.25 4/16 25  
#> 3     2     12      2  0.25 2/8 25   
#> 4     2      8      6  0.5  6/12 50  
#> 5     3     10      7  0.5  7/14 50  
#> 6     3     14      5  0.5  5/10 50

^{Created on 2022-04-07 by the reprex package (v2.0.0)}