Pandas: Keep only the rows only if certain column value appears N times in past N months

user_13019735 0
Viewed 831

I want to keep the rows that recharged(recharge_number) more than 2 times from a single recharge number in the last 3 months(month_n = 4 to 6). Assume the current month is 7. If any recharge number meets that condition then keep all the information associated with that number.

account_no  recharge_number     year    month_n       
52          1300002            2021    6        
52          1300002            2021    5
52          1300002            2021    4
52          1300002            2021    1
52          1644460            2021    6
52          1644460            2021    5
52          1644460            2021    2
70          1553984            2020    12
70          1553984            2020    11
91          1915689            2021    6
91          1915689            2021    5
91          1915689            2021    4
91          1915689            2020    12
91          1915689            2020    11
91          1915689            2020    10
52          1300002            2020    9

output:

account_no  recharge_number     year    month_n       
52          1300002            2021    6        
52          1300002            2021    5
52          1300002            2021    4
91          1915689            2021    6
91          1915689            2021    5
91          1915689            2021    4

i tried this by the following code. Is this the right way or Is there any better solution?

df = df.groupby(['account_no','recharge_number','year','month']).recharge_number.agg('count').to_frame('recharge_count').reset_index()

df[((df.month_n >=4) & (df.year ==2021) & (df.recharge_count>=3))]
pandas-groupby pandas aggregate count python
2 Answers

Idea is use months periods, so you can for previuos monts simply subtract n periods and filter by Series.between:

per = pd.Period('2021-07')
#exclusive, so 2021-04, 2021-05, 2021-06 is tested
prev = per - 4
print (prev)
2021-03

dat=pd.to_datetime(df[['year','month_n']].rename(columns={'month_n':'month'}).assign(day=1))
df['per'] = dat.dt.to_period('m')

mask1 = df['per'].between(prev, per, inclusive=False)
df = df[mask1]

df=df[df.groupby(['account_no','recharge_number']).recharge_number.transform('size').gt(2)]
print (df)
    account_no  recharge_number  year  month_n      per
0           52          1300002  2021        6  2021-06
1           52          1300002  2021        5  2021-05
2           52          1300002  2021        4  2021-04
9           91          1915689  2021        6  2021-06
10          91          1915689  2021        5  2021-05
11          91          1915689  2021        4  2021-04

EDIT: Here is alternative with helper columns for betwee masks and for count number of Trues (matched rows) is used sum, last masks are chained by & for bitweise AND and | for bitwise OR:

cols = df.columns

per = pd.Period('2021-07')

dat=pd.to_datetime(df[['year','month_n']].rename(columns={'month_n':'month'}).assign(day=1))
df['per'] = dat.dt.to_period('m')

#exclusive, so 2021-04, 2021-05, 2021-06 is tested
df['prev3'] = df['per'].between(per - 4, per, inclusive=False)
df['prev6'] = df['per'].between(per - 7, per, inclusive=False)

prev3_groups = df.groupby(['account_no','recharge_number']).prev3.transform('sum').gt(2)
prev6_groups = df.groupby(['account_no','recharge_number']).prev6.transform('sum').gt(5)

df = df.loc[(df['prev3'] & prev3_groups) | (df['prev6'] & prev6_groups), cols]
print (df)
    account_no  recharge_number  year  month_n
0           52          1300002  2021        6
1           52          1300002  2021        5
2           52          1300002  2021        4
9           91          1915689  2021        6
10          91          1915689  2021        5
11          91          1915689  2021        4

Another data for test:

print (df)
    account_no  recharge_number  year  month_n
0           52          1300002  2021        6
1           52          1644460  2021        5
2           52          1644460  2021        4
3           52          1644460  2021        1
4           52          1644460  2021        6
5           52          1644460  2021        5
6           52          1644460  2021        2
7           70          1553984  2020       12
8           70          1553984  2020       11
9           91          1915689  2021        6
10          91          1915689  2021        5
11          91          1915689  2021        4
12          91          1915689  2020       12
13          91          1915689  2020       11
14          91          1915689  2020       10
15          52          1300002  2020        9

cols = df.columns

per = pd.Period('2021-07')

dat=pd.to_datetime(df[['year','month_n']].rename(columns={'month_n':'month'}).assign(day=1))
df['per'] = dat.dt.to_period('m')

#exclusive, so 2021-04, 2021-05, 2021-06 is tested
df['prev3'] = df['per'].between(per - 4, per, inclusive=False)
df['prev6'] = df['per'].between(per - 7, per, inclusive=False)

prev3_groups = df.groupby(['account_no','recharge_number']).prev3.transform('sum').gt(2)
prev6_groups = df.groupby(['account_no','recharge_number']).prev6.transform('sum').gt(5)

df = df.loc[(df['prev3'] & prev3_groups) | (df['prev6'] & prev6_groups), cols]
print (df)
    account_no  recharge_number  year  month_n
1           52          1644460  2021        5
2           52          1644460  2021        4
3           52          1644460  2021        1
4           52          1644460  2021        6
5           52          1644460  2021        5
6           52          1644460  2021        2
9           91          1915689  2021        6
10          91          1915689  2021        5
11          91          1915689  2021        4
user_29010020

You can calculate the time delta with your date as reference and use groupby.filter and query to subset your rows:

# make datetime
date = pd.to_datetime(df[['year', 'month_n']].rename(columns={'month_n': 'month'}).assign(day=1))

# check dates not older than 90 days from reference (2021-07)
df['gt_3months'] = date.gt(pd.to_datetime('2021-07')-pd.Timedelta('90days'))

# groupy account_no and filter
(df.groupby('account_no')
   .filter(lambda g: g['gt_3months'].sum()>=2) # check that there are 2 or more occurrences in the last 3 months
   .query('gt_3months == True') # keep only the recent occurrences
   .drop('gt_3months', axis=1)  # drop temporary column
)

output:

    account_no  recharge_number  year  month_n
0           52          1300002  2021        6
1           52          1300002  2021        5
4           52          1644460  2021        6
5           52          1644460  2021        5
9           91          1915689  2021        6
10          91          1915689  2021        5
user_163434640
Related