CODEWITHSUNDEEP
pythonpython-3.xpandasdataframepandas-groupby
The Great
The Great

Reputation: 7733

Elegant way to drop records in pandas based on size/count of a record

This isn't a duplicate. I am not trying drop rows based on Index

I have a dataframe like as shown below

df = pd.DataFrame({
'subject_id':[1,1,1,1,1,1,1,2,2,2,2,2],
'time_1' :['2173-04-03 12:35:00','2173-04-03 12:50:00','2173-04-05 
12:59:00','2173-05-04 13:14:00','2173-05-05 13:37:00','2173-07-06 
13:39:00','2173-07-08 11:30:00','2173-04-08 16:00:00','2173-04-09 
22:00:00','2173-04-11 04:00:00','2173- 04-13 04:30:00','2173-04-14 
08:00:00'],
'val' :[5,2,3,1,1,6,5,5,8,3,4,6]})
df['time_1'] = pd.to_datetime(df['time_1'])
df['day'] = df['time_1'].dt.day

enter image description here

I would like to drop records based on subject_id if their count is <=5.

This is what I tried

df1 = df.groupby(['subject_id']).size().reset_index(name='counter')
df1[df1['counter']>5] # this gives the valid subject_id = 1 has count more than 5)

Now using this subject_id, I have to get the base dataframe rows for that subject_id

There might be an elegant way to do this.

I would like to get the output as shown below. I would like have my base dataframe rows

enter image description here

Upvotes: 1

Views: 71

Answers (1)

ansev
ansev

Reputation: 30930

Use:

df[df.groupby('subject_id')['subject_id'].transform('size')>5]

Output:

   subject_id              time_1  val  day
0           1 2173-04-03 12:35:00    5    3
1           1 2173-04-03 12:50:00    2    3
2           1 2173-04-05 12:59:00    3    5
3           1 2173-05-04 13:14:00    1    4
4           1 2173-05-05 13:37:00    1    5
5           1 2173-07-06 13:39:00    6    6
6           1 2173-07-08 11:30:00    5    8

Upvotes: 3

Related Questions