CODEWITHSUNDEEP
pythonpandaspandas-groupby
giser_yugang
giser_yugang

Reputation: 6176

How to use Multiindex to be aggregated into list in pandas?

I have a dataframe like this :

    class1  class2  values  values2
0        1       0       1        5
1        1       1       2        8
2        1       0       3        3
3        2       0       5        6
4        2       0       2        5
5        2       1       4        2
6        2       1       2        3
7        2       1       3        1
8        3       0       1        3
9        3       0       3        3
10       3       1       4        2
11       3       1       2        4

I hope to set Multiindex based on class1 and class2, and then aggregate value and value2 into list. So I want to get the result should like this:

   class1  class2     values    values2
0       1       0     [1, 3]     [5, 3]
1       1       1        [2]        [8]
2       2       0     [5, 2]     [6, 5]
3       2       1  [4, 2, 3]  [2, 3, 1]
4       3       0     [1, 3]     [3, 3]
5       3       1     [4, 2]     [2, 4]

I tried it done by: 

df.groupby(['class1']).agg(lambda x: x.tolist()).reset_index()

It's no problem. But I tried Multiindex by: 

df.groupby(['class1','class2']).agg(lambda x: x.tolist()).reset_index()

Show error:

ValueError: Function does not reduce

I also tried it done by: 

df.groupby(['class1', 'class2'])['values'].apply(lambda x: x.tolist()).reset_index()

This method can only be handled separately for value or Value2.

Can anyone help me with a better way? Thanks in advance

Upvotes: 2

Views: 293

Answers (1)

piRSquared
piRSquared

Reputation: 294468

You can aggregate with tuple then convert to list with applymap

df.groupby(['class1', 'class2']).agg(tuple).applymap(list).reset_index()

   class1  class2     values    values2
0       1       0     [1, 3]     [5, 3]
1       1       1        [2]        [8]
2       2       0     [5, 2]     [6, 5]
3       2       1  [4, 2, 3]  [2, 3, 1]
4       3       0     [1, 3]     [3, 3]
5       3       1     [4, 2]     [2, 4]

Upvotes: 2

Related Questions