# Generating a sequence based on value in another column in Python

I have the following data frame:

I would like to add a column with value equal to 1 if flag is 0 and incrementally add 1 in the following rows until the next 0 is encountered (as given in the example below).

I have been able to generate the sequence, but the code is extremely slow, so is there a faster way to generate the sequence?

• Can you show us your attempt? Feb 24, 2019 at 9:25

I don't know how you have tested it the first time, here is my logic. It supposes the first element in flag is 0!

df = pd.DataFrame({'memberid': [1]*11,
'flag': [0,0,1,1,0,1,0,0,0,1,1],
})
df['seq'] = ""
for i in range(0, len(df)):
df.loc[i, 'seq'] = 1 if df.loc[i, 'flag'] == 0 else  df.loc[i - 1, 'seq'] + 1
print(df)


Another solution using lambda:

df = pd.DataFrame({'memberid': [1] * 11,
'flag': [0, 0, 1, 1, 0, 1, 0, 0, 0, 1, 1]
})

def f(flag):
global previous_seq
previous_seq = 1 if flag == 0 else previous_seq + 1
return previous_seq

previous_seq = 0
df['seq'] = df[['flag']].apply(lambda x: f(*x), axis=1)
print(df)


I am not sure if it is faster than the first solution....

Use this single line of code which is superfast because just using the dict with update() function which takes O(1) time and lambda function overall takes O(N) times so O(n) * O(1) = O(N)

b = {}
b[0] = 1
df['flags'].map(lambda x : [b.update({0 : 1}) , x+1][1] if x < 1 else (lambda : ([x+b[0] , b.update({0 : b[0]+1})][0]))())


This would work superfast.

I've tried this 3 ways iterating through the flags (counting the continuous 1s) and this was the fastest for the same dataframe (large enough to neglect small variation in time on retries).

I'm maintaining two lists (one for flag, one for seq) and a counter variable.

We loop through flags and append the corresponding seq to seq_list. As you described, we keep track of count if we keep seeing 1 and reset to 1 if we see a 0 in the flags_list.

We add seq_list as a column to the dataframe once we're done.

seq_list = []
counter = 0
flag_list = list(df['flag'])
for flag in flag_list:
if(flag == 0):
counter = 1
seq_list.append(counter)
else:
counter += 1
seq_list.append(counter)

df['seq'] = seq_list


Other variants I tried include

• Directly iterating through each flag element in the dataframe (using .loc) and adding to the seq_list which gets added as a column but this took 16x of the time for the method above.
seq_list = []
counter = 0

for i in range(df.shape[0]):
if(df.loc[i,'flag'] == 0):
counter = 1
seq_list.append(counter)
else:
counter += 1
seq_list.append(counter)

df['seq'] = seq_list

• Directly iterating through each flag element in the dataframe (using .loc) and modifying the dataframe right away (again, using .loc) but this was even slower and took 5x-10x the time for the previous method.
counter = 1
df['seq'] = 0

for i in range(df.shape[0]):
if(df.loc[i,'flag'] == 0):
counter = 1
df.loc[i,'seq'] = counter
else:
counter += 1
df.loc[i,'seq'] = counter