[Python] Data transformation on pandas dataframe to connect related rows based on shared values

I have a table of company data that links subsidiary to parent companies as shown in the left hand side table of the screenshot. I need to transform the data into the table on the right hand side of the screenshot. This requires tracing through the two columns of the table and making the link between individual rows.

So far the only thing I have tried is to join the table against itself recursively.. but I am thinking that some kind of tree structure here would make more sense? I.e. creating a branch of all connected companies with the 'ultimate parent company' as the trunk?

These concepts are new to me so appreciate any input

Reproducible input:

df = pd.DataFrame({'Subsidiary Company': ['Company B', 'Company C', 'Company D', 'Company 2', 'Company 3'],
'Parent Company': ['Company A', 'Company B', 'Company C', 'Company 1', 'Company 2']})

Continue reading...