Pandas: How to find the first valid column among a series of columns
<p>I have a dataset of different sections of a race in a pandas dataframe from which I need to calculate certain features. It looks something like this:</p>

<pre><code>id         distance     timeto1000m    timeto800m    timeto600m   timeto400m   timeto200m    timetoFinish
1          1400m        10             21            30           39           50            60    
2          1200m        0              19            31           42           49            57   
3          1800m        0              0             0            38           49            62   
4          1000m        0              0             29           40           48            61
</code></pre>

<p>So, what I need to do is for each row find the first <code>timetoXXm</code> column that is non-zero and the correspoding distance <code>XX</code>. For instance, for <code>id=1</code> that would be 1000m, for <code>id=3</code> that would be 400m etc. </p>

<p>I can do this with a series of <code>if..elif..else</code> conditions but was wondering if there is a better way of doing this kind of lookup in pandas/numpy?</p>

<p>You can do it like this, first filter the cols of interest and take a slice, then call <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.idxmin.html" rel="nofollow"><code>idxmin</code></a> on the cols of interest to return the columns where the boolean condition is met:</p>

<pre><code>In [11]:
df_slice = df.ix[:,df.columns.str.startswith('time')]
df_slice[df_slice!=0].idxmin(axis=1)

Out[11]:
0    timeto1000m
1     timeto800m
2     timeto400m
3     timeto600m
dtype: object

In [15]:
df['first_valid'] = df_slice[df_slice!=0].idxmin(axis=1)
df[['id','first_valid']]

Out[15]:
   id  first_valid
0   1  timeto1000m
1   2   timeto800m
2   3   timeto400m
3   4   timeto600m
</code></pre>

python pandas numpy feature-extraction