Adding a variable to tidy data whose value is based on a column-row calculation

user_5965312 0
Viewed 46

My current project contains data that's got a structure like: 

my_df <- tibble(
  zn = c("hm","hm", "hm", "h60","h60","h60", "h85","h85","h85"),
  nm = c("c", "cA", "cB", "c","cA", "cB", "c","cA", "cB"), 
  val = c(60, 40, 20, 250, 150, 100, 400, 250, 150),
  znt = c(100, 100, 100, 300, 300, 300, 500, 500, 500), 
)

my_df
# A tibble: 9 x 4
zn    nm      val   znt
  <chr> <chr> <dbl> <dbl>
1 hm    c        60   100
2 hm    cA       40   100
3 hm    cB       20   100
4 h60   c       250   300
5 h60   cA      150   300
6 h60   cB      100   300
7 h85   c       400   500
8 h85   cA      250   500
9 h85   cB      150   500

And I would like to add a new variable, c0, whose value is defined c0 = znt - c for each zn. The final result would look like: 

# A tibble: 12 x 4
   zn    nm      val   znt
   <chr> <chr> <dbl> <dbl>
 1 hm    c0       40   100
 2 hm    c        60   100
 3 hm    cA       40   100
 4 hm    cB       20   100
 5 h60   c0       50   300
 6 h60   c       250   300
 7 h60   cA      150   300
 8 h60   cB      100   300
 9 h85   c0      100   500
10 h85   c       400   500
11 h85   cA      250   500
12 h85   cB      150   500

I have an idea about how to do it but it seems very convoluted and I'm hoping there's a better way. If this could be accomplished using something from the tidyverse that'd be awesome, too.

r
3 Answers

Another option:

library(tidyverse)

my_df %>%
  group_split(zn) %>%
  map_dfr(~ add_row(.data = ., 
                    zn = .$zn[1], 
                    nm = 'c0', 
                    val = .$znt[1] - .$val[.$nm == 'c'], 
                    znt = .$znt[1],
                    .before = 1))

Output:

# A tibble: 12 x 4
   zn    nm      val   znt
 * <chr> <chr> <dbl> <dbl>
 1 h60   c0       50   300
 2 h60   c       250   300
 3 h60   cA      150   300
 4 h60   cB      100   300
 5 h85   c0      100   500
 6 h85   c       400   500
 7 h85   cA      250   500
 8 h85   cB      150   500
 9 hm    c0       40   100
10 hm    c        60   100
11 hm    cA       40   100
12 hm    cB       20   100
user_83890030 
my_df %>% 
   bind_rows(my_df %>% filter(nm == "c") %>% 
   mutate(nm = "c0" ,  val = znt - val)) %>% 
   arrange(zn, nm)

gives 

zn    nm      val   znt
   <chr> <chr> <dbl> <dbl>
 1 h60   c       250   300
 2 h60   c0       50   300
 3 h60   cA      150   300
 4 h60   cB      100   300
 5 h85   c       400   500
 6 h85   c0      100   500
 7 h85   cA      250   500
 8 h85   cB      150   500
 9 hm    c        60   100
10 hm    c0       40   100
11 hm    cA       40   100
12 hm    cB       20   100
user_123310880

Another option is to go wide, calculate, then go long.

my_df %>%
  pivot_wider(names_from = "nm", values_from = "val") %>%
  mutate(c0 = znt - c) %>%
  pivot_longer(cols = -c(zn, znt), names_to = "nm", values_to = "val")
#> # A tibble: 12 x 4
#>    zn      znt nm      val
#>    <chr> <dbl> <chr> <dbl>
#>  1 hm      100 c        60
#>  2 hm      100 cA       40
#>  3 hm      100 cB       20
#>  4 hm      100 c0       40
#>  5 h60     300 c       250
#>  6 h60     300 cA      150
#>  7 h60     300 cB      100
#>  8 h60     300 c0       50
#>  9 h85     500 c       400
#> 10 h85     500 cA      250
#> 11 h85     500 cB      150
#> 12 h85     500 c0      100
user_97785130
Related