CODEWITHSUNDEEP
pythonpandasgroup-by
Omido
Omido

Reputation: 71

Pandas: Filtering groupby data based on some conditions

I am trying to filter out a subset of data in the following code.

I want to filter those columns with FG='Y' if there is only one element in that group. Also, between those groups that have both combinations of 'N' and 'Y' in FG column, I will choose it if and only if the FG='Y' is submitted after 60 days of FG='N'.

from datetime import timedelta
import datetime as dt
from dateutil.parser import parse
import pandas as pd
import numpy as np
data={'Name':['A','A','A','B','B','B','C','D','D','D','E','E','E','F','G','G','G','H','H','H'],'FG':['Y','Y','Y','N','N','Y','Y','Y','Y','Y','Y','N','N','N','Y','N','N','Y','Y','N'],
    'Program': ['Eval','Eval','Eval','IB','Eval','IB','PO','PO','Info','IB','Info','Info','Info','Ted', 'Info','Ted','Ted','PO','PO','PO'],
    'Date':['2016/10/01','2017/10/01','2016/11/11','2017/10/01','2016/10/01','2017/10/02','2017/10/01','2017/10/01','2017/06/03',
            '2017/10/01','2017/10/21','2017/10/21','2017/08/01','2017/10/10', '2017/10/21','2017/08/01','2017/10/10', '2017/04/01','2017/01/30','2017/01/01']}
df=pd.DataFrame(data=data,columns=['Name','FG','Program', 'Date'])
df['Date']=pd.to_datetime(df['Date']).dt.date
df=df.sort_values('Date', ascending=True).drop_duplicates(subset=['Name', 'FG','Program'], keep='last')

df['check']=df.groupby(['Name', 'Program']).Date.transform('min')
df['check']=df['check']+timedelta(60)

mask=df.groupby(['Name','Program']).apply(lambda x : ((x.FG=='Y') & (x.Date>= x.check)) if len(x.Date)>1 else x.FG=='Y')).values

X=df[mask]

The expected output should be

Name  FG  Program  Date
A     Y   Eval     2017-10-01
C     Y   PO       2017-10-01
D     Y   Info     2017-06-03
D     Y   PO       2017-10-01
D     Y   IB       2017-10-01
G     Y   Info     2017-10-21
H     Y   PO       2017-04-01

It seems like my filter in the mask variable does not work. Also, any suggestion to compare the date for FG='N' to the FG='Y' would be greatly appreciated

Upvotes: 1

Views: 108

Answers (2)

andrew_reece
andrew_reece

Reputation: 21264

You can get your desired result using groupby and apply, you don't need to create df.check ahead of time:

def filterer(x):
    y = x.FG.eq('Y')
    n = x.FG.eq('N')
    if 'N' in x.FG.values:
        if x.loc[y, 'Date'].values > x.loc[n, 'Date'].values + timedelta(60):
            return x.loc[y]
    elif 'Y' in x.FG.values:
        return x

(df.groupby(['Name','Program'])
   .apply(filterer)
   .sort_values(["Name","Date"])
   .reset_index(drop=True)
)

Output:

  Name FG Program        Date
0    A  Y    Eval  2017-10-01
1    C  Y      PO  2017-10-01
2    D  Y    Info  2017-06-03
3    D  Y      IB  2017-10-01
4    D  Y      PO  2017-10-01
5    G  Y    Info  2017-10-21
6    H  Y      PO  2017-04-01

Upvotes: 2

BENY
BENY

Reputation: 323226

By using np.where

mask=df.groupby(['Name','Program']).\
       apply(lambda x : np.where(len(x.Date)>1,(x.FG=='Y') & (x.Date>= x.check),x.FG=='Y')).\
             apply(pd.Series).stack().values


df.sort_values(['Name','Program']).loc[mask]
Out[827]: 
   Name FG Program        Date       check
1     A  Y    Eval  2017-10-01  2017-11-30
6     C  Y      PO  2017-10-01  2017-11-30
9     D  Y      IB  2017-10-01  2017-11-30
8     D  Y    Info  2017-06-03  2017-08-02
7     D  Y      PO  2017-10-01  2017-11-30
14    G  Y    Info  2017-10-21  2017-12-20
17    H  Y      PO  2017-04-01  2017-03-02

Upvotes: 3

Related Questions