Python converting date and time as pandas index

Question

I want to convert my datetime column to be pandas dataframe index. This is my dataframe

    Date               Observed Min Max Sum Count
0   09/15/2018 12:00:00 AM  2   0   2   10  5
1   09/15/2018 01:00:00 AM  1   0   2   25  20
2   09/15/2018 02:00:00 AM  1   0   1   21  21
3   09/15/2018 03:00:00 AM  1   0   2   23  22
4   09/15/2018 04:00:00 AM  1   0   1   21  21

And I want the Date to be the index for the dataframe.

I've looked for answers and have tried this code

dateparse = lambda dates: pd.datetime.strptime(dates, '%m/%d/%Y %I:%M:%S').strftime('%m/%d/%Y %I:%M:%S %p')
data = pd.read_csv('mandol.csv', sep=';', parse_dates=['Date'], index_col = 'Date', date_parser=dateparse)
data.head()

but the result is still error -> ValueError: unconverted data remains: AM

how can I solve this?

data.set_index('Date', inplace=True)?

DirtyBit
– DirtyBit

2019-04-02 10:01:29 +00:00
Commented Apr 2, 2019 at 10:01 — DirtyBit
– DirtyBit, Commented Apr 2, 2019 at 10:01

kerwei · Accepted Answer · 2019-04-02 10:05:25Z

Use pd.to_datetime() to convert the Date column and set_index() to set it as your dataframe index.

import pandas as pd

>>>df
    Date               Observed Min Max Sum Count
0   09/15/2018 12:00:00 AM  2   0   2   10  5
1   09/15/2018 01:00:00 AM  1   0   2   25  20
2   09/15/2018 02:00:00 AM  1   0   1   21  21
3   09/15/2018 03:00:00 AM  1   0   2   23  22
4   09/15/2018 04:00:00 AM  1   0   1   21  21

df.Date = pd.to_datetime(df.Date)
df.set_index('Date', inplace=True)

>>>df
                     Unnamed: 0  Observed  Min  Max  Sum  Count
Date
2018-09-15 00:00:00           0         2    0    2   10      5
2018-09-15 01:00:00           1         1    0    2   25     20
2018-09-15 02:00:00           2         1    0    1   21     21
2018-09-15 03:00:00           3         1    0    2   23     22
2018-09-15 04:00:00           4         1    0    1   21     21

perl · Accepted Answer · 2019-04-02 10:11:43Z

1

We can set the index to be the Date column values converted with to_datetime (I'm using pop to get values of the Date column and remove it from the DataFrame at the same time):

df.index = pd.to_datetime(df.pop('Date'))

print(df)

Output:

                     Observed  Min  Max  Sum  Count
Date                                               
2018-09-15 00:00:00         2    0    2   10      5
2018-09-15 01:00:00         1    0    2   25     20
2018-09-15 02:00:00         1    0    1   21     21
2018-09-15 03:00:00         1    0    2   23     22
2018-09-15 04:00:00         1    0    1   21     21

edited Apr 2, 2019 at 10:11

answered Apr 2, 2019 at 10:06

perl

9,9811 gold badge14 silver badges23 bronze badges

Comments

roschach · Accepted Answer · 2019-04-02 13:34:01Z

1

Have a look at set_index() method.

edited Apr 2, 2019 at 13:34

roschach

9,63617 gold badges92 silver badges141 bronze badges

answered Apr 2, 2019 at 10:03

krits

681 silver badge9 bronze badges

Comments

mrzo · Accepted Answer · 2019-04-02 10:19:42Z

0

If you use this code, it sets the second column (Date) as index and transforms it with the standard datetime parser provided by pandas.to_datetime:

ds = pd.read_csv('mandol.csv', sep=';', index_col=1, parse_dates=True)

parse_dates=True automatically transforms the index to a pandas Datetime object.

edited Apr 2, 2019 at 10:19

answered Apr 2, 2019 at 10:14

mrzo

2,1451 gold badge18 silver badges28 bronze badges

Collectives™ on Stack Overflow

Python converting date and time as pandas index

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related