Filter out dataframe rows within groups that are not an exact multiple of a previous year

user_8340106 0
Viewed 72

I'd like to filter out columns within each group of 'ticker' so that what remains are only rows exactly 1, 2, 3, etc. years before my most recent time stamp within each ticker. For example:

data

index     date         ticker    dividends
0         3/31/2019    AA        0.626
1         12/31/2018   AA        0.611
2         9/30/2018    AA        0.611
3         6/30/2018    AA        0.04
4         3/31/2018    AA        0.035
5         12/31/2017   AA        0.02
6         12/31/2018   CC        0.035
7         6/30/2018    CC        0.075
8         12/31/2017   CC        0.04
9         12/31/2016   CC        0.035
10        12/31/2015   CC        0.04
11        6/30/2015    CC        0.06

I'd like:

index     date         ticker    dividends
0         3/31/2019    AA        0.626
4         3/31/2018    AA        0.035
6         12/31/2018   CC        0.035
8         12/31/2017   CC        0.04
9         12/31/2016   CC        0.035
10        12/31/2015   CC        0.04

I've messed around for some time with data.groupby('ticker').filter ... but I just can't figure out a pythonic way to do this, especially given that within each group of 'ticker' the most recent date is not always the same and the spacing between dates within each group differs.

pandas-groupby pandas dataframe python
2 Answers

Create a date series from your date column:

d = pd.to_datetime(df['date'])

Take the most recent date for each ticker symbol:

r = d.groupby(df['ticker']).transform('max')

Take rows from your DF where the month/day components are the same:

new_df = df[d.dt.month.eq(r.dt.month) & d.dt.day.eq(r.dt.day)]

This gives you:

             date ticker  dividends
index                              
0       3/31/2019     AA      0.626
4       3/31/2018     AA      0.035
6      12/31/2018     CC      0.035
8      12/31/2017     CC      0.040
9      12/31/2016     CC      0.035
10     12/31/2015     CC      0.040
user_12527590

You can write a function:

def filt(group):
    first_day = group.iloc[0, 1]
    return (group['date'].dt.day==first_day.day) & (group['date'].dt.month==first_day.month)

Then filter out:

df['date'= = pd.to_datetime(df['date'])
df[df.groupby('ticker').apply(filt).values]



index     date         ticker    dividends
0         3/31/2019    AA        0.626
4         3/31/2018    AA        0.035
6         12/31/2018   CC        0.035
8         12/31/2017   CC        0.04
9         12/31/2016   CC        0.035
10        12/31/2015   CC        0.04

Output of the function is:

df.groupby('ticker').apply(filt)

ticker    
AA      0      True
        1     False
        2     False
        3     False
        4      True
        5     False
CC      6      True
        7     False
        8      True
        9      True
        10     True
        11    False
user_87273390
Related