2

I am trying to group a column in a data frame, and I want the other columns to be added to a list of elements.

I have a data frame similar to this:

Route  Station  Position
A1      X1         P1        
A1      X2         P2        
A1      X3         P3      
B2      Y1         P1   
B2      Y2         P2

The expected output it should look like this: 

Route  Station  Position
A1   [X1,X2,X3] [P1,P2,P3]       
B2    [Y1,Y2]    [P1,P2]

What is the correct way to do it?

Ruben Dario Guarnizo Martinez
  • 360
  • 4
  • 15

2 Answers2

6

Use:

df.groupby('Route', as_index=False).agg(list)

Output:

  Route       Station      Position
0    A1  [X1, X2, X3]  [P1, P2, P3]
1    B2      [Y1, Y2]      [P1, P2]
Scott Boston
  • 147,308
  • 15
  • 139
  • 187
  • i am trying to do the same as OP except that i want to convert only one colunn to list, while taking other column as is. going by the OP's example, I have done: `df.groupby('Route', as_index=False).agg({{'Station': lambda x: x, 'Position': lambda x: list(x)}})` and doing this gives me, `ValueError: Must produce aggregated value` – Naveen Reddy Marthala Jan 13 '22 at 12:50
  • 1
    @NaveenReddyMarthala Try: `df[['Route','Station']].merge(df.groupby('Route')['Position'].agg(list), on='Route')` – Scott Boston Jan 13 '22 at 12:55
  • 1
    thanks for the comment, this worked for me: https://stackoverflow.com/questions/65093644/pandas-group-by-one-column-and-aggregate-other-column-to-list/65093870#65093870 – Naveen Reddy Marthala Jan 13 '22 at 13:06
0
>>> m
    R   S   P
0  A1  X1  P1
1  A1  X2  P2
2  A1  X3  P3
3  B2  Y1  P1
4  B2  Y2  P2
>>> m.groupby('R').agg( lambda x: list(x) )
               S             P
R                             
A1  [X1, X2, X3]  [P1, P2, P3]
B2      [Y1, Y2]      [P1, P2]
>>>
painterner
  • 11
  • 1