Work with date column in DataFrame in Python Pandas?

Question

I have DataFrame like below:

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

And I need to create DataFrame with columns:

New1 = amount of the days from the last "acc" agreemtn until today 28.12
New2 = amount of the days from the last "rem" agreement until today 28.12

Result like below:

How did values 24 and 25 come? Can you please explain the logic? — Mayank Porwal
– Mayank Porwal, Commented Dec 28, 2020 at 12:57
today date is 28.12 so we have 24 days from 04.12 (last acc agreement in ID1) and 25 days from 03.12 (last rem agreement in ID 2) — dingaro
– dingaro, Commented Dec 28, 2020 at 13:02

Mayank Porwal · Accepted Answer · 2020-12-28 14:39:58Z

1

Like this:

In [2608]: t = pd.to_datetime('today').normalize()

In [2615]: In [2627]: x = abs(df.groupby(['ID', 'status'])['date'].max() - t).dt.days.reset_index()
In [2619]: y = x.pivot('ID', 'status', 'date')

In [2620]: y
Out[2620]: 
status   acc  other   rem
ID                       
1       24.0    NaN   NaN
2        NaN    NaN  25.0
3        NaN   23.0   NaN

Note: You can rename acc, rem to New1 and New2. I've kept it as is for more understanding.

edited Dec 28, 2020 at 14:39

answered Dec 28, 2020 at 13:12

Mayank Porwal

34.2k9 gold badges45 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Suhas Mucherla · Accepted Answer · 2020-12-28 13:26:37Z

0

code:

df=df.groupby(['status'])['date'].agg('last').reset_index()
df['diff']=abs(pd.to_datetime('today').day-df['date'].dt.day)
df_final=df.pivot(columns='status',values='diff')

output:

df_final
Out[104]: 
status   acc  other   rem
0       24.0    NaN   NaN
1        NaN   23.0   NaN
2        NaN    NaN  25.0

answered Dec 28, 2020 at 13:26

Suhas Mucherla

1,4131 gold badge7 silver badges17 bronze badges

Collectives™ on Stack Overflow

Work with date column in DataFrame in Python Pandas?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related