Looking for a way to transform a parent-child table into a several level table

[fjlmarco]

Hi, everyone,

I am looking for a way to transform a table like the "parent_child" table into a table whose columns correspond to the different levels found in the previous table. Basically, I would like to turn 'parent_child' into 'wanted_table'.

parent_child <- data.frame(
  child = c("Paris", "Lyon","France", "Tokyo", "Japan", "NewYork", "USA", "Casablanca", "Morocco", "Beijing", "China"),
  parent = c("France", "France", "Europe", "Japan", "Asia", "USA", "America", "Morocco", "Africa", "China", "Asia")
)
wanted_table <- data.frame(
  level_1 = c("Europe", "Europe", "Asia", "Asia", "America", "Africa"),
  level_2 = c("France", "France", "Japan", "China", "USA", "Morocco"),
  level_3 = c("Paris", "Lyon", "Tokyo", "Beijing", "NewYork", "Casablanca")
)

That is:

Turn this:

Into this:

Any help would be highly appreciated.

Many thanks.

[DavoWW]

Hi @fjlmarco,
This works:

parent_child <- data.frame(
  child = c("Paris", "Lyon","France", "Tokyo", "Japan", "NewYork", "USA", "Casablanca", "Morocco", "Beijing", "China"),
  parent = c("France", "France", "Europe", "Japan", "Asia", "USA", "America", "Morocco", "Africa", "China", "Asia")
)

suppressPackageStartupMessages(library(tidyverse))

full_join(parent_child, parent_child, by=c("parent" = "child")) %>% 
  filter(!is.na(parent.y)) %>% 
  filter(!is.na(child)) %>% 
  select(parent.y, parent, child) %>% 
  rename(level_1=parent.y, level_2=parent, level_3=child) %>% 
  arrange(desc(level_1))
#>   level_1 level_2    level_3
#> 1  Europe  France      Paris
#> 2  Europe  France       Lyon
#> 3    Asia   Japan      Tokyo
#> 4    Asia   China    Beijing
#> 5 America     USA    NewYork
#> 6  Africa Morocco Casablanca

^{Created on 2021-10-16 by the reprex package (v2.0.1)}

[fjlmarco]

Yes, it works perfectly and the solution is elegant.

Thanks @DavoWW!