CODEWITHSUNDEEP
pythonpandaspandas-groupby
Martin Yordanov Georgiev
Martin Yordanov Georgiev

Reputation: 477

Groupby data - show min and max dates and corresponding values

I have the following frame:

frame=pd.DataFrame(data=(['London','2020-08-31',3,4],['London','2020-09-30',1,5],
                         ['London','2020-10-31',2,6],['London','2020-11-30',7,3],
                         ['Paris','2020-08-31',9,3],['Paris','2020-09-30',1,8],
                         ['Paris','2020-10-31',4,5],['Paris','2020-11-30',2,9],
                         ['Paris','2020-12-31',3,8]),columns=["Location","Date","X","Y"])

it looks like that:

    Location    Date    X   Y
0   London  2020-08-31  3   4
1   London  2020-09-30  1   5
2   London  2020-10-31  2   6
3   London  2020-11-30  7   3
4   Paris   2020-08-31  9   3
5   Paris   2020-09-30  1   8
6   Paris   2020-10-31  4   5
7   Paris   2020-11-30  2   9
8   Paris   2020-12-31  3   8

I want to groupby Location and show the min and max dates and their corresponding values. Expected output is like that:

                                                   X                               Y    
Location    Min_Date    Max_Date    Min_Date_Value  Max_Date_Value  Min_Date_Value  Max_Date_Value
London      8/31/2020   11/30/2020        3               7                4              3
Paris       8/31/2020   12/31/2020        9               3                3              8

I can produce the min and max dates by frame.groupby(by="Location")["Date"].agg(["min","max"]) but afterwards I struggle to visualize also the columns with corresponding values for each date.

Upvotes: 1

Views: 57

Answers (1)

anky
anky

Reputation: 75080

You can try the below, if you want the aggregation on date and then pull corresponding rows:

u = frame.groupby(by="Location")["Date"].agg(["min","max"])
out = (u.stack().reset_index(name='Date').merge(frame)
       .set_index(['Location','level_1']).unstack())
out.columns = out.columns.map('_'.join)

print(out)

            Date_max    Date_min  X_max  X_min  Y_max  Y_min
Location                                                    
London    2020-11-30  2020-08-31      7      3      3      4
Paris     2020-12-31  2020-08-31      3      9      8      3

Upvotes: 3

Related Questions