Pandas creating new column based on consecutive duplicates

user_4422966 0
Viewed 752

I have a Pandas dataframe like the one below, where column A is a series of strings, and values in column B are true/false depending on whether the value of column A is the same as the value of column A in the previous row.

A    B       
1    False          
1    True             
1b   False          
1b   True                
1b   True    
1    False

I want to add a new column, C, that assigns the same value (it can be any value) to all consecutive duplicate entries, but this value must be unique from the values assigned to other groups of consecutive duplicate entries. For example:

A    B        C
1    False    1       
1    True     1         
1b   False    2         
1b   True     2            
1b   True     2
1    False    3

Any thoughts about how to go about this in an efficient way?

pandas shift boolean numpy python
3 Answers

Try with groupby ngroup + 1 be sure to sort=False to make sure groups are created in the order they appear in the DataFrame:

df['C'] = df.groupby(['A', (~df['B']).cumsum()], sort=False).ngroup() + 1

    A      B  C
0   1  False  1
1   1   True  1
2  1b  False  2
3  1b   True  2
4  1b   True  2
5   1  False  3

Or either of the following can be used directly assuming values appear in sorted order with cumsum:

df['C'] = (~df['B']).cumsum()

    A      B  C
0   1  False  1
1   1   True  1
2  1b  False  2
3  1b   True  2
4  1b   True  2
5   1  False  3

This would be equivalent to:

df['A'].ne(df['A'].shift()).cumsum()

0    1
1    1
2    2
3    2
4    2
5    3
Name: A, dtype: int32

Which would be the standard way to solve this problem if the B column was not already calculated.

user_154978880

Try with shift combine with cumsum

df['C'] = df.A.ne(df.A.shift()).cumsum()
Out[191]: 
0    1
1    1
2    2
3    2
4    2
5    3
Name: A, dtype: int64
user_79645270

I think that's what you are looking for.

df['C'] = df.groupby('A').ngroup() + 1
user_85002140
Related