CODEWITHSUNDEEP
pythonpandas
connor449
connor449

Reputation: 1679

How to combine values in a row depending on value in another row in pandas

I have a pandas dataframe with several columns (words, start time, stop time, speaker). I want to combine all values in the 'word' column while the values in the 'speaker' column do not change. In addition, I want to keep the 'start' value for the first word and the 'stop' value for the last word in the combination.

I currently have:

      word        start  stop      speaker
0      but   2.72  2.85        2
1   that's   2.85  3.09        2
2  alright   3.09  3.47        2
3    we'll   8.43  8.69        1
4     have   8.69  8.97        1
5       to   8.97  9.07        1
6     okay   9.19 10.01        2
7     sure  10.02 11.01        2
8    what?  11.02 12.00        1

However, I would like to turn this into:

       word        start start speaker
0  but that's alright  2.72  3.47  2
1       we'll have to  8.43  9.07  1
2           okay sure  9.19 11.01  2
3               what? 11.02 12.00  1

Upvotes: 1

Views: 54

Answers (1)

cs95
cs95

Reputation: 402553

We'll use GroupBy.agg with a dict of aggfuncs:

(df.groupby('speaker', as_index=False, sort=False)
   .agg({'word': ' '.join, 'start': 'min', 'stop': 'max',}))

   speaker                word  start  stop
0        2  but that's alright   2.72  3.47
1        1       we'll have to   8.43  9.07

To group by consecutive occurrences, use the shifting cumsum trick, then use that as the second grouper along with "speaker":

gp1 = df['speaker'].ne(df['speaker'].shift()).cumsum()

(df.groupby(['speaker', gp1], as_index=False, sort=False)
   .agg({'word': ' '.join, 'start': 'min', 'stop': 'max',}))

   speaker                word  start   stop
0        2  but that's alright   2.72   3.47
1        1       we'll have to   8.43   9.07
2        2           okay sure   9.19  11.01
3        1               what?  11.02  12.00

Upvotes: 3

Related Questions