Timestamp analysis Pandas

user_14576516 0
Viewed 84

I have a dataset which looks something like this:

ID      DATE       TAG
S3800   1999-07-02 D
S1190   1999-07-02 C
S3131   1999-07-02 C
S3131   1999-07-04 C
S3131   1999-07-05 D

I am trying to calculate the minimum and maximum time gap between records for each ID(in days). For example:

ID    MIN_TIME_GAP MAX_TIME_GAP 
S3131 1            3

The column DATE is in format datetine64[ns]. How do I achieve this in Pandas?

pandas time-series dataframe python
1 Answers

Try:

# if they aren't sorted already:
df = df.sort_values(by="DATE")

x = df.groupby("ID").agg(
    MIN_TIME_GAP=("DATE", lambda x: np.min(x.diff())),
    MAX_TIME_GAP=("DATE", lambda x: x.max() - x.min()),
)
print(x.dropna())

Prints:

      MIN_TIME_GAP MAX_TIME_GAP
ID                             
S3131       1 days       3 days

EDIT: To convert Timedeltas to days:

# convert to days:
x["MIN_TIME_GAP"] = x["MIN_TIME_GAP"].dt.days
x["MAX_TIME_GAP"] = x["MAX_TIME_GAP"].dt.days
print(x)

Prints:

       MIN_TIME_GAP  MAX_TIME_GAP
ID                               
S3131             1             3
user_100359850
Related