CODEWITHSUNDEEP
pythonregexpandasmerge
Nikko
Nikko

Reputation: 1572

Pandas merging two rows with regex conditions

Suppose I have a dataframe:

    name                                            lat         lon
0   Changchong-Luya Creek (Sta. 0+000-1+680) Start  15.085700   120.919967
1   Sudan Creek (Sta. 0+000 - 3+200) Start          15.073000   120.915930
2   Sudan Creek (Sta. 0+000 - 3+200) End            15.079350   120.893350
3   Nikko Boss Creek (Sta. 0+000 - 3+000) Start     15.079980   120.912930
4   Nikko Boss Creek (Sta. 0+000 - 3+000) End       15.076720   120.925670

I want to merge two rows if conditions are met:

  1. They are the same creek
  2. If one is the Start and the other is the End of the same creek

The resulting dataframe is:

  name                  start_lat  start_lon  end_lat    end_lon   
0 Sudan Creek           15.073000 120.915930  15.079350  120.893350
1 Nikko Boss Creek      15.079980 120.912930  15.076720  120.925670

I have iterated over the rows, but is there some pandas way with the mix of regex perhaps?

Upvotes: 1

Views: 495

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150785

Here's my approach:

# extract the information and join
new_df = df.join(df['name'].str.extract('(.*) \(.*\) (Start|End)'))

# pivot 
new_df = new_df.pivot(index=0, columns=1, values=['lat','lon'])

# flatten column names
new_df.columns = [f'{b.lower()}_{a}' for a,b in new_df.columns]

# remove nan
new_df.dropna(how='any')

Output:

                   end_lat  start_lat    end_lon  start_lon
0                                                          
Nikko Boss Creek  15.07672   15.07998  120.92567  120.91293
Sudan Creek       15.07935   15.07300  120.89335  120.91593

Upvotes: 2

Related Questions