我有一个扁平的层次结构，大约有10层深。有一把钥匙，但不幸的是，这把钥匙没有被遵守，所以不能用于我的目的。
我需要将这个平面表转换成一个广泛的层次结构，我唯一的关系就是行id和父id。
我的数据看起来像这样（简化）；
id name description code sub_level_name parent_id
1 Parent Company My Big Company MBC Company
2 Franchise1 My Franchise1 MF1 Franchise 1
3 Store1 My Store1 MS1 Store 2
4 Store2 My Store2 MS2 Store 2
5 Store1Owner My Store1Owner MSO1 Store Owner 3
6 Store2Owner My Store2Owner MSO2 Store Owner 4我希望结果是这样的；
company_name company_description company_code franchise_name franchise_description franchise_code store_name store_description store_code storeowner_name storeowner_description storeowner_code
Parent Company My Big Company MBC Franchise1 My Franchise1 MF1 Store1 My Store1 MS1 Store1Owner My Store1Owner MSO1
Parent Company My Big Company MBC Franchise1 My Franchise1 MF1 Store2 My Store2 MS2 Store2Owner My Store2Owner MSO2通常我会更广泛地使用pivot\u，并使用sub\u level\u name列，但这就是我所说的在更大范围内没有得到遵守（这个表有~7000行，可以深入10行）
我觉得我需要做的是将父id与id匹配，并根据列的子级名称连接列，以创建列类型“x.name，x.description，x.code”），并逐行执行此操作，直到它到达层次结构的顶层。
这方面的任何帮助都是非常好的—不必是r解决方案，sql解决方案也非常好。
编辑：dput源数据
structure(list(id = c(1, 2, 3, 4, 5, 6), name = c("Parent Company",
"Franchise1", "Store1", "Store2", "Store1Owner", "Store2Owner"
), description = c("My Big Company", "My Franchise1", "My Store1",
"My Store2", "My Store1Owner", "My Store2Owner"), code = c("MBC",
"MF1", "MS1", "MS2", "MSO1", "MSO2"), sub_level_name = c("Company",
"Franchise", "Store", "Store", "Store Owner", "Store Owner"),
parent_id = c(NA, 1, 2, 2, 3, 4)), class = c("spec_tbl_df",
"tbl_df", "tbl", "data.frame"), row.names = c(NA, -6L), spec = structure(list(
cols = list(id = structure(list(), class = c("collector_double",
"collector")), name = structure(list(), class = c("collector_character",
"collector")), description = structure(list(), class = c("collector_character",
"collector")), code = structure(list(), class = c("collector_character",
"collector")), sub_level_name = structure(list(), class = c("collector_character",
"collector")), parent_id = structure(list(), class = c("collector_double",
"collector"))), default = structure(list(), class = c("collector_guess",
"collector")), skip = 1), class = "col_spec"))
1条答案
按热度按时间w8f9ii691#
你可以尝试创建一个
id每列sub_level_name，删除不需要的列并以宽格式获取数据。