finding difference of a column row-by-row in R

Question

I have a subset of data as below:

structure(list(id = c(100, 101, 102, 103, 104, 105), 
           `family id` = c(1,1, 2, 2, 3, 3), 
           disease = c(1, 0, 0, 1, 1, 0), 
           val = c("3.1", "6.2", "2.45", "7.77", "4.56", "2.1")), 
      class = c("tbl_df", "tbl","data.frame"), row.names = c(NA, -6L))

I want to find the difference: value of sibling with disease(1) - value of sibling with no disease(0)?

the output should be as below:

Answer 1

Adding a helper id column and using tidyr::pivot_wider you could do:

library(dplyr)
library(tidyr)

df |>
  group_by(`family id`) |>
  mutate(id1 = row_number(), val = as.numeric(val)) |>
  ungroup() |>
  pivot_wider(names_from = id1, values_from = -c(id1, `family id`), names_sep = "") |>
  mutate(difference = ifelse(disease1 == 1, val1 - val2, val2 - val1))
#> # A tibble: 3 × 8
#>   `family id`   id1   id2 disease1 disease2  val1  val2 difference
#>         <dbl> <dbl> <dbl>    <dbl>    <dbl> <dbl> <dbl>      <dbl>
#> 1           1   100   101        1        0  3.1   6.2       -3.1 
#> 2           2   102   103        0        1  2.45  7.77       5.32
#> 3           3   104   105        1        0  4.56  2.1        2.46