How can I calculate the elapsed months using pandas? I have write the following, but this code is not elegant. Could you tell me a better way?
import pandas as pd df = pd.DataFrame([pd.Timestamp('20161011'), pd.Timestamp('20161101') ], columns=['date']) df['today'] = pd.Timestamp('20161202') df = df.assign( elapsed_months=(12 * (df["today"].map(lambda x: x.year) - df["date"].map(lambda x: x.year)) + (df["today"].map(lambda x: x.month) - df["date"].map(lambda x: x.month)))) # Out[34]: # date today elapsed_months # 0 2016-10-11 2016-12-02 2 # 1 2016-11-01 2016-12-02 1
Advertisement
Answer
Update for pandas 0.24.0:
Since 0.24.0 has changed the api to return MonthEnd object from period subtraction, you could do some manual calculation as follows to get the whole month difference:
12 * (df.today.dt.year - df.date.dt.year) + (df.today.dt.month - df.date.dt.month) # 0 2 # 1 1 # dtype: int64
Wrap in a function:
def month_diff(a, b): return 12 * (a.dt.year - b.dt.year) + (a.dt.month - b.dt.month) month_diff(df.today, df.date) # 0 2 # 1 1 # dtype: int64
Prior to pandas 0.24.0. You can round the date to Month with to_period()
and then subtract the result:
df['elapased_months'] = df.today.dt.to_period('M') - df.date.dt.to_period('M') df # date today elapased_months #0 2016-10-11 2016-12-02 2 #1 2016-11-01 2016-12-02 1