How can I create a column with counts between a column and a list in Pandas?
Another solution using Series.str.count
:
df['score'] = df['answer'].str.count('|'.join(correct_list))
[out]
answer score
0 cats, dogs, pigs 2
1 cats, dogs 2
2 dogs, pigs 1
3 cats 1
4 pigs 0
Update
As pointed out by @PrinceFrancis, if catsdogs
shouldn't be counted as 2
, then you can change your regex pattern to suit:
df = pd.DataFrame({'answer': ['cats, dogs, pigs', 'cats, dogs', 'dogs, pigs', 'cats', 'pigs', 'catsdogs']})
pat = '|'.join([fr'\b{x}\b' for x in correct_list])
df['score'] = df['answer'].str.count(pat)
[out]
answer score
0 cats, dogs, pigs 2
1 cats, dogs 2
2 dogs, pigs 1
3 cats 1
4 pigs 0
5 catsdogs 0
We can also use Series.explode
:
df['score']=df['answer'].str.split(', ').explode().isin(correct_list).groupby(level=0).sum()
print(df)
answer score
0 cats, dogs, pigs 2.0
1 cats, dogs 2.0
2 dogs, pigs 1.0
3 cats 1.0
4 pigs 0.0
You could do as follows
correct_list = ['cats','dogs']
df['score'] = df['answer'].str.split(', ')
df['score'] = df['score'].apply(lambda x: sum(el in x for el in correct_list))
df
It will give you the following result
answer score
0 cats,dogs,pigs 2
1 cats,dogs 2
2 dogs,pigs 1
3 cats 1
4 pigs 0