2

I have the following dataframe, that I need to group into three groups (two groups with quantity=0) while returning the min/max period for each group.

    period  quantity
0        1         0
1        2         0
2        3         0
3        4         0
4        5         0
5        6         0
6        7         0
7        8        10
8        9        10
9       10        10
10      11        10
11      12        10
12      13        10
13      14        10
14      15        10
15      16        10
16      17         0
17      18         0
18      19         0
19      20         0
20      21         0
21      22         0
22      23         0
23      24         0

What I need is:

    quantity    min   max
0          0      1     7
1         10      8    16
2          0     17    24

What can I try?

halfer
  • 19,824
  • 17
  • 99
  • 186
man1la
  • 35
  • 2
  • Does this answer your question? [How to groupby consecutive values in pandas DataFrame](https://stackoverflow.com/questions/40802800/how-to-groupby-consecutive-values-in-pandas-dataframe) – Alex Aug 26 '21 at 08:51

2 Answers2

5

Here's one solution, based on this answer.

(
    df.groupby([df["quantity"].diff().ne(0).cumsum(), df["quantity"]])["period"]
    .aggregate(["min", "max"])
    .droplevel(0)
    .reset_index()
)

returning

   quantity  min  max
0         0    1    7
1        10    8   16
2         0   17   24
Alex
  • 6,610
  • 3
  • 20
  • 38
Quixotic22
  • 2,894
  • 1
  • 6
  • 14
  • 1
    The second element in the groupby argument list can just be the string "quantity". – w-m Aug 26 '21 at 09:24
0

You could also try this:

(df.groupby([(df.quantity != df.quantity.shift()).cumsum(), df.quantity])
  .agg({'period': ['min', 'max']})
  .droplevel(0)
  .reset_index())
heretolearn
  • 6,387
  • 4
  • 30
  • 53