Concatenating strings across two rows in pandas dataframes

Question

I have a table like so after performing some data scraping on a pdf:

index colA           colB                     colC colD colE colF colG
-------------------------------------------------------------------
 1    ABCD  veryLongTextThatShouldNotCutOff   12   x    x    x    x
 2    ABCD  veryLongText                      14   x    x    x    x
 4    NaN   ThatShouldNotCutOff               NaN  NaN  NaN  NaN  NaN
 5    ABCD  veryLongTextThatShouldNotCutOff   12   x    x    x    x
 6    ABCD  veryLongTextThatShouldNotCutOff   x    19   x    x    x
 7    ABCD  veryLongText                      21   x    x    x    x
 9    NaN   ThatShouldNotCutOff               NaN  NaN  NaN  NaN  NaN
 10   ABCD  veryLongTextThatShouldNotCutOff   13   x    x    x    x
...

I need to clean the data up so that information in rows with index 2 and 4 are merged together like so, and with the indexes in the right order again.

index colA           colB                     colC colD colE colF colG
-------------------------------------------------------------------
 1    ABCD  veryLongTextThatShouldNotCutOff   12   x    x    x    x
 2    ABCD  veryLongTextThatShouldNotCutOff   14   x    x    x    x
 3    ABCD  veryLongTextThatShouldNotCutOff   12   x    x    x    x
 4    ABCD  veryLongTextThatShouldNotCutOff   x    19   x    x    x
 5    ABCD  veryLongTextThatShouldNotCutOff   21   x    x    x    x
 6    ABCD  veryLongTextThatShouldNotCutOff   13   x    x    x    x
...

Essentially, some rows with colA as NaN needs to be "concatenated" with the previous rows.

How do I do it using pandas?

mozway · Accepted Answer · 2022-01-06 13:03:06Z

3

You could use the non NaN values in colA to set up a group and merge the colB. Then drop the NaN rows:

group = df['colA'].notna().cumsum()
df['colB'] = df.groupby(group)['colB'].transform('sum')

df = df.dropna(subset=['colA'])

output:

   index  colA                             colB  colC colD colE colF colG
0      1  ABCD  veryLongTextThatShouldNotCutOff  12.0    x    x    x    x
1      2  ABCD  veryLongTextThatShouldNotCutOff  14.0    x    x    x    x
3      5  ABCD  veryLongTextThatShouldNotCutOff  12.0    x    x    x    x

edited Jan 6, 2022 at 13:03

answered Jan 6, 2022 at 12:55

mozway

267k13 gold badges55 silver badges106 bronze badges

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

5 Comments

Gedas Miksenas Over a year ago

colB now has Nan, but he needed concatination instead of that nan.

mozway Over a year ago

thanks @GedasMiksenas that was a mistake of mine, I needed transform

yamero yo Over a year ago

is there a way to do without groupby? I don't think it is the right approach. I've updated my qn for more clarity

yamero yo Over a year ago

@mozway sorry, check again. I feel that a sliding window approach is more appropriate in my case, but I'm not sure how to do that in pandas

mozway Over a year ago

@yameroyo I don't see a fundamental difference (except having a few more rows)

Collectives™ on Stack Overflow

Concatenating strings across two rows in pandas dataframes

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related