Replacing dates in dataframe with NaN

Question

I have imported an .xls file into a dataframe with pandas. I want to replace all dates (05/04/19 etc) with NaN. How would I call all dates to be replaced at once

Answer 1

A variation of that last option is to force all columns to datetime with the errors='coerce' option, then create a new boolean dataframe using isnan, and use the boolean dataframe to filter the original dataframe and replace the values.

Answer 2

You can import numpy as np, then set the entire column of your dataframe with np.nan.
df["col_name"] = np.nan
I think it should do the job.

Answer 3

Why not just drop the column?

Answer 4

A general option is using a regular expression to filter for dates (this only works if they all follow the same pattern) and replace them.
Another thing you can try is looping through all values, try if they can be converted to datetime type and if they can replace them.

Answer 5

So dates are not in a particular column but all over the table wherever values are missing?

Answer 6

For columns that have numeric objects you can try this ("c" is the column):
pd.to_numeric(c, errors='coerce')
This converts the column to a numeric type and replaces all values that cannot be parsed as numeric with NaN.

Answer 7

That last one sounds like a good idea. I'll give it a try tomorrow and tell you how I get on. Cheers for the help

Answer 8

Dates have been used in the spreadsheet where there is no data. I want to treat dates as if they were NaN so I can fill the dataframe with fillna() and create a decision tree.

Answer 9

Correct

Answer 10

John did you try it? If so, did it work? I'm curious because I've never actually done this myself.