Ggtcf

the following dataframe which contains data for the same company (column ID) at different dates (column date). I would like to delete the observations for which there are less than 3 days.

The starting dataset is

df = pd.DataFrame("ID":"0":1,"1":1,"2":1,"3":1,"4":4,"5":4,"6":4,"7":2,"8":2,"9":3,"10":3,
 "date":"0":1421020800000,"1":1421193600000,"2":1422489600000,"3":1423353600000,"4":1421020800000,"5":1421107200000,"6":1421193600000,"7":1421020800000,"8":1421107200000,"9":1421452800000,"10":1421539200000,
 "variable":"0":28,"1":62,"2":60,"3":72,"4":28,"5":61,"6":62,"7":23,"8":70,"9":32,"10":55)
df.date = pd.to_datetime(df.date, unit='ms')
df.sort_values(by=["ID", "date"],inplace=True)

In the above dataframe, only company with ID = 4 satisfies the requirement and I would like to delete the others.

I wrote the following code but it has an obvious problem and I can't figure out how to fix it:

df['delete'] = 0
for name, group in df.groupby(by = "ID"):
 if group.shape[0] < 3:
 df.loc[df['ID']==name,'delete'] = 1
df = df.loc[df['delete'] == 0,:]

The above code keeps both companies with ID=1 and ID=4; ID=1 should be cancelled because it contains 4 datapoints but at maximum two of them are on consecutive days (while I want to impose a minimum of 3).

Any help would be much appreciated. Thank you

IIUC using diff + cumsum with date column create the group key New, then we just using groupby + filter the unwanted groups

df['New']=df.groupby('ID').date.apply(lambda x : x.diff().dt.days.ne(1).cumsum())
yourdf=df.groupby(['ID','New']).filter(lambda x : len(x)>=3)
yourdf
Out[809]: 
 ID date variable New
4 4 2015-01-12 28 1
5 4 2015-01-13 61 1
6 4 2015-01-14 62 1

I think you could replace the "group.shape[0]" applying a moving window of 3 days and count items.

df = pd.DataFrame("ID":"0":1,"1":1,"2":1,"3":1,"4":4,"5":4,"6":4,"7":2,"8":2,"9":3,"10":3,
 "date":"0":1421020800000,"1":1421193600000,"2":1422489600000,"3":1423353600000,"4":1421020800000,"5":1421107200000,"6":1421193600000,"7":1421020800000,"8":1421107200000,"9":1421452800000,"10":1421539200000,
 "variable":"0":28,"1":62,"2":60,"3":72,"4":28,"5":61,"6":62,"7":23,"8":70,"9":32,"10":55)
df.date = pd.to_datetime(df.date, unit='ms')
df.sort_values(by=["ID", "date"],inplace=True)

df['delete'] = 0
for name, group in df.groupby(by = "ID"):
 group.set_index('date',inplace=True)

 if group.rolling(window='3D',min_periods=0).count()['delete'].max() < 3:
 df.loc[df['ID']==name,'delete'] = 1
df = df.loc[df['delete'] == 0,:]

df['delete'] = 0
for name, group in df.groupby(by = "ID"):
 if group.shape[0] != 3:
 df.loc[df['ID']==name,'delete'] = 1
df = df.loc[df['delete'] == 0,:]

you may set wrong in if group.shape[0] != 3