I have a dataframe that contains columns of various item recommendations, and the elements are represented as a list (in reality all lists have 10 elements in them, but this is less important):
user_id actual predicted popular random
u1 [a,b,c] [a,b,d] [c,e,f] [d,e,f]
u2 [a,b,d] [a,b,c] [c,e,f] [a,b,c]
u3 [c,e,f] [a,c,e] [c,e,f] [a,c,f]
u4 [c,e,f] [a,e,f] [c,e,f] [a,d,f]
u5 [b,e,f] [a,b,e] [c,e,f] [a,c,e]
While I have some separate statistics regarding predicted, I would like to compare how close to the actual lists these predicted, popular and random lists are. popular has always the same three items.
I was thinking of calculating the percentages for each case and then averaging:
user_id predicted popular random
u1 0.66 0.33 0
u2 0.66 0 0.33
u3 0.66 1 0.66
u4 0.66 1 0.33
u5 0.66 0.33 0.33
Normally, I would do something like:
setA = set(listA)
setB = set(listB)
overlap = setA & setB
universe = setA | setB
result = float(len(overlap)) / len(setA) * 100
But how can I do this for a large dataframe?
EDIT: I tried the answer that was proposed and got the following error:
KeyError Traceback (most recent call last)
~\anaconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance)
2894 try:
-> 2895 return self._engine.get_loc(casted_key)
2896 except KeyError as err:
pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()
pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()
pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()
pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()
KeyError: 0
The above exception was the direct cause of the following exception:
KeyError Traceback (most recent call last)
<ipython-input-50-9dd44429ffdc> in <module>
2 for i in range(df.shape[0]):
3 for col in ["predicted", "popular", "random"]:
----> 4 df.loc[i, f"{col}_pct"] = (len(test.loc[i, "actual"] & test.loc[i, col]) / len(test.loc[i, "actual"]) * 100)
~\anaconda3\lib\site-packages\pandas\core\indexing.py in __getitem__(self, key)
871 # AttributeError for IntervalTree get_value
872 pass
--> 873 return self._getitem_tuple(key)
874 else:
875 # we by definition only have the 0th axis
~\anaconda3\lib\site-packages\pandas\core\indexing.py in _getitem_tuple(self, tup)
1042 def _getitem_tuple(self, tup: Tuple):
1043 try:
-> 1044 return self._getitem_lowerdim(tup)
1045 except IndexingError:
1046 pass
~\anaconda3\lib\site-packages\pandas\core\indexing.py in _getitem_lowerdim(self, tup)
784 # We don't need to check for tuples here because those are
785 # caught by the _is_nested_tuple_indexer check above.
--> 786 section = self._getitem_axis(key, axis=i)
787
788 # We should never have a scalar section here, because
~\anaconda3\lib\site-packages\pandas\core\indexing.py in _getitem_axis(self, key, axis)
1108 # fall thru to straight lookup
1109 self._validate_key(key, axis)
-> 1110 return self._get_label(key, axis=axis)
1111
1112 def _get_slice_axis(self, slice_obj: slice, axis: int):
~\anaconda3\lib\site-packages\pandas\core\indexing.py in _get_label(self, label, axis)
1057 def _get_label(self, label, axis: int):
1058 # GH#5667 this will fail if the label is not present in the axis.
-> 1059 return self.obj.xs(label, axis=axis)
1060
1061 def _handle_lowerdim_multi_index_axis0(self, tup: Tuple):
~\anaconda3\lib\site-packages\pandas\core\generic.py in xs(self, key, axis, level, drop_level)
3489 loc, new_index = self.index.get_loc_level(key, drop_level=drop_level)
3490 else:
-> 3491 loc = self.index.get_loc(key)
3492
3493 if isinstance(loc, np.ndarray):
~\anaconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance)
2895 return self._engine.get_loc(casted_key)
2896 except KeyError as err:
-> 2897 raise KeyError(key) from err
2898
2899 if tolerance is not None:
KeyError: 0