Matching Strings and Count Frequency

Admin 1
Viewed 56

I have a list of companies with their subsidiaries, the data looks as below:

CompanyName            Employees
Microsoft China          1
Microsoft India          1
Microsoft Europe         1
Apple Inc                1
Apple Data Inc           1
Apple Customer Service Inc   1
Data Corp                1
Data SHCH                1
Data India               1
City Corp                1
Data City                 1

If two companies have same words (e.g. Apple Inc and Apple Data Inc), they are considered one company. I will group those companies together, and calculate their total number of employees.

The expected return should be:
Company     Employees
Microsft     3
Apple        3
Data         3
City         2
  1. The company will return the common word
  2. The Employees return the sum of company and its subsidiaries

Most of the pandas function doesn't really work in this case. Any suggestions on For Loop?

pandas python
2 Answers
  • As you requested in the comments
    • If the company is always the first word in CompanyName
# extract company as word at index 0
df.CompanyName = df.CompanyName.str.split(expand=True)[0]

# groupby company name and count
dfg = df.groupby('CompanyName').agg({'CompanyName': 'count'})

# display(dfg)
             CompanyName
CompanyName             
Apple                  3
City                   1
Data                   4
Microsoft              3
user_77588040

I don't think there's a 'very' simple way to do what you want. But it's not too complex too.

First, you need to define clearly the ~criterion to decide wich names are the same 'company'. We can try with "get the first world and see if it matches", obviously it's not a perfect approach, but it'll do for now.

Then, you can create an object to store your new data. I would recommend a dictionary, with entries like company: (total employees).

You'll now iterate over the rows of the dataframe, with apply and a function to do what you want. It'll look like this:

dict = {}

def aggregator(row):
    word1 = row.company.split(" ")[0]
    
    if word1 in dict.keys:
       dict[word1] += row.employees

    else:
        dict[word1] = row.employees

dataframe.apply(aggregator, axis = 1)
user_93975850
Related