Combining sub-sub data with parent data in Excel or R?

user_13376090 0
Viewed 73

I'm incredibly unsure what to call this problem, I feel like there might be a compsci term for it but I'm not familiar with it.

As a trivial example, let's say I have a simple example of parent company and subcompanies identified by unique numbers. What I want, is if Company A owns Company B, and Company B owns Company C, I want my data to reflect that Company A owns Company C. Here's a trivial example

What I would like to do is transform this data:

 Parent Sub 
 133    922 
 199    633 
 210    677 
 633    792

To This Data where it takes sub-sub company 792 and assigns it to company 199 as well.

 Parent Sub 
 133    922 
 199    633 
 210    677 
 199    792

I feel like I could brute force this with VBA where it just does a search for every subcompany to see if it has a subcompany in turn repetitively, but I'm wondering if there's a smarter pre-built way of doing it already in excel somehow.

Also willing to transfer the data to R and try something there if if there's an easier solution in R.

r excel
2 Answers

That can be done with a join (or merge), to add the sub-sub-company in a third column, then you just need to take the rows and append them to the original data frame.

df <- read.table(text = "Parent Sub 
133    922 
199    633 
210    677 
633    792", header=TRUE)

left_join(df,df,
          by=c("Sub"="Parent")) %>%
  select(Parent, Sub = Sub.y) %>%
  filter(!is.na(Sub)) %>%
  bind_rows(df)

#  Parent Sub
#1    199 792
#2    133 922
#3    199 633
#4    210 677
#5    633 792

EDIT: I hadn't noticed that in your example you actually replace the existing row. That's not a problem, we just need to look for Parents that are also Subs, instead of looking for Subs that are also Parents:

left_join(df,df,
          by=c("Parent"="Sub")) %>%
  mutate(Parent = if_else(is.na(Parent.y), Parent, Parent.y)) %>%
  select(-Parent.y)

#  Parent Sub
#1    133 922
#2    199 633
#3    210 677
#4    199 792

EDIT 2: An Excel equivalent to this second approach could be that one. Suppose you put your data in A2:B5 (the headers in A1:A2). Then, you can enter this in C2:

=INDIRECT("A"&(1+MATCH(A2,B$2:B$5,0)))

So now column C will contain the hyper-Parent company if the Parent company was in the Sub column.

Excel snapshot

Then you just need a step to replace column A when column C is non-NA (something like =IFNA(C2,A2))

For method 1, I don't know how to do that in Excel, since we need to add rows to an existing data table (or bind two tables). If we're willing to do that manually, then again put your data in A2:B5, and in D2 use this:

=XLOOKUP(A2,B$2:B$5,A$2:A$5)

In E2 use that:

=IF(D2="#N/A","",B2)

Excel snapshot for adding rows

So here the logic was that we find Parents that are also Subs, and we can just get their sub-Sub. In Excel, you would need to find a way to get rid of the #N/A and assemble the data in A:B with the one in D:E, not sure if it's worth it.

user_39383600

Okay, this is ludicrously complicated, but I think it works. There is probably a smarter way to extract hierarchical data using R's igraph package, but hopefully somebody will be along to point out what I can simplify.

library(igraph)

dat <- read.table(text="Parent Sub 
 133    922 
 199    633 
 210    677 
 633    792
 211    133", header=TRUE)

# if <- represents ownership, we have:
# 211 <- 133 <- 922
# 199 <- 633 <- 792
# 210 <- 677

Create a graph object that stores the relationships between each Parent and Sub row. See plot(g) to get a better idea of what this outputs.

g     <- graph_from_data_frame(dat)

components, finds which values belong in groups, or 'connected components' in the igraph lingo.

cmp   <- components(g)

degree is used to identify how many out-going and in-coming connections relate to each value. In this case, I'm particularly interested in those root values which don't have a parent.

dg    <- degree(g, mode="in")
roots <- names(cmp$membership) %in% names(dg)[dg == 0]

And finally, this is just a bunch of matching up so that each Sub value is related to the root value in each group.

dat$Parent <- names(cmp$membership[roots])[
 match(
   cmp$membership[match(dat$Sub, names(cmp$membership))],
   cmp$membership[roots]
  )
]
dat

#  Parent Sub
#1    211 922
#2    199 633
#3    210 677
#4    199 792
#5    211 133

Results look okay.

user_4968030
Related