Comparing 'key answer' and 'answers' DataFrame in Pandas

user_14133605 0
Viewed 154

I have this main df1:

Name | Course | Q_1 | Q_2 | ... | Q_60
John |  Phys  |  A  |  C  | ... |  D 
Karen|  Math  |  C  |  C  | ... |  E
 ... |  ...   | ... | ... | ... | ...

(~1200 names)

The key answer for reference is df2:

1   2   3   4   ...   60
A | C | C | E | ... | D

I want to compare df1 with df2 to answer questions of this type:

  • Which questions did the students answer correctly?
  • How many students got Q_3, Q_4, Q_5 and Q_10 right?

I've already tried to simply do conditional compare but this only gives me a np.array of booleans: is it possible to index the position of True/False matches to any given answer, returning something like:

df3:

Name | Course | Q_1 | Q_2 | ... |Q_60
John |  Phys  |True |True | ... |True
Karen|  Math  |False|True | ... |False
...........

And then make a conditional count of the True matches storing its position to get the solution?

pandas rows dataframe python
1 Answers

Use add_preffix and eq:

t=df2.add_prefix('Q_').iloc[0]
df1.set_index(['Name','Course']).eq(t,1).reset_index()

Example

With dummy data:

print(df1)
   Name Course Q_1 Q_2 Q_3
0   John   Phys   A   C   D
1  Karen   Math   C   C   E

print(df2)
   1  2  3
0  A  C  C

t=df2.add_prefix('Q_').iloc[0]
df1.set_index(['Name','Course']).eq(t,1).reset_index()

    Name Course    Q_1   Q_2    Q_3
0   John   Phys   True  True  False
1  Karen   Math  False  True  False
user_136762020
Related