Pandas DataFrame group by consecutive same values on multiple columns-CodePudding

I need to regroup consecutive rows with same values for a list of columns. Thanks to this I've found how to do it for one column, but I can't make it work for more than one.

My question is quite close from this one but I can't make it work as I'd like either.

Here is a working snippet where I need the columns user, group, value1 and value2 to be identical to regroup the rows:

#! /bin/python3

import pandas as pd

data = [{"user":"paul","group":"accounting","value1":"foo","value2":3,"value3":"random123"},{"user":"paul","group":"accounting","value1":"foo","value2":3,"value3":"random456"},{"user":"paul","group":"accounting","value1":"foo","value2":3,"value3":"random789"},{"user":"paul","group":"accounting","value1":"foo","value2":5,"value3":"random789"},{"user":"paul","group":"accounting","value1":"foo","value2":5,"value3":"random789"},{"user":"paul","group":"accounting","value1":"foo","value2":5,"value3":"random158"},{"user":"jack","group":"administration","value1":"foo","value2":5,"value3":"random487"},{"user":"jack","group":"administration","value1":"foo","value2":5,"value3":"random435"},{"user":"jack","group":"administration","value1":"bar","value2":3,"value3":"random483"},{"user":"jack","group":"administration","value1":"foo","value2":3,"value3":"random431"},{"user":"jack","group":"administration","value1":"foo","value2":3,"value3":"random478"},{"user":"paul","group":"accounting","value1":"foo","value2":5,"value3":"random759"},{"user":"jack","group":"administration","value1":"bar","value2":3,"value3":"random431"},{"user":"jack","group":"administration","value1":"foo","value2":3,"value3":"random478"}]

df = pd.DataFrame(data)
print(df)
print("----")
grouped = df.groupby(((df['value2'].shift() != df['value2'])).cumsum())
for k, v in grouped:
    print(f'[group {k}]')
    print(v)

It outputs this:

[group 1]
   user       group value1  value2     value3
0  paul  accounting    foo       3  random123
1  paul  accounting    foo       3  random456
2  paul  accounting    foo       3  random789
[group 2]
   user           group value1  value2     value3
3  paul      accounting    foo       5  random789
4  paul      accounting    foo       5  random789
5  paul      accounting    foo       5  random158
6  jack  administration    foo       5  random487
7  jack  administration    foo       5  random435
[group 3]
    user           group value1  value2     value3
8   jack  administration    bar       3  random483
9   jack  administration    foo       3  random431
10  jack  administration    foo       3  random478
[group 4]
    user       group value1  value2     value3
11  paul  accounting    foo       5  random759
[group 5]
    user           group value1  value2     value3
12  jack  administration    bar       3  random431
13  jack  administration    foo       3  random478

But I need this:

[group 1]
   user       group value1  value2     value3
0  paul  accounting    foo       3  random123
1  paul  accounting    foo       3  random456
2  paul  accounting    foo       3  random789
[group 2]
   user           group value1  value2     value3
3  paul      accounting    foo       5  random789
4  paul      accounting    foo       5  random789
5  paul      accounting    foo       5  random158
[group 3]
    user           group value1  value2     value3
6  jack  administration    foo       5  random487
7  jack  administration    foo       5  random435
[group 4]
    user           group value1  value2     value3
8   jack  administration    bar       3  random483
[group 5]
    user           group value1  value2     value3
9   jack  administration    foo       3  random431
10  jack  administration    foo       3  random478
[group 6]
    user       group value1  value2     value3
11  paul  accounting    foo       5  random759
[group 7]
    user           group value1  value2     value3
12  jack  administration    bar       3  random431
[group 8]
    user           group value1  value2     value3
13  jack  administration    foo       3  random478

I tried multiple columns in the groupby but to no avail:

grouped = df.groupby(((df[['user', 'value2']].shift() != df[['user', 'value2']])).cumsum())

#returns
ValueError: Grouper for '<class 'pandas.core.frame.DataFrame'>' not 1-dimensional

CodePudding user response：

Create consecutive groups by compare columns from list with DataFrame.any and then add cumulative sum:

cols = ['user','group','value1','value2']

grouped = df.groupby(((df[cols].shift() != df[cols]).any(axis=1)).cumsum())
for k, v in grouped:
    print(f'[group {k}]')
    print(v)

[group 1]
   user       group value1  value2     value3
0  paul  accounting    foo       3  random123
1  paul  accounting    foo       3  random456
2  paul  accounting    foo       3  random789
[group 2]
   user       group value1  value2     value3
3  paul  accounting    foo       5  random789
4  paul  accounting    foo       5  random789
5  paul  accounting    foo       5  random158
[group 3]
   user           group value1  value2     value3
6  jack  administration    foo       5  random487
7  jack  administration    foo       5  random435
[group 4]
   user           group value1  value2     value3
8  jack  administration    bar       3  random483
[group 5]
    user           group value1  value2     value3
9   jack  administration    foo       3  random431
10  jack  administration    foo       3  random478
[group 6]
    user       group value1  value2     value3
11  paul  accounting    foo       5  random759
[group 7]
    user           group value1  value2     value3
12  jack  administration    bar       3  random431
[group 8]
    user           group value1  value2     value3
13  jack  administration    foo       3  random478