Home > OS >  Alphabetic ascending filling dataframe column until another column is True/changes, then start again
Alphabetic ascending filling dataframe column until another column is True/changes, then start again

Time:09-07

My aim is to fill "Links" column alphabetically ascending until "Node" changes(or "NewNode"=True)

Here's what i have tried.

import pandas as pd
import numpy as np
import string

data = {'Node': ['Node_1', 'Node_1','Node_1','Node_1','Node_2','Node_2','Node_2']}
df = pd.DataFrame(data=data)

l=list(string.ascii_uppercase)

def link_def(x):
    a = 0
    if x == 'True':
        l[0]
    else:
        a = a 1
        l[a]
    return l[a]
df['NewNode'] = np.where(df['Node'].shift() != df['Node'],True,"")
df['Links'] = df['NewNode'].apply(lambda row : link_def(row))

and this is the output

     Node NewNode Links 
0  Node_1    True     A
1  Node_1             B
2  Node_1             B
3  Node_1             B
4  Node_2    True     A
5  Node_2             B
6  Node_2             B

Desired output is

     Node NewNode Links 
0  Node_1    True     A
1  Node_1             B
2  Node_1             C
3  Node_1             D
4  Node_2    True     A
5  Node_2             B
6  Node_2             C

How can I get the desired output?

CodePudding user response:

You can achieve this with vectorial code, using groupby.cumcount and a mapping:

df['NewNode'] = df['Node'].ne(df['Node'].shift())
df['Links'] = (df.groupby(df['NewNode'].cumsum())
               .cumcount()
               .map(dict(enumerate(string.ascii_uppercase)))
              )

If you don't need the intermediate "NewNode" column:

df['Links'] = (df.groupby(df['Node']
                          .ne(df['Node'].shift())
                          .cumsum())
               .cumcount()
               .map(dict(enumerate(string.ascii_uppercase)))
              )

NB. this is limited to 26 replacements.

output:

     Node  NewNode Links
0  Node_1     True     A
1  Node_1    False     B
2  Node_1    False     C
3  Node_1    False     D
4  Node_2     True     A
5  Node_2    False     B
6  Node_2    False     C
  • Related