Python Pandas - using .loc to select with AND and OR on multiple columns

Question

I've a situation where I'm trying to select some scenarios from a dataframe in a single pass. The following code is what I'm using at the moment:

dfWater1 = left_merged.loc[left_merged.BVG_2M.isin(['34']) and left_merged.VHC_SC.isin(['6. Nil veg']) and left_merged.wetland.isin(['Estuarine wetlands (e.g. mangroves).', 'Lacustrine wetland (e.g. lake).']) | left_merged.RE.isin(['water', 'reef', 'ocean', 'estuary', 'canal'])].copy()

Alternatively, with some extra brackets to contain the AND and separate the OR:

dfWater1 = left_merged.loc[(left_merged.BVG_2M.isin(['34']) and left_merged.VHC_SC.isin(['6. Nil veg']) and left_merged.wetland.isin(['Estuarine wetlands (e.g. mangroves).', 'Lacustrine wetland (e.g. lake).'])) | (left_merged.RE.isin(['water', 'reef', 'ocean', 'estuary', 'canal']))].copy()

Basically, I'm asking for rows to be selected where:

   (
      Column BVG_2M = 34
         AND
      Column VHC_SC = '6. Nil veg'
         AND
      Column wetland is one of the following ['Estuarine wetlands (e.g. mangroves).', 'Lacustrine wetland (e.g. lake).']
   )
OR
   (
      Column RE is one of the following ['water', 'reef', 'ocean', 'estuary', 'canal']
   )

The dataset is pretty big, so I'd like to try and keep the select fast (hence using .loc and approaching this in a vectorised fashion), and try to avoid creating more data frames than necessary to preserve memory, if possible.

My real issue I think, is that I'm not sure how to structure the .loc statement, or even if I can do it like this.

Error Message

File "C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\pandas\core\generic.py", line 1479, in __nonzero__
    f"The truth value of a {type(self).__name__} is ambiguous. "
ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

David Erickson · Accepted Answer · 2020-10-23 01:24:16Z

2

You should use & instead of and as well as wrap parentheses around each condition. Formatting on new lines with everything lining up also helps prevent mistakes with parentheses:

dfWater1 = left_merged.loc[((left_merged.BVG_2M.isin(['34'])) &
                            (left_merged.VHC_SC.isin(['6. Nil veg'])) &
                            (left_merged.wetland.isin(['Estuarine wetlands (e.g. mangroves).', 'Lacustrine wetland (e.g. lake).']))) 
                          | (left_merged.RE.isin(['water', 'reef', 'ocean', 'estuary', 'canal']))].copy()

edited Oct 23, 2020 at 1:24

answered Oct 23, 2020 at 1:14

David Erickson

16.7k2 gold badges21 silver badges37 bronze badges

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

3 Comments

anakaine Over a year ago

Thanks David. Was the secret just the addition of extra brackets and changing from AND to &? This seems to have worked.

David Erickson Over a year ago

Yes, I think both; however, I am not 100%. I always use the extra brackets and & as I remember being punished by errors when first learning python when not doing those two things.

anakaine Over a year ago

Sage advice. Thanks.

Collectives™ on Stack Overflow

Python Pandas - using .loc to select with AND and OR on multiple columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related