sql max() over (partition by) in pandas

Question

I'm trying to get the same SQL output for max over partition by, but in pandas. the goal is to replace the did_renew==No with Yes, but under specific conditions and for a group of the dataframe This is my datafreame:

        date_id               sf_id renewal_date is_up_for_renewal     did_renew  datediff
168  2020-09-01  0010O00001n1s1rQAA  2020-09-30   Yes               Undetermined NaN      
169  2020-08-01  0010O00001n1s1rQAA  2020-09-30   No                Undetermined  1.0     
170  2020-07-01  0010O00001n1s1rQAA  2020-09-30   No                Undetermined  1.0     
171  2020-06-01  0010O00001n1s1rQAA  2020-09-30   No                Undetermined  1.0     
172  2020-05-01  0010O00001n1s1rQAA  2020-09-30   No                Undetermined  1.0     
173  2020-04-01  0010O00001n1s1rQAA  2020-09-30   No                Undetermined  1.0     
174  2020-03-01  0010O00001n1s1rQAA  2020-09-30   No                Undetermined  1.0     
175  2020-02-01  0010O00001n1s1rQAA  2020-09-30   No                Undetermined  1.0     
176  2020-01-01  0010O00001n1s1rQAA  2020-09-30   No                Undetermined  1.0     
177  2019-12-01  0010O00001n1s1rQAA  2020-09-30   No                Undetermined  1.0     
178  2019-11-01  0010O00001n1s1rQAA  2020-09-30   No                Undetermined  1.0     
179  2019-10-01  0010O00001n1s1rQAA  2020-09-30   No                Undetermined  1.0     
180  2019-08-01  0010O00001n1s1rQAA  2019-08-31   Yes               No            2.0     
181  2019-07-01  0010O00001n1s1rQAA  2019-08-31   No                No            1.0     
182  2019-06-01  0010O00001n1s1rQAA  2019-08-31   No                No            1.0     
183  2019-05-01  0010O00001n1s1rQAA  2019-08-31   No                No            1.0     
184  2019-04-01  0010O00001n1s1rQAA  2019-08-31   No                No            1.0     
185  2019-03-01  0010O00001n1s1rQAA  2019-08-31   No                No            1.0     
186  2019-02-01  0010O00001n1s1rQAA  2019-08-31   No                No            1.0     
187  2019-01-01  0010O00001n1s1rQAA  2019-08-31   No                No            1.0     
188  2018-12-01  0010O00001n1s1rQAA  2019-08-31   No                No            1.0     
189  2018-11-01  0010O00001n1s1rQAA  2019-08-31   No                No            1.0     
190  2018-10-01  0010O00001n1s1rQAA  2019-08-31   No                No            1.0     
191  2018-09-01  0010O00001n1s1rQAA  2019-08-31   No                No            1.0     
192  2018-08-01  0010O00001n1s1rQAA  2019-08-31   No                No            1.0

In SQL I would write: case when dafediff=2 then max('Yes') over (partition by sf_id,renewal_date) end That would have created a new column with values only for rows 180-192 (see the renewal date is different for rows 168-179, 180-192) This is how the results should be in column target:

        date_id               sf_id renewal_date is_up_for_renewal     did_renew  datediff        target
168  2020-09-01  0010O00001n1s1rQAA  2020-09-30   Yes               Undetermined NaN        Undetermined
169  2020-08-01  0010O00001n1s1rQAA  2020-09-30   No                Undetermined  1.0       Undetermined
170  2020-07-01  0010O00001n1s1rQAA  2020-09-30   No                Undetermined  1.0       Undetermined
171  2020-06-01  0010O00001n1s1rQAA  2020-09-30   No                Undetermined  1.0       Undetermined
172  2020-05-01  0010O00001n1s1rQAA  2020-09-30   No                Undetermined  1.0       Undetermined
173  2020-04-01  0010O00001n1s1rQAA  2020-09-30   No                Undetermined  1.0       Undetermined
174  2020-03-01  0010O00001n1s1rQAA  2020-09-30   No                Undetermined  1.0       Undetermined
175  2020-02-01  0010O00001n1s1rQAA  2020-09-30   No                Undetermined  1.0       Undetermined
176  2020-01-01  0010O00001n1s1rQAA  2020-09-30   No                Undetermined  1.0       Undetermined
177  2019-12-01  0010O00001n1s1rQAA  2020-09-30   No                Undetermined  1.0       Undetermined
178  2019-11-01  0010O00001n1s1rQAA  2020-09-30   No                Undetermined  1.0       Undetermined
179  2019-10-01  0010O00001n1s1rQAA  2020-09-30   No                Undetermined  1.0       Undetermined
180  2019-08-01  0010O00001n1s1rQAA  2019-08-31   Yes               No            2.0       Yes         
181  2019-07-01  0010O00001n1s1rQAA  2019-08-31   No                No            1.0       Yes         
182  2019-06-01  0010O00001n1s1rQAA  2019-08-31   No                No            1.0       Yes         
183  2019-05-01  0010O00001n1s1rQAA  2019-08-31   No                No            1.0       Yes         
184  2019-04-01  0010O00001n1s1rQAA  2019-08-31   No                No            1.0       Yes         
185  2019-03-01  0010O00001n1s1rQAA  2019-08-31   No                No            1.0       Yes         
186  2019-02-01  0010O00001n1s1rQAA  2019-08-31   No                No            1.0       Yes         
187  2019-01-01  0010O00001n1s1rQAA  2019-08-31   No                No            1.0       Yes         
188  2018-12-01  0010O00001n1s1rQAA  2019-08-31   No                No            1.0       Yes         
189  2018-11-01  0010O00001n1s1rQAA  2019-08-31   No                No            1.0       Yes         
190  2018-10-01  0010O00001n1s1rQAA  2019-08-31   No                No            1.0       Yes         
191  2018-09-01  0010O00001n1s1rQAA  2019-08-31   No                No            1.0       Yes         
192  2018-08-01  0010O00001n1s1rQAA  2019-08-31   No                No            1.0       Yes

The full dataframe would include many groups of sf_id's so I know I need to use the groupby method for [sf_id,renewal_date] but not sure how to accomplish this Thanks in advance!

user_6361531 · Answer

IIUC,

df['target'] = (df.assign(target=df['datediff']==2))\
                  .groupby(['sf_id', 'renewal_date'])['target']\
                  .transform('max').map({True:'Yes',False:'Undetermined'})

Output:

        date_id               sf_id renewal_date is_up_for_renewal     did_renew  datediff        target
168  2020-09-01  0010O00001n1s1rQAA   2020-09-30               Yes  Undetermined       NaN  Undetermined
169  2020-08-01  0010O00001n1s1rQAA   2020-09-30                No  Undetermined       1.0  Undetermined
170  2020-07-01  0010O00001n1s1rQAA   2020-09-30                No  Undetermined       1.0  Undetermined
171  2020-06-01  0010O00001n1s1rQAA   2020-09-30                No  Undetermined       1.0  Undetermined
172  2020-05-01  0010O00001n1s1rQAA   2020-09-30                No  Undetermined       1.0  Undetermined
173  2020-04-01  0010O00001n1s1rQAA   2020-09-30                No  Undetermined       1.0  Undetermined
174  2020-03-01  0010O00001n1s1rQAA   2020-09-30                No  Undetermined       1.0  Undetermined
175  2020-02-01  0010O00001n1s1rQAA   2020-09-30                No  Undetermined       1.0  Undetermined
176  2020-01-01  0010O00001n1s1rQAA   2020-09-30                No  Undetermined       1.0  Undetermined
177  2019-12-01  0010O00001n1s1rQAA   2020-09-30                No  Undetermined       1.0  Undetermined
178  2019-11-01  0010O00001n1s1rQAA   2020-09-30                No  Undetermined       1.0  Undetermined
179  2019-10-01  0010O00001n1s1rQAA   2020-09-30                No  Undetermined       1.0  Undetermined
180  2019-08-01  0010O00001n1s1rQAA   2019-08-31               Yes            No       2.0           Yes
181  2019-07-01  0010O00001n1s1rQAA   2019-08-31                No            No       1.0           Yes
182  2019-06-01  0010O00001n1s1rQAA   2019-08-31                No            No       1.0           Yes
183  2019-05-01  0010O00001n1s1rQAA   2019-08-31                No            No       1.0           Yes
184  2019-04-01  0010O00001n1s1rQAA   2019-08-31                No            No       1.0           Yes
185  2019-03-01  0010O00001n1s1rQAA   2019-08-31                No            No       1.0           Yes
186  2019-02-01  0010O00001n1s1rQAA   2019-08-31                No            No       1.0           Yes
187  2019-01-01  0010O00001n1s1rQAA   2019-08-31                No            No       1.0           Yes
188  2018-12-01  0010O00001n1s1rQAA   2019-08-31                No            No       1.0           Yes
189  2018-11-01  0010O00001n1s1rQAA   2019-08-31                No            No       1.0           Yes
190  2018-10-01  0010O00001n1s1rQAA   2019-08-31                No            No       1.0           Yes
191  2018-09-01  0010O00001n1s1rQAA   2019-08-31                No            No       1.0           Yes
192  2018-08-01  0010O00001n1s1rQAA   2019-08-31                No            No       1.0           Yes

Details, much like your case when I am creating/assign a temporary column 'target' that is True when datediff equals to 2. Then, I am going to groupby this column just like your partition by on 'sf_id' and 'renewal_date'. Next we use transform to get that max 'target' for that group hence create True for all records in a group where any record has datediff equal to 2. Lastly, we use map to change the True to Yes and False to Undetermined.

sql max() over (partition by) in pandas

1 Answers