Conditionally concatenate strings in a Pandas dataframe

Question

I have a dataframe that looks like:

df:

      Source    Text    
      Agent     aa      
      Agent     ab      
      Visitor   ac      
      Agent     ad      
      Visitor   ae      
      Visitor   ba      
      Visitor   bb      
      Agent     bc      
      Agent     bd      
      Agent     be

I would like to create a new dataframe that looks the following:

      Source    Text    
      Agent     aa ab   
      Visitor   ac      
      Agent     ad      
      Visitor   ae ba bb 
      Agent     bc bd be

So, for each new instance of an agent/visitor talking I want to concatenate the text of the different things one person said in a new cell.

I've found this post, however this is too specific and I don't see how to apply this to my dataframe.

jezrael · Accepted Answer · 2019-04-11 12:19:33Z

1

You can groupby by Series which is create by cumsum of shifted column Source by shift with join, last use double Series.reset_index - first for remove first level of MultiIndex and second for column from Source:

g = df['Source'].ne(df['Source'].shift()).cumsum()
df1 = (df.groupby([g, 'Source'])['Text']
         .apply(' '.join)
         .reset_index(level=0, drop=True)
         .reset_index())
print (df1)
    Source      Text
0    Agent     aa ab
1  Visitor        ac
2    Agent        ad
3  Visitor  ae ba bb
4    Agent  bc bd be

answered Apr 11, 2019 at 12:19

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Emil Over a year ago

If I run this code, I get the following error: TypeError: sequence item 12: expected str instance, float found. What can I do to overcome this, given your code?

jezrael Over a year ago

@Emil - I think there are missing values in column Text, how working first remove them by df = df.dropna(subset=['Text']) and then apply my solution?

Shehan Ishanka · Accepted Answer · 2019-04-11 13:07:53Z

0

Try this.

d=df.groupby("Source").groups
print(pd.DataFrame([ [k,' '.join(list(df.groupby("Source").get_group(k)["Text"]))] for k in d.keys() ],columns=["Source","Text"]))

answered Apr 11, 2019 at 13:07

Shehan Ishanka

5934 silver badges5 bronze badges

Collectives™ on Stack Overflow

Conditionally concatenate strings in a Pandas dataframe

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related