I have a pandas dataframe that contains two columns, components, and sub-components (first table below). I would like to add a product column as shown below (second table). The problem is, some (not all) components are actually sub-components (B is also contained in the sub-component column in this simplified example). I would like some help/hints to write code in python to create this product column.
I envisage something like this but am finding it difficult to get started:
Iterate through Component column look for matches in the sub-component column, if no match is found write Component name to row in Product column - where a match is found in the sub-component column, look for the name of Component corresponding to sub-component and write it to the next row in the column Product. The line containing the "component in the sub-component column" can then be deleted.
Component Sub-component A a A **B** A c B d B e B n B a B v B c B i C g C s C g C a C c C i C q C g C v C b C l Product Component Sub-component A A a *A A **B*** A A c A B d A B e A B n A B a A B v A B c A B i C C g C C s C C g C C a C C c C C i C C q C C g C C v C C b C C l