[FIXED] How to create a date based on year and month name in dataframe?

Issue

I want to create a date based on year and month name.I wrote a code to print dates in the console.

code :

import pandas as pd
import numpy as np

data = {'month': ['APRIL', 'MAY', 'JUNE', 'JULY', 'AUGUST', 'SEPTEMBER', 'OCTOBER', 'NOVEMBER', 'DECEMBER', 'JANUARY', 'FEBRUARY', 'MARCH'],
    'kpi': ['SALES', 'SALES QUANTITY', 'SALES', 'SALES', 'SALES', 'SALES', 'SALES', 'SALES QUANTITY', 'SALES', 'SALES', 'SALES', 'SALES'],
    'financial_year': [2022, 2023, 2023, 2023, 2023, 2023, 2023, 2023, 2023, 2023, 2023, 2023]
    }

# Create DataFrame
df = pd.DataFrame(data)
print (pd.to_datetime(df['financial_year'].astype(str)  + df['month'], format='%Y%B'))

It generates output like below:

0    2022-04-01
1    2023-05-01
2    2023-06-01
3    2023-07-01
4    2023-08-01
5    2023-09-01
6    2023-10-01
7    2023-11-01
8    2023-12-01
9    2023-01-01
10   2023-02-01
11   2023-03-01

But I want to generate output in the below format:

0    2022-04-30
1    2023-05-01
2    2023-06-30
3    2023-07-31
4    2023-08-31
5    2023-09-30
6    2023-10-31
7    2023-11-01
8    2023-12-31
9    2023-01-31
10   2023-02-28 
11   2023-03-31

Required output is based on some conditions:

1. If the kpi is sales then the day in the date should be last day of that particular month otherwise it should be 01.
2. For the month of April last day should be based on leap year.

I need the output when all the columns are of object type.

Can anyone suggest a solution for this?

Solution

Filter rows with kpi for SALES and add offsets.MonthEnd for last day of month:

df['Date'] = pd.to_datetime(df['financial_year'].astype(str)  + df['month'], format='%Y%B')

df.loc[df['kpi'].eq('SALES'), 'Date'] += pd.offsets.MonthEnd(0) 
print (df)
        month             kpi  financial_year       Date
0       APRIL           SALES            2022 2022-04-30
1         MAY  SALES QUANTITY            2023 2023-05-01
2        JUNE           SALES            2023 2023-06-30
3        JULY           SALES            2023 2023-07-31
4      AUGUST           SALES            2023 2023-08-31
5   SEPTEMBER           SALES            2023 2023-09-30
6     OCTOBER           SALES            2023 2023-10-31
7    NOVEMBER  SALES QUANTITY            2023 2023-11-01
8    DECEMBER           SALES            2023 2023-12-31
9     JANUARY           SALES            2023 2023-01-31
10   FEBRUARY           SALES            2023 2023-02-28
11      MARCH           SALES            2023 2023-03-31

Answered By - jezrael