CODEWITHSUNDEEP
python-3.xpandasdataframe
yellow_smile
yellow_smile

Reputation: 129

Find no of days gap for a specific ID when it has a flag X in other column

I want to calculate the no. of days gap for when the 'flag' column is equal to 'X' for same IDs.

The dataframe that I have:

ID      Date        flag
1       1-1-2020    X
1       10-1-2020   null
1       15-1-2020   X
2       1-2-2020    X
2       10-2-2020   X 
2       15-2-2020   X
3       15-2-2020   null

The dataframe I want:

ID      Date        flag     no_of_days
1       1-1-2020    X        14
1       10-1-2020   null     null
1       15-1-2020   X        null
2       1-2-2020    X        9
2       10-2-2020   X        8
2       18-2-2020   X        null
3       15-2-2020   null     null

Thanks in advance.

Upvotes: 1

Views: 57

Answers (1)

jezrael
jezrael

Reputation: 863166

First filter rows by X in boolean indexing and then subtract shifted column per groups by DataFrameGroupBy.shift and last convert timedeltas to days by Series.dt.days:

df['Date'] = pd.to_datetime(df['Date'], dayfirst=True)

df['new'] = df[df['flag'].eq('X')].groupby('ID')['Date'].shift(-1).sub(df['Date']).dt.days
print (df)
   ID       Date flag   new
0   1 2020-01-01    X  14.0
1   1 2020-01-10  NaN   NaN
2   1 2020-01-15    X   NaN
3   2 2020-02-01    X   9.0
4   2 2020-02-10    X   8.0
5   2 2020-02-18    X   NaN
6   3 2020-02-15  NaN   NaN

Upvotes: 1

Related Questions