Unique groupnumber to a dateset by true or false

user_15705988 0
Viewed 16

I was trying for a few days to get my program an step up.

I want to group and count all my groups.

date pump group
2018-01-06 12:01:00 false 0
2018-01-06 12:01:30 true 1
2018-01-06 12:02:00 true 1
2018-01-06 12:02:30 false 0
2018-01-06 12:03:00 true 2
2018-01-06 12:03:30 true 2
2018-01-06 12:04:00 true 2
2018-01-06 12:04:30 false 0

with:

group = df['pump'].eq(0).cumsum()

i get

date pump group
2018-01-06 12:01:00 false 1
2018-01-06 12:01:30 true 1
2018-01-06 12:02:00 true 1
2018-01-06 12:02:30 false 2
2018-01-06 12:03:00 true 2
2018-01-06 12:03:30 true 2
2018-01-06 12:04:00 true 2
2018-01-06 12:04:30 false 3

so the last false is always with the true group.

Thanks fot the help.

pandas-groupby pandas
1 Answers

You can create groups staring by first Trues group by chain shifted mask by Series.shift with cumulative sum by Series.cumsum, last set 0 for False values in Series.where:

df['group1'] = ((df['pump'] & ~df['pump'].shift(fill_value=False)).cumsum()
                                                                  .where(df['pump'], 0))
print (df)
                  date   pump  group  group1
0  2018-01-06 12:01:00  False      0       0
1  2018-01-06 12:01:30   True      1       1
2  2018-01-06 12:02:00   True      1       1
3  2018-01-06 12:02:30  False      0       0
4  2018-01-06 12:03:00   True      2       2
5  2018-01-06 12:03:30   True      2       2
6  2018-01-06 12:04:00   True      2       2
7  2018-01-06 12:04:30  False      0       0

Why is wrong first solution: (seems working in sample)

df['group1'] = (~df['pump']).cumsum().where(df['pump'], 0)
print (df)
                  date   pump  group  group1
0  2018-01-06 12:01:00  False      0       0
1  2018-01-06 12:01:30   True      1       1
2  2018-01-06 12:02:00   True      1       1
3  2018-01-06 12:02:30  False      0       0
4  2018-01-06 12:03:00   True      2       2
5  2018-01-06 12:03:30   True      2       2
6  2018-01-06 12:04:00   True      2       2
7  2018-01-06 12:04:30  False      0       0

but if change data:

df['group1'] = (~df['pump']).cumsum().where(df['pump'], 0)
print (df)
                  date   pump  group  group1
0  2018-01-06 12:01:00  False      0       0
1  2018-01-06 12:01:30  False      0       0 <- changed false
2  2018-01-06 12:02:00   True      1       2 <- starting by 2
3  2018-01-06 12:02:30  False      0       0
4  2018-01-06 12:03:00   True      2       3
5  2018-01-06 12:03:30   True      2       3
6  2018-01-06 12:04:00   True      2       3
7  2018-01-06 12:04:30  False      0       0


df['group1'] = (~df['pump']).cumsum().where(df['pump'], 0)
print (df)
                  date   pump  group  group1
0  2018-01-06 12:01:00   True      1       0 <- changed false, starting true group by 0
1  2018-01-06 12:01:30   True      1       0
2  2018-01-06 12:02:00   True      1       0
3  2018-01-06 12:02:30  False      0       0
4  2018-01-06 12:03:00   True      2       1
5  2018-01-06 12:03:30   True      2       1
6  2018-01-06 12:04:00   True      2       1
7  2018-01-06 12:04:30  False      0       0
user_29010020
Related