Pandas: How select rows by multiple criteria (if not nan and equals to specific value)

user_5661316 0
Viewed 1366

I have a DataFrame

index Street House Building
1 ABC 20 a
2 ABC 20 b
3 ABC 21 NaN
4 BCD 2 1

Need to create a multiple selection from this DataFrame:

  • If Street == str_filter;
  • If House == house_filter;
  • If Building == build_filter AND If Building is not NULL;

I've already tried df[(df['Street'] == str_filter) & (df['House'] == house_filter) & ((df['Building'] == build_filter) & (pd.notnull(df['Building'])))

But doesn't lead to a result I particularly want to see. I have to check if the Building value is not NaN and if it's true select the row with the certain Building num. However, I also want to select the row if it has NaN value for the Building but also meets other criteria.

Another idea was to create lists for the set of filter values and the set of this values meeting pd.notnull criteria:

filter_values = [str_filter, house_filter, build_filter] 
notnull_values = [pd.notnull(entry) for entry in filter_values]

This one doesn't meet the performance criteria, because I have extremely huge DataFrame and creating additional lists with additional filtering will lead to the out-performance. Possible solution may lay in the df.loc function, but I don't know how to realise it.

To summarise, the problem is the following: How to create multiple selection in pandas with conditions for NaN values?

UPD: It seems that the function I have to use is df[... & (df['Building'] == 'a' if pd.notnull(df['Building']))] using an analogy with lambda apply trick

pandas nan selection python
2 Answers

Since you said in a comment that you also had issues with duplicates, I've added an option to remove these.

I have recreated your dataset with this, and added a duplicate.

df = pd.DataFrame({
    "Street" : ["ABC", "ABC", "ABC", "BCD", "ABC"],
    "House" : [20, 20, 21, 2, 21],
    "Building" : ['a', 'b', np.NaN, 1, np.NaN]
})

To remove duplicates from your dataset you can simply run this block:

df = df.drop_duplicates()

I am assuming that your filter might be a whitelist of strings or numbers that you want to include in your final dataset. Therefore I took the liberty of defining a filter as this:

street_filter = ["ABC", "BCD"]

To clean the dataset you could then use this method:

def get_mask(df, street_filter, house_filter, building_filter):
  not_na_mask = df['Building'].notna()
  # hopefully smaller df to query
  df = df[not_na_mask]

  street_mask = df['Street'].apply(lambda x: x in street_filter)
  df = df[street_mask]

  house_mask = df['House'].apply(lambda x: x in house_filter)
  df = df[house_mask]

  building_mask = df['Building'].apply(lambda x: x in building_filter)
  return df[building_mask]

Example

street_filter = ["ABC", "BCD"]
house_filter = [20, 21]
building_filter = ["a"]
get_mask(df, street_filter, house_filter, building_filter)

    Street  House   Building
 0  ABC     20      a
user_94795640

A rather simple way to do it would be this:

if df[df['Building'].isnull()].empty:
    df[df['Street'] == str_filter][df['House'] == house_filter][df['Building'] == build_filter]
else:
    df[df['Street'] == str_filter][df['House'] == house_filter][df['Building'].isnull()]

I'm not sure if this would satisfy your performance requirements?

user_106221900
Related