Pandas: Change Day

I have a series in datetime format, and need to change the day to 1 for each entry. I have thought of numerous simple solutions, but none of them works for me. For now, the only th

Solution 1:

You can use .apply and datetime.replace, eg:

import pandas as pd
from datetime import datetime

ps = pd.Series([datetime(2014, 1, 7), datetime(2014, 3, 13), datetime(2014, 6, 12)])
new = ps.apply(lambda dt: dt.replace(day=1))

Gives:

02014-01-0112014-03-0122014-06-01dtype:datetime64[ns]

Solution 2:

The other answer works, but any time you use apply, you slow your code down a lot. I was able to get an 8.5x speedup by writing a quick vectorized Datetime replace for a series.

defvec_dt_replace(series, year=None, month=None, day=None):
    return pd.to_datetime(
        {'year': series.dt.year if year isNoneelse year,
         'month': series.dt.month if month isNoneelse month,
         'day': series.dt.day if day isNoneelse day})

Apply:

%timeit dtseries.apply(lambda dt: dt.replace(day=1))
# 4.17 s ± 38.3 ms per loop (mean ± std. dev. of7 runs, 1 loop each)

Vectorized:

%timeit vec_dt_replace(dtseries, day=1)
# 491 ms ± 6.48 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

Note that you could face errors by trying to change dates to ones that don't exist, like trying to change 2012-02-29 to 2013-02-29. Use the errors argument of pd.to_datetime to ignore or coerce them.

Data generation: Generate series with 1 million random dates:

import pandas as pd
import numpy as np

# Generate random dates. Modified from: https://stackoverflow.com/a/50668285defpp(start, end, n):
    start_u = start.value // 10 ** 9
    end_u = end.value // 10 ** 9return pd.Series(
        (10 ** 9 * np.random.randint(start_u, end_u, n)).view('M8[ns]'))

start = pd.to_datetime('2015-01-01')
end = pd.to_datetime('2018-01-01')
dtseries = pp(start, end, 1000000)
# Remove time component
dtseries = dtseries.dt.normalize()

Solution 3:

The other two answers work, but neither is very elegant nor in the spirit of the pandas library. Instead, consider this, which is also ever so slightly faster in my tests then Kyle Barron's vectorized answer. It's a one liner that does not require defining any outside functions, is vectorized, and stays within the pandas ecosystem:

import pandas as pd
dtseries.dt.to_period('M').dt.to_timestamp()

This method has the added benefit of supporting many other frequencies to floor to, such as weekly ('W') or business days ('B') that would be trickier to implement with the vectorized approach above.

You can find the abbreviations for various other frequencies in the relevant doc page.

This of course assumes that dtseries is a datetime series, if not you can easily convert it with pd.to_datetime(my_series).

This solution also allows for great flexibility in using various offsets. For example, to use the tenth day of the month:

from pandas.tseries.offsets import DateOffset
dtseries.dt.to_period('M').dt.to_timestamp() + DateOffset(days=10)

I recommend you check the doc for pandas offsets. The offsets pandas provides support a lot of rather complex offsets, such as business days, holidays, business hours, etc... Those would be extremely cumbersome to implement by hand as proposed by the answers of @KyleBarron and @JonClements. Consider this example for instance, to get dates offset 5 business days from the start of the month:

from pandas.tseries.offsets import BusinessDay
dtseries.dt.to_period('M').dt.to_timestamp() + BusinessDay(n=5)