CODEWITHSUNDEEP
pythonpandasdatetime
Iqbal Pratama
Iqbal Pratama

Reputation: 139

Python converting date and time as pandas index

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?

Upvotes: 2

Views: 4457

Answers (4)

krits
krits

Reputation: 68

Have a look at set_index() method.

Upvotes: 1

mrzo
mrzo

Reputation: 2135

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.

Upvotes: 0

perl
perl

Reputation: 9941

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

Upvotes: 1

kerwei
kerwei

Reputation: 1842

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

Upvotes: 3

Related Questions