CODEWITHSUNDEEP
python-3.xpandasdataframe
ah bon
ah bon

Reputation: 10011

Remove groups when specific date not contained in date column in Pandas

Given a dataframe as follows:

  city district      date  price
0   bj       cy  2019/3/1    NaN
1   bj       cy  2019/4/1    6.0
2   sh       hp  2019/2/1    4.0
3   sh       hp  2019/3/1    4.0
4   bj       hd  2019/3/1    7.0
5   bj       hd  2019/4/1    NaN

How could I remove groups of city and date, if they didn't have entry of 2019/4/1.

At this case, groups of sh and hp should be removed, since it only has data for 2019/2/1 and 2019/3/1.

My desired output will like this:

  city district      date  price
0   bj       cy  2019/3/1    NaN
1   bj       cy  2019/4/1    6.0
2   bj       hd  2019/3/1    7.0
3   bj       hd  2019/4/1    NaN

Sincere thanks for your kind help.

Upvotes: 1

Views: 72

Answers (1)

jezrael
jezrael

Reputation: 862481

Solution with DataFrameGroupBy.filter:

df['date'] = pd.to_datetime(df['date'])

f = lambda x: x['date'].eq('2019-04-01').any()
df = df.groupby(['city','district']).filter(f)
print (df)
  city district       date  price
0   bj       cy 2019-03-01    NaN
1   bj       cy 2019-04-01    6.0
4   bj       hd 2019-03-01    7.0
5   bj       hd 2019-04-01    NaN

Faster solution with GroupBy.transform and GroupBy.any:

df = (df[df.assign(t = df['date'].eq('2019-04-01'))
           .groupby(['city','district'])['t'].transform('any')])
print (df)
  city district       date  price
0   bj       cy 2019-03-01    NaN
1   bj       cy 2019-04-01    6.0
4   bj       hd 2019-03-01    7.0
5   bj       hd 2019-04-01    NaN

Upvotes: 1

Related Questions