I have the following code that generates the two columns.
import pandas as pd
data = {'Group': ['1', '1', '1', '1', '1', '1',
'2', '2', '2', '2', '2', '2',
'3', '3', '3', '3', '3', '3',
'4', '4', '4', '4', '4', '4',],
'Test1': ['ABC', 'CDE', 'EFG', 'GHI', 'IJK', 'KLM',
'MNO', 'OPQ', 'QRS', 'STU', 'UVW', 'WXYZ',
'ABC', 'CDE', 'EFG', 'GHI', 'IJK', 'KLM',
'MNO', 'OPQ', 'QRS', 'STU', 'UVW', 'WXYZ',],
'Test2': ['1234','4567', '8910', '1112', '1314', '1415',
'1516', '1718', '1920', '2122', '2324', '2526',
'2728', '2930', '3132', '3334', '3536', '3738',
'2940', '4142', '4344', '4546', '4748', '4950'],
'Value': [True, True, False, False, False, True,
True, True, True, True, True, True,
True, True, True, True, True, False,
True, True, True, False, True, True,],
}
df = pd.DataFrame(data)
print(df)
So, by checking the last 2, 3, or 4 rows in each group if they return False, I want to return False. And if all the values are True then, I want to return true for all rows. From the above code, the expected outcome is this. If we check for the last 3 rows in each group
Group | Value
----- | -----
1 | False
1 | False
1 | False
2 | True
2 | True
2 | True
3 | False
3 | False
3 | False
4 | False
4 | False
4 | False
Update, per updated question and comments below:
df[['Test1','Test2']].merge(df.groupby('Group')['Value'].apply(lambda x: x.iloc[-3:].mul(x.iloc[-3:].min(), level=0))\
.reset_index(), left_index=True, right_on='level_1').drop('level_1', axis=1)
Output:
Test1 Test2 Group Value
0 GHI 1112 1 False
1 IJK 1314 1 False
2 KLM 1415 1 False
3 STU 2122 2 True
4 UVW 2324 2 True
5 WXYZ 2526 2 True
6 GHI 3334 3 False
7 IJK 3536 3 False
8 KLM 3738 3 False
9 STU 4546 4 False
10 UVW 4748 4 False
11 WXYZ 4950 4 False
IIUC, try this:
df.groupby('Group')['Value'].apply(lambda x: x.iloc[-3:].mul(x.iloc[-3:].min(), level=0))\
.reset_index()\
.drop('level_1', axis=1)
Output:
Group Value
0 1 False
1 1 False
2 1 False
3 2 True
4 2 True
5 2 True
6 3 False
7 3 False
8 3 False
9 4 False
10 4 False
11 4 False