Get the value for a id with the latest date at least 6 months ago in a pandas DataFrame

user_11027270 0
Viewed 464

Apologies for the vague question, please help me make this question more clear apart from answering it.

Below is the pandas.DataFrame that I have "df" and the pandas.DataFrame that I would like "df1".

"df1" has a column "val_6_months_ago" that shows the "val" of an "id" 6 months ago.

from datetime import datetime
import pandas as pd

df = pd.DataFrame({'id': ['a', 'a', 'a', 'b', 'b'], 
                   'val': [1, 2, 3, 4, 5], 
                   'date': [datetime(2019,4,1), datetime(2019,12,1), datetime(2020,1,1), datetime(2018,1,1), datetime(2020,1,1)]})

df1 = pd.DataFrame({'id':['a', 'a', 'a', 'b', 'b'], 
                    'val':[1, 2, 3, 4, 5], 
                    'date':[datetime(2019,4,1), datetime(2019,12,1), datetime(2020,1,1), datetime(2018,1,1), datetime(2020,1,1)],
                    'val_6_months_ago':[None, 1, 1, None, 4]})
pandas dataframe python
1 Answers

Let's use a 'self-join' with pd.merge_asof and calculate date using pd.DateOffset:

df['date_6_months_ago'] = df['date'] - pd.DateOffset(months=6)

df_out = pd.merge_asof(df.sort_values('date_6_months_ago'), df[['id','date','val']].sort_values('date'), 
                       left_on='date_6_months_ago', 
                       right_on='date', 
                       by='id',
                       direction='backward', 
                       suffixes=('','_6_months_ago'))\
           .drop('date_6_months_ago', axis=1)

Output:

  id  val       date  val_6_months_ago
1  a    1 2019-04-01               NaN
2  a    2 2019-12-01               1.0
3  a    3 2020-01-01               1.0
0  b    4 2018-01-01               NaN
4  b    5 2020-01-01               4.0
user_63615310
Related