Pandas - How to use a column as the string value in command-CodePudding

I am trying to create a fiscal quarter column in pandas. If the fiscal year-end is March, I could do this with the following code:

df['fiscalquarter'] = df['Date'].dt.to_period('Q-MAR')

However, instead of 'MAR', I want to use the column in my dataframe that has the month abbreviation. My column values are JAN, FEB, MAR, etc. How do I tell pandas to look at the value in that column (fiscalmonth) to create the quarter?

Output of print(df.head()):

           ID  fiscalmonth        Date  DateSubYear fiscalmonthabr  
5021        1            2  2001-03-29         2001            FEB  
5780        2            2  2001-04-03         2001            FEB  
7024        3            2  2001-05-02         2001            FEB  
7307        4            2  2001-05-11         2001            FEB  
8076        4            2  2001-06-14         2001            FEB

I would like to see:

           ID fiscalquater 
5021        1       2002Q1   
5780        2       2002Q1   
7024        3       2002Q1   
7307        4       2002Q1  
8076        4       2002Q2

CodePudding user response：

You can use:

fiscal_quarter = lambda x: x['Date'].to_period(x['fiscalmonthabr'])
df['fiscalquater'] = df.assign(fiscalmonthabr='Q-'   df['fiscalmonthabr'],
                               Date=pd.to_datetime(df['Date'])) \
                       .apply(fiscal_quarter, axis=1)
print(df)

# Output
      ID  fiscalmonth       Date  DateSubYear fiscalmonthabr fiscalquater
5021   1            2 2001-03-29         2001            FEB       2002Q1
5780   2            2 2001-04-03         2001            FEB       2002Q1
7024   3            2 2001-05-02         2001            FEB       2002Q1
7307   4            2 2001-05-11         2001            FEB       2002Q1
8076   4            2 2001-06-14         2001            FEB       2002Q2