Python Pandas score Multiple Choice items from a key

Question

I'm newish to the Pandas library in Python and I'm trying to score student responses to a set of Multiple Choice items read from a csv file (much larger number of items and students) and I have a key. I wrote base python code that does this easily and I've included it to help make sure it's clear what I'm trying to do:

#Create example data 5 items for 6 students
A,B,C,D = "A","B","C","D"
df =  [[A,B,C,D,C],
       [A,B,C,B,C],
       [A,D,D,B,C],
       [A,B,C,C,C],
       [A,B,B,D,C],
       [A,B,C,D,C]]
# Score the items and add to data
key = ["A","B","C","D","C"]
for line in df:
    score = sum([1 for i,j in zip(line,key) if i==j])
    line = line.append(score)

# Now print the example for clarity
print ("I1 I2 I3 I4 I5 Score")
for i in df:
    for j in i:
        print (j, end="  ")
    print()

Which prints this, which is what I want to learn to do in Pandas:

I1 I2 I3 I4 I5 Score
A  B  C  D  C  5  
A  B  C  B  C  4  
A  D  D  B  C  2  
A  B  C  C  C  4  
A  B  B  D  C  4  
A  B  C  D  C  5

So here is my start, but clearly I have much to learn:

import pandas as pd
d = {'I1': {0: 'A', 1: 'A', 2: 'A', 3: 'A', 4: 'A', 5: 'A'},
 'I2': {0: 'B', 1: 'B', 2: 'D', 3: 'B', 4: 'B', 5: 'B'},
 'I3': {0: 'C', 1: 'C', 2: 'D', 3: 'C', 4: 'B', 5: 'C'},
 'I4': {0: 'D', 1: 'B', 2: 'B', 3: 'C', 4: 'D', 5: 'D'},
 'I5': {0: 'C', 1: 'C', 2: 'C', 3: 'C', 4: 'C', 5: 'C'}}
df = pd.DataFrame(d)
key = ["A","B","C","D","C"]
df['Score'] = sum ([1 for x,y in zip(df.iloc[:,0:5],key[:]) if x==y])
print (df)

But, clearly I fail:

  I1 I2 I3 I4 I5  Score
0  A  B  C  D  C      0
1  A  B  C  B  C      0
2  A  D  D  B  C      0
3  A  B  C  C  C      0
4  A  B  B  D  C      0
5  A  B  C  D  C      0

Thanks for trying to educate me...

user_15239951 · Answer

IIUC, you are looking for:

df['Score'] = df.eq(key).sum(axis=1)  # equivalent to (df == key).sum(axis=1)
print(df)

# Output
  I1 I2 I3 I4 I5  Score
0  A  B  C  D  C      5
1  A  B  C  B  C      4
2  A  D  D  B  C      2
3  A  B  C  C  C      4
4  A  B  B  D  C      4
5  A  B  C  D  C      5

Your dataframe has 5 columns and you have a list (a vector) of 5 elements. Pandas is smart enough to compare each row to your list and return a boolean values. Finally sum True values on index axis to get the score.

Python Pandas score Multiple Choice items from a key

1 Answers