Create repeating values index in Pandas dataframe

Question

Suppose I have a df

t status
1 ok
2 ok
3 ok
4 closed
5 closed
6 closed
7 bad input
8 bad input
9 closed
10 closed
11 ok
12 ok
13 closed
14 closed

I want to identify at what time "closed" appears and for how long.

So the result should be

t status    index
1 ok          0
2 ok          0
3 ok          0
4 closed      1
5 closed      1
6 closed      1
7 bad input   0
8 bad input   0
9 closed      2
10 closed     2
11 ok         0
12 ok         0
13 closed     3
14 closed     3

I tried standard "for loop" approach but it is not feasible for large dataframe. I am thinking of a solution using numpy where and repeat

np.where(tmp['status']=='Closed', 1, 0)

I am stuck on adding 1 everytime "Closed" reappears

BENY · Accepted Answer · 2019-07-28 15:32:05Z

2

IIUC we using shift cumsum create the condition

df['New']=0
df.loc[df.status=='closed','New']=(df.status.eq('closed')&df.status.shift().ne('closed')).cumsum()
df
     t    status  New
0    1        ok    0
1    2        ok    0
2    3        ok    0
3    4    closed    1
4    5    closed    1
5    6    closed    1
6    7  badinput    0
7    8  badinput    0
8    9    closed    2
9   10    closed    2
10  11        ok    0
11  12        ok    0
12  13    closed    3
13  14    closed    3

answered Jul 28, 2019 at 15:32

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

anky · Accepted Answer · 2019-07-28 17:14:57Z

1

trying something different:

import more_itertools as mit

s=df[df.status.eq('closed')].index.tolist() #get list of index which matches condition
d={v_:k+1 for k,v in enumerate(mit.consecutive_groups(s)) for v_ in v}
df.assign(New=df.index.map(d).fillna(0).astype(int)) #assign this back df=df.assign(..

     t     status  New
0    1         ok    0
1    2         ok    0
2    3         ok    0
3    4     closed    1
4    5     closed    1
5    6     closed    1
6    7  bad input    0
7    8  bad input    0
8    9     closed    2
9   10     closed    2
10  11         ok    0
11  12         ok    0
12  13     closed    3
13  14     closed    3

answered Jul 28, 2019 at 17:14

anky

75.3k11 gold badges46 silver badges76 bronze badges

Collectives™ on Stack Overflow

Create repeating values index in Pandas dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related