python - Fill in NaN values in dataframe intervals according to conditions of a column

Question

I have a dataframe with me which has an ID column and description column with START and STOP values represented by the ID's. Every START-STOP pair is denoted by an ID and it is incremented to 1 on next appearance of the pair.

I need to increment the ID by 1 right after the STOP element has occured (update the NaN's of course) and this same ID should continue till I find the next STOP. Also how to take care of the first START-STOP pair since the main problem focuses on covering STOP-STOP event? Also, any number of events or segments can be there inside the START-STOP or a STOP-STOP pair

I would like to have like this in the end

This kind of needs to be applied for hundreds of thousands of rows and not a bunch of rows shown as sample. Kindly help me on this! Thanks in advance :)

question from:https://stackoverflow.com/questions/66059546/fill-in-nan-values-in-dataframe-intervals-according-to-conditions-of-a-column

Answer 1

# create a group-tag by every STOP
cond = df.SEG_DESC == 'STOP'
df['tag'] = cond.cumsum()
df.loc[cond, 'tag'] = df.loc[cond, 'tag'] - 1

# for every tag-group use back fillna
df['ID_START_STOP'] = df.groupby('tag')['ID_START_STOP'].bfill().astype(int)