Days calculation in DataFrame in Python Pandas?

user_14790334 0
Viewed 43

I have DataFrame with clients' agreements like below:

rng = pd.date_range('2020-12-01', periods=5, freq='D')
df = pd.DataFrame({ "ID" : ["1", "2", "1", "2", "2"], "Date": rng})

And I need to create new DataFrame with calculation based on above df:

  1. New1 = amount of days from the first agreement until today (16.12)
  2. New2 = amount of days from the last agreement until today (16.12)

To be more precision I need to create df like below:

enter image description here

pandas dataframe date python
2 Answers

Use Series.rsub for subtract from right side with today and convert timedeltas to days by Series.dt.days and then aggregate by GroupBy.agg for GroupBy.first and GroupBy.last values per groups:

now = pd.to_datetime('today')

df = (df.assign(new = df['Date'].rsub(now).dt.days)
        .groupby('ID').agg(New1 = ('new', 'first'),
                           New2 = ('new', 'last')))
        .reset_index()
print (df)
  ID  New1  New2
0  1    15    13
1  2    14    11
user_29010020

Maybe try groupby:

New1 = pd.to_datetime('today') - df.groupby("ID")['Date'].min()
New2 = pd.to_datetime('today') - df.groupby("ID")['Date'].max()
df2 = pd.DataFrame({'ID': df['ID'].drop_duplicates(), 'New1': New1.tolist(), 'New2': New2.tolist()})
print(df2)

Output:

  ID    New1    New2
0  1 15 days 13 days
1  2 14 days 11 days
user_87083640
Related