title: Multiple Column + Conditional Joins
body: <p>I have two data frames that I would like to join together (bike rides data frame and a bike stations data frame).</p>
  
  <p>I have been working with pandas library but I can't seem to write the code to manipulate the join perfectly. Initially, I was just joining on the key "station_id" but I found a more recently updated stations data set that included more stations, the problem is there are some stations that do not have a station_id. For those stations, I wanted to join on matching the latitude and longitude coordinates. </p>
  
  <p>Initial code for when I was just using station_id to join the data frames</p>
  
  <pre><code>rides_df = rides_df.rename(columns = {'start_station_id': 'station_id'})
  rides_df = rides_df.merge(stations_df[['station_id','station_name']],
                           on = 'station_id', how = 'left')
  rides_df = rides_df.rename(columns = {'station_id':'start_station_id',
                                       'station_name':'station_name_start'})
  
  #merge ending station name
  rides_df = rides_df.rename(columns = {'end_station_id': 'station_id'})
  rides_df = rides_df.merge(stations_df[['station_id', 'station_name']],
                           on = 'station_id', how = 'left')
  rides_df = rides_df.rename(columns = {'station_id':'end_station_id',
                                       'station_name': 'station_name_end'})
  </code></pre>
  
  <p>The rides data frame is structured as follows (sampled):</p>
  
  <pre><code>rides_df = pd.DataFrame([[1912818,'Round Trip',3014,34.0566101,-118.23721,3014,34.0566101,-118.23721],
                   [1933383,'Round Trip',3016,34.0528984,-118.24156,3016,34.0528984,-118.24156],
                    [1944197,'Round Trip',3016,34.0528984,-118.24156,3016,34.0528984,-118.24156],
                    [1940317,'Round Trip','NaN',34.03352,-118.24184,'NaN',34.03352,-118.24184],
                    [1944075,'One Way',3021,34.0456085,-118.23703,3016,34.0566101,-118.23721]]
                   , columns = ['trip_id','trip_route_category','start_station_id','start_lat',
                                'start_lon','end_station_id','end_lat','end_lon'])
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/BA9Xf.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/BA9Xf.png" alt="enter image description here"></a></p>
  
  <p>The stations data frame is structured as follows (sampled):</p>
  
  <pre><code>stations_df = pd.DataFrame([['Union Station West Portal',34.05661,-118.23721,3014],
                              ['Los Angeles &amp; Temple',34.0529,-118.24156,3016],
                              ['Grand &amp; Olympic',34.04373,-118.26014,3018],
                              ['12th &amp; Hill',34.03861,-118.26086,3019],
                              ['Hill &amp; Washington',34.03105,-118.26709,3020],
                              ['Row DTLA',34.03352,-118.24184,'NaN']], 
                             columns = ['station_name', 'lat', 'lon','station_id'])
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/3WBhN.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/3WBhN.png" alt="enter image description here"></a>
  What I want is to add the station name for the starting location and ending location on the rides data frame so I would have a column for "Start_Station_Name" and "End_Station_Name". I would want to join on "station_id" but if station_id is NaN then to match lat&amp;lon for both start and end.</p>
  
  <pre><code>The data frame that I want as a result is structured as follows:
  want_df = pd.DataFrame([[1912818,'Round Trip','Union Station West Portal',3014,34.0566101,-118.23721,'Union Station West Portal',3014,34.0566101,-118.23721],
                   [1933383,'Round Trip','Los Angeles &amp; Temple',3016,34.0528984,-118.24156,'Los Angeles &amp; Temple',3016,34.0528984,-118.24156],
                    [1944197,'Round Trip','Los Angeles &amp; Temple',3016,34.0528984,-118.24156,'Los Angeles &amp; Temple',3016,34.0528984,-118.24156],
                    [1940317,'Round Trip','Row DTLA','Nan',34.03352,-118.24184,'Row DTLA','Nan',34.03352,-118.24184],
                  [1944075,'One Way','NaN',3021,34.0456085,-118.23703,'Los Angeles &amp; Temple',3016,34.0566101,-118.23721]]
                   , columns = ['trip_id','trip_route_category','start_station_name','start_station_id','start_lat',
                                'start_lon','end_station_name','end_station_id','end_lat','end_lon'])
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/0D6cO.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0D6cO.png" alt="enter image description here"></a></p>