0

I am trying to convert the DateTimeIndex of a DataFrame into a Day Index. This is my Dataframe:

data = pd.read_csv('EURUSD=X.csv')
data['DateTime'] = pd.to_datetime(data['Gmt time'])
data = data.set_index('DateTime')

    Open    High    Low Close   Volume
DateTime                    
2003-04-05 21:00:00 1.12284 1.12338 1.12242 1.12305 29059.0996
2003-04-05 22:00:00 1.12274 1.12302 1.12226 1.12241 26091.8008
2003-04-05 23:00:00 1.12235 1.12235 1.12160 1.12169 29240.9004
2003-05-05 00:00:00 1.12161 1.12314 1.12154 1.12258 29914.8008
2003-05-05 01:00:00 1.12232 1.12262 1.12099 1.12140 28370.6992

I tried using:

data['Day'] = data.date()

This gives the following error:

AttributeError: 'DataFrame' object has no attribute 'date'

I also tried using:

data.groupby(["Day"])["close"]

giving me a KeyError: 'Day'

This is my desired output:

    Open    High    Low Close   Volume
DateTime                    
2003-04-05  1.12284 1.12338 1.12242 1.12305 29059.0996
2003-04-05  1.12274 1.12302 1.12226 1.12241 26091.8008
2003-04-05  1.12235 1.12235 1.12160 1.12169 29240.9004
2003-05-05  1.12161 1.12314 1.12154 1.12258 29914.8008
2003-05-05  1.12232 1.12262 1.12099 1.12140 28370.6992
ErnestBidouille
  • 1,071
  • 4
  • 16
Niko
  • 144
  • 8
  • pandas does not handle date and time separately. There is no date index, only datetime. – FObersteiner Oct 18 '22 at 11:43
  • `data['DateTime'] = pd.to_datetime(data['Gmt time']).dt.date` – jezrael Oct 18 '22 at 11:43
  • I found a solution which works, all I had to do was use: 'data.index = data.index.normalize()' to get the desired output: ' Open High Low Close Volume DateTime 2003-04-05 1.12284 1.12338 1.12242 1.12305 29059.0996 2003-04-05 1.12274 1.12302 1.12226 1.12241 26091.8008 2003-04-05 1.12235 1.12235 1.12160 1.12169 29240.9004 2003-05-05 1.12161 1.12314 1.12154 1.12258 29914.8008 2003-05-05 1.12232 1.12262 1.12099 1.12140 28370.6992' – Niko Oct 18 '22 at 11:48

1 Answers1

1

The Answer is:

data.index = data.index.normalize()

    Open    High    Low Close   Volume
DateTime                    
2003-04-05  1.12284 1.12338 1.12242 1.12305 29059.0996
2003-04-05  1.12274 1.12302 1.12226 1.12241 26091.8008
2003-04-05  1.12235 1.12235 1.12160 1.12169 29240.9004
2003-05-05  1.12161 1.12314 1.12154 1.12258 29914.8008
2003-05-05  1.12232 1.12262 1.12099 1.12140 28370.6992
Niko
  • 144
  • 8
  • alternatively `data.index = data.index.floor("d")` - both commands floor the date/time to the date. It keeps the data type datetime64, i.e. a DatetimeIndex, with all the functionality that comes with it. – FObersteiner Oct 18 '22 at 11:56