concat column values with previous row if there is null in another column in same row

Question

I have a data frame like this,

df:

col1      col2       col3
 1        cat          4
nan       dog         nan 
 3        tiger         3
 2        lion          9
 nan      frog         nan
 nan     elephant      nan

I want to create a data frame from this data frame that id there is nan values in col1, col2 values will be added to the previous row value.

for example the desired output data frame will be:

col1     col2             col3
 1      catdog             4
 3       tiger             3
 2     lionfrogelephant    9

How to do this using pandas ?

How working my solution?

jezrael
– jezrael

2019-01-09 10:22:48 +00:00
Commented Jan 9, 2019 at 10:22 — jezrael
– jezrael, Commented Jan 9, 2019 at 10:22
yes, thanks , working

Kallol
– Kallol

2019-01-09 10:28:39 +00:00
Commented Jan 9, 2019 at 10:28 — Kallol
– Kallol, Commented Jan 9, 2019 at 10:28
Thank you for accepting!

jezrael
– jezrael

2019-01-09 10:29:42 +00:00
Commented Jan 9, 2019 at 10:29 — jezrael
– jezrael, Commented Jan 9, 2019 at 10:29

jezrael · Accepted Answer · 2019-01-08 11:22:42Z

1

Use forward filling missing values and aggregate join:

cols = ['col1','col3']
df[cols] = df[cols].ffill()
df = df.groupby(cols)['col2'].apply(''.join).reset_index()
print (df)
   col1  col3              col2
0   1.0   4.0            catdog
1   2.0   9.0  lionfrogelephant
2   3.0   3.0             tiger

Or if necessary forward filling missing values in all columns:

df = df.ffill().groupby(['col1','col3'])['col2'].apply(''.join).reset_index()
print (df)
   col1  col3              col2
0   1.0   4.0            catdog
1   2.0   9.0  lionfrogelephant
2   3.0   3.0             tiger

answered Jan 8, 2019 at 11:22

jezrael

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

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

1 Comment

SUDHEER KUMAR Over a year ago

Mine is same problem, but data is not always missing in col1 or col3, sometime in col2 also. And when data is missing in col2, data is present in either col1 or col3 or in both. How to deal in this scenario. How to successfully attach present row data to previous row data?

Collectives™ on Stack Overflow

concat column values with previous row if there is null in another column in same row

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related