Pandas Slow. Want first occurrence in DataFrame
<p>I have a DataFrame of <code>people</code>. One of the columns in this DataFrame is a <code>place_id</code>. I also have a DataFrame of places, where one of the columns is <code>place_id</code> and another is <code>weather</code>. For every person, I am trying to find the corresponding weather. Importantly, many people have the same <code>place_id</code>s. </p>

<p>Currently, my setup is this:</p>

<pre><code>def place_id_to_weather(pid):
    return place_df[place_df['place_id'] == pid]['weather'].item() 

person_df['weather'] = person_df['place_id'].map(place_id_to_weather)`
</code></pre>

<p>But this is untenably slow. I would like to speed this up. I suspect that I could achieve a speedup like this:</p>

<p>Instead of returning <code>place_df[...].item()</code>, which does a search for <code>place_id == pid</code> for that entire column and returns a series, and then grabbing the first item in that series, I really just want to curtail the search in <code>place_df</code> after the first match <code>place_df['place_id']==pid</code> has been found. After that, I don't need to search any further. How do I limit the search to first occurrences only?</p>

<p>Are there other methods I could use to achieve a speedup here? Some kind of join-type method?</p>

<p>I think you need <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.drop_duplicates.html" rel="nofollow"><code>drop_duplicates</code></a> with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.merge.html" rel="nofollow"><code>merge</code></a>, if there is only common columns <code>place_id</code> and <code>weather</code> in both <code>DataFrames</code>, you can omit parameter <code>on</code> (it depends of data, maybe <code>on='place_id'</code> is necessary):</p>

<pre><code>df1 = place_df.drop_duplicates(['place_id'])
print (df1)

print (pd.merge(person_df, df1))
</code></pre>

<p>Sample data:</p>

<pre><code>person_df = pd.DataFrame({'place_id':['s','d','f','s','d','f'],
                          'A':[4,5,6,7,8,9]})
print (person_df)
   A place_id
0  4        s
1  5        d
2  6        f
3  7        s
4  8        d
5  9        f

place_df = pd.DataFrame({'place_id':['s','d','f', 's','d','f'],
                         'weather':['y','e','r', 'h','u','i']})
print (place_df)
  place_id weather
0        s       y
1        d       e
2        f       r
3        s       h
4        d       u
5        f       i
</code></pre>



<pre><code>def place_id_to_weather(pid):
    #for first occurence add iloc[0]
    return place_df[place_df['place_id'] == pid]['weather'].iloc[0]

person_df['weather'] = person_df['place_id'].map(place_id_to_weather)
print (person_df)
   A place_id weather
0  4        s       y
1  5        d       e
2  6        f       r
3  7        s       y
4  8        d       e
5  9        f       r
</code></pre>

<hr>

<pre><code>#keep='first' is by default, so can be omit
print (place_df.drop_duplicates(['place_id']))
  place_id weather
0        s       y
1        d       e
2        f       r

print (pd.merge(person_df, place_df.drop_duplicates(['place_id'])))
   A place_id weather
0  4        s       y
1  7        s       y
2  5        d       e
3  8        d       e
4  6        f       r
5  9        f       r
</code></pre>

python pandas