CODEWITHSUNDEEP
pythonpandasdataframe
Quincy
Quincy

Reputation: 143

How to add a column to show transition from one row to the next for a pandas dataframe?

I have the following dataframe:

df = pd.DataFrame( columns = ['Name','Level']) 
df['Name'] = ['Ahmed','Ahmed','Ahmed','Ahmed','Beth','Beth','Beth','Beth','Carlos','Carlos','Carlos','Carlos']
df['Level'] =['a','b','c','d','a','b','c','d','a','b','c','d']

I would like to show the change in level by row for each name and convey it in an additional column as follows:

df['Expected Result'] = ['a','a to b','b to c','c to d','a','a to b','b to c','c to d','a','a to b','b to c','c to d']

How would I be able to achieve this?

Upvotes: 1

Views: 144

Answers (2)

mrammah
mrammah

Reputation: 225

Could you please explain what you're trying to achieve because I'm sure there is a much more elegant and meaningful approach to whatever problem you're actually trying to solve. At any rate, this code should achieve what you want for now:

import pandas as pd

def createTransition(name, level):
    name_holder = []
    result = []
    for idx in range(len(name)-1):
        if (name[idx] not in name_holder) :
            result.append(level[idx])
            name_holder.append(name[idx])
        if name[idx] in name_holder and name[idx + 1] not in name_holder:
            continue
        else:
            result.append(f"{level[idx]} to {level[idx + 1]}")
    return result

df["expected_result"] = createTransition(df["Name"], df["Level"])

Upvotes: 0

jezrael
jezrael

Reputation: 862601

Use DataFrameGroupBy.shift with add original values and last replace first values per groups by original column in Series.fillna:

df['Expected Result1'] = ((df.groupby('Name')['Level'].shift() + ' to ' + df['Level'])
                            .fillna(df['Level']))

Another solution:

df['Expected Result1'] =(df.groupby('Name')['Level'].shift().str.cat(df['Level'],sep=' to ')
                           .fillna(df['Level']))

print (df)
      Name Level Expected Result Expected Result1
0    Ahmed     a               a                a
1    Ahmed     b          a to b           a to b
2    Ahmed     c          b to c           b to c
3    Ahmed     d          c to d           c to d
4     Beth     a               a                a
5     Beth     b          a to b           a to b
6     Beth     c          b to c           b to c
7     Beth     d          c to d           c to d
8   Carlos     a               a                a
9   Carlos     b          a to b           a to b
10  Carlos     c          b to c           b to c
11  Carlos     d          c to d           c to d

Upvotes: 1

Related Questions