find value for all ids in row and then sum python-CodePudding

I have 2 tables w/ matching ids. I want to return the value in table 2 associated with each col in table 1 and create table 3. I know how to do this in excel using vlookup. I also know that I should use join or merge when trying to do something like vlookup. However, I don't know how to get my desired result here because I can not simply drag the formula to another cell like in excel.

update it would also be helpful to me if I could just return the desired sum rather than the table cols and the sum. So table 3 would just be sum of grades.

I made up a very simple example using fake data. Please see my desired result below.

Table 1
         Student 1   Student 2  Student 3
    0   22882884    22882885    22882945
    1   22882884    22882885    22882935
    
Table 2    
        Student ID Grade
    0   22882884   4.0
    1   22882885   3.5
    2   22882945   2.75
    3   22882935   3.25
Table 3
        Student 1   Student 2  Student 3  Sum of Grades
    0   4.0           3.5       2.75      10.25  
    1   4.0           3.5       3.25      9.75

CodePudding user response：

You can stack, map, and assign the sum:

out = (df1
 .stack()
 .map(df2.set_index('Student ID')['Grade'])
 .unstack()
 .assign(**{'Sum of Grades': lambda d: d.sum(axis=1)})
)

output:

   Student 1  Student 2  Student 3  Sum of Grades
0        4.0        3.5       2.75          10.25
1        4.0        3.5       3.25          10.75

An alternative with broken steps:

s = df2.set_index('Student ID')['Grade']
out = df1.apply(lambda c: c.map(s))
out['Sum of Grades'] = out.sum(axis=1)

CodePudding user response：

You could use itertuples:

df3 = df1.replace(dict(df2.set_index('Student ID')['Grade'].itertuples()))
df3['Sum of Grades'] = df3.sum(1)
 
   student 1  student 2  student 3    Sum of Grades
0        4.0        3.5       2.75            10.25
1        4.0        3.5       3.25            10.75