Would like to read in Two columns of dates but only get One

I have a text.csv file with 6 columns. I want 2 columns read in as dates for later differences. However, I only get ONE column coming back as a datetime. Any ideas?

Also, I have several empty dates that return nan NOT 0(zeros) as in na_values = 0??

import pandas as pd
CSV = 'text.csv'
df = pd.read_csv(CSV,
skiprows = 0,
na_values = 0,
parse_dates = ['Date of Sign Up', 'Birth Date'],
usecols = ['Date of Sign Up', 'A', 'B', 'C', 'D', 'Birth Date'])

df.info() # Check info for column types and nan...


RangeIndex: 969 entries, 0 to 968
Data columns (total 6 columns):
Date of Sign Up 969 non-null datetime64[ns]
A 969 non-null object
B 969 non-null object
C 969 non-null object
D 969 non-null object
Birth Date 969 non-null object ## <== Why doesn't this column read as datetime?
dtypes: datetime64[ns](1), object(5)
memory usage: 45.5+ KB


Birth Date

read_csv

na_values='0'

1 Answer
1

There is problem some values in Birth Date are contains at least one not parseable datetime, so read_csv silently not parse column.

Birth Date

read_csv

You can check this values by:

dates = pd.to_datetime(df['Birth Date'], errors='coerce')

print (df.loc[dates.isnull(), 'Birth Date'])


Another solution is parse this problematic values to NaT:

NaT

df['Birth Date'] = pd.to_datetime(df['Birth Date'], errors='coerce')


I try test if 0 is correctly parsed to NaT:

0

NaT

import pandas as pd

temp=u"""Date,a
2017-04-03,0
2017-04-04,1
0,2
2017-04-06,3
2017-04-07,4
2017-04-08,5"""
#after testing replace 'pd.compat.StringIO(temp)' to 'filename.csv'
df = pd.read_csv(pd.compat.StringIO(temp), na_values = 0, parse_dates=['Date'])

print (df)
Date a
0 2017-04-03 NaN
1 2017-04-04 1.0
2 NaT 2.0
3 2017-04-06 3.0
4 2017-04-07 4.0
5 2017-04-08 5.0

print (df.dtypes)

Date datetime64[ns]
a float64
dtype: object


If there is a few non parseable values:

import pandas as pd

temp=u"""Date,a
2017-04-03,0
string,1
0,2
2017-04-06,3
2017-04-07,4
2017-04-08,5"""
#after testing replace 'pd.compat.StringIO(temp)' to 'filename.csv'
df = pd.read_csv(pd.compat.StringIO(temp), na_values = [0, 'string'], parse_dates=['Date'])

print (df)
Date a
0 2017-04-03 NaN
1 NaT 1.0
2 NaT 2.0
3 2017-04-06 3.0
4 2017-04-07 4.0
5 2017-04-08 5.0

print (df.dtypes)
Date datetime64[ns]
a float64
dtype: object


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.