pandas - groupby column with multiple values

Question

I want to display the users that have used a value.

import pandas as pd
user = ['alice', 'bob', 'tim', 'alice']
val = [['a','b','c'],['a'],['c','d'],['a','d']]
df = pd.DataFrame({'user': user, 'val': val})

user    val
'alice'      [a, b, c]
'bob'        [a]
'tim'        [c, d]
'alice'      [a, d]

Desired output:

val     users
a      [alice,bob]
b      [alice]
c      [alice,tim]
d      [alice,tim]

Any ideas?

cs95 · Accepted Answer · 2018-03-12T08:11:09.467

4

Step 1
Reshape your data -

from itertools import chain

df = pd.DataFrame({
    'val' : list(chain.from_iterable(df.val.tolist())), 
    'user' : df.user.repeat(df.val.str.len())
})

Step 2
Use groupby + apply + unique:

df.groupby('val').user.apply(lambda x: x.unique().tolist())

val
a    [alice, bob]
b         [alice]
c    [alice, tim]
d    [tim, alice]
Name: user, dtype: object

edited Mar 12 '18 at 08:11

answered Mar 12 '18 at 07:40

cs95

379,657
97
704
746

It is not the same as OP's desired output. – pe-perry Mar 12 '18 at 07:48
Shouldn't row 'c' and 'd' be `[1, 3]` (User 1 and 3 have values 'c' and 'd'), but your codes give `[1, 1]`? – pe-perry Mar 12 '18 at 08:05
I want to show the actual users. One second let me update my output. The users as numbers are confusing, that's my fault. – qrs Mar 12 '18 at 08:06

score 1 · Answer 2 · answered Mar 12 '18 at 08:09

1

This is my approach.

df2 = (df
       .set_index('user')
       .val
       .apply(pd.Series)
       .stack()
       .reset_index(name='val')  # Reshape the data
       .groupby(['val'])
       .user
       .apply(lambda x: sorted(set(x))))  # Show users that use the value

Output:

print(df2)
# val
# a    [alice, bob]
# b         [alice]
# c    [alice, tim]
# d    [alice, tim]
# Name: user, dtype: object

answered Mar 12 '18 at 08:09

pe-perry

2,591
2
22
33

@qrs If performance is important, you may want to take another look at the other answers – cs95 Mar 12 '18 at 08:22
@cᴏʟᴅsᴘᴇᴇᴅ Would you mind telling us why your code is faster? – pe-perry Mar 12 '18 at 08:26
2

Sure, no worries. `apply(pd.Series)` is generally considered very slow. I learned this the hard way :) – cs95 Mar 12 '18 at 08:26

jezrael · Answer 3 · 2018-03-12T08:23:54.757

1

I think need:

df2 = (pd.DataFrame(df['val'].values.tolist(), index=df['user'].values)
         .stack()
         .reset_index(name='val')
         .groupby('val')['level_0']
         .unique()
         .reset_index()
         .rename(columns={'level_0':'user'})
     )
print(df2)
  val          user
0   a  [alice, bob]
1   b       [alice]
2   c  [alice, tim]
3   d  [tim, alice]

edited Mar 12 '18 at 08:23

answered Mar 12 '18 at 08:10

jezrael

822,522
95
1,334
1,252

score 0 · Answer 4 · answered Mar 12 '18 at 07:40

0

Don't have enough reputation to write this as a comment, but this question has the answer: How to print dataframe without index

basically, change the last line to:

print(df2.to_string(index=False))

answered Mar 12 '18 at 07:40

David L

441
3
9

No, that is isn't it. – cs95 Mar 12 '18 at 07:41

pandas - groupby column with multiple values

4 Answers4