Count occurrence of column values in other dataframe column

user_10437064 0
Viewed 53

I have two dataframes and I want to count the occurrence of "classifier" in "fullname". My problem is that my script counts a word like "carrepair" only for one classifier and I would like to have a count for both classifiers. I would also like to add one random coordinate that matches the classifier.

First dataframe:

enter image description here

Second dataframe:

enter image description here

Result so far:

enter image description here

Desired Result:

enter image description here

My script so far:

 import pandas as pd

fl = pd.read_excel (r'fullname.xlsx')
clas= pd.read_excel (r'classifier.xlsx')

fl.fullname= fl.fullname.str.lower()
clas.classifier = clas.classifier.str.lower()

pat = '({})'.format('|'.join(clas['classifier'].unique()))

fl['fullname'] = fl['fullname'].str.extract(pat, expand = False)

clas['count_of_classifier'] = clas['classifier'].map(fl['fullname'].value_counts())
print(clas)

Thanks!

pandas dataframe count python
1 Answers

You could try this:

import pandas as pd

fl = pd.read_excel (r'fullname.xlsx')
clas= pd.read_excel (r'classifier.xlsx')
fl.fullname= fl.fullname.str.lower()
clas.classifier = clas.classifier.str.lower()

# Add a new column to 'fl' containing either 'repair' or 'car'
for value in clas["classifier"].values:
    fl.loc[fl["fullname"].str.contains(value, case=False), value] = value

# Count values and create a new dataframe
new_clas = pd.DataFrame(
    {
        "classifier": [col for col in clas["classifier"].values],
        "count": [fl[col].count() for col in clas["classifier"].values],
    }
)

# Merge 'fl' and 'new_clas'
new_clas = pd.merge(
    left=new_clas, right=fl, how="left", left_on="classifier", right_on="fullname"
).reset_index(drop=True)

# Keep only expected columns
new_clas = new_clas.reindex(columns=["classifier", "count", "coordinate"])

print(new_clas)
# Outputs
classifier    count    coordinate
repair        3        52.520008, 13.404954
car           3        54.520008, 15.404954
user_112460560
Related