Thanks for taking the time to look at my question.
I try to convert two date columns in a pandas dataframe using the function below. I use this function, because the "Closed Date" has 4221 lines, so it should not crash on the null cells.
Ultimately, the change results into a dataframe of the original row numbers. So, I don't want to loose the rows that have null values at closed dates.
Dataframe overview:
<class 'pandas.core.frame.DataFrame'>
Int64Index: 4272 entries, 0 to 4271
Data columns (total 4 columns):
Created Date 4272 non-null object
Closed Date 4221 non-null object
Agency 4272 non-null object
Borough 4272 non-null object
dtypes: object(4)
designed function:
col='Closed Date'
df[(df[col].notnull())] = df[(df[col].notnull())].apply(lambda x:datetime.datetime.strptime(x,'%m/%d/%Y %I:%M:%S %p'))
generated error:
TypeError Traceback (most recent call last)
<ipython-input-155-49014bb3ecb3> in <module>()
9
10 col='Closed Date'
---> 11 df[(df[col].notnull())] = df[(df[col].notnull())].apply(lambda x:datetime.datetime.strptime(x,'%m/%d/%Y %I:%M:%S %p'))
12 print(type(df[(df[col].notnull())]))
/anaconda/lib/python3.6/site-packages/pandas/core/frame.py in apply(self, func, axis, broadcast, raw, reduce, args, **kwds)
4358 f, axis,
4359 reduce=reduce,
-> 4360 ignore_failures=ignore_failures)
4361 else:
4362 return self._apply_broadcast(f, axis)
/anaconda/lib/python3.6/site-packages/pandas/core/frame.py in _apply_standard(self, func, axis, ignore_failures, reduce)
4454 try:
4455 for i, v in enumerate(series_gen):
-> 4456 results[i] = func(v)
4457 keys.append(v.name)
4458 except Exception as e:
<ipython-input-155-49014bb3ecb3> in <lambda>(x)
9
10 col='Closed Date'
---> 11 df[(df[col].notnull())] = df[(df[col].notnull())].apply(lambda x:datetime.datetime.strptime(x,'%m/%d/%Y %I:%M:%S %p'))
12 print(type(df[(df[col].notnull())]))
TypeError: ('strptime() argument 1 must be str, not Series', 'occurred at index Created Date')
I think you need only to_datetime - it convert NaN to NaT, so all values are datetimes in column:
col='Closed Date'
df[col] = pd.to_datetime(df[col], format='%m/%d/%Y %I:%M:%S %p')
Sample:
df = pd.DataFrame({'Closed Date':['05/01/2016 05:10:10 AM',
'05/01/2016 05:10:10 AM',
np.nan]})
col='Closed Date'
df[col] = pd.to_datetime(df[col], format='%m/%d/%Y %I:%M:%S %p')
print (df)
Closed Date
0 2016-05-01 05:10:10
1 2016-05-01 05:10:10
2 NaT
print (df.dtypes)
Closed Date datetime64[ns]
dtype: object