How to calculate the average periode duration of date series with panda?

user_13940870 0
Viewed 52

I would like to calculate the mean periode duration for the occurrence of different events.

I got data where every event has an id and is tracked in a single line identified by its id. every time an event occurs the date of occurrence is saved.

df_starting_point = pd.DataFrame(
                      [{'id': 3, '0': pd.to_datetime('2020-11-23T00:00:00.000000000') , '1': np.nan, '2' : np.nan},
                       {'id': 123, '0': pd.to_datetime('2020-03-22T00:00:00.000000000') , '1': np.nan, '2' : np.nan},
                       {'id': 13215, '0': pd.to_datetime('2020-03-23T00:00:00.000000000') , '1': pd.to_datetime('2020-03-28T00:00:00.000000000'), '2' : pd.to_datetime('2020-04-03T00:00:00.000000000')},
                       {'id': 123, '0': pd.to_datetime('2020-03-22T00:00:00.000000000') , '1': pd.to_datetime('2020-03-23T00:00:00.000000000'), '2' : np.nan}
                      ])

Now I want to calculate the distance between every neighboring dates in weeks and calculate the mean periode duration for the occurrence of every event/id tracked.

The dataframe I want to calculate the mean durations of would look like this:

df_end_point = pd.DataFrame(
                      [{'id': 3, '0': np.nan , '1': np.nan},
                       {'id': 123, '0': np.nan , '1': np.nan},
                       {'id': 13215, '0': pd.to_datetime('2020-03-23T00:00:00.000000000') -  pd.to_datetime('2020-03-28T00:00:00.000000000'), '1': pd.to_datetime('2020-03-28T00:00:00.000000000') - pd.to_datetime('2020-04-03T00:00:00.000000000')},
                       {'id': 123, '0': pd.to_datetime('2020-03-22T00:00:00.000000000') - pd.to_datetime('2020-03-23T00:00:00.000000000'), '1': np.nan}
                      ])

Is there any way to do this elegantly? I would be grateful if I don't need to program this :)

Thank you, my friends!

pandas timedelta date python
1 Answers

based on what I understood, you can do a shift on axis=1, period -1 and subtract, create a mask on the same specification :

df_end_point = df_starting_point.set_index("id")
df_end_point= (df_end_point.sub(df_end_point.shift(-1,axis=1))
               .dropna(how='all',axis=1).reset_index())

print(df_end_point)

      id       0       1
0      3     NaT     NaT
1    123     NaT     NaT
2  13215 -5 days -6 days
3    123 -1 days     NaT

EARLIER VERSION:

df_end_point = df_starting_point.set_index("id")

df_end_point = (df_end_point.diff(-1,axis=1)
                .mask(df_end_point.isna().shift(-1,axis=1).fillna(False))
                .dropna(how='all',axis=1).reset_index())
user_98406370
Related