CODEWITHSUNDEEP
pythonpandas
Javier
Javier

Reputation: 730

Combine Rows in Column and Remove Blank Rows

I'm trying to get the output below but I'm not sure how to phrase it properly. Basically, I need to combine the Address column into one row, and delete the empty rows. Below is my intended output with a reproducible example as well.

Intended Output

    Bank           Address                Distance
0   A   Townhall Road #01-1001               10 
1   B   MapleTree Castle 10 Pathway 31       99

Reproducible Example

import pandas as pd 

data = {'Bank':['A', '', 'B', '', ''], 
        'Address':['Townhall Road', '#01-1001', 'MapleTree', 'Castle 10', 'Pathway 31'],
        'Distance':['10', '', '99', '', '']} 

pd.DataFrame(data)

Sample Dataframe

    Bank    Address   Distance
0   A   Townhall Road   10
1       #01-1001    
2   B   MapleTree       99
3       Castle 10   
4       Pathway 31

Upvotes: 2

Views: 446

Answers (1)

jezrael
jezrael

Reputation: 862691

First replace empty values by empty strings and forward filling missing values. Then use GroupBy.agg with aggregate join and GroupBy.first:

df['Bank'] = df['Bank'].replace('', np.nan).ffill()
df = df.groupby('Bank', as_index=False).agg({'Address':' '.join, 'Distance':'first'})
print (df)
  Bank                         Address Distance
0    A          Townhall Road #01-1001       10
1    B  MapleTree Castle 10 Pathway 31       99

Upvotes: 3

Related Questions