Using a function to calculate new column values from old column values in pandas row by row
<p>I know there are many questions on this topic, but none of the suggested answers seem to work in this case, which I thought was trivial, but has been killing me for 2 days now.</p>

<p>This is my first effort to use pandas to process an export file from an eye-tracker. The export file contains 50 or so columns and 2 of them contain pupil dilation measures, PupilLeft and PupilRight. I want to create a new column, PupilAvg, which averages the two. When the eye tracker can't read one or both pupils, it records a -1. Since the required logic is simple but seemed a little long for a lambda, I wrote a function to return values for my new column:</p>

<pre><code>def getEyeAvg(left, right): 
    # calcs avg for Left and Right where one or both may be missing (= -1)
    if left == -1 and right == -1: return np.nan
    if left == -1: return right
    if right == -1: return left  
    return (left + right)/2.0 
</code></pre>

<p>Here's an example version of the dataframe:</p>

<pre><code>In[25]: dfd = pd.DataFrame.from_items([('PupilLeft', [3., -1., 4., -1]), ('PupilRight', [4., 4., -1., -1])])

In[26]: dfd
Out[26]: 
   PupilLeft  PupilRight
0        3.0         4.0
1       -1.0         4.0
2        4.0        -1.0
3       -1.0        -1.0
</code></pre>

<p>I want to insert my new column after PupilRight, so I try the command:</p>

<pre><code>In[27]: dfd.insert(2, 'PupilAvg', getEyeAvg(dfd.PupilLeft, dfd.PupilRight))
</code></pre>

<p>What I expect for PupilAvg is:</p>

<pre><code>   PupilLeft  PupilRight  PupilAvg
0        3.0         4.0       3.5
1       -1.0         4.0       4.0
2        4.0        -1.0       4.0
3       -1.0        -1.0       NaN
</code></pre>

<p>Of course this doesn't work and I get </p>

<pre><code>ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().
</code></pre>

<p>I've seen variations of this question asked over and over again, and it seems each answer uses some different "trick" that seems incomprehensible to me, given my relative beginner status. For example, I want neither 'any' nor 'all' rows where left == -1, I just want the current row, but this seems to be a request that pandas finds very difficult to handle.</p>

<p>It would be incredibly helpful if someone could provide a clear general solution to this problem, which basically boils down to </p>

<blockquote>
  <p>"I want to use a function to calculate values for a new column using values from other columns on a row-by-row basis, not all at once. You know, just like in Excel. Is there a simple, general way to do that?"</p>
</blockquote>

<p>This is particularly hard for folks like me who are trying to transition from Excel solutions to python/pandas, because Excel is naturally row-by-row. You just enter a formula in the first row cell and copy it all the way down the column. Clearly that mindset has ill-prepared me for pandas.</p>

<p>There's an easy way to achieve your goal while operating on whole columns.</p>

<pre><code>dfd.replace({-1:np.nan}, inplace=True)
dfd['PupilAvg'] = dfd.mean(axis=1)
</code></pre>

<p>If you need to retain the original -1 values for some reason, just copy them first and then proceed. Everything in pandas is easier with explicit nan values.</p>

<p>Your original code is failing because you're passing the entire column of data into getEyeAvg. In your example, it's trying to evaluate pd.DataFrame.from_items([('PupilLeft', [3., -1., 4., -1]) == -1, not 3. == 1. Operating on the entire column at once is the default mode in pandas, so it does require a new way of thinking. There isn't any one best way to do this, because the approaches that make the most sense coming from Excel (loop across rows directly by index or use df.apply(lambda, axis=1)) are much slower than using whole columns.</p>

python pandas dataframe calculated-columns