Pandas: evaluate two dataframes against each other

Question

Let's imagine a remember/recall game. There are rooms with a table full of chocolate and other stuff. You have 20seconds to look at each table. Later on you are asked what you've seen. This gives two datasets. One with the configuration of the tables and another one of what you can remember. The task is to evaluate which items you have recalled correctly and which one's you can not remember. In this task we don't care about the brand of the product. Just the type.

Here's a sample configuration for two rooms.

config = [
        {'room': 'room1', 'kind': 'chocolate', 'brand': 'Mars'},
        {'room': 'room1', 'kind': 'chocolate', 'brand': 'Mars'},
        {'room': 'room1', 'kind': 'chocolate', 'brand': 'Milka'},
        {'room': 'room1', 'kind': 'nuts', 'brand': 'Bahlsen'},
        {'room': 'room2', 'kind': 'chocolate', 'brand': 'Mars'},
        {'room': 'room2', 'kind': 'nuts', 'brand': 'Ültje'},
        {'room': 'room2', 'kind': 'nuts', 'brand': 'Bahlsen'}
        ]

import pandas as pd
df_config = pd.DataFrame(config).sort_values(['room'])
df_config

Now you got 20second to memorize the times. Afterwards you are asked what you've seen. Here's what you remember:

recall = [
        {'room': 'room1', 'kind': 'chocolate'},
        {'room': 'room1', 'kind': 'chocolate'},
        {'room': 'room1', 'kind': 'nuts'},
        {'room': 'room2', 'kind': 'nuts'}
        ]

import pandas as pd
df_recall = pd.DataFrame(recall).sort_values(['room'])
df_recall

Obviously you've seen two chocolate bars in room 1 thus you missed the third one. For room 2 you missed the second bag of nuts. So, the evaluation result would be somewhat like this:

correct = [
        {'room': 'room1', 'kind': 'chocolate', 'brand': 'Mars', 'eval': 'correct'}, # first chocolate room1
        {'room': 'room1', 'kind': 'chocolate', 'brand': 'Mars', 'eval': 'correct'}, # second chocolate room1
        {'room': 'room1', 'kind': 'nuts', 'brand': 'Bahlsen', 'eval': 'correct'}, # first nuts room1
        {'room': 'room2', 'kind': 'nuts', 'brand': 'Ültje', 'eval': 'correct'}, # first nuts room2
        ]

incorrect = [
        {'room': 'room1', 'kind': 'chocolate', 'brand': 'Milka', 'eval': 'incorrect'}, # third chocolate room1 not recalled
        {'room': 'room2', 'kind': 'chocolate', 'brand': 'Mars', 'eval': 'incorrect'}, # first chocolate room2 not recalled
        {'room': 'room2', 'kind': 'nuts', 'brand': 'Bahlsen', 'eval': 'incorrect'} # second nuts room2 not recalled
        ]

I was thinking to merge both datasets based on the room and then group by the room and evaluate each group. Either by iterating over the group or using df.groupy(['room']).apply(my_function). The problem is, that the merge creates a quite huge group for each room and I'm not sure how to evaluate this.

df = pd.merge(df_config, df_recall, on='room', suffixes=('', '_recall'))

Any idea is welcome!

Thanks

Pandas: evaluate two dataframes against each other

1 Answers