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

I have the following information.

Let's say I have the following list.

my_list = [2,3,4,5]

My dataframe is as follows:

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?

CodePudding user response：

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

CodePudding user response：

Here is a way using map() and where():

s1 = df['col1'].map(lambda x: any([i in my_list for i in x]))
s2 = df['Score'].eq(0)
df.assign(Value = df['Value'].where((s1 | s2),df['Value']))

Output:

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

CodePudding user response：

You can do it with apply

df=["Value"] =(df.apply(lambda x: "Hot" if  len(set(x["Col1"]).intersection(my_list)) >0 
                                            and x["Score"] >0 else x["Value"],axis=1) )

output:

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