CODEWITHSUNDEEP
pythonpandasnumpypandas-groupby
Mykola Zotko
Mykola Zotko

Reputation: 17834

Get value before each consecutive group

I have the following table:

        B      C
0  banana  False
1   apple   True
2  banana   True
3   apple  False
4   apple   True
5  banana   True
6   apple  False
7   apple   True
8   apple   True
9   apple  False

I need to find all consecutive groups in the column 'C' and fill those groups with the value that comes before each of the groups in the column 'B'. I can achieve this with:

consec_groups = (df['C'] != df['C'].shift()).cumsum()
df['Result'] = df['B'].shift().groupby(consec_groups).transform('first')

        B      C  Result
0  banana  False     NaN
1   apple   True  banana
2  banana   True  banana
3   apple  False  banana
4   apple   True   apple
5  banana   True   apple
6   apple  False  banana
7   apple   True   apple
8   apple   True   apple
9   apple  False   apple

How can I do the same if I have another column 'A' to groupby all data. So I want to do the same operation but for each group in the column 'A' separately. For example:

   A       B      C
0  1  banana  False
1  1   apple   True
2  1  banana   True
3  1   apple  False
4  1   apple   True
5  2  banana   True
6  2   apple  False
7  2   apple   True
8  2   apple   True
9  2   apple  False

Desired result:

   A       B      C  Result
0  1  banana  False     NaN
1  1   apple   True  banana
2  1  banana   True  banana
3  1   apple  False  banana
4  1   apple   True   apple
5  2  banana   True     NaN
6  2   apple  False  banana
7  2   apple   True   apple
8  2   apple   True   apple
9  2   apple  False   apple

Upvotes: 0

Views: 41

Answers (1)

BENY
BENY

Reputation: 323286

Same logic just adding additional groupby to B and before first

df['new'] = df['B'].groupby(df['A']).shift().groupby([df.A,consec_groups]).transform('first')
Out[10]: 
0       NaN
1    banana
2    banana
3    banana
4     apple
5       NaN
6    banana
7     apple
8     apple
9     apple
Name: B, dtype: object

Upvotes: 2

Related Questions