CODEWITHSUNDEEP
pythonpandasdatetimedataframe
Arjun
Arjun

Reputation: 85

How to select rows of unique dates in DateTimeIndex

Suppose i have a DataFrame with DateTimeIndex like this: 

Date_TimeOpen   High    Low     Close   Volume  
2018-01-22 11:05:00 948.00  948.10  947.95  948.10  9820.0
2018-01-22 11:06:00 948.10  949.60  948.05  949.30  33302.0
2018-01-22 11:07:00 949.25  949.85  949.20  949.85  20522.0
2018-03-27 09:15:00 907.20  908.80  905.00  908.15  126343.0
2018-03-27 09:16:00 908.20  909.20  906.55  906.60  38151.0
2018-03-29 09:30:00 908.90  910.45  908.80  910.15  46429.0

I want to select only the first row of each Unique Date (discard Time) so that i get such output as below:

Date_Time   Open    High    Low     Close   Volume
2018-01-22 11:05:00 948.00  948.10  947.95  948.10  9820.0
2018-03-27 09:15:00 907.20  908.80  905.00  908.15  126343.0
2018-03-29 09:30:00 908.90  910.45  908.80  910.15  46429.0

I tried with loc and iloc but it dint helped.

Any help will be greatly appreciated.

Upvotes: 1

Views: 887

Answers (1)

Dani Mesejo
Dani Mesejo

Reputation: 61910

You need to group by date and get the first element of each group:

import pandas as pd

data = [['2018-01-22 11:05:00', 948.00, 948.10, 947.95, 948.10, 9820.0],
        ['2018-01-22 11:06:00', 948.10, 949.60, 948.05, 949.30, 33302.0],
        ['2018-01-22 11:07:00', 949.25, 949.85, 949.20, 949.85, 20522.0],
        ['2018-03-27 09:15:00', 907.20, 908.80, 905.00, 908.15, 126343.0],
        ['2018-03-27 09:16:00', 908.20, 909.20, 906.55, 906.60, 38151.0],
        ['2018-03-29 09:30:00', 908.90, 910.45, 908.80, 910.15, 46429.0]]

df = pd.DataFrame(data=data)
df = df.set_index([0])
df.columns = ['Open', 'High', 'Low', 'Close', 'Volume']

result = df.groupby(pd.to_datetime(df.index).date).head(1)

print(result)

Output

                      Open    High     Low   Close    Volume
0                                                           
2018-01-22 11:05:00  948.0  948.10  947.95  948.10    9820.0
2018-03-27 09:15:00  907.2  908.80  905.00  908.15  126343.0
2018-03-29 09:30:00  908.9  910.45  908.80  910.15   46429.0

Upvotes: 3

Related Questions