Find matches between columns and manipulate data based on match

Question

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

Answer 1

One possible way to solve this would be to create a dictionary of which components should be changed and then use replace. So in the example above, we want to create a dictionary {'B': 'A'} since all B should be replaced by A in the new Product column.

This can be done as follows:

components = df['Component'].unique()
d = df[df['Sub-component'].isin(components)].set_index('Sub-component')['Component'].to_dict()

and then we can create the new column:

df['Product'] = df['Component'].replace(d)

Resulting dataframe:

    Component   Sub-component   Product
0   A           a               A
1   A           B               A
2   A           c               A
3   B           d               A
4   B           e               A
5   B           n               A
6   B           a               A
7   B           v               A
8   B           c               A
9   B           i               A
10  C           g               C
11  C           s               C
12  C           g               C
13  C           a               C
14  C           c               C
15  C           i               C
16  C           q               C
17  C           g               C
18  C           v               C
19  C           b               C
20  C           l               C