Home > Software engineering >  How do I adjust the dates of a column in pandas according to a threshhold?
How do I adjust the dates of a column in pandas according to a threshhold?

Time:08-29

I have a data frame with a datetime column like so:

  dates
0 2017-09-19
1 2017-08-28
2 2017-07-13

I want to know if there is a way to adjust the dates with this condition:

  1. If the day of the date is before 15, then change the date to the end of last month.
  2. If the day of the date is 15 or after, then change the date to the end of the current month.

My desired output would look something like this:

  dates
0 2017-09-30
1 2017-08-31
2 2017-06-30

CodePudding user response:

Using np.where and Josh's suggestion of MonthEnd, this can be simplified a bit.

Given:

        dates
0  2017-09-19
1  2017-08-28
2  2017-07-13

Doing:

from pandas.tseries.offsets import MonthEnd

# Where the day is less than 15, 
    # Give the DateEnd of the previous month.
# Otherwise, 
    # Give the DateEnd of the current month.

df.dates = np.where(df.dates.dt.day.lt(15), 
                    df.dates.add(MonthEnd(-1)), 
                    df.dates.add(MonthEnd(0)))
print(df)

# Output:

       dates
0 2017-09-30
1 2017-08-31
2 2017-06-30

CodePudding user response:

Easy with MonthEnd

Let's set up the data:

dates = pd.Series({0: '2017-09-19', 1: '2017-08-28', 2: '2017-07-13'})
dates = pd.to_datetime(dates)

Then:

from pandas.tseries.offsets import MonthEnd

pre, post = dates.dt.day < 15, dates.dt.day >= 15
dates.loc[pre] = dates.loc[pre]   MonthEnd(-1)
dates.loc[post] = dates.loc[post]   MonthEnd(1)

Explanation: create masks (pre and post) first. Then use the masks to either get month end for current or previous month, as appropriate.

  • Related