Home > Back-end >  Change and add values in dataframe depending on order ID
Change and add values in dataframe depending on order ID

Time:02-03

So my table currently looks like this:

Roll No Subject
1 Math
1 Science
2 History
2 Geography
2 Politics
3 FInance

Based on this I want to add a new column

Roll No Subject Details
1 Math Other subject to Math
1 Science Math to Science
2 History Other subject to History
2 Geography History to Geography
2 Politics Geography to Politics
3 Finance Other subject to Finance

I am really confused as to how this would be. I am able to write the first instance of a roll no as "Other subject to xyz" but cannot seem to understand how the following values will carry over. Thank you

CodePudding user response:

Using df.groupby() and .transform() methods with lambda

df["Details"] = (df.groupby("Roll No")
                    .Subject.transform(lambda x: x.shift()
                    .add(" to ").fillna("Other subject to ")   x))
print(df)

   Roll No    Subject                   Details
0        1       Math     Other subject to Math
1        1    Science           Math to Science
2        2    History  Other subject to History
3        2  Geography      History to Geography
4        2   Politics     Geography to Politics
5        3    Finance  Other subject to Finance

CodePudding user response:

You can use only pandas.DataFrame.shift on columns "Roll No", "Subject" then use numpy.where.

import numpy as np

ss = df['Subject'].shift()
sr = df['Roll No'].shift()
r1 = ss   ' to '   df['Subject']
r2 = 'Other subject to '   df['Subject']
df['res'] = np.where(sr==df['Roll No'], r1, r2)
print(df)

Output:

   Roll No    Subject                       res
0        1       Math     Other subject to Math
1        1    Science           Math to Science
2        2    History  Other subject to History
3        2  Geography      History to Geography
4        2   Politics     Geography to Politics
5        3    FInance  Other subject to FInance
  • Related