pandas: filter group by multiple conditions?

Question

I have a data frame that looks like this:

df = pd.DataFrame([
  {'id': 123, 'date': '2016-01-01', 'is_local': True },
  {'id': 123, 'date': '2017-01-01', 'is_local': False },
  {'id': 124, 'date': '2016-01-01', 'is_local': True },
  {'id': 124, 'date': '2017-01-01', 'is_local': True }
])
df.date = df.date.astype('datetime64[ns]')

I want to get a list of all the IDs for which is_local was True at the start of 2016, but False at the start of 2017. I've started by grouping by ID:

gp = df.groupby('id')

Then I've tried this just to filter by the second of these conditions (as a way of getting started), but it's returning all the groups:

gp.apply(lambda x: ~x.is_local & (x.date > '2016-12-31'))

How can I filter in the way I need?

score 8 · Accepted Answer · answered May 05 '17 at 09:04

8

d1 = df.set_index(['id', 'date']).is_local.unstack()
d1.index[d1['2016-01-01'] & ~d1['2017-01-01']].tolist()

[123]

answered May 05 '17 at 09:04

piRSquared

285,575
57
475
624

score 3 · Answer 2 · answered May 05 '17 at 09:10

3

Another way of doing this is through pivoting:

In [24]: ids_by_dates = df.pivot(index='id', columns='date',values='is_local')

In [25]: ids_by_dates['2016-01-01'] & ~ids_by_dates['2017-01-01']
Out[25]: 
id
123     True
124    False

answered May 05 '17 at 09:10

Ami Tavory

74,578
11
141
185

Raja Sattiraju · Answer 3 · 2017-05-05T09:45:58.027

You can try using the datetime module from datetime library and pass multiple conditions for the dataframe

from datetime import datetime
df = pd.DataFrame([
  {'id': 123, 'date': '2016-01-01', 'is_local': True },
  {'id': 123, 'date': '2017-01-01', 'is_local': False },
  {'id': 124, 'date': '2016-01-01', 'is_local': True },
  {'id': 124, 'date': '2017-01-01', 'is_local': True }
])
df.date = df.date.astype('datetime64[ns]')

Use multiple conditions for slicing out the required dataframe

a = df[(df.is_local==True) & (df.date<datetime(2016,12,31) & (df.date>datetime(2015,12,31))]
b = df[(df.is_local==False) & (df.date<datetime(2017,12,31)) & (df.date>datetime(2016,12,31))]

Use pandas concatenate later

final_df = pd.concat((a,b))

will output you rows 1 and 2

    date        id  is_local
2   2016-01-01  124 True
1   2017-01-01  123 False

In single line as follows

final_df = pd.concat((df[(df.is_local==True) & (df.date<datetime(2016,12,31) & (df.date>datetime(2015,12,31))], df[(df.is_local==False) & (df.date<datetime(2017,12,31)) & (df.date>datetime(2016,12,31))]))

Thanks - how would I use this to get all rows for which `has_local` is True at start of 2016 and False at 2017? — Richard, May 05 '17 at 09:22
I could only think of a dirty solution where u add multiple conditions and concat them togeother.. Edited my answer accordingly.. — Raja Sattiraju, May 05 '17 at 09:35
I also edited my answer with another condition to confine the year to 2016 and 2017 respectively — Raja Sattiraju, May 05 '17 at 09:47

pandas: filter group by multiple conditions?

3 Answers3