Pandas if condition from multiple columns's strings

Question

I am trying to change the price of some of the items based on the country and the product string.

I have the table below.

country         product                 price
Us              blue good apple         9
US              red bad Apple           10
Us              blue bad apple          12
Canada          blue excel apple        8
Canada          yellow good Mango       15
Mexico          orange bad Orange       16
Costa           yellow med Orange       15
Brazil          red good Orange         19
Brazil          blue bad apple          9
Guatemala       purple meh Pear         32
Guatemala       green sweet Melon       4
Honduras        grade 2 orange          5

From this table, if the price of fruits that has strings "blue" and "apple" but not from "Canada" is less than 11 then I would like to set the price of those to 11. So the result of those conditions would be. (the price of two rows are changed)

country         product                 price
Us              blue good apple         11
US              red bad Apple           10
Us              blue bad apple          12
Canada          blue excel apple        8
Canada          yellow good Mango       15
Mexico          orange bad Orange       16
Costa           yellow med Orange       15
Brazil          red good Orange         19
Brazil          blue bad apple          11
Guatemala       purple meh Pear         32
Guatemala       green sweet Melon       4
Honduras        grade 2 orange          5

I have tried

df.loc[((df['product'].str.lower().str.contains('apple')) &
        (~df['country'].str.lower().str.contains('canada')) &
        (~df['product'].str.lower().str.contains('red'))), 'Price'] = 11

But I am not sure how to compare the price and change those specific products. I tried multiple things but all don't seem to work.

(changing and breaking up the product name into a different column is not an option for me)

Can you just add a condition & (df.Price < 11) to your example to select the prices that you want to set to 11? — Engineero
– Engineero, Commented Jul 8, 2019 at 17:44

user3483203 · Accepted Answer · 2019-07-08 17:45:28Z

4

Split out your conditions so it's clear what you are doing

c1 = df['country'].str.lower().ne('canada')
c2 = df['product'].str.contains('(?i)(?=.*blue)(?=.*apple)')
c3 = df['price'] < 11

df.assign(price=np.where(c1 & c2 & c3, 11, df['price']))

      country            product  price
0          Us    blue good apple     11
1          US      red bad Apple     10
2          Us     blue bad apple     12
3      Canada   blue excel apple      8
4      Canada  yellow good Mango     15
5      Mexico  orange bad Orange     16
6       Costa  yellow med Orange     15
7      Brazil    red good Orange     19
8      Brazil     blue bad apple     11
9   Guatemala    purple meh Pear     32
10  Guatemala  green sweet Melon      4
11   Honduras     grade 2 orange      5

If you want to modify in-place, use loc

df.loc[c1 & c2 & c3, 'price'] = 11

answered Jul 8, 2019 at 17:45

user3483203

51.3k10 gold badges72 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

piRSquared Over a year ago

I always get hung up on how to do an unordered and in regex. I'll have to try to remember this.

Collectives™ on Stack Overflow

Pandas if condition from multiple columns's strings

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related