Value calculation based on date in DataFrame in Python Pandas?

Question

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"],
                   "value" : [100, 200, 300, 400, 500],
                   "status" : ["active", "finished", "active", "finished", "active"],
                   "Date": rng})

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

New1 = Value of the last agreement with status 'active'
New2 = Value of the last agreement with status 'finished'

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

U13-Forward · Accepted Answer · 2020-12-16 11:39:15Z

4

Try using this long:

df1 = df.loc[df['status'] == "active"]
df2 = df.loc[df['status'] == "finished"]
df1 = df1.groupby("ID")['value'].last()
df2 = df2.groupby("ID")['value'].last()
IDs = df["ID"].drop_duplicates()
new_df = pd.DataFrame({"ID": IDs, "New1": df1.reindex(IDs).tolist(), "New2": df2.reindex(IDs).tolist()})
print(new_df)

Output:

  ID  New1   New2
0  1   300    NaN
1  2   500  400.0

answered Dec 16, 2020 at 11:39

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

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

Comments

jezrael · Accepted Answer · 2020-12-16 12:07:43Z

2

Use DataFrame.sort_values by both columns with DataFrame.pivot_table with aggregate function last:

df = (df.sort_values(['ID','Date'])
        .pivot_table(index='ID', columns='status', values='value', aggfunc='last')
        .rename(columns={'active':'New1','finished':'New2'})[['New1','New2']]
        .reset_index()
        .rename_axis(None,axis=1)
       )
print (df)
  ID   New1   New2
0  1  300.0    NaN
1  2  500.0  400.0

If datetimes are sorted per groups solution is simplier:

df = (df.pivot_table(index='ID', columns='status', values='value', aggfunc='last')
        .rename(columns={'active':'New1','finished':'New2'})[['New1','New2']]
        .reset_index()
        .rename_axis(None,axis=1)
       )
print (df)

edited Dec 16, 2020 at 12:07

answered Dec 16, 2020 at 11:33

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

Value calculation based on date 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