CODEWITHSUNDEEP
pythonpandasdataframe
Yeanlinggg
Yeanlinggg

Reputation: 11

Return the column that contains value in another column in Python Pandas

I have the following dataframe in Pandas. There're 1 column shows 'Name', 1 column shows "Status", and uncertain amount of columns show "Comment".

| Name     | Status   | Comment1   | Comment2 | Comment... | CommentN |
| -------- | -------- | --------   | -------- | --------   | -------- |
| Abby     | Valid    | Abby.aom   | Cindy.bom|    ...     | Lulu.com |
| Bob      | Valid    | Ed.com     | Dan.bom  |    ...     | Bob.aom  |
| Chris    | Blocked  | Herdon.aom | Chris.bom|    ...     | Judy.com |

The DataFrame created from dictionary is:

# dictionary with list object in values
comments = {
    'Name' : ['Abby', 'Bob', 'Chris'],
    'Status' : ['Valid', 'Valid', 'Blocked'],
    'Comment1' : ['Abby.aom', 'Ed.com', 'Herdon.aom'],
    'Comment2' : ['Cindy.bom', 'Dan.bom', 'Chris.bom'],
    ...
    'CommentN' : ['Lulu.com', 'Bob.aom', 'Judy.com'] 
}
  
# creating a Dataframe object 
df = pd.DataFrame(comments)

I would like to return the value in 'Comment' column that contains 'Name'. For exaple, if 'Name' is in 'Comment1', then I would like to return the value in 'Comment1', if 'Name' is in 'Comment2', then I would like to return the value in 'Comment2'. The result should be something like this:

| Name     | Result   |
| -------- | -------- |
| Abby     | Abby.aom |
| Bob      | Bob.aom  |
| Chris    | Chris.bom|

Is there any way to do that? Any help would be much appreciated!

Upvotes: 1

Views: 109

Answers (1)

ziying35
ziying35

Reputation: 1305

try this:

tmp = (df.apply(
    lambda x: x[1:].values[x[1:].str.contains(x[0])],
    axis=1)
    .apply(pd.Series))
out = df[['Name']].join(tmp.set_axis(['Result'], axis=1))
print(out)
>>>
    Name    Result
0   Abby    Abby.aom
1   Bob     Bob.aom
2   Chris   Chris.bom

Upvotes: 1

Related Questions