Use a list to conditionally fill a new column based on values in multiple columns
<p>I am trying to populate a new column within a pandas dataframe by using values from several columns. The original columns are either <code>0</code> or '1' with exactly a single <code>1</code> per series. The new column would correspond to df['A','B','C','D'] by populating <code>new_col = [1, 3, 7, 10]</code> as shown below. (A <code>1</code> at <code>A</code> means <code>new_col = 1</code>; if <code>B=1</code>,<code>new_col = 3</code>, etc.)</p>

<pre><code>df    
         A    B    C    D
1        1    0    0    0
2        0    0    1    0
3        0    0    0    1
4        0    1    0    0
</code></pre>

<p>The new <code>df</code> should look like this.</p>

<pre><code>df    
         A    B    C    D   new_col
1        1    0    0    0         1
2        0    0    1    0         7
3        0    0    0    1        10
4        0    1    0    0         3
</code></pre>

<p>I've tried to use <code>map</code>, <code>loc</code>, and <code>where</code> but can't seem to formulate an efficient way to get it done. Problem seems very close <a href="http://stackoverflow.com/questions/36551128/how-to-fill-a-column-conditionally-to-values-in-an-other-column-being-in-a-list">to this</a>. A couple other posts I've looked at <a href="http://stackoverflow.com/questions/10715519/conditionally-fill-column-values-based-on-another-columns-value-in-pandas">1</a> <a href="http://stackoverflow.com/questions/19226488/python-pandas-change-one-value-based-on-another-value">2</a> <a href="http://stackoverflow.com/questions/17071871/select-rows-from-a-dataframe-based-on-values-in-a-column-in-pandas">3</a>. None of these show how to use multiple columns conditionally to fill a new column based on a list.</p>

<p>I can think of a few ways, mostly involving <code>argmax</code> or <code>idxmax</code>, to get either an ndarray or a Series which we can use to fill the column.</p>

<p>We could drop down to <code>numpy</code>, find the maximum locations (where the 1s are) and use those to index into an array version of new_col:</p>

<pre><code>In [148]: np.take(new_col,np.argmax(df.values,1))
Out[148]: array([ 1,  7, 10,  3])
</code></pre>

<p>We could make a Series with new_col as the values and the columns as the index, and index into that with idxmax:</p>

<pre><code>In [116]: pd.Series(new_col, index=df.columns).loc[df.idxmax(1)].values
Out[116]: array([ 1,  7, 10,  3])
</code></pre>

<p>We could use get_indexer to turn the column idxmax results into integer offsets we can use with new_col:</p>

<pre><code>In [117]: np.array(new_col)[df.columns.get_indexer(df.idxmax(axis=1))]
Out[117]: array([ 1,  7, 10,  3])
</code></pre>

<p>Or (and this seems very wasteful) we could make a new frame with the new columns and use idxmax directly:</p>

<pre><code>In [118]: pd.DataFrame(df.values, columns=new_col).idxmax(1)
Out[118]: 
0     1
1     7
2    10
3     3
dtype: int64
</code></pre>

python list python-2.7 pandas