CODEWITHSUNDEEP
pythonpandasdataframedatetime
J Rock
J Rock

Reputation: 261

How to convert month name and year to datetime with pandas

I am trying to convert the index of my data frame to a datetime object using pandas, but keep receiving this error--

"ValueError: time data 'Jan 20' does not match format '%b, %y' (match)".

I double checked the datetime format and removed the hyphen, but still no luck. How can I fix this?

Here is what I've tried:

import pandas as pd

table = pd.read_html('https://www.finra.org/investors/learn-to-invest/advanced-investing/margin-statistics')

#set the index to date column
df = table[0].set_index('Month/Year')

df.index = df.index.str.replace("-", " ")

df.index = pd.to_datetime(df.index, format="%b, %y")

Upvotes: 0

Views: 3091

Answers (2)

NYC Coder
NYC Coder

Reputation: 7594

Small change:

df.index = pd.to_datetime(df.index, format="%b-%y")
print(df)

            Debit Balances in Customers' Securities Margin Accounts  ...  Free Credit Balances in Customers' Securities Margin Accounts
Month/Year                                                           ...
2020-01-01                                             561812        ...                                             186847
2020-02-01                                             545127        ...                                             197716
2020-03-01                                             479291        ...                                             226202
2020-04-01                                             524696        ...                                             217187

[4 rows x 3 columns]

Upvotes: 1

Anshul
Anshul

Reputation: 1413

I think you had an extra comma. This is working fine for me:

df.index = pd.to_datetime(df.index, format="%b %y")

print(df.index)

Output:

DatetimeIndex(['2020-01-01', '2020-02-01', '2020-03-01', '2020-04-01'], dtype='datetime64[ns]', name='Month/Year', freq=None)

Upvotes: 2

Related Questions