How to do Backward/forward Data grouping in a dataframe?

Question

There are different types of group level available in the actual data set, We would be required different types of group-wise report. (Note: group wise report is not simple hence please read below example carefully) Requirement Example when we need to report 4th level group report then need to sum data field till the last value before 4th level. The report number might any number given in the "Group" column. (Kindly refer Expected output for more clarity) For Rest of the filed, treatment as below: purchase_date, Info1 & Info2: Pickup date available at the group level data : Sum Actual Database: Group purchase_date Info1 Info2 data 2 03/23/2019 A B 57 3 03/24/2019 A B 48 4 03/25/2019 A B 40 1 03/26/2019 Q B 60 2 03/27/2019 Q B 42 3 03/28/2019 Q B 33 4 03/29/2019 Q B 36 1 03/30/2019 R B 54 2 03/31/2019 R B 57 3 04/01/2019 R B 53 4 04/02/2019 R B 56 1 04/03/2019 A B 48 2 04/04/2019 A B 40 3 04/05/2019 A B 45 4 04/06/2019 A B 60 1 04/07/2019 A B 38 2 04/08/2019 A B 58 Expected output after grouping data as per the above requirement: 4th group level report: Group purchase_date Info1 Info2 data 4 03/25/2019 A B 145 4 03/29/2019 Q B 171 4 04/02/2019 R B 220 4 04/06/2019 A B 193 4 04/08/2019 A B 96 2nd group level Report Group purchase_date Info1 Info2 data 2 03/23/2019 A B 57 2 03/27/2019 Q B 190 2 03/31/2019 R B 180 2 04/04/2019 A B 197 2 04/08/2019 A B 201 I have tried it with group by function but I am not able to Kick-off it. Can anyone help me to get the desired output? Thanks in advance. JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

Answer 1

With the following function f(z, l) with parameter l being the required level and z being your DataFrame: def f(z, l): x = (z['Group'] == l) | (z.index == z.index[-1]) cs = z['data'].cumsum() dx = cs - cs.where(x, np.nan).ffill().shift(1).fillna(0) return z[x].drop('data', 1).assign(Group=l).join(dx) print(f(df, 2)) print(f(df, 4)) Output: Group purchase_date Info1 Info2 data 0 2 03/23/2019 A B 57.0 4 2 03/27/2019 Q B 190.0 8 2 03/31/2019 R B 180.0 12 2 04/04/2019 A B 197.0 16 2 04/08/2019 A B 201.0 Group purchase_date Info1 Info2 data 2 4 03/25/2019 A B 145.0 6 4 03/29/2019 Q B 171.0 10 4 04/02/2019 R B 220.0 14 4 04/06/2019 A B 193.0 16 4 04/08/2019 A B 96.0 It basically calculates cumulative sums with cumsum resetting them every time we hit a record with the given level or the last record in the DataFrame. Update: to make the last row in purchase_date always equal to previous row + 4 days: def f(z, l): x = (z['Group'] == l) | (z.index == z.index[-1]) cs = z['data'].cumsum() dx = cs - cs.where(x, np.nan).ffill().shift(1).fillna(0) zz = z[x].drop('data', 1).assign(Group=l).join(dx) zz['purchase_date'] = pd.to_datetime(zz['purchase_date']) zz.at[zz.index[-1], 'purchase_date'] = zz.at[zz.index[-2], 'purchase_date'] + pd.Timedelta('4d') return zz