Converting year and day of year into datetime index in pandas

Question

I have a dataframe:

 year  doy
 2000   49   
 2000   65   
 2000   81   
 2001   97   
 2001  113   
 2001  129   
 2001  145   
 2001  161 

I want to create a datetime index for this dataframe. Here is what I am doing:

df.index = pandas.DatetimeIndex(df['doy'].apply(lambda x: date(2000, 1, 1)+ relativedelta(days=int(x)-1)))

However, this creates a datetime index which only uses 2000 as year. How can I fix that?

Answer 1

In pd.to_datetime() you may specify the format and origin date as:

pd.to_datetime(df['doy'], unit='D', origin=pd.Timestamp(df['year']))

This method does all the hard work for you.

Answer 2

You can use the date specifier %j to extract the day of year. So combine the two columns, shift the year, and convert to datetime!

pd.to_datetime(df['year'] * 1000 + df['doy'], format='%Y%j')

returns

0   2000-02-18
1   2000-03-05
2   2000-03-21
3   2001-04-07
4   2001-04-23
5   2001-05-09
6   2001-05-25
7   2001-06-10
dtype: datetime64[ns]