Python Pandas NLTK Frequency Distribution for Tokenized Words in Dataframe Column with a Groupby

Question

I have the following sample dataframe:

No  category    problem_definition
175 2521       ['coffee', 'maker', 'brewing', 'properly', '2', '420', '420', '420']
211 1438       ['galley', 'work', 'table', 'stuck']
912 2698       ['cloth', 'stuck']
572 2521       ['stuck', 'coffee']

The problem_definition field has already been tokenized with stop gap words removed.

I want to create a frequency distribution that outputs another Pandas dataframe:

1) with the frequency occurrence of each word in problem_definition 2) with the frequency occurrence of each word in problem_definition by category field

Sample desired output below for case 1):

text       count
coffee     2
maker      1
brewing    1
properly   1
2          1
420        3
stuck      3
galley     1
work       1
table      1
cloth      1

Sample desired output below for case 2):

category    text       count
2521        coffee     2
2521        maker      1
2521        brewing    1
2521        properly   1
2521        2          1
2521        420        3
2521        stuck      1
1438        galley     1
1438        work       1
1438        table      1
1438        stuck      1
2698        cloth      1
2698        stuck      1

I tried the following code to accomplish 1):

from nltk.probability import FreqDist
import pandas as pd

fdist = FreqDist(df['problem_definition_stopwords'])

TypeError: unhashable type: 'list'

I have no idea how to do 2)

Are your expected `counts` grouped by `category`? – BernardL Nov 28 '18 at 22:18 — BernardL, Nov 28 '18 at 22:18
yes, counts of distinct words grouped by category – PineNuts0 Nov 28 '18 at 22:19 — PineNuts0, Nov 28 '18 at 22:19

BENY · Accepted Answer · 2018-11-28T22:30:54.960

2

Using unnesting , I step by step introduced couple of methods for achieve this type of problems , for fun I just link the question here

unnesting(df,['problem_definition'])
Out[288]: 
  problem_definition   No  category
0             coffee  175      2521
0              maker  175      2521
0            brewing  175      2521
0           properly  175      2521
0                  2  175      2521
0                420  175      2521
0                420  175      2521
0                420  175      2521
1             galley  211      1438
1               work  211      1438
1              table  211      1438
1              stuck  211      1438
2              cloth  912      2698
2              stuck  912      2698
3              stuck  572      2521
3             coffee  572      2521

Then just do regular groupby + size for case 2

unnesting(df,['problem_definition']).groupby(['category','problem_definition']).size()
Out[290]: 
category  problem_definition
1438      galley                1
          stuck                 1
          table                 1
          work                  1
2521      2                     1
          420                   3
          brewing               1
          coffee                2
          maker                 1
          properly              1
          stuck                 1
2698      cloth                 1
          stuck                 1
dtype: int64

About the case 1 value_counts

unnesting(df,['problem_definition'])['problem_definition'].value_counts()
Out[291]: 
stuck       3
420         3
coffee      2
table       1
maker       1
2           1
brewing     1
galley      1
work        1
cloth       1
properly    1
Name: problem_definition, dtype: int64

Myself define function

def unnesting(df, explode):
    idx=df.index.repeat(df[explode[0]].str.len())
    df1=pd.concat([pd.DataFrame({x:np.concatenate(df[x].values)} )for x in explode],axis=1)
    df1.index=idx
    return df1.join(df.drop(explode,1),how='left')

edited Nov 28 '18 at 22:30

answered Nov 28 '18 at 22:23

BENY

317,841
20
164
234

thanks, i get 'unnesting' is not defined ... do I need to import a package to use unnesting? – PineNuts0 Nov 28 '18 at 22:27
@PineNuts0 go to my linked page and run my own def function https://stackoverflow.com/a/53218939/7964527 function at the end – BENY Nov 28 '18 at 22:28
trying it out now – PineNuts0 Nov 28 '18 at 22:30
@PineNuts0 see above . ...see the other page accept answer at the bottom – BENY Nov 28 '18 at 22:31
So Case 1 I'm getting the error: ValueError: zero-dimensional arrays cannot be concatenated BUT Case 2 worked! – PineNuts0 Nov 28 '18 at 22:36
@PineNuts0 you mean `unnesting(df,['problem_definition'])['problem_definition'].value_counts()` have problem ? – BENY Nov 28 '18 at 22:41
I mean unnesting(df,['problem_definition']) has the error – PineNuts0 Nov 28 '18 at 22:43
wait ... it worked! sorry my fault; thank you so much – PineNuts0 Nov 28 '18 at 22:44
one more question: I want the counts field to sort from largest to smallest by category ... ? – PineNuts0 Nov 28 '18 at 22:53
CHeck with `sort_index` and `sort_values` @PineNuts0 – BENY Nov 28 '18 at 22:56

BernardL · Answer 2 · 2018-11-28T23:41:12.287

You can also expand the list by its category and then do a groupby and size.

import pandas as pd
import numpy as np

df = pd.DataFrame( {'No':[175,572],
                    'category':[2521,2521],
                    'problem_definition': [['coffee', 'maker', 'brewing', 'properly', '2', '420', '420', '420'],
                                          ['stuck', 'coffee']]} )

c = df.groupby('category')['problem_definition'].agg('sum').reset_index()

lst_col = 'problem_definition'

c = pd.DataFrame({
      col:np.repeat(c[col].values, c[lst_col].str.len())
      for col in c.columns.drop(lst_col)}
    ).assign(**{lst_col:np.concatenate(c[lst_col].values)})[c.columns]

c.groupby(['category','problem_definition']).size()
>>
category  problem_definition
2521      2                     1
          420                   3
          brewing               1
          coffee                2
          maker                 1
          properly              1
          stuck                 1
dtype: int64

Or you can also use a Counter to help you store the count values grouped by category:

import pandas as pd
import numpy as np
from collections import Counter

df = pd.DataFrame( {'No':[175,572],
                    'category':[2521,2521],
                    'problem_definition': [['coffee', 'maker', 'brewing', 'properly', '2', '420', '420', '420'],
                                          ['stuck', 'coffee']]} )

c = df.groupby('category')['problem_definition'].agg('sum').reset_index()
c['problem_definition'] = c['problem_definition'].apply(lambda x: Counter(x).items())

lst_col = 'problem_definition'

s = pd.DataFrame({
      col:np.repeat(c[col].values, c[lst_col].str.len())
      for col in c.columns.drop(lst_col)}
    ).assign(**{'text':np.concatenate(c[lst_col].apply(lambda x: [k for (k,v) in x]))}
    ).assign(**{'count':np.concatenate(c[lst_col].apply(lambda x: [v for (k,v) in x]))} )

s

Python Pandas NLTK Frequency Distribution for Tokenized Words in Dataframe Column with a Groupby

2 Answers2