Pandas Apply returns matrix instead of single column
<p>This is probably a stupid question, but I have been trying for a while and I can't seem to get it to work.</p>

<p>I have a dataframe:</p>

<pre><code>   df1 = pd.DataFrame({'Type': ['A','A', 'B', 'F', 'C', 'G', 'A', 'E'], 'Other': [999., 999., 999., 999., 999., 999., 999., 999.]})
</code></pre>

<p>I now want to create a new column based on the column <code>Type</code>. For this I have second dataframe:</p>

<pre><code>    df2 = pd.DataFrame({'Type':['A','B','C','D','E','F', 'G'],'Value':[1, 1, 2, 3, 4, 4, 5]})
</code></pre>

<p>that I am using as a lookup table.</p>

<p>When I try something like:</p>

<pre><code>    df1.apply(lambda x: df2.Value[df2.Type == x['Type']],axis=1)
</code></pre>

<p>I get a matrix instead of a single column:</p>

<pre><code>    Out[21]: 
        0   1   2   4   5   6
    0   1 NaN NaN NaN NaN NaN
    1   1 NaN NaN NaN NaN NaN
    2 NaN   1 NaN NaN NaN NaN
    3 NaN NaN NaN NaN   4 NaN
    4 NaN NaN   2 NaN NaN NaN
    5 NaN NaN NaN NaN NaN   5
    6   1 NaN NaN NaN NaN NaN
    7 NaN NaN NaN   4 NaN NaN
</code></pre>

<p>What I want however is this:</p>

<pre><code>        0
    0   1
    1   1
    2   1
    3   4
    4   2
    5   5
    6   1
    7   4
</code></pre>

<p>What am I doing wrong?</p>

<p>You can use <code>map</code> to achieve this:</p>

<pre><code>In [62]:
df1['Type'].map(df2.set_index('Type')['Value'],na_action='ignore')

Out[62]:
0    1
1    1
2    1
3    4
4    2
5    5
6    1
7    4
Name: Type, dtype: int64
</code></pre>

<p>If you modified your <code>apply</code> attempt to the following then it would've worked:</p>

<pre><code>In [70]:
df1['Type'].apply(lambda x: df2.loc[df2.Type == x,'Value'].values[0])

Out[70]:
0    1
1    1
2    1
3    4
4    2
5    5
6    1
7    4
Name: Type, dtype: int64
</code></pre>

<p>If we look at what you tried:</p>

<pre><code>df1.apply(lambda x: df2.Value[df2.Type == x['Type']],axis=1)
</code></pre>

<p>this is trying to compare the 'type' and return the 'value' the problem here is that you're returning a Series with the index of <code>df2</code>, this is confusing pandas and causing the matrix to be returned. You can see this if we hard code 'B' as an example:</p>

<pre><code>In [75]:
df2.Value[df2.Type == 'B']

Out[75]:
1    1
Name: Value, dtype: int64
</code></pre>

python pandas apply