CODEWITHSUNDEEP
python
mooseman
mooseman

Reputation: 2017

Python to create buy month from date

I need to create a column in the data frame for a buy month that is from the 22nd of the month through the 21st of the next month. The buymonth should be just be the first day of the month . Example: date buymonth 12/17/16 >> 12/01/16 12/23/16 >> 01/01/17

I have tried lots of code but always get an error when trying to compare the day greater than 21

If df.loc[:,'date'].dt.day > 21:

Throws the error ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

tmp2["buymonth"] = tmp2['date'] + relativedelta(months=+1)

Gives me the error TypeError: incompatible type [object] for a datetime/timedelta operation

Upvotes: 0

Views: 79

Answers (1)

Brad Solomon
Brad Solomon

Reputation: 40878

Use np.where and pandas' date offset functionality.

import pandas as pd
from pandas.tseries.offsets import MonthBegin

df = DataFrame({'date' : pd.date_range('1/1/2010', periods=365)})
df.loc[:, 'buymonth'] = np.where(df.loc[:,'date'].dt.day > 21, 
        df.loc[:,'date'] + MonthBegin(1), 
        df.loc[:,'date'] + MonthBegin(1) - MonthBegin(1))

df.iloc[20:30]
Out[9]: 
         date   buymonth
20 2010-01-21 2010-01-01
21 2010-01-22 2010-02-01
22 2010-01-23 2010-02-01
23 2010-01-24 2010-02-01
24 2010-01-25 2010-02-01
25 2010-01-26 2010-02-01
26 2010-01-27 2010-02-01
27 2010-01-28 2010-02-01
28 2010-01-29 2010-02-01
29 2010-01-30 2010-02-01

Upvotes: 1

Related Questions