Timm S. Timm S. - 12 days ago 6
R Question

r - hierarchical data frame from child/parent relations

I have a child - parent data.frame that I want to transform to a complete hierarchical list with all levels and a level number. The example data below goes to three levels, but it could be more. What function can I use to transform the data?

Source:

data.frame(name = c("land", "water", "air", "car", "bicycle", "boat", "balloon",
"airplane", "helicopter", "Ford", "BMW", "Airbus"), parent = c(NA, NA, NA,
"land", "land", "water", "air", "air", "air", "car", "car", "airplane"))

name parent
1 land <NA>
2 water <NA>
3 air <NA>
4 car land
5 bicycle land
6 boat water
7 balloon air
8 airplane air
9 helicopter air
10 Ford car
11 BMW car
12 Airbus airplane


Destination:

data.frame(level1 = c("land", "water", "air", "land", "land", "water", "air",
"air", "air", "land", "land", "air"), level2 = c(NA, NA, NA, "car", "bicylcle",
"boat", "balloon", "airplane", "helicopter", "car", "car", "airplane"),
level3 = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, "Ford", "BMW", "Airbus"),
level_number = c(1, 1, 1, 2, 2, 2, 2, 2, 2, 3, 3, 3))

level1 level2 level3 level_number
1 land <NA> <NA> 1
2 water <NA> <NA> 1
3 air <NA> <NA> 1
4 land car <NA> 2
5 land bicylcle <NA> 2
6 water boat <NA> 2
7 air balloon <NA> 2
8 air airplane <NA> 2
9 air helicopter <NA> 2
10 land car Ford 3
11 land car BMW 3
12 air airplane Airbus 3

Answer

Usind data.table you can do the following:

require(data.table)
l <- list() # initialize empty list
setDT(dat) 
setkey(dat, parent) # setting up the data as keyed data.table
current_lvl <- dat[is.na(parent), .(level_number = 1), keyby=.(level1 = name)]

By not current_lvl looks as follows (keyed by level1)

   level1 level_number
1:    air            1
2:   land            1
3:  water            1

The trick is now to join dat and current_lvl and modify the result appropriately:

  current_lvl <- current_lvl[dat][ # Join the data.tables
!is.na(level_number)][ #exclude non-child-rows
  ,level_number := level_number + 1] # increment level_number
setnames(current_lvl, "name", paste0("level",ind+1)) # rename column
setkeyv(current_lvl, paste0("level",ind+1)) # set key

Which gives you (keyed by level2)

   level1 level_number     level2
1:    air            2   airplane
2:    air            2    balloon
3:   land            2    bicycle
4:  water            2       boat
5:   land            2        car
6:    air            2 helicopter

Put this to work in a while-loop as follows:

while(nrow(current_lvl) > 0){
  ind <- length(l) + 1
  l[[ind]] <- current_lvl
  current_lvl <- current_lvl[dat][!is.na(level_number)][,level_number := level_number + 1]
  if(nrow(current_lvl) == 0L){
    break
  }
  setnames(current_lvl, "name", paste0("level",ind+1))
  setkeyv(current_lvl, paste0("level",ind+1))
}

You can have a look at l to see the outcome. Combining this via rbindlist gives you what you desire

res <- rbindlist(l, fill=TRUE)
setcolorder(res, sort(names(res)))
res

what results in

> res
    level_number level1     level2 level3
 1:            1    air         NA     NA
 2:            1   land         NA     NA
 3:            1  water         NA     NA
 4:            2    air   airplane     NA
 5:            2    air    balloon     NA
 6:            2   land    bicycle     NA
 7:            2  water       boat     NA
 8:            2   land        car     NA
 9:            2    air helicopter     NA
10:            3    air   airplane Airbus
11:            3   land        car    BMW
12:            3   land        car   Ford
Comments