How to groupby consecutive values in pandas DataFrame
16,979
Solution 1
You can use groupby
by custom Series
:
df = pd.DataFrame({'a': [1, 1, -1, 1, -1, -1]})
print (df)
a
0 1
1 1
2 -1
3 1
4 -1
5 -1
print ((df.a != df.a.shift()).cumsum())
0 1
1 1
2 2
3 3
4 4
5 4
Name: a, dtype: int32
for i, g in df.groupby([(df.a != df.a.shift()).cumsum()]):
print (i)
print (g)
print (g.a.tolist())
a
0 1
1 1
[1, 1]
2
a
2 -1
[-1]
3
a
3 1
[1]
4
a
4 -1
5 -1
[-1, -1]
Solution 2
Using groupby
from itertools
data from Jez
from itertools import groupby
[ list(group) for key, group in groupby(df.a.values.tolist())]
Out[361]: [[1, 1], [-1], [1], [-1, -1]]
Solution 3
Series.diff
is another way to mark the group boundaries (a!=a.shift
means a.diff!=0
):
consecutives = df['a'].diff().ne(0).cumsum()
# 0 1
# 1 1
# 2 2
# 3 3
# 4 4
# 5 4
# Name: a, dtype: int64
And to turn these groups into a Series of lists (see the other answers for a list of lists), aggregate with groupby.agg
or groupby.apply
:
df['a'].groupby(consecutives).agg(list)
# a
# 1 [1, 1]
# 2 [-1]
# 3 [1]
# 4 [-1, -1]
# Name: a, dtype: object
Author by
Bryan Fok
Updated on July 23, 2022Comments
-
Bryan Fok almost 2 years
I have a column in a DataFrame with values:
[1, 1, -1, 1, -1, -1]
How can I group them like this?
[1,1] [-1] [1] [-1, -1]
-
Eran H. over 5 yearsIn case you want to use this solution to
.groupby()
consecutive dates with 1 hour difference, change the condition todf['date'].diff() != pd.Timedelta('1 hour')
-
XoXo about 5 yearsthis answer is more explicit than the accepted
cumsum()
solution -
XoXo about 5 yearsfrom the document:
The operation of groupby() is similar to the uniq filter in Unix. It generates a break or new group every time the value of the key function changes
-
XoXo about 5 yearsgithub.com/pandas-dev/pandas/issues/5494 asks for the same behaviour with the
itertools.groupby()
, but it'sContributions Welcome, No action on 6 Jul 2018
-
smci over 4 yearsInstead of
==
, there's actually a vectorized.ne()
function:df.a.ne(df.a.shift())
-
Rich Andrews almost 4 yearsWhile this is a literal answer to the question, it loses the oft-needed labeling of the group of consecutive values.