How to group a pandas dataframe to list of Traces?

user_14045537 0
Viewed 77

I have sample data like below

import pandas as pd

data_dict = {"Col_1" : ["AB", "AC", "AD", "BD", "BC", "BW", "KA", "KB", "KK", "KL"],
             "Col_2" : [1,2,1,2,2,1,1,1,2,2,]}

data = pd.DataFrame(data_dict)

  Col_1 Col_2
0   AB  1
1   AC  2
2   AD  1
3   BD  2
4   BC  2
5   BW  1
6   KA  1
7   KB  1
8   KK  2
9   KL  2

I'm trying to group the data with Col_2 and return the grouped values as a list with two columns as ID and Traces as a DataFrame.

Required output - Dataframe

ID   Traces
1    ["AB", "AD", "BW", "KA", "KB"],
2    ["AC", "BD", "BC", "KK", "KL"]}
pandas-groupby pandas dataframe python
2 Answers

Rename the column Col_2 as ID, then group the dataframe by ID, finally apply aggregate using named aggregation passing i.e. passing it as keyword argument where keyword is the column name you want i.e. Traces, argument is the tuple with column name i.e. 'Col_2to perform aggregation on, and the aggregation function i.e.list`. (If needed, you can reset the index at last)

>>> data.rename(columns={'Col_2': 'ID'}).groupby('ID').agg(Traces=('Col_1', list))

                  Traces
ID                      
1   [AB, AD, BW, KA, KB]
2   [AC, BD, BC, KK, KL]
user_91363480

Use -

data.groupby('Col_2')['Col_1'].apply(list).reset_index().rename(columns={'Col_2': 'ID', 'Col_1': 'Traces'})

Output

ID                Traces
0   1  [AB, AD, BW, KA, KB]
1   2  [AC, BD, BC, KK, KL]
user_40980130
Related