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

1 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.