Long data calculate new values and append

user_6779355 0
Viewed 41
set.seed(2022)
df <- data.frame(key1=rep(c('a','b'),each=2),
                 key2=rep(c('x1','x2'),2),
                 v1=runif(4),
                 v2=runif(4)
                 )

I want to calculate new values x3=x2/x1 for each key1,then append to primary dataframe.The total result I want such as:

  key1 key2        v1        v2
1    a   x1 0.6702825 0.6736447
2    a   x2 0.2144166 0.9945389
3    b   x1 0.7663272 0.3333884
4    b   x2 0.9322578 0.9882694
5    a   x3   x2/x1       x2/x1   <---new values
6    b   x3   x2/x1       x2/x1   <---new values

Thx!

dataframe r
3 Answers

A possible approach using dplyr -

library(dplyr)

df %>%
  bind_rows(df %>%
  group_by(key1) %>%
  summarise(across(starts_with('v'), 
                   ~.[match('x2', key2)]/.[match('x1', key2)]), 
            key2 = 'x3'))

#  key1 key2        v1         v2
#1    a   x1 0.8159777 0.18472999
#2    a   x2 0.6472593 0.63579085
#3    b   x1 0.1203286 0.07429900
#4    b   x2 0.5438002 0.04197593
#5    a   x3 0.7932317 3.44173054
#6    b   x3 4.5192932 0.56495961

data

set.seed(2022)
df <- data.frame(key1=rep(c('a','b'),each=2),
                 key2=rep(c('x1','x2'),2),
                 v1=runif(4),
                 v2=runif(4))
user_39629140

You can use group_modify with add_row from dplyr:

library(dplyr)

df %>% 
  group_by(key1) %>% 
  group_modify(~ add_row(.x, 
                         key2 = "x3",
                         v1 = .x$v1[.x$key2=="x2"] / .x$v1[.x$key2=="x1"],
                         v2 = .x$v2[.x$key2=="x2"] / .x$v2[.x$key2=="x1"]))

  key1  key2        v1     v2
  <chr> <chr>    <dbl>  <dbl>
1 a     x1     0.00660 0.189 
2 a     x2     0.0796  0.0898
3 a     x3    12.1     0.476 
4 b     x1     0.111   0.775 
5 b     x2     0.245   0.884 
6 b     x3     2.21    1.14
user_134606020

Another possible solution:

library(tidyverse)

df %>% 
  group_by(key1) %>% 
  slice(c(1:2, 2)) %>%
  mutate(key2 = str_c("x", 1:3),
    across(v1:v2,~ c(first(.x), last(.x), last(.x) / first(.x)))) %>%   
  ungroup

#> # A tibble: 6 × 4
#>   key1  key2     v1     v2
#>   <chr> <chr> <dbl>  <dbl>
#> 1 a     x1    0.816 0.185 
#> 2 a     x2    0.647 0.636 
#> 3 a     x3    0.793 3.44  
#> 4 b     x1    0.120 0.0743
#> 5 b     x2    0.544 0.0420
#> 6 b     x3    4.52  0.565
user_115644870
Related