1

I have the following df with Date elements being a string followed by YYYY.MM :

df =
             Date    Value
0  name   2019.06      1.0
1  string 2018.03      1.6
2  string 2017.12      1.0
3  string 2016.09      1.7
4  name   2018.09      6.0
...

And I would like to convert the Date column to the last business day (Monday to Friday) of its month. So I could get this output:

df =
             Date    Value
0      2019-06-28      1.0
1      2018-03-30      1.6
2      2017-12-29      1.0
3      2016-09-30      1.7
4      2018-09-28      6.0
...

I tried re.search to start by searching for the date parts of each element of the column, but I can't figure out the solution for this.

eduardo2111
  • 379
  • 3
  • 21
  • Just to be clear, the last business day of any month is a weekday (Monday through Friday) with the highest date value possible for that month. So you need to know two things, what is the last day of the month, and is it a business day? If not then move back a date at a time until you find a weekday at that date. – Display name Jun 08 '20 at 15:38
  • Yes, I meant exactly that by business day = weekday (Monday through Friday) – eduardo2111 Jun 08 '20 at 15:40
  • This works: https://stackoverflow.com/questions/37441323/finding-last-business-day-of-a-month-in-python – Display name Jun 08 '20 at 15:58

1 Answers1

2

Split and add monthend:

d = pd.to_datetime(df['Date'].str.split().str[-1])
print(df.assign(Date=d + pd.offsets.BMonthEnd(1)))

        Date  Value
0 2019-06-28    1.0
1 2018-03-30    1.6
2 2017-12-29    1.0
3 2016-09-30    1.7
4 2018-09-28    6.0
anky
  • 74,114
  • 11
  • 41
  • 70
  • 1
    Not sure why the `split()` is necessary, but the `pd.offsets.BMonthEnd` is great. – r.ook Jun 08 '20 at 15:55
  • 1
    @r.ook the data looks like `name 2019.06 ` – anky Jun 08 '20 at 15:56
  • 1
    Aaaaah, I thought that was just bad formatting on the question itself. Bad assumption on my part! – r.ook Jun 08 '20 at 15:57
  • You'll need an import for this to work. from pandas.tseries.offsets import BMonthEnd – Display name Jun 08 '20 at 16:02
  • @Displayname you can also import it as i did. works for me (pandas 0.25.3) – anky Jun 08 '20 at 16:02
  • @anky this works thank you. What if I have another dataframe but the date has no string and is only `%YYYY.MM`. How can I split this? Because I'm getting errors, python is considering it a float number and does not split it in the `"." ` – eduardo2111 Jun 08 '20 at 16:27
  • Then ignore the split and just convert to datetime.. :) – anky Jun 08 '20 at 16:30
  • For example, if I do that to an element like `2019.06` it is converting it to `1970-01-01` – eduardo2111 Jun 08 '20 at 16:32
  • I can't test this now as it's bedtime but are you passing any format? If yes try letting pandas infer it – anky Jun 08 '20 at 16:36
  • I tried passing format like format = ''%Y.%m` and is writing `1970-01-01 00:00:00.2019`. And it is the same if I don't put any format – eduardo2111 Jun 08 '20 at 16:40