Pandas DataFrame - Replace NULL value of Datetime column with some old date

Question

Question: How can we replace the NULL values of a datetime column in Pandas DataFrame to something like 1900-01-01 00:00:00.000?

I am using Pandas data frame to import a large data file into a SQL Server 2019 table. My following code correctly replaces NULL values of numeric columns to 0, and NULL values of object (string) columns to empty string. But it does not change the NULL values of the datetime columns to 1900-01-01 00:00:00.000:

import sqlalchemy as sq
import datetime
import pandas as pd
import numpy as np

............
............
c = df.select_dtypes(np.datetime64).columns
df[c] = df[c].fillna('1900-01-01 00:00:00.000')
df.fillna('', inplace=True)

Ref: pandas.DataFrame.select_dtypes and this SO post

In the line df[cols] = df[c].fillna('1900-01-01 00:00:00.000'), what is cols? — Peter Leimbigler
– Peter Leimbigler, Commented Apr 15, 2022 at 14:53
@PeterLeimbigler That was a typo. I corrected that. Thank you for pointing it out early. — nam
– nam, Commented Apr 15, 2022 at 14:56

Daniel Weigel · Accepted Answer · 2022-04-15 18:59:44Z

0

I guess the issue is that 'fillna' doesn't pick up 'NULL' as this is a sql keyword for NA values - but not for pandas. Maybe try replacing directly that string in your code?

df[c].replace('NULL','1900-01-01 00:00:00.000')

answered Apr 15, 2022 at 18:59

Daniel Weigel

1,1372 gold badges11 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pandas DataFrame - Replace NULL value of Datetime column with some old date

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related