CODEWITHSUNDEEP
pythonstringpandasnlp
PANDA
PANDA

Reputation: 137

Find the most frequent string in a Data frame

I am new to Python programming.I have a pandas data frame in which two string columns are present.

Data frame is like below:

Case    Action
Create   Create New Account
         Create New Account
         Create New Account
         Create New Account
         Create Old Account
Delete   Delete New Account
         Delete New Account
         Delete Old Account
         Delete Old Account
         Delete Old Account

Here we can see in Create, out 5 actions 4 actions has been Create New Account. Means 4/5(=80%) .Similarly in Delete case maximum cases are Delete Old Account. So my requirement is when next time any case comes like Create, i should get o/p as Crate New Account with frequency score.

Expected O/P :

Case    Action              Score
Create  Create New Account  80
Delete  Delete Old Account  60

Upvotes: 0

Views: 59

Answers (1)

BENY
BENY

Reputation: 323326

Using crosstab before groupby tail

pd.crosstab(df.Case,df.Action,normalize='index').stack().sort_values().groupby(level=0).tail(1)
Out[769]: 
Case    Action          
Delete  DeleteOldAccount    0.6
Create  CreateNewAccount    0.8
dtype: float64

Or do it with where

pdf=pd.crosstab(df.Case,df.Action,normalize='index')
pdf.where(pdf.eq(pdf.max(1),axis=0)).stack()
Out[781]: 
Case    Action          
Create  CreateNewAccount    0.8
Delete  DeleteOldAccount    0.6
dtype: float64

Upvotes: 1

Related Questions