CODEWITHSUNDEEP
pythonpandasdatetime
Xiaowan Wen
Xiaowan Wen

Reputation: 145

Pandas how to create new date column with variable month column and find first business day of immediate following Quarter?

I have a dataframe like below:

Date        ID  Month
01/12/2019  A   3
02/01/2019  B   2
03/15/2019  C   1

I'd like to create a new column add month to date and find the first business day of the immediate following quarter:

Date        ID  Month New_Date    Q_BGN_BDay
01/12/2019  A   3     4/12/2019   7/1/2019
02/01/2019  B   2     4/01/2019   4/1/2019
03/15/2019  C   12    3/15/2020   4/1/2020
01/01/2018  D   1     2/01/2018   4/2/2018

I tried to use

from dateutil.relativedelta import relativedelta
New_Date = Date + relativedelta(months=3)

for each date it works. but when it become list or df. it's not working.

df['New_Date'] = df['Date'] + relativedelta(months=df['Month'])

I can probably use a for loop function to iterate through, but wondering if there's better ways to do it.

Upvotes: 0

Views: 286

Answers (2)

Xiaowan Wen
Xiaowan Wen

Reputation: 145

Step 1: a function calculate first business day of next month (first business day only considers weekday vs weekend; holidays not considered)

def Next_Q_BDay(date):
    date =pd.to_datetime(date)
    next_quarter = round((date.month - 1) / 3 + 1)+1
    if next_quarter >=5:
        next_quarter = 1
    first_date = datetime(date.year, 3 * int(next_quarter) - 2, 1)
    mid_date = datetime(date.year, 3 * int(next_quarter) - 2, 15)
    next_q_bday = pd.date_range(first_date,mid_date, freq='BMS')[0]
    return next_q_bday

step 2:

df['Q_BDAY'] = pd.to_datetime(df['New']).apply(Next_Q_BDay)

Upvotes: 0

BENY
BENY

Reputation: 323286

Let us try

df['Date'] = pd.to_datetime(df.Date,dayfirst=False) 
df['New'] = df['Date']+df.Month.apply(lambda x : pd.DateOffset(months=x))
df
        Date ID  Month         New
0 2019-01-12  A      3  2019-04-12
1 2019-02-01  B      2  2019-04-01
2 2019-03-15  C      1  2019-04-15

Upvotes: 2

Related Questions