Pandas group by with multiple groups for same value

user_14997469 0
Viewed 376

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?

pandas python
2 Answers

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
user_102912910

You could also try this:

(df.groupby([(df.quantity != df.quantity.shift()).cumsum(), df.quantity])
  .agg({'period': ['min', 'max']})
  .droplevel(0)
  .reset_index())
user_13627350
Related