15

I have a column in DataFrame containing list of categories. For example:

0                                                    [Pizza]
1                                 [Mexican, Bars, Nightlife]
2                                  [American, New, Barbeque]
3                                                     [Thai]
4          [Desserts, Asian, Fusion, Mexican, Hawaiian, F...
6                                           [Thai, Barbeque]
7                           [Asian, Fusion, Korean, Mexican]
8          [Barbeque, Bars, Pubs, American, Traditional, ...
9                       [Diners, Burgers, Breakfast, Brunch]
11                                [Pakistani, Halal, Indian]

I am attempting to do two things:

1) Get unique categories - My approach is have a empty set, iterate through series and append each list.

my code:

unique_categories = {'Pizza'}
for lst in restaurant_review_df['categories_arr']:
    unique_categories = unique_categories | set(lst)

This give me a set of unique categories contained in all the lists in the column.

2) Generate pie plot of category counts and each restaurant can belong to multiple categories. For example: restaurant 11 belongs to Pakistani, Indian and Halal categories. My approach is again iterate through categories and one more iteration through series to get counts.

Are there simpler or elegant ways of doing this?

Thanks in advance.

rohan
  • 527
  • 1
  • 6
  • 19

1 Answers1

25

Update using pandas 0.25.0+ with explode

df['category'].explode().value_counts()

Output:

Barbeque       3
Mexican        3
Fusion         2
Thai           2
American       2
Bars           2
Asian          2
Hawaiian       1
New            1
Brunch         1
Pizza          1
Traditional    1
Pubs           1
Korean         1
Pakistani      1
Burgers        1
Diners         1
Indian         1
Desserts       1
Halal          1
Nightlife      1
Breakfast      1
Name: Places, dtype: int64

And with plotting:

df['category'].explode().value_counts().plot.pie(figsize=(8,8))

Output:

enter image description here

For older verions of pandas before 0.25.0 Try:

df['category'].apply(pd.Series).stack().value_counts()

Output:

Mexican        3
Barbeque       3
Thai           2
Fusion         2
American       2
Bars           2
Asian          2
Pubs           1
Burgers        1
Traditional    1
Brunch         1
Indian         1
Korean         1
Halal          1
Pakistani      1
Hawaiian       1
Diners         1
Pizza          1
Nightlife      1
New            1
Desserts       1
Breakfast      1
dtype: int64

With plotting:

df['category'].apply(pd.Series).stack().value_counts().plot.pie()

Output: enter image description here

Per @coldspeed's comments

from itertools import chain
from collections import Counter

pd.DataFrame.from_dict(Counter(chain(*df['category'])), orient='index').sort_values(0, ascending=False)

Output:

Barbeque     3
Mexican      3
Bars         2
American     2
Thai         2
Asian        2
Fusion       2
Pizza        1
Diners       1
Halal        1
Pakistani    1
Brunch       1
Breakfast    1
Burgers      1
Hawaiian     1
Traditional  1
Pubs         1
Korean       1
Desserts     1
New          1
Nightlife    1
Indian       1
Community
  • 1
  • 1
Scott Boston
  • 147,308
  • 15
  • 139
  • 187
  • 2
    `df['category'].apply(pd.Series)` nooo!! You should consider Counter + itertools.chain instead. – cs95 Aug 12 '18 at 22:42
  • @coldspeed Yes, for speed but if you are doing quick analysis, I go with easiest and is readable atleast for me. – Scott Boston Aug 12 '18 at 23:09
  • I would probably do both, instead of doing one and hoping OP can figure out the other. Anyway, this is going to be "quick" for data that is a few thousand rows or less. – cs95 Aug 12 '18 at 23:26