I have a pandas data frame `df`

like:

a b A 1 A 2 B 5 B 5 B 4 C 6

I want to **group by the first column and get second column as lists in rows**:

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

Is it possible to do something like this using pandas groupby?

Skip to content
# python – How to group dataframe rows into list in pandas groupby

## The Question :

*338 people think this question is useful*
*The Question Comments :*
## The Answer 1

*484 people think this answer is useful*
## The Answer 2

*54 people think this answer is useful*
### If performance is important go down to numpy level:

### Tests:

## The Answer 3

*41 people think this answer is useful*
## The Answer 4

*27 people think this answer is useful*
## The Answer 5

*23 people think this answer is useful*
## The Answer 6

*18 people think this answer is useful*
## The Answer 7

*10 people think this answer is useful*
## The Answer 8

*7 people think this answer is useful*
## The Answer 9

*2 people think this answer is useful*
## The Answer 10

*2 people think this answer is useful*
## The Answer 11

*0 people think this answer is useful*
## The Answer 12

*0 people think this answer is useful*
### Related Posts

#### java – Iterate through a HashMap

#### Get all unique values in a JavaScript array (remove duplicates)

#### r – Reshaping data.frame from wide to long format

#### About Author

##### moting1a

2021-01-17

I have a pandas data frame `df`

like:

a b A 1 A 2 B 5 B 5 B 4 C 6

I want to **group by the first column and get second column as lists in rows**:

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

Is it possible to do something like this using pandas groupby?

You can do this using `groupby`

to group on the column of interest and then `apply`

`list`

to every group:

In [1]: df = pd.DataFrame( {'a':['A','A','B','B','B','C'], 'b':[1,2,5,5,4,6]}) df Out[1]: a b 0 A 1 1 A 2 2 B 5 3 B 5 4 B 4 5 C 6 In [2]: df.groupby('a')['b'].apply(list) Out[2]: a A [1, 2] B [5, 5, 4] C [6] Name: b, dtype: object In [3]: df1 = df.groupby('a')['b'].apply(list).reset_index(name='new') df1 Out[3]: a new 0 A [1, 2] 1 B [5, 5, 4] 2 C [6]

import numpy as np df = pd.DataFrame({'a': np.random.randint(0, 60, 600), 'b': [1, 2, 5, 5, 4, 6]*100}) def f(df): keys, values = df.sort_values('a').values.T ukeys, index = np.unique(keys, True) arrays = np.split(values, index[1:]) df2 = pd.DataFrame({'a':ukeys, 'b':[list(a) for a in arrays]}) return df2

In [301]: %timeit f(df) 1000 loops, best of 3: 1.64 ms per loop In [302]: %timeit df.groupby('a')['b'].apply(list) 100 loops, best of 3: 5.26 ms per loop

A handy way to achieve this would be:

df.groupby('a').agg({'b':lambda x: list(x)})

Look into writing Custom Aggregations: https://www.kaggle.com/akshaysehgal/how-to-group-by-aggregate-using-py

To solve this for several columns of a dataframe:

In [5]: df = pd.DataFrame( {'a':['A','A','B','B','B','C'], 'b':[1,2,5,5,4,6],'c' ...: :[3,3,3,4,4,4]}) In [6]: df Out[6]: a b c 0 A 1 3 1 A 2 3 2 B 5 3 3 B 5 4 4 B 4 4 5 C 6 4 In [7]: df.groupby('a').agg(lambda x: list(x)) Out[7]: b c a A [1, 2] [3, 3] B [5, 5, 4] [3, 4, 4] C [6] [4]

This answer was inspired from Anamika Modi‘s answer. Thank you!

As you were saying the `groupby`

method of a `pd.DataFrame`

object can do the job.

Example

L = ['A','A','B','B','B','C'] N = [1,2,5,5,4,6] import pandas as pd df = pd.DataFrame(zip(L,N),columns = list('LN')) groups = df.groupby(df.L) groups.groups {'A': [0, 1], 'B': [2, 3, 4], 'C': [5]}

which gives and index-wise description of the groups.

To get elements of single groups, you can do, for instance

groups.get_group('A') L N 0 A 1 1 A 2 groups.get_group('B') L N 2 B 5 3 B 5 4 B 4

Use any of the following `groupby`

and `agg`

recipes.

# Setup df = pd.DataFrame({ 'a': ['A', 'A', 'B', 'B', 'B', 'C'], 'b': [1, 2, 5, 5, 4, 6], 'c': ['x', 'y', 'z', 'x', 'y', 'z'] }) df a b c 0 A 1 x 1 A 2 y 2 B 5 z 3 B 5 x 4 B 4 y 5 C 6 z

To aggregate multiple columns as lists, use any of the following:

df.groupby('a').agg(list) df.groupby('a').agg(pd.Series.tolist) b c a A [1, 2] [x, y] B [5, 5, 4] [z, x, y] C [6] [z]

To group-listify a single column only, convert the groupby to a `SeriesGroupBy`

object, then call `SeriesGroupBy.agg`

. Use,

df.groupby('a').agg({'b': list}) # 4.42 ms df.groupby('a')['b'].agg(list) # 2.76 ms - faster a A [1, 2] B [5, 5, 4] C [6] Name: b, dtype: object

It is time to use `agg`

instead of `apply`

.

When

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

If you want multiple columns stack into list , result in `pd.DataFrame`

df.groupby('a')[['b', 'c']].agg(list) # or df.groupby('a').agg(list)

If you want single column in list, result in `ps.Series`

df.groupby('a')['b'].agg(list) #or df.groupby('a')['b'].apply(list)

Note, result in `pd.DataFrame`

is about 10x slower than result in `ps.Series`

when you only aggregate single column, use it in multicolumns case .

If looking for a **unique** **list** while grouping multiple columns this could probably help:

df.groupby('a').agg(lambda x: list(set(x))).reset_index()

Let us using `df.groupby`

with list and `Series`

constructor

pd.Series({x : y.b.tolist() for x , y in df.groupby('a')}) Out[664]: A [1, 2] B [5, 5, 4] C [6] dtype: object

The easiest way I have see no achieve most of the same thing at least for one column which is similar to Anamika’s answer just with the tuple syntax for the aggregate function.

df.groupby('a').agg(b=('b','unique'), c=('c','unique'))

Here I have grouped elements with “|” as a separator

import pandas as pd df = pd.read_csv('input.csv') df Out[1]: Area Keywords 0 A 1 1 A 2 2 B 5 3 B 5 4 B 4 5 C 6 df.dropna(inplace = True) df['Area']=df['Area'].apply(lambda x:x.lower().strip()) print df.columns df_op = df.groupby('Area').agg({"Keywords":lambda x : "|".join(x)}) df_op.to_csv('output.csv') Out[2]: df_op Area Keywords A [1| 2] B [5| 5| 4] C [6]

Answer based on @EdChum’s comment on his answer. Comment is this –

groupby is notoriously slow and memory hungry, what you could do is sort by column A, then find the idxmin and idxmax (probably store this in a dict) and use this to slice your dataframe would be faster I think

Let’s first create a dataframe with 500k categories in first column and total df shape 20 million as mentioned in question.

df = pd.DataFrame(columns=['a', 'b']) df['a'] = (np.random.randint(low=0, high=500000, size=(20000000,))).astype(str) df['b'] = list(range(20000000)) print(df.shape) df.head()

# Sort data by first column df.sort_values(by=['a'], ascending=True, inplace=True) df.reset_index(drop=True, inplace=True) # Create a temp column df['temp_idx'] = list(range(df.shape[0])) # Take all values of b in a separate list all_values_b = list(df.b.values) print(len(all_values_b))

# For each category in column a, find min and max indexes gp_df = df.groupby(['a']).agg({'temp_idx': [np.min, np.max]}) gp_df.reset_index(inplace=True) gp_df.columns = ['a', 'temp_idx_min', 'temp_idx_max'] # Now create final list_b column, using min and max indexes for each category of a and filtering list of b. gp_df['list_b'] = gp_df[['temp_idx_min', 'temp_idx_max']].apply(lambda x: all_values_b[x[0]:x[1]+1], axis=1) print(gp_df.shape) gp_df.head()

This above code takes 2 minutes for 20 million rows and 500k categories in first column.