vectorized look-up of values in pandas dataframe: another example
<p>I've looked for example at 
<a href="https://stackoverflow.com/questions/13893227/vectorized-look-up-of-values-in-pandas-dataframe">Vectorized look-up of values in Pandas dataframe</a></p>

<p>but somehow my problem is a little bit different and i can't find the right way to do it, it's a simple vlookup</p>

<p>so i have a DataFrame </p>

<pre><code>PoliceStations_raw=pd.DataFrame(
[['BAYVIEW'   ,37.729732,-122.397981],
 ['CENTRAL'   ,37.798732,-122.409919],
 ['INGLESIDE' ,37.724676,-122.446215],
 ['MISSION'   ,37.762849,-122.422005],
 ['NORTHERN'  ,37.780186,-122.432467],
 ['PARK'      ,37.767797,-122.455287],
 ['RICHMOND'  ,37.779928,-122.464467],
 ['SOUTHERN'  ,37.772380,-122.389412],
 ['TARAVAL'   ,37.743733,-122.481500],
 ['TENDERLOIN',37.783674,-122.412899]],columns=['PdDistrict','XX','YY'])
</code></pre>

<p>i also defined </p>

<pre><code>PoliceStations=PoliceStations_raw.transpose()
</code></pre>

<p>Then i have another table df which has a column 'PdDistrict' containing a categorical variable which can take values like 'BAYVIEW', 'CENTRAL' etc...</p>

<p>i would like to have a column
df['XX'] which would return for each row of df the corresponding entry in the PoliceStations_raw...</p>

<p>i can't find the right syntax ... thanks for your help</p>

<p>if possible, i prefer the syntax involving PoliceStations_raw (instead of the transposed table), as i think this table is more "natural"...</p>

<p>I tried this but it doesn't work</p>

<pre><code>df_raw['value'] = PoliceStations.lookup('XX',df_raw['PdDistrict'])
</code></pre>

<blockquote>
  <p>--------------------------------------------------------------------------- ValueError                                Traceback (most recent call
  last)  in ()
  ----> 1 df_raw['value'] = PoliceStations.lookup('XX',df_raw['PdDistrict'])</p>
  
  <p>/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/pandas/core/frame.pyc
  in lookup(self, row_labels, col_labels)    2641         n =
  len(row_labels)    2642         if n != len(col_labels):
  -> 2643             raise ValueError('Row labels must have same size as column labels')    2644     2645         thresh = 1000</p>
  
  <p>ValueError: Row labels must have same size as column labels</p>
</blockquote>

<p>although I think i've made no labelling mistakes</p>

<pre><code>df_raw['PdDistrict'].cat.categories
</code></pre>

<blockquote>
  <p>Index([u'BAYVIEW', u'CENTRAL', u'INGLESIDE', u'MISSION', u'NORTHERN',
  u'PARK',
         u'RICHMOND', u'SOUTHERN', u'TARAVAL', u'TENDERLOIN'],
        dtype='object')</p>
</blockquote>

<p>EDIT: </p>

<p>I am also trying the following:</p>

<pre><code>PoliceStations_raw=pd.DataFrame(
[['BAYVIEW'   ,37.729732,-122.397981],
 ['CENTRAL'   ,37.798732,-122.409919],
 ['INGLESIDE' ,37.724676,-122.446215],
 ['MISSION'   ,37.762849,-122.422005],
 ['NORTHERN'  ,37.780186,-122.432467],
 ['PARK'      ,37.767797,-122.455287],
 ['RICHMOND'  ,37.779928,-122.464467],
 ['SOUTHERN'  ,37.772380,-122.389412],
 ['TARAVAL'   ,37.743733,-122.481500],
 ['TENDERLOIN',37.783674,-122.412899]],columns=['PdDistrict','XX','YY'])


df1=pd.DataFrame([[0,'CENTRAL'],[1,'TARAVAL'],[3,'CENTRAL'],[2,'BAYVIEW']])
df1.columns = ['Index','PdDistrict']


  Index PdDistrict
0   0   CENTRAL
1   1   TARAVAL
2   3   CENTRAL
3   2   BAYVIEW
</code></pre>

<p>despite having typed in sort=False, the returned object has merged the table but is using PdDistrict as some Index and has changed the order of the rows of the original left dataframe.</p>

<p>help please !</p>

<pre><code>pd.merge(df1,PoliceStations_raw,sort=False)
</code></pre>

<p>is returning me this</p>

<pre><code>  Index PdDistrict  XX        YY
0   0   CENTRAL 37.798732   -122.409919
1   3   CENTRAL 37.798732   -122.409919
2   1   TARAVAL 37.743733   -122.481500
3   2   BAYVIEW 37.729732   -122.397981
</code></pre>