pandas.DataFrame: mappings a column of a list of keys to a column of the list of values
<p>Here is the input:</p>

<pre><code>df = pd.DataFrame({'keys': [('K0', 'K1'), ('K1', 'K2')],
                     'A': ['A0', 'A1']})
lookup_df = pd.DataFrame({'val': ['V1', 'V2', 'V3']},
                          index = ['K0', 'K1', 'K2'])
</code></pre>

<p>After some "join" operation, I'd like a new column be added to <code>df</code>, which maps the key in <code>keys</code> in <code>df</code> to the <code>val</code> in <code>lookup_df</code>.</p>

<p>The output should be:</p>

<pre><code>pd.DataFrame({'keys': [('K0', 'K1'), ('K1', 'K2')],
              'val': [('V0', 'V1'), ('V1', 'V2')],  
              'A': ['A0', 'A1']})
</code></pre>

<p>One way I can think of is:</p>

<pre><code>df['val'] = df['keys'].apply(lambda ks: 
    list(map(lambda k: lookup_df.loc[k].val, ks)))
</code></pre>

<p>Is there other better ways to achieve this?</p>

<p>you can do it this way:</p>

<pre><code>In [83]: df['val'] = df['keys'].str.join(',').str.split(',', expand=True).stack().map(lookup_df.val).unstack().apply(tuple)

In [84]: df
Out[84]:
    A      keys       val
0  A0  (K0, K1)  (V1, V2)
1  A1  (K1, K2)  (V2, V3)

In [85]: lookup_df
Out[85]:
   val
K0  V1
K1  V2
K2  V3
</code></pre>

<p>or a bit nicer, but slower method (thanks to <a href="http://stackoverflow.com/questions/39429189/pandas-dataframe-mappings-a-column-of-a-list-of-keys-to-a-column-of-the-list-of#comment66188610_39430871">@Boud</a>):</p>

<pre><code>In [5]: df['val'] = df['keys'].apply(pd.Series).stack().map(lookup_df.val).unstack().apply(tuple)

In [6]: df
Out[6]:
    A      keys       val
0  A0  (K0, K1)  (V1, V2)
1  A1  (K1, K2)  (V2, V3)
</code></pre>

<p><strong>Timings against 10K rows DF:</strong></p>

<pre><code>In [18]: big = pd.concat([df] * 10**5, ignore_index=True)

In [19]: x = big.head(10**4)

In [20]: x.shape
Out[20]: (10000, 2)

In [21]: %timeit x['keys'].str.join(',').str.split(',', expand=True).stack().map(lookup_df.val).unstack().apply(tuple)
10 loops, best of 3: 75.1 ms per loop

In [22]: %timeit x['keys'].apply(pd.Series).stack().map(lookup_df.val).unstack().apply(tuple)
1 loop, best of 3: 5.5 s per loop

In [23]: %timeit x['keys'].apply(pd.Series).replace(lookup_df.val).apply(tuple)
1 loop, best of 3: 5.52 s per loop

In [24]: %%timeit
   ....: dk = pd.DataFrame(x['keys'].tolist()).applymap(lambda x: lookup_df.val[x])
   ....: x['val'] = zip(dk[0], dk[1])
   ....:
1 loop, best of 3: 1.66 s per loop
</code></pre>

<p><strong>Conclusion:</strong> the ugliest method is currently the fastest one</p>

python pandas dataframe