Create new date column in python pandas

Question

I'm trying to create a new date column based on an existing date column in my dataframe. I want to take all the dates in the first column and make them the first of the month in the second column so:

03/15/2019 = 03/01/2019

I know I can do this using:

df['newcolumn'] = pd.to_datetime(df['oldcolumn'], format='%Y-%m-%d').apply(lambda dt: dt.replace(day=1)).dt.date

My issues is some of the data in the old column is not valid dates. There is some text data in some of the rows. So, I'm trying to figure out how to either clean up the data before I do this like:

if oldcolumn isn't a date then make it 01/01/1990 else oldcolumn

Or, is there a way to do this with try/except?

Any assistance would be appreciated.

JoergVanAken · Accepted Answer · 2019-03-13 15:42:02Z

2

At first we generate some sample data:

df = pd.DataFrame([['2019-01-03'], ['asdf'], ['2019-11-10']], columns=['Date'])

This can be safely converted to datetime

df['Date'] = pd.to_datetime(df['Date'], errors='coerce')
mask = df['Date'].isnull()
df.loc[mask, 'Date'] = dt.datetime(1990, 1, 1)

Now you don't need the slow apply

df['New'] = df['Date'] + pd.offsets.MonthBegin(-1)

edited Mar 13, 2019 at 15:42

answered Mar 13, 2019 at 15:18

JoergVanAken

1,2969 silver badges13 bronze badges

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

1 Comment

Steve Estes Over a year ago

This is a more elegant solution, to be sure, but I wouldn't call apply "slow", necessarily. See e.g.: geeksforgeeks.org/…

Erfan · Accepted Answer · 2019-03-13 15:32:55Z

1

Try with the argument errors=coerce. This will return NaT for the text values.

df['newcolumn'] = pd.to_datetime(df['oldcolumn'], 
                                 format='%Y-%m-%d', 
                                 errors='coerce').apply(lambda dt: dt.replace(day=1)).dt.date

For example

# We have this dataframe
    ID        Date
0  111  03/15/2019
1  133  01/01/2019
2  948       Empty
3  452  02/10/2019

# We convert Date column to datetime
df['Date'] = pd.to_datetime(df.Date, format='%m/%d/%Y', errors='coerce')

Output

    ID       Date
0  111 2019-03-15
1  133 2019-01-01
2  948        NaT
3  452 2019-02-10

edited Mar 13, 2019 at 15:32

answered Mar 13, 2019 at 15:12

Erfan

43.3k10 gold badges75 silver badges86 bronze badges

3 Comments

caddie Over a year ago

That looks like a great solution, but I get the following error when trying to implement it: TypeError: to_datetime() got an unexpected keyword argument 'error'

Erfan Over a year ago

You misspelled error you forgot the S, its errors.

caddie Over a year ago

ah...thank you for clarifying and for your help in general. much appreciated.

Collectives™ on Stack Overflow

Create new date column in python pandas

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related