create a 'group number' column for a pandas data frame column of '0' and '1' s

Question

How to get the data frame below

dd = pd.DataFrame({'val':[0,0,1,1,1,0,0,0,0,1,1,0,1,1,1,1,0,0],
             'groups':[1,1,1,1,1,2,2,2,2,2,2,3,3,3,3,3,'ignore','ignore']})

     val    groups
0     0       1
1     0       1
2     1       1
3     1       1
4     1       1
5     0       2
6     0       2
7     0       2
8     0       2
9     1       2
10    1       2
11    0       3
12    1       3
13    1       3
14    1       3
15    1       3
16    0  ignore
17    0  ignore

I have a series df.val with has values [0,0,1,1,1,0,0,0,0,1,1,0,1,1,1,1,0,0].
How to create df.groups from df.val.

first 0,0,1,1,1 will form group 1,(i.e. from the beginning upto next occurrence of 0 after 1's)
0,0,0,0,1,1 will form group 2, (incremental group number, starting where previous group ended uptill next occurrence of 0 after 1's),...etc

Can anyone please help.

Duplicate. Does this help you: https://stackoverflow.com/questions/22219004/grouping-rows-in-list-in-pandas-groupby — Boendal, Dec 19 '19 at 13:23
@jezrael does not have `1`'s following zeroes, so have to avoid that group while doing `groupby` later — Shijith, Dec 19 '19 at 13:27
big thanks guys `@jezrael` and `@YO and BEN_W`, couldn't find any solution after trying for more tan 1 hour. — Shijith, Dec 19 '19 at 13:46

jezrael · Accepted Answer · 2019-12-19T13:38:42.533

First test if next value after 0 is 1 and create groups by sumulative sums by Series.cumsum:

s = (dd['val'].eq(0) & dd['val'].shift().eq(1)).cumsum().add(1)

Then convert last group to ignore if last value of data are 0 with numpy.where:

mask = s.eq(s.max()) & (dd['val'].iat[-1] == 0)
dd['new'] = np.where(mask, 'ignore', s)
print (dd)
    val  groups     new
0     0       1       1
1     0       1       1
2     1       1       1
3     1       1       1
4     1       1       1
5     0       2       2
6     0       2       2
7     0       2       2
8     0       2       2
9     1       2       2
10    1       2       2
11    0       3       3
12    1       3       3
13    1       3       3
14    1       3       3
15    1       3       3
16    0  ignore  ignore
17    0  ignore  ignore

score 1 · Answer 2 · answered Dec 19 '19 at 13:28

IIUC first we do diff and cumsum , then we need to find the condition to ignore the previous value we get (np.where)

s=df.val.diff().eq(-1).cumsum()+1
df['New']=np.where(df['val'].eq(1).groupby(s).transform('any'),s,'ignore')
df
    val  groups     New
0     0       1       1
1     0       1       1
2     1       1       1
3     1       1       1
4     1       1       1
5     0       2       2
6     0       2       2
7     0       2       2
8     0       2       2
9     1       2       2
10    1       2       2
11    0       3       3
12    1       3       3
13    1       3       3
14    1       3       3
15    1       3       3
16    0  ignore  ignore
17    0  ignore  ignore

create a 'group number' column for a pandas data frame column of '0' and '1' s

2 Answers2