Pandas, Datetime NaT Removal and replace with None, 'str' object has no attribute 'strftime'

Question

I am converting my date from yyyymmdd to yyyy-mm-dd hh:mm:ss using:

d7['date'] = pd.to_datetime(d7['date'], format='%Y%m%d', errors='coerce').dt.strftime('%Y-%m-%d %H:%M:%S.%f')

but I am getting error AttributeError: 'str' object has no attribute 'strftime'

I have tried implementing this Remove dtype datetime NaT but having a hard time combining it with my formatting above.

in my date column ill get dates like 2028-01-31 00:00:00.000000 or NaT, I want to make the NaT blanks or None instead.

Sample dataframe:

2019-11-01 00:00:00.000000

2019-11-01 00:00:00.000000

2019-11-01 00:00:00.000000

2019-11-04 00:00:00.000000

2019-11-01 00:00:00.000000

2019-11-01 00:00:00.000000

2019-11-01 00:00:00.000000

2019-11-01 00:00:00.000000

2019-11-01 00:00:00.000000

NaT

Thank you.

It will be helpful if you include raw data. Please [provide a reproducible copy of the DataFrame with `to_clipboard`](https://stackoverflow.com/questions/52413246/provide-a-reproducible-copy-of-the-dataframe-with-to-clipboard/52413247#52413247) — Trenton McKinney, Dec 14 '19 at 20:05
"Also a bonus would be to remove the trailing .0 in my dates." You're using `.dt.strftime('%Y-%m-%d %H:%M:%S.%f')`. Drop `.%f`. — Trenton McKinney, Dec 14 '19 at 20:11
"I am converting my date from yyyymmdd to yyyy-mm-dd hh:mm:ss using:" What you just posted, doesn't look like the **raw** data. Post the raw data. — Trenton McKinney, Dec 14 '19 at 20:15
thanks for the tip on the dropping `.%f`. My whole df has many columns and rows. Do you need full dataframe? My first NaT doesnt come till row 30000 so I just placed it in with some dates for you — excelguy, Dec 14 '19 at 20:25
What is the purpose of replacing NaT with None? Do you want to drop those rows? — Trenton McKinney, Dec 14 '19 at 20:31
just replace them with 'None'. Trying to make it match another file. — excelguy, Dec 14 '19 at 20:37

Trenton McKinney · Answer 1 · 2019-12-14T22:05:54.730

"I am converting my date from yyyymmdd to yyyy-mm-dd hh:mm:ss", therefore 20180101 is an example of the initial date format.
Remove dtype datetime NaT expects a datetime format, but pd.to_datetime(d7['date'], format='%Y%m%d', errors='coerce').dt.strftime('%Y-%m-%d %H:%M:%S.%f') creates a string.

import pandas as pd

# sample data
df = pd.DataFrame({'date': ['20180101', '', '20190101']})

# df view
       date
0  20180101
1          
2  20190101

# convert to datetime format
df.date = pd.to_datetime(df.date, format='%Y%m%d', errors='coerce')

# df view
        date
0 2018-01-01
1        NaT
2 2019-01-01

# apply method from link
# use None if you want NoneType.  If you want a string, use 'None'
df.date = df.date.apply(lambda x: x.strftime('%Y-%m-%d %H:%M:%S') if not pd.isnull(x) else None)

# final output
                  date
0  2018-01-01 00:00:00
1                 None
2  2019-01-01 00:00:00

To find problem rows, try the following code instead.

The function will return a str, None or print any 'date' row that causes an AttributeError

df = pd.DataFrame({'date': ['20180101', '', '20190101', 'abcd', 3456]})
df.date = pd.to_datetime(df.date, format='%Y%m%d', errors='coerce')

# convert from datetime format to string format
def convert(x) -> (str, None):
    try:
        return x.strftime('%Y-%m-%d %H:%M:%S') if not pd.isnull(x) else None
    except AttributeError:
        return None
        print(x)      

# apply the function
df.date = df.date.apply(lambda x: convert(x))

# output df
                  date
0  2018-01-01 00:00:00
1                 None
2  2019-01-01 00:00:00
3                 None
4                 None

hey trenton im still getting the same error doing this. `str object has no attribute strftime` — excelguy, Dec 14 '19 at 21:03
@excelguy Then there is likely something wrong with the data that isn't being included in the question. If you post the data somewhere, I can look at it, otherwise I don't have any other suggestions — Trenton McKinney, Dec 14 '19 at 21:23

Pandas, Datetime NaT Removal and replace with None, 'str' object has no attribute 'strftime'

1 Answers1