CODEWITHSUNDEEP
pythonpandas
yudhiesh
yudhiesh

Reputation: 6799

How to remove all the dates where the value is not the max?

Here is the dataframe where there are multiple values for each date but I just want the max Data_value for each date. Note this data spans from 2005-2014.

        ID             Date    Element  Data_Value
49030   USC00207312 2005-01-01  TMAX    150
55424   USC00207308 2005-01-01  TMAX    150
18261   USC00205050 2005-01-01  TMAX    56
18049   USW00014853 2005-01-01  TMAX    56
60994   USW00004848 2005-01-01  TMAX    133
31715   USC00205451 2005-01-01  TMAX    156

Upvotes: 3

Views: 70

Answers (1)

Scott Boston
Scott Boston

Reputation: 153460

Try using groupby with idxmax and boolean indexing:

df.loc[df.groupby('Date')['Data_Value'].idxmax()]

Output:

                 ID        Date Element  Data_Value
31715  USC00205451  2005-01-01    TMAX         156

Upvotes: 4

Related Questions