CODEWITHSUNDEEP
pythonpandasdataframe
The Dodo
The Dodo

Reputation: 719

Keeping only the rows that satisfies a condition with respect to an another column

So right now I have a Pandas DF like this:

Name     Year      Label

Jeff     2018        0
Jeff     2019        1
Matt     2018        0
John     2018        0
Mary     2018        1
Mary     2019        1

I want to keep all the rows for each unique name that has both Years 2018 and 2019.

The result should look something like this:

Name     Year      Label

Jeff     2018        0
Jeff     2019        1
Mary     2018        1
Mary     2019        1

Matt and John were removed because they didn't have both 2018 AND 2019.

Any ideas would be appreciated!

Upvotes: 1

Views: 56

Answers (3)

Ben.T
Ben.T

Reputation: 29635

You can do an inner merge on 'Name', once selecting both years independently in df, to get the 'Name' that have both years, then use isin:

df.loc[df.Name.isin(df[df.Year == 2018].merge(df[df.Year == 2019],
                                              on='Name',how='inner').Name)]
   Name  Year  Label
0  Jeff  2018      0
1  Jeff  2019      1
4  Mary  2018      1
5  Mary  2019      1

Upvotes: 1

BENY
BENY

Reputation: 323226

Using crosstab select all the name with two year , then using isin 

s=pd.crosstab(df.Name,df.Year)[[2018,2019]].eq(1).sum(1)==2
df.loc[df.Name.isin(s.index[s])]
Out[463]: 
   Name  Year
0  Jeff  2018
1  Jeff  2019
4  Mary  2018
5  Mary  2019

Upvotes: 3

cs95
cs95

Reputation: 402423

Using groupby + transform:

m1 = df.Year.eq(2018)   
m2 = df.Year.eq(2019)

df[m1.groupby(df.Name).transform('any') & m2.groupby(df.Name).transform('any')]

  Name  Year
0  Jeff  2018
1  Jeff  2019
4  Mary  2018
5  Mary  2019

Generalising:

years = [2018, 2019]
M = [df.Year.eq(year) for year in years]
df[np.logical_and.reduce([m.groupby(df.Name).transform('any') for m in M])]

   Name  Year
0  Jeff  2018
1  Jeff  2019
4  Mary  2018
5  Mary  2019

Upvotes: 2

Related Questions