1

grouping rows in list in pandas groupby

i have found question and need to go a step further

output required by this question was 

A [1,2]
B [5,5,4]
C [6]

what I'am trying to achieve is 

A B C
1 5 6
2 5 
  4

i have tried using

grouped=dataSet.groupby('Column1')
df = grouped.aggregate(lambda x: list(x))

output im Stucked with is

df.T
Column1  A     B       C
        [1,2] [5,5,4] [6]
Mukesh Suthar
  • 69
  • 11
  • Why is literally every single pandas question today featuring columns of lists? They're terrible to work with, and not what pandas is meant for. Please re-evaluate your strategy. – cs95 Mar 07 '18 at 11:48
  • hi there, I'm new to pandas and here I stuck badly scratching my head since this morning, okay i'll make a note that pandas is not suitable for columns to list. can you suggest me any strategy to solve this problem? I've tried using pivot but didn't found any solution. – Mukesh Suthar Mar 07 '18 at 11:53

2 Answers2

1

I think here there is no need to use columns of lists. You can achieve your result using a simple dictionary comprehension over the groups generated by groupby:

out = pd.concat({key: 
                    group['b'].reset_index(drop=True)
                for key, group in df.groupby('a')}, axis=1)

which gives the desired output:

out
Out[59]: 
     A  B    C
0  1.0  5  6.0
1  2.0  5  NaN
2  NaN  4  NaN
FLab
  • 7,136
  • 5
  • 36
  • 69
0

I believe you need create DataFrame by contructor:

df = pd.DataFrame( {'a':['A','A','B','B','B','C'], 'b':[1,2,5,5,4,6]})

s = df.groupby('a')['b'].apply(list)
df = pd.DataFrame(s.values.tolist(), index=s.index).T
print (df)
a    A    B    C
0  1.0  5.0  6.0
1  2.0  5.0  NaN
2  NaN  4.0  NaN
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252