CODEWITHSUNDEEP
pythonpandas
Peter
Peter

Reputation: 23

pandas.to_datetime return NaT

Try to transform date data to datetime

df = pd.read_csv('https://accounts.profunds.com/etfdata/ByFund/SH-historical_nav.csv')
df.Date = df.Date.astype('str')
df['Date'] = pd.to_datetime(df.Date, format='%m/%d/%y',errors='coerce')

Unfortunately, the Date column turns out to be NaT.

Does anyone know what is the reason for the error and how to fix it?

Thanks

Upvotes: 1

Views: 5904

Answers (1)

jezrael
jezrael

Reputation: 862511

There is format MM/DD/YYYY so need %Y for match YYYY, %y is used for match YY format of years:

df['Date'] = pd.to_datetime(df.Date, format='%m/%d/%Y',errors='coerce')

Or use parse_dates parameter in read_csv for convert first column to datetimes:

df = pd.read_csv('https://accounts.profunds.com/etfdata/ByFund/SH-historical_nav.csv', 
                 parse_dates=[0]) 

print (df)
          Date          ProShares Name Ticker       NAV  Prior NAV  \
0    2020-05-07  ProShares Short S&P500     SH   24.3868    24.6804   
1    2020-05-06  ProShares Short S&P500     SH   24.6804    24.5130   
2    2020-05-05  ProShares Short S&P500     SH   24.5130    24.7370   
3    2020-05-04  ProShares Short S&P500     SH   24.7370    24.8417   
4    2020-05-01  ProShares Short S&P500     SH   24.8417    24.1702   
...         ...                     ...    ...       ...        ...   
3495 2006-06-19  ProShares Short S&P500     SH  140.0000   140.0000   
3496 2006-06-16  ProShares Short S&P500     SH  140.0000   140.0000   
3497 2006-06-15  ProShares Short S&P500     SH  140.0000   140.0000   
3498 2006-06-14  ProShares Short S&P500     SH  140.0000   140.0000   
3499 2006-06-13  ProShares Short S&P500     SH  140.0000   140.0000   

      NAV Change (%)  NAV Change ($)  Shares Outstanding (000)  \
0           -1.18961         -0.2936                 163130.83   
1            0.68290          0.1674                 161730.83   
2           -0.90553         -0.2240                 159405.83   
3           -0.42147         -0.1047                 159405.83   
4            2.77821          0.6715                 156130.83   
...              ...             ...                       ...   
3495         0.00000          0.0000                    375.71   
3496         0.00000          0.0000                      0.71   
3497         0.00000          0.0000                      0.71   
3498         0.00000          0.0000                      0.71   
3499         0.00000          0.0000                      0.71   

      Assets Under Management  
0                3.978239e+09  
1                3.991581e+09  
2                3.907515e+09  
3                3.943222e+09  
4                3.878555e+09  
...                       ...  
3495             5.259996e+07  
3496             9.996000e+04  
3497             9.996000e+04  
3498             9.996000e+04  
3499             9.996000e+04  

[3500 rows x 9 columns]

Upvotes: 1

Related Questions