CODEWITHSUNDEEP
pythonlistdataframecount
ProSyth
ProSyth

Reputation: 167

How to search for items in a list in a dataframe and count them

I have the following dataframe with its output.

import pandas as pd
animal = ["monkey"]  + ["tiger"] + ["bat"]
valores = [1] + [2] + [3]
dframe = pd.DataFrame({"animal": animal, "valor": valores})

dataframe:

    animal  valor
0   monkey  1
1   tiger   2
2   bat     3

I have a list that I want to search for item by item in the dataframe and if match then creates a new column with a counter, the word can be uppercase or lowercase and would sum equally.

other_list = ['monkey', 'tiger', 'Tiger','tiger','Monkey']

    animal  valor count
0   monkey  1     2
1   tiger   2     3
2   bat     3     0

Thank you.

Upvotes: 3

Views: 65

Answers (2)

Gerges
Gerges

Reputation: 6499

The first part, I'd go with @Dani, but convert counter to series:

from collections import Counter
counts = pd.Series(Counter(map(str.lower, other_list)), name='counts')

The second part to add this to the dataframe, I'd use append or join to avoid looping: 

dframe = dframe.set_index('animal').join(counts).reset_index()
dframe['counts'] = dframe.counts.fillna(0).astype(int)

Upvotes: 1

Dani Mesejo
Dani Mesejo

Reputation: 61910

You could use collections.Counter:

from collections import Counter
other_list = ['monkey', 'tiger', 'Tiger','tiger','Monkey']
counts = Counter(map(str.lower, other_list))

dframe['count'] = [counts.get(ani.lower(), 0) for ani in dframe['animal']]

print(dframe)

Output

   animal  valor  count
0  monkey      1      2
1   tiger      2      3
2     bat      3      0

Upvotes: 1

Related Questions