How to combine separated rows in a Dataframe cell into a list

Question

I'm looking to combine pandas rows in a Dataframe by matching userID into a list. There are 1000+ userIDs and multiple entries for each.

I want one row for each user. I found a thread that does the complete opposite of what I am trying to do, which is: How to explode a list inside a Dataframe cell into separate rows Thanks in advance, guys.

what would need to be done for a rows with string and number?

  userID    alcohol 
  U1001     No_Alcohol_Served 7.0
            Wine-Beer 2.0
  U1002     Full_Bar 1.0
            No_Alcohol_Served 3.0
            Wine-Beer 6.0
  U1003     Full_Bar 2.0
            No_Alcohol_Served 8.0
            Wine-Beer 3.0
  U1004    No_Alcohol_Served 4.0
           Wine-Beer 4.0

I would like to say something like:

U1001 : No_Alcohol_served:7.0, Wine-Beer:2.0
U1002 : Full_Bar:1.0, No_Alcohol_served:3.0, Wine_beer:6.0

so on and so forth

Also related https://stackoverflow.com/questions/45928415/why-does-df-applytuple-work-but-not-df-applylist — user3483203, Jun 03 '18 at 22:13
Please see http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question — PM 2Ring, Jun 03 '18 at 22:29
Is that really the format of your dataframe? Some rows have 3 columns, some have 2, that shouldn't work. — user3483203, Jun 03 '18 at 22:32
Two columns: 1) for UserID and 2) for type followed by a number — Tuxedo, Jun 03 '18 at 22:36
It's clearly a groupby result. Please read about how to create a [mcve] and also read [ask] before asking future questions. — user3483203, Jun 03 '18 at 22:43

score 1 · Answer 1 · answered Jun 03 '18 at 22:10

1

You could try something like this:

df.groupby('userID').apply(lambda x: x['name'].tolist())

Example:

Given a df:

  userID name
0  U1001    a
1  U1001    b
2  U1001    c
3  U1002    d
4  U1002    e
5  U1003    f

>>> df.groupby('userID').apply(lambda x: x['name'].tolist())
userID
U1001    [a, b, c]
U1002       [d, e]
U1003          [f]
dtype: object

answered Jun 03 '18 at 22:10

sacuL

49,704
8
81
106

Thank you! How would I do part 2? I just edited it. Thank you – Tuxedo Jun 03 '18 at 22:21
1

Can you post your dataframe as text please? Otherwise it is impossible to help you. – user3483203 Jun 03 '18 at 22:28
Updated! Sorry for the inconvenience – Tuxedo Jun 03 '18 at 22:38

How to combine separated rows in a Dataframe cell into a list

1 Answers1