I have a pandas DataFrame with index column = date.
Input:
value
date
1986-01-31 22.93
1986-02-28 15.46
I want to floor the date to the first day of that month
Output:
value
date
1986-01-01 22.93
1986-02-01 15.46
What I tried:
df.index.floor('M')
ValueError: <MonthEnd> is a non-fixed frequency
This is potentially because the df is generated by
df = df.resample("M").sum() (The output of this code is the input at the beginning of the question)
I also tried df = df.resample("M", convention='start').sum(). However, it does not work.
I know in R, it is easy to just call floor(date, 'M').
there is a pandas issue about the floor problem
the suggested way is
import pandas as pd
pd.to_datetime(df.date).dt.to_period('M').dt.to_timestamp()
You can use timeseries offset MonthBegin
from pandas.tseries.offsets import MonthBegin
df['date'] = pd.to_datetime(df['date']) - MonthBegin(1)
Edit: The above solution does not handle the dates which are already floored to the beginning of the month. Here is an alternative solution.
Here is a dataframe with additional test cases:
value
date
1986-01-31 22.93
1986-02-28 15.46
2018-01-01 20.00
2018-02-02 25.00
With timedelta method,
df.index = pd.to_datetime(df.index)
df.index = df.index - pd.to_timedelta(df.index.day - 1, unit='d')
value
date
1986-01-01 22.93
1986-02-01 15.46
2018-01-01 20.00
2018-02-01 25.00
This will do the trick and no imports necessary. Numpy has a dtype datetime64 which by default pandas sets to [ns] as seen by checking the dtype. You can change this to month, which will start on the first of the month by accessing the numpy array and changing the type.
df.date = pd.to_datetime(df.date.values.astype('datetime64[M]'))
It would be nice if pandas would implement this with their own astype() method but unfortunately you cannot.
The above works for data as datetime values or strings, if you already have your data as datetime[ns] type you can omit the pd.to_datetime() and just do:
df.date = df.date.values.astype('datetime64[M]')