Create a data frame from another containing a hierarchical taxonomy based on a column

user_10957279 0
Viewed 123

I have a table containing a hierarchical structure of some categories:

for example

"cat" belongs to "domestic animals", and "domestic animals" belong to "animals"

below I show my data with segment.parent that tells the macro-category to which each entity belongs to. I want to create a new table where I only have the leaves of the hierarchy and a variable for the two macro categories it belongs to

segment.id     Name.                 segment.parent
   1            cat                       3
   2            dog                       3
   3            domestic animals          4
   4            animals                   NA
   5            cake                      7
   6            ice-cream                 7
   7            dessert                   8
   8            food                      NA
   9            main-course               8

what I want to obtain is the following

segment.id     Name                 segment.parent   Name.parent              Name.parent.parent
   1            cat                       3           domestic animals          animals
   2            dog                       3           domestic animals          animals
   5            cake                      7           dessert                   food
   6            ice-cream                 7           dessert                   food
dataframe r
3 Answers

The first thing you must is select the segments that are parents. Those that are not parents will be leaves.

parent.ids <- unique(df$segment.parent)

Now using the dplyr() library you can filter only those entities whose id's are not parent ids, then join two times to obtain the father categories.

library(dplyr)

df %>%
  subset(! segment.id %in% parent.ids) %>%
  left_join(df, by = c("segment.parent" = "segment.id"), suffix = c("", ".x")) %>%
  left_join(df, by = c("segment.parent.x" = "segment.id"), suffix = c("", ".x")) %>%
  select(segment.id, Name., segment.parent, Name.parent = Name..x, Name.parent.parent = Name..x.x) %>%
  print(row.names = F)

Output

 segment.id       Name. segment.parent      Name.parent Name.parent.parent
          1         cat              3 domestic animals            animals
          2         dog              3 domestic animals            animals
          5        cake              7          dessert               food
          6   ice-cream              7          dessert               food
          9 main-course              8             food               <NA>

I've take the liberty of using left_join(), which will retrieve rows even when there isn't a father category. That's why main-course appears in the output listing. If you want to bring only those items who have at least two level above, you can replace left_join() with inner_join() instead.

user_23328490

I'm not sure how big of a data set you are working with but my suggestion would be to have a second data set (i.e. data2) which holds the corresponding segment.parent values along with the other two variables. Then with dplyr you can join the data sets and filter the duplicates.

library(dplyr)
data3 <- inner_join(data1, data2, by = "segment.parent") %>%
  filter(duplicated(data3))

> data3
  segment.id     Name. segment.parent      Name.parent Name.parent.parent
1          1       cat              3 domestic animals            animals
2          2       dog              3 domestic animals            animals
3          5      cake              7           desert               food
4          6 ice-cream              7           desert               food

Your data:

data1 <- data.frame(segment.id = 1:9,
                    Name. = c("cat", "dog", "domestic animals", "animals", "cake", "ice-cream", "dessert", "food", "main-course"),
                    segment.parent = c(3, 3, 4, NA, 7, 7, 8, NA, 8))

data2  <- data.frame(segment.parent = c(3, 3, 7, 7),
                     Name.parent = c("domestic animals", "domestic animals", "desert", "desert"),
                     Name.parent.parent = c("animals", "animals", "food", "food"))
user_86762600

As others have mentioned, I'm not sure how big your dataset is. The below solution is a short-term solution. You may need to reshape your data slightly and look into pivoting. 

library(dplyr)

df <- data.frame(segment.id = seq(1, 9),
                 Name = c("cat", "dog",
                           "domestic animals", "animals",
                           "cake", "ice-cream",
                           "dessert", "food",
                           "main-course"),

                 segment.parent = c(3, 3, 4, NA,
                                    7, 7, 8, NA, 8),
                 stringsAsFactors = FALSE)


df %>%
  dplyr::mutate(Name.parent = case_when(
    segment.parent == 3 ~ "domestic animals",
    segment.parent == 7 ~ "dessert",
  )) %>% 
  dplyr::mutate(Name.parent.parent = case_when(
      Name.parent == "domestic animals" ~ "animals",
      Name.parent == "dessert" ~ "food"
  )) %>% 
  na.omit()
user_83411610
Related