CODEWITHSUNDEEP
pandaspandas-groupby
Elizabeth Orrico
Elizabeth Orrico

Reputation: 171

Select all rows of dataframe that have a minimum value for a group

I have a dataframe of dates, times, and values, and I would like to create a new dataframe with the date and value of the earliest time for each date (think like an opening stock price)

For example,

date    time   value
1/12    9:07      10
1/12    9:03      13
1/13   10:35       8
1/13   11:02      15
1/13   11:54       6

I would want:

date    value
1/12       13
1/13        8

Since those values correspond to the earliest time for each date.

So far I got:

timegroup = (result.groupby('date')['time'].min()).to_dict()

But can't figure out where to go from here.

Upvotes: 2

Views: 291

Answers (1)

ansev
ansev

Reputation: 30920

Use DataFrame.sort_values + DataFrame.drop_duplicates.

df.sort_values(['date','time']).drop_duplicates(subset ='date')[['date','value']]
#   date  value
#1  1/12     13
#2  1/13      8

or 

df.sort_values(['date','time']).groupby('date',as_index=False).first()[['date','value']]
#    date  value
# 0  1/12     13
# 1  1/13      8

Upvotes: 1

Related Questions