How can I use a value in a dataframe to look up an attribute

Say I have the 2 Dataframes below; one with a list of students and test scores, and different student sessions that made up of the students. Say I want to add a new column, “Sum”, to df with the sum of the scores for each session and a new column for the number of years passed since the most recent year that either student took the test, “Years Elapsed”. What is the best way to accomplish this? I can make the students a class and make each student an object but then I am stuck on how to link the object to their name in the dataframe.

data1 = {'Student': ['John','Kim','Adam','Sonia'],
        'Score': [92,100,76,82],
        'Year': [2015,2013,2016,2018]}
df_students = pd.DataFrame(data1, columns=['Student','Score','Year'])

data2 = {'Session': [1,2,3,4],
        'Student1': ['Sonia','Kim','John','Adam'],
        'Student2': ['Adam','Sonia','Kim','John']}

df = pd.DataFrame(data2, columns=['Session','Student1','Student2'])

The desired outcome:

outcome = {'Session': [1,2,3,4],
        'Student1': ['Sonia','Kim','John','Adam'],
        'Student2': ['Adam','Sonia','Kim','John'],
        'Sum':[158, 182, 192, 168],
        'Years Elapsed': [4,4,7,6]}

df_outcome = pd.DataFrame(outcome, columns=['Session','Student1','Student2','Sum','Years Elasped'])

I have made a Class called Students and made each student an object but after this is where I am stuck.

df_students.columns= df_students.columns.str.lower()

class Student:
    def __init__(self, s, sc, yr):
        self.student = s
        self.score = sc
        self.year = yr

students = [Student(row.student, row.score, row.year) for index, row in df_students.iterrows()]

#check to see if list of objects was created correctly
s1 = students[1] 


Thanks in advance!


Reliance Jio Brings Back Rs 499 Plan with OTT Benefit, 2GB Daily Data

Plask — Free AI motion capture and animation tool on a browser