CODEWITHSUNDEEP
python-3.xpandas
Number Logic
Number Logic

Reputation: 894

Convert dates to specific format

I have a pandas dataframe that looks like:

import pandas as pd
df1 = pd.DataFrame({'Counterparty':['Bank','Client','Bank','Bank','Bank','Bank'],
         'Date':['4Q18','1Q19','2Q19','4Q21','FY22','H123']
        })

I want to convert the 'Date' column from a string to a date such that the date is the last date for that particular period. ie 'FQ18'= 31st Dec 2018, '1Q19' = 31st Mar 2019, 'FY22' = 31st Dec 2022,'H123'= 30th June 2023

Any suggestions how to achieve this ?

Upvotes: 1

Views: 111

Answers (1)

Brad Solomon
Brad Solomon

Reputation: 40918

As mentioned by @jpp, you're going to have to do some customization. There isn't existing functionality to map "FY22" to 2022-12-31, to my knowledge. Here's something to get you started, based on the limited example you've shown:

import re

import pandas as pd
from pandas.core.tools.datetimes import DateParseError
from pandas.tseries import offsets

halfyr = re.compile(r'H(?P<half>\d)(?P<year>\d{2})')
fiscalyr = re.compile(r'FY(?P<year>\d{2})')


def try_qend(date):
    try:
        return pd.to_datetime(date) + offsets.QuarterEnd()
    except (DateParseError, ValueError):
        halfyr_match = halfyr.match(date)
        if halfyr_match:
            half, year = [int(i) for i in halfyr_match.groups()]
            month = 6 if half == 1 else 12
            return pd.datetime(2000 + year, month, 1) + offsets.MonthEnd()
        else:
            fiscalyr_match = fiscalyr.match(date)
            if fiscalyr_match:
                year = int(fiscalyr_match.group('year'))
                return pd.datetime(2000 + year, 12, 31)
            else:
                # You're SOL
                return pd.NaT


def parse_dates(dates):
    return pd.to_datetime([try_qend(date) for date in dates])

Assumptions:

  • All years are 20yy, not 19xx.
  • The regex patterns here completely describe the year-half/fiscal-year syntax set.

Example:

dates = ['4Q18','1Q19','2Q19','4Q21','FY22','H123']

parse_dates(dates)

DatetimeIndex(['2018-12-31', '2019-03-31', '2019-06-30', '2021-12-31',
               '2022-12-31', '2023-06-30'],
              dtype='datetime64[ns]', freq=None)

Upvotes: 1

Related Questions