Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

How do I find first and last value of each day in pandas dataframe

I have a pandas DataFrame like the below:

Price Date
25149.570 2/5/2017 14:22
24799.680 2/5/2017 14:22
24799.680 2/5/2017 14:22
14570.000 2/5/2017 14:47
14570.001 2/5/2017 14:47
14570.001 2/5/2017 14:47
14570.000 2/5/2017 15:01
14570.001 2/5/2017 15:01
14570.001 2/5/2017 15:01
14600.000 2/6/2017 17:49
14600.000 2/6/2017 17:49
14800.000 2/6/2017 17:49
14600.000 2/6/2017 17:49
14600.000 2/6/2017 17:49
14600.000 2/6/2017 18:30
14600.000 2/6/2017 18:30
14800.000 2/6/2017 18:30
14600.000 2/6/2017 18:30
14600.000 2/6/2017 18:30

I want to find first and last value of each day based on Date column. The result can be like the below for the first day:

Date first last
2/5/2017 25149.57 14570.001

I try to use this Q/A solution but it does not work.
How do I find First and Last Value of each day (group by date)?

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

You could convert "Date" column values to dates (without hours); then groupby it and use first and last to get the desired outcome:

out = df.groupby(pd.to_datetime(df['Date']).dt.strftime('%m/%d/%Y'))['Price'].agg(['first', 'last']).reset_index()

Output:

         Date     first       last
0  02/05/2017  25149.57  14570.001
1  02/06/2017  14600.00  14600.000
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading