Create datetime column from month and day with year based on month

Question

I have columnar data of dates of the form mm-dd as shown. I need to add the correct year (dates October to December are 2017 and dates after 1-1 are 2018) and make a datetime object. The code below works, but it's ugly. Is there a more Pythonic way to accomplish this?

import pandas as pd
from datetime import datetime
import io

data = '''Date
1-3
1-2
1-1
12-21
12-20
12-19
12-18'''

df = pd.read_csv(io.StringIO(data))

for i,s in enumerate(df.Date):
    s = s.split('-')
    if int(s[0]) >= 10:
        s = s[0]+'-'+s[1]+'-17'
    else:
        s = s[0]+'-'+s[1]+'-18'
    df.Date[i] = pd.to_datetime(s)
    print(df.Date[i])

Prints:

2018-01-03 00:00:00
2018-01-02 00:00:00
2018-01-01 00:00:00
2017-12-21 00:00:00
2017-12-20 00:00:00
2017-12-19 00:00:00
2017-12-18 00:00:00

Do you have more dates than those shown in your toy `data` or is that an all-inclusive list of dates you're interested in? — Jarad, May 04 '18 at 20:31
I'll be reading in an Excel file ~2 times a week, comparing enrollment data for this year against last year. I just put a minimal example in the OP. — Matt Wood, May 07 '18 at 13:53

score 3 · Answer 1 · answered May 04 '18 at 21:12

You can conver the date to pandas datetimeobjects. Then modify their year with datetime.replace. See docs for more information.

You can use the below code:

df['Date'] = pd.to_datetime(df['Date'], format="%m-%d")
df['Date'] = df['Date'].apply(lambda x: x.replace(year=2017) if x.month in(range(10,13)) else x.replace(year=2018))

Output:

       Date
0   2018-01-03
1   2018-01-02
2   2018-01-01
3   2017-12-21
4   2017-12-20
5   2017-12-19
6   2017-12-18

score 2 · Answer 2 · answered May 04 '18 at 20:48

This is one way using pandas vectorised functionality:

df['Date'] = pd.to_datetime(df['Date'] + \
             np.where(df['Date'].str.split('-').str[0].astype(int).between(10, 12),
                      '-2017', '-2018'))

print(df)

        Date
0 2018-01-03
1 2018-01-02
2 2018-01-01
3 2017-12-21
4 2017-12-20
5 2017-12-19
6 2017-12-18

Create datetime column from month and day with year based on month

2 Answers2