How to create columns based on date 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", "1", "1", "2"],
                   "Date" : rng,
                   "kind" : ["active", "not_active", "active", "active", "not_active"],
                   "status" : ["b2", "b2", "g8", "g8", "v10"]})

And i need to create DataFrame which will show what was the status of the last active and the last not active agreement. Below I upload example of result:

jezrael · Accepted Answer · 2020-12-21 12:54:57Z

1

If datetimes are sorted you can use DataFrame.pivot_table:

df = (df.pivot_table(index='ID', columns='kind', values='status', aggfunc='last')
        .reset_index()
        .rename_axis(None, axis=1))

If not sure if sorted add DataFrame.sort_values:

df = (df.sort_values(['ID','Date'])
        .pivot_table(index='ID', columns='kind', values='status', aggfunc='last')
        .reset_index()
        .rename_axis(None, axis=1))
print (df)
  ID active not_active
0  1     g8        NaN
1  2    NaN        v10

answered Dec 21, 2020 at 12:54

jezrael

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

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

Collectives™ on Stack Overflow

How to create columns based on date in DataFrame in Python Pandas?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related