Process a list in a Dataframe column
<p>I created a DataFrame <code>neighbours</code> using <code>sim_measure_i</code> which is also a DataFrame.</p>

<pre><code>neighbours= sim_measure_i.apply(lambda s: s.nlargest(k).index.tolist(), axis =1)
</code></pre>

<p><code>neighbours</code> looks like this: </p>

<pre><code>1500                       [0, 1, 2, 3, 4]
1501                       [0, 1, 2, 3, 4]
1502                       [0, 1, 2, 3, 4]
1503     [7230, 12951, 13783, 8000, 18077]
1504                     [1, 3, 6, 27, 47]
</code></pre>

<p>The second column here has lists - I want to iterate over this DataFrame and work on the list such that I can read each element in the list - say 7230 and lookup a score for 7230 in another DataFrameI have which contains (id, score). </p>

<p>I would then like to add a column to this DataFrame such that it looks like </p>

<pre><code>test_case_id               nbr_list             scores             
1500                       [0, 1, 2, 3, 4]        [+1, -1, -1, +1, -1]
1501                       [0, 1, 2, 3, 4]        [+1, +1, +1, -1, -1]
1502                       [0, 1, 2, 3, 4]        [+1, +1, +1, -1, -1]
1503     [7230, 12951, 13783, 8000, 18077]        [+1, +1, +1, -1, -1]
1504                     [1, 3, 6, 27, 47]        [+1, +1, +1, -1, -1]
</code></pre>

<p>Edit: I've written a method <code>get_scores()</code></p>

<pre><code>def get_scores(list_of_neighbours):
    score_matrix = []
    for x, val in enumerate(list_of_neighbours):
        score_matrix.append(df.iloc[val].score)
    return score_matrix
</code></pre>

<p>When I try to use <code>lambda</code> on each of <code>nbr_list</code>, I get this error: </p>

<pre><code>TypeError: ("cannot do positional indexing on &lt;class 'pandas.indexes.numeric.Int64Index'&gt; with these indexers [0] of &lt;type 'str'&gt;", u'occurred at index 1500')
</code></pre>

<p>The code causing this issue: </p>

<pre><code>def nearest_neighbours(similarity_matrix, k):
    neighbours = pd.DataFrame(similarity_matrix.apply(lambda s: s.nlargest(k).index.tolist(), axis =1))
    neighbours = neighbours.rename(columns={0 : 'nbr_list'})

    nbr_scores = neighbours.apply(lambda l: get_scores(l.nbr_list), axis=1)

    print neighbours
</code></pre>

<p>Original Data Frame:</p>

<pre><code>In [68]: df
Out[68]: 
   test_case_id                   neighbours_lists
0          1500                    [0, 1, 2, 3, 4]
1          1501                    [0, 1, 2, 3, 4]
2          1502                    [0, 1, 2, 3, 4]
3          1503  [7230, 12951, 13783, 8000, 18077]
4          1504                  [1, 3, 6, 27, 47]
</code></pre>

<p>Custom function which takes id and list and does some computation to evaluate score:</p>

<pre><code>In [69]: def g(_id, nbs):
    ...:     return ['-1' if (_id + 1) % (nb + 1) else '+1' for nb in nbs]
    ...: 
</code></pre>

<p><a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.apply.html" rel="nofollow">Apply</a> custom function to all rows of original data frame:</p>

<pre><code>In [70]: scores = df.apply(lambda x: g(x.test_case_id, x.neighbours_lists), axis=1)
</code></pre>

<p><a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.to_frame.html" rel="nofollow">Convert</a> the scores series to a data frame and <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.concat.html" rel="nofollow">concat</a> it with the original data frame:    </p>

<pre><code>In [71]: df = pd.concat([df, scores.to_frame(name='scores')], 1)

In [72]: df
Out[72]: 
   test_case_id                   neighbours_lists                scores
0          1500                    [0, 1, 2, 3, 4]  [+1, -1, -1, -1, -1]
1          1501                    [0, 1, 2, 3, 4]  [+1, +1, -1, -1, -1]
2          1502                    [0, 1, 2, 3, 4]  [+1, -1, +1, -1, -1]
3          1503  [7230, 12951, 13783, 8000, 18077]  [-1, -1, -1, -1, -1]
4          1504                  [1, 3, 6, 27, 47]  [-1, -1, +1, -1, -1]
</code></pre>

python pandas numpy dataframe