Groupby values within a pandas dataframe array column

Question

I have a dataframe containing a few columns with arrays. Here's a sample of one of the columns:

   key            arraylist
0  PROJECT-13051  [value1, value2, value4]
1  PROJECT-13050  [value2, value3, value4]
2  PROJECT-13049  [value1, value2, value3]
3  PROJECT-13048  [value3, value4, value5]
4  PROJECT-13047  [value1, value2, value5]

I pull this data from a sql database as comma seperated, then use the following to set as a list:

df[arraylist] = df[arraylist].apply(literal_eval)

I'd like group by the arraylist column and get the size of each value within the array:

df.groupby('arraylist').size()

This is resulting in the error TypeError: unhashable type: 'list'

I'd like to get an output like so:

arraylist
value1      3
value2      4
value3      3
value4      3
value5      2
dtype: int64

Any help would be greatly appreciated!

Henry Ecker · Accepted Answer · 2021-06-15 13:30:21Z

6

Try with explode + value_counts:

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

value2    4
value1    3
value4    3
value3    3
value5    2
Name: arraylist, dtype: int64

Optional sort_index for sorted as in OP:

df['arraylist'].explode().value_counts().sort_index()

value1    3
value2    4
value3    3
value4    3
value5    2
Name: arraylist, dtype: int64

Or with natsorted for correct natural alphanumeric sorting:

from natsort import natsorted

df['arraylist'].explode().value_counts().loc[lambda s: natsorted(s.index)]

value1    3
value2    4
value3    3
value4    3
value5    2
Name: arraylist, dtype: int64

DataFrame and Imports Used:

from ast import literal_eval

import pandas as pd

df = pd.DataFrame({
    'key': ['PROJECT-13051', 'PROJECT-13050', 'PROJECT-13049',
            'PROJECT-13048', 'PROJECT-13047'],
    'arraylist': ['["value1", "value2", "value4"]',
                  '["value2", "value3", "value4"]',
                  '["value1", "value2", "value3"]',
                  '["value3", "value14", "value5"]',
                  '["value1", "value2", "value5"]']
})
df['arraylist'] = df['arraylist'].apply(literal_eval)

edited Jun 15, 2021 at 13:30

answered Jun 15, 2021 at 13:23

Henry Ecker♦

35.8k19 gold badges48 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Simon Edwards Over a year ago

Thanks Henry, looks like the explode function was what I was after!

Collectives™ on Stack Overflow

Groupby values within a pandas dataframe array column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related