Creating a list based on column conditions
<p>I have a DataFrame <code>df</code></p>

<pre><code>&gt;&gt;df
   LED  CFL  Incan  Hall  Reading
0  3    2    1      100   150
1  2    3    1      150   100
2  0    1    3      200   150
3  1    2    4      300   250
4  3    3    1      170   100
</code></pre>

<p>I want to create two more column which contain <code>lists</code>, one for <code>"Hall"</code> and another for <code>"Reading"</code></p>

<pre><code>&gt;&gt;df_output
   LED  CFL  Incan  Hall  Reading Hall_List Reading_List
0  3    2    1      100   150     [0,2,0]   [2,0,0]
1  2    3    1      150   100     [0,3,0]   [2,0,0]
2  0    1    3      200   150     [0,1,0]   [0,0,2]
3  1    2    4      300   250     [0,2,0]   [1,0,0]
4  3    3    1      100   100     [0,2,0]   [2,0,0]
</code></pre>

<p>Each value within the list is populated as follows:</p>

<pre><code>cfl_rating = 50
led_rating = 100
incan_rating = 25
</code></pre>

<p>For the <code>Hall_List</code>:</p>

<p>The preference is CFL > LED > Incan. And only one of them will be used (either CFL or LED or Incan).</p>

<p>We first check if <code>CFL != 0</code> , if <code>True</code> then we calculate <code>min(ceil(Hall/CFL_rating),CFL)</code>. For <code>index=0</code> this evaluates to 2. Hence we have [0,2,0] whereas for <code>index=2</code> we have [0,1,0].</p>

<p>Similarly for <code>Reading_List</code>, the preference is LED > Incan > CFL.
For <code>index=2</code>, we have <code>LED == 0</code>, so we calculate <code>min(ceil(Reading/Incan_rating),Incan)</code> and hence Reading_List is <code>[0,0,2]</code></p>

<p><strong>My question is:</strong>
Is there a <em>"pandas/pythony-way"</em> of doing this? I am currently iterating through each row, and using if-elif-else conditions to assign values.</p>

<p>My code snippet looks like this:</p>

<pre><code>#Hall_List
for i in range(df.shape[0]):
    Hall = []
    if (df['CFL'].iloc[i] != 0):
        Hall.append(0)
        Hall.append(min((math.ceil(df['Hall'].iloc[i]/cfl_rating)),df['CFL'].iloc[i]))
        Hall.append(0)

    elif (df['LED'].iloc[i] != 0):
        Hall.append(min((math.ceil(df['Hall'].iloc[i]/led_rating)),df['LED'].iloc[i]))
        Hall.append(0)
        Hall.append(0)

    else:
        Hall.append(0)
        Hall.append(0)
        Hall.append(min((math.ceil(df['Hall'].iloc[i]/incan_rating)),df['Incan'].iloc[i]))

    df['Hall_List'].iloc[i] = Hall
</code></pre>

<p>This is really slow and definitely feels like a bad way to code this.</p>

<p>I shorten your formula for simplicity sake but you should use df.apply(axis=1)</p>

<p>this take every row and return and ndarray, then you can apply whatever function you want such has :</p>

<pre><code>df = pd.DataFrame([[3, 2, 1, 100, 150], [2, 3, 1, 150, 100]], columns=['LED', 'CFL', 'Incan', 'Hall', 'Reading'])

def create_list(ndarray):
    if ndarray[1] != 0:
        result = [0, ndarray[1], 0]
    else:
        result = [ndarray[2], 0, 0]
    return result

df['Hall_List'] = df.apply(lambda x: create_list(x), axis=1)
</code></pre>

<p>just change the function to whatever you like here.</p>

<pre><code>In[49]: df
Out[49]: 
   LED  CFL  Incan  Hall  Reading  Hall_List
0    3    2      1   100      150  [0, 2, 0]
1    2    3      1   150      100  [0, 3, 0]
</code></pre>

<p>hope this helps</p>

python pandas