Check if a value exist in a column and change in another Pandas based on different conditions: Pandas

I have the following information.

Let’s say I have the following list.

my_list = [2,3,4,5]

My dataframe is as follows:

df

Col1      Score  Value
[1,3,6]    0      Hot
[7]        10     Mild
[10,11,2]  50     Cool
[5,9]      0      Cool
[2,5,6]    100    Mild

I would like to check if one of the value from the list my_list exist in the column Col1. If it exist, change the value in Value column to Hot in the corresponding row by checking if Score > 0 unless keep the value in Value column as it is.

I would like to see something like below.

Col1      Score  Value
[1,3,6]    0      Hot
[7]        10     Mild
[10,11,2]  50     Hot
[5,9]      0      Cool
[2,5,6]    100    Hot

I am just looking for a simple script that can iterate and check in every row and change a value in another column the corresponding row.

Can any one help on this?

>Solution :

First explode Col1 to check if the values are in my_list, then groupby+any to get a Series of booleans (True if at least one item is in the list).

Then use this and a condition on Score to mask the Value with "Hot" if the two conditions are met:

match = df['Col1'].explode().isin(my_list).groupby(level=0).any()
df['Value'] = df['Value'].mask(match & df['Score'].gt(0), 'Hot')

output:

          Col1  Score Value
0    [1, 3, 6]      0   Hot
1          [7]     10  Mild
2  [10, 11, 2]     50   Hot
3       [5, 9]      0  Cool
4    [2, 5, 6]    100   Hot