Duplicate last row of a group/id in a dataframe, based on certain condition

I have the below dataframe:

#Load the required libraries
import pandas as pd
#Create dataset
data = {'id': [1, 1, 1, 1, 1,1, 1, 1, 1, 1, 1,
2, 2,
3, 3, 3, 3, 3, 3,
4, 
5, 5, 5, 5, 5,5, 5, 5,5],
'cycle': [1,2, 3, 4, 5,6,7,8,9,10,11,
1,2, 
1,2, 3, 4, 5,6,
1,
1,2, 3, 4, 5,6,7,8,9,],
'Salary': [7, 7, 7,8,9,10,11,12,13,14,15,
4, 5,
8,9,10,11,12,13,
8,
7, 7,9,10,11,12,13,14,15,],
'Children': ['No', 'Yes', 'Yes', 'Yes', 'Yes', 'No','No', 'Yes', 'Yes', 'Yes', 'No',
'Yes', 'No', 
'No','Yes', 'Yes', 'No','No', 'Yes',
'Yes',
'No',  'Yes', 'No', 'No', 'Yes', 'Yes', 'Yes', 'Yes', 'No',],
'Days': [123, 128, 66, 66, 120, 141, 52,96, 120, 141, 52,
96, 120,
15,123, 128, 66, 120, 141,
141,
123, 128, 66, 123, 128, 66, 120, 141, 52,],
}
#Convert to dataframe
df = pd.DataFrame(data)
print("df = \n", df)

The above dataframe looks as such:

Here, every id has different cycles as per the 'cycle' column. For example,

id-1 has maximum 11 cycles.

id-2 has maximum 2 cycle.

id-3 has maximum 6 cycles.

id-4 has maximum 1 cycle.

id-5 has maximum 9 cycles.

I have a certain threshold limit on 'cycles'. Let's say cycle_threshold = 3

If maximum cycles for and id <= cycle_threshold, then duplicate the last row of that group/id till the cycle reaches 4.

For example,

For id-2, since maximum cycles are 2 (which is less than 3), repeat the last row of id-2, till the cycle becomes 4.

For id-4, since maximum cycles are 1 (which is less than 3), repeat the last row of id-4, till the cycle becomes 4.

The rest of the group's/id's remain as it is.

The result looks as such:

Can somebody please let me know how do I achieve this task in Python?

You can idetify id by compare maximal values of cycle per groups and for filtered groups create MultiIndex.from_product and reepat last value by DataFrame.reindex with parameter method='ffill':

cycle_threshold = 3
M = 4
#get maximal cycle per id
s = df.groupby('id')['cycle'].max()
#get id less or equal threshold
ids = s.index
展开收缩
#test groups by id match list ids
mask = df['id'].isin(ids)
#create MultiIndex with range(1,M + 1) for filtered groups
df1 = df[mask].set_index(['id','cycle'])
mux = pd.MultiIndex.from_product([ids, range(1, M + 1)], names=['id','cycle'])
#append new groups with sorting by id and cycle
df2 = (pd.concat([df[~mask],
df1.reindex(mux, method='ffill').reset_index()])
.sort_values(['id','cycle'], ignore_index=True))

Another idea is create final MultiIndex and reindex all rows:

cycle_threshold = 3
M = 4
s = df.groupby('id')['cycle'].max()
ids = s.index
展开收缩
mask = df['id'].isin(ids)
df2 = df.set_index(['id','cycle'])
mux = (pd.MultiIndex.from_product([ids, range(1, 5)], 
names=['id','cycle']).append(df2[~mask.to_numpy()].index)
.sort_values())
df2 = df2.reindex(mux, method='ffill').reset_index()

print (df2)
id  cycle  Salary Children  Days
0    1      1       7       No   123
1    1      2       7      Yes   128
2    1      3       7      Yes    66
3    1      4       8      Yes    66
4    1      5       9      Yes   120
5    1      6      10       No   141
6    1      7      11       No    52
7    1      8      12      Yes    96
8    1      9      13      Yes   120
9    1     10      14      Yes   141
10   1     11      15       No    52
11   2      1       4      Yes    96
12   2      2       5       No   120
13   2      3       5       No   120
14   2      4       5       No   120
15   3      1       8       No    15
16   3      2       9      Yes   123
17   3      3      10      Yes   128
18   3      4      11       No    66
19   3      5      12       No   120
20   3      6      13      Yes   141
21   4      1       8      Yes   141
22   4      2       8      Yes   141
23   4      3       8      Yes   141
24   4      4       8      Yes   141
25   5      1       7       No   123
26   5      2       7      Yes   128
27   5      3       9       No    66
28   5      4      10       No   123
29   5      5      11      Yes   128
30   5      6      12      Yes    66
31   5      7      13      Yes   120
32   5      8      14      Yes   141
33   5      9      15       No    52

Create a dataframe with repeated rows then concatenate to the original one and finally sort it:

import numpy as np
cycle_threshold = 3
def fix_cycle(df):
lgt = len(df)
if lgt < cycle_threshold:
df1 = df.reindex(df.index[-1].repeat(cycle_threshold - lgt + 1))
df1['cycle'] += np.arange(len(df1)) + 1
return df1
df1 = df.groupby('id').apply(fix_cycle).droplevel(0)
out = pd.concat([df, df1]).sort_index(kind='stable', ignore_index=True)

Output:

>>> out
id  cycle  Salary Children  Days
0    1      1       7       No   123
1    1      2       7      Yes   128
2    1      3       7      Yes    66
3    1      4       8      Yes    66
4    1      5       9      Yes   120
5    1      6      10       No   141
6    1      7      11       No    52
7    1      8      12      Yes    96
8    1      9      13      Yes   120
9    1     10      14      Yes   141
10   1     11      15       No    52
11   2      1       4      Yes    96
12   2      2       5       No   120
13   2      3       5       No   120  # <- HERE
14   2      4       5       No   120  # <- HERE
15   3      1       8       No    15
16   3      2       9      Yes   123
17   3      3      10      Yes   128
18   3      4      11       No    66
19   3      5      12       No   120
20   3      6      13      Yes   141
21   4      1       8      Yes   141
22   4      2       8      Yes   141  # <- HERE
23   4      3       8      Yes   141  # <- HERE
24   4      4       8      Yes   141  # <- HERE
25   5      1       7       No   123
26   5      2       7      Yes   128
27   5      3       9       No    66
28   5      4      10       No   123
29   5      5      11      Yes   128
30   5      6      12      Yes    66
31   5      7      13      Yes   120
32   5      8      14      Yes   141
33   5      9      15       No    52

Details:

>>> df1
id  cycle  Salary Children  Days
12   2      3       5       No   120
12   2      4       5       No   120
19   4      2       8      Yes   141
19   4      3       8      Yes   141
19   4      4       8      Yes   141