How to assign a unique id for a sequence of repeated column value in pandas dataframe?

Question

I have a dataframe call it dfA,

ID   Chronological   Label         
1    1               None         
2    0               ONPEAPLFPH   
3    0               JFECJGSQNS   
4    1               None         
5    1               None         
6    0               MGMELTIVTJ    
7    1               None         
8    1               None         
9    1               None

I want to assign a unique_id to the column Chronological such that each consequent repeated values has a "common" unique_id. That is I want the following desired output,

ID   Chronological   Label         unique_id 
1    1               None          1
2    0               ONPEAPLFPH    2
3    0               JFECJGSQNS    3
4    1               None          4
5    1               None          4
6    0               MGMELTIVTJ    5 
7    1               None          6
8    1               None          6
9    1               None          6

I tried using a non-vectorized solution using for-loop but it is really slow,

starting_index = 0
unique_id = 1
dfs = []
for cL in dfA['Label'].unique():
    if cL != "None":
        current_index = dfA[dfA['Label']==cL].index.values[0]
        sliced_df = dfA.iloc[starting_index:current_index+1, :]
        sliced_df_ = sliced_df.copy()
        if len(sliced_df_)>=1:
            sliced_df_['unique_id'] = unique_id
            starting_index = current_index
            unique_id += 1
            dfs.append(sliced_df_)
df_concat = pd.concat(dfs, axis=0)

Is there a more efficient way to solve it?

Scott Boston · Accepted Answer · 2021-11-27 21:41:43Z

2

Try this:

df['unique_id'] = (df['Chronological'].eq(0) | 
                   (df['Chronological'] != df['Chronological'].shift())
                  ).cumsum()

Output:

   ID  Chronological       Label  unique_id
0   1              1        None          1
1   2              0  ONPEAPLFPH          2
2   3              0  JFECJGSQNS          3
3   4              1        None          4
4   5              1        None          4
5   6              0  MGMELTIVTJ          5
6   7              1        None          6
7   8              1        None          6
8   9              1        None          6

answered Nov 27, 2021 at 21:41

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

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

Collectives™ on Stack Overflow

How to assign a unique id for a sequence of repeated column value in pandas dataframe?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related