Obtaining which element in a df column appears most frequently for each individual element in another df column (list of various individual strings)

user_9972520 0
Viewed 63

I have a column called 'tags' in my pandas dataframe that is a list of multiple strings.

[abc, 123, xyz]
[456, 123]
[abc, 123, xyz]

and I have another column tech that has a single string each

win
mac
win

Please let me know if there is a way for me to get which element in tech appears most frequently for each element in tags. For example, 'abc' is most frequently associated with 'win' as compared to the other techs. So the output should look like : 

abc win
123 win
xyz win
456 mac
pandas dataframe associations python
3 Answers

IIUC , you can explode the Tags column and use crosstab with idxmax:

Input:

d = {'Tags':[['abc', 123, 'xyz'],[456, 123],['abc', 123, 'xyz']],
     'tech':['win','mac','win']}
df = pd.DataFrame(d)
print(df)

              Tags tech
0  [abc, 123, xyz]  win
1       [456, 123]  mac
2  [abc, 123, xyz]  win

Solution:

m = df.explode('Tags')
out = pd.crosstab(m['Tags'],m['tech']).idxmax(1)


Tags
123    win
456    mac
abc    win
xyz    win
dtype: object
user_98406370

Hello I suggest the following : 


import pandas as pd
# I reproduce your example
df = pd.DataFrame({"tags": [["abc", "123", "xyz"], ["456", "123"], ["abc", "123", "xyz"]],
                   "tech": ["win", "mac", "win"]})
# I use explode to have one row per tag
df = df.explode(column="tags")
# then I set index for tags
df = df.set_index("tags").sort_index()

# And then I take the most frequent value by defining a mode function
def mode(x):
    '''
    Returns mode 
    '''
    return x.value_counts().index[0]
res = df.groupby(level=0).agg(mode)

I get 

     tech
tags     
123   win
456   mac
abc   win
xyz   win
user_115610190

If you also want the associated frequency with the tags:

import pandas as pd
from collections import Counter


df = pd.DataFrame({'tech':['win', 'mac', 'win'], 
              'tags':[['abc', 123, 'xyz'], [456, 123], ['abc', 234, 'xyz']]})

df = df.groupby('tech').sum() # concatenate by tech the lists

df['freq'] = [Counter(el) for el in df['tags']] # convert each list to a dict of frequency

final_df = pd.DataFrame()

# explode the column of dicts
for row in df.iterrows():
    tech = row[0]      # get the value in the metric column
    for key, value in row[1][1].items():
        tmp_df = pd.DataFrame({
            'tech':tech,
            'tag': key,
            'frequency': value
        }, index=[0])

        final_df = final_df.append(tmp_df) # append the tmp_df to our final df

final_df = final_df.reset_index(drop=True)
user_19305430
Related