Groupby and Aggregate over a pandas column with list as elements, and get unique values in list

user_6248229 0
Viewed 884

I have the following dataframe

df = pd.DataFrame(
    {
        "id": [1, 2, 1, 3],
        "values": [[111, 121, 131], [211, 221, 281], [111, 191], [301, 321]],
    }
)
# df
id  values
1   [111, 121, 131]
2   [211, 221, 281]
1   [111, 191]
3   [301, 321]

I want to get the following after groupby and aggregation step

id  values
1   [111, 121, 131, 191]
2   [211, 221, 281]
3   [301, 321]

I am using the following, but it is giving me an error -

new_df = df.groupby(["id"]).agg({"values": lambda val: set(val)}).reset_index()

TypeError: unhashable type: 'list'
pandas-groupby pandas dataframe python performance
3 Answers

One way to do it -

df.groupby('id')['values'].sum().apply(lambda x: list(set(x))).reset_index()

Output

    id  values
0   1   [191, 131, 121, 111]
1   2   [221, 211, 281]
2   3   [301, 321]
user_40980130

This should work

replicate your example:

import pandas as pd

df = pd.DataFrame({'id': [1, 2, 1, 3], 'values': [[111, 121, 131], [211, 221, 281], [111, 191], [301, 321]]})

Solve question

df = df.groupby(by='id').sum()
df[['values']] = df[['values']].applymap(lambda x: set(x))

The output

    values
id  
1   {121, 131, 191, 111}
2   {281, 211, 221}
3   {321, 301}
user_68962310

You can use df.explode then use GroupBy.unique here.

df.explode('values').groupby('id')['values'].unique().reset_index()
    id  values
0   1   [191, 131, 121, 111]
1   2   [221, 211, 281]
2   3   [301, 321]
user_124164530
Related