pandas: column with highest occurring string

Question

I have a table of words with mixed categorical classifications. I want to make it so that the 'common type' column is of the highest occurring (mode) classification label such that each row has a label.

word   type          common type
post | WORK_OF_ART | WORK_OF_ART 
post | WORK_OF_ART | WORK_OF_ART 
post | WORK_OF_ART | WORK_OF_ART 
post | WORK_OF_ART | WORK_OF_ART 
post | WORK_OF_ART | WORK_OF_ART 
post |       OTHER | WORK_OF_ART 
post | WORK_OF_ART | WORK_OF_ART 
post | WORK_OF_ART | WORK_OF_ART 
post |       OTHER | WORK_OF_ART
-----|--------------------------
sign |       OTHER | OTHER
sign | WORK_OF_ART | OTHER 
sign |       OTHER | OTHER
sign | WORK_OF_ART | OTHER 
sign |       OTHER | OTHER 
sign |       OTHER | OTHER 
sign | WORK_OF_ART | OTHER

I the following function but on a dataframe of 1m+ rows the runtime is abysmal

def replace_most_common_type(frame, word):
    common_type = frame[frame['word']==word]['type'].value_counts().idxmax()
    frame.loc[frame['word']==word, 'type'] = common_type

unique_words = master_frame['word'].unique()
for idx, word in unique_words:
    replace_most_common_type(master_frame, word)

built in pandas methods tend to be numpy vectorized so any solution using native pandas functions is appreciated

score 2 · Accepted Answer · answered Jan 08 '20 at 21:15

Given your data:

In [1]: df
Out[1]:
    word         type
0   post  WORK_OF_ART
1   post  WORK_OF_ART
2   post  WORK_OF_ART
3   post  WORK_OF_ART
4   post  WORK_OF_ART
5   post        OTHER
6   post  WORK_OF_ART
7   post  WORK_OF_ART
8   post        OTHER
9   sign        OTHER
10  sign  WORK_OF_ART
11  sign        OTHER
12  sign  WORK_OF_ART
13  sign        OTHER
14  sign        OTHER
15  sign  WORK_OF_ART

You can do group by word and then find the most common type per word by using value_counts, as shown in this answer. Note that you can save the "most common" series to a variable and then rename it so that your column names don't conflict.

In [2]: s = df.groupby('word')['type'].agg(lambda x: x.value_counts().index[0])
   ...: s.name = 'common type'
   ...: df.merge(s, on='word')
Out[2]:
    word         type  common type
0   post  WORK_OF_ART  WORK_OF_ART
1   post  WORK_OF_ART  WORK_OF_ART
2   post  WORK_OF_ART  WORK_OF_ART
3   post  WORK_OF_ART  WORK_OF_ART
4   post  WORK_OF_ART  WORK_OF_ART
5   post        OTHER  WORK_OF_ART
6   post  WORK_OF_ART  WORK_OF_ART
7   post  WORK_OF_ART  WORK_OF_ART
8   post        OTHER  WORK_OF_ART
9   sign        OTHER        OTHER
10  sign  WORK_OF_ART        OTHER
11  sign        OTHER        OTHER
12  sign  WORK_OF_ART        OTHER
13  sign        OTHER        OTHER
14  sign        OTHER        OTHER
15  sign  WORK_OF_ART        OTHER

pandas: column with highest occurring string

1 Answers1