Is it possible to lengthen a given column based another column

August 30, 2023

This is what I want to achieve: For each child, I would like to have the mother right below him/her.

library(tidyverse)

mydata <- tribble(
  ~id, ~child, ~mother,
  1, "John", "Jane",
  1, "George", "Jane",
  2, "Matthew", "Angela",
  3, "Gwel", "Macrine",
  3, "Tim", "Macrine",
  3, "Jim", "Macrine"
)

# Desired

newdata <- tribble(
  ~id, ~child, ~mother,
  1, "John", "Jane",
  1, "George", "Jane",
  1, "Jane", NA,
  2, "Matthew", "Angela",
  2, "Angela", NA,
  3, "Gwel", "Macrine",
  3, "Tim", "Macrine",
  3, "Jim", "Macrine",
  3, "Macrine", NA
)

>Solution :

You can use group_modify from the dplyr package to add_row per group.

library(dplyr)

mydata |> 
  group_by(id) |> 
  group_modify(~add_row(., child = unique(.$mother), mother = NA))

#> # A tibble: 9 × 3
#> # Groups:   id [3]
#>      id child   mother 
#>   <dbl> <chr>   <chr>  
#> 1     1 John    Jane   
#> 2     1 George  Jane   
#> 3     1 Jane    <NA>   
#> 4     2 Matthew Angela 
#> 5     2 Angela  <NA>   
#> 6     3 Gwel    Macrine
#> 7     3 Tim     Macrine
#> 8     3 Jim     Macrine
#> 9     3 Macrine <NA>