By Arun


2017-06-05 12:03:24 8 Comments

I tried to use groupby to group rows with multiple values.

col val
A  Cat
A  Tiger
B  Ball
B  Bat

import pandas as pd
df = pd.read_csv("Inputfile.txt", sep='\t')
group = df.groupby(['col'])['val'].sum()

I got

A CatTiger
B BallBat

I want to introduce a delimiter, so that my output looks like

A Cat-Tiger
B Ball-Bat

I tried,

group = df.groupby(['col'])['val'].sum().apply(lambda x: '-'.join(x))

this yielded,

A C-a-t-T-i-g-e-r
B B-a-l-l-B-a-t

What is the issue here ?

Thanks,

AP

2 comments

@MaxU 2017-06-05 12:13:03

Alternatively you can do it this way:

In [48]: df.groupby('col')['val'].agg('-'.join)
Out[48]:
col
A    Cat-Tiger
B     Ball-Bat
Name: val, dtype: object

UPDATE: answering question from the comment:

In [2]: df
Out[2]:
  col    val
0   A    Cat
1   A  Tiger
2   A  Panda
3   B   Ball
4   B    Bat
5   B  Mouse
6   B    Egg

In [3]: df.groupby('col')['val'].agg('-'.join)
Out[3]:
col
A       Cat-Tiger-Panda
B    Ball-Bat-Mouse-Egg
Name: val, dtype: object

Last for convert index or MultiIndex to columns:

df1 = df.groupby('col')['val'].agg('-'.join).reset_index(name='new')

@rich 2019-07-25 09:27:44

Does this work when joining more than two rows together at a time? I tried this using a new line character to join rows, the result is that the first two rows are joined with a new line, the rest are concatenated together without a delimiter.

@jezrael 2019-08-29 15:04:15

Added reset_index, because this, be free modify answer.

@ℕʘʘḆḽḘ 2017-06-05 12:08:36

just try

group = df.groupby(['col'])['val'].apply(lambda x: '-'.join(x))

Related Questions

Sponsored Content

27 Answered Questions

[SOLVED] Renaming columns in pandas

18 Answered Questions

[SOLVED] Selecting multiple columns in a pandas dataframe

27 Answered Questions

[SOLVED] Add one row to pandas DataFrame

22 Answered Questions

[SOLVED] How to iterate over rows in a DataFrame in Pandas

15 Answered Questions

[SOLVED] Delete column from pandas DataFrame

7 Answered Questions

[SOLVED] Apply multiple functions to multiple groupby columns

7 Answered Questions

24 Answered Questions

[SOLVED] Adding new column to existing DataFrame in Python pandas

19 Answered Questions

[SOLVED] Get list from pandas DataFrame column headers

9 Answered Questions

Sponsored Content