Max of pandas dataframe columns multiplied together
<p>Given this data:</p>

<pre><code>data = {'C1_IND' : [1,1,0,0,1],
        'C1_PRICE' : [55,84,0,0,103],
        'P1_IND' : [1,0,0,1,1],
        'P1_PRICE' : [72,0,0,33,95]}
df = pd.DataFrame(data)
</code></pre>

<p>How can I create a variable in the same dataframe that is:</p>

<pre><code>max(C1_IND*C1_PRICE,P1_IND*P1_PRICE)
</code></pre>

<p>Also, would there be any issues if there are nulls in that data? </p>

<p>I think you can select columns by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.filter.html" rel="nofollow"><code>filter</code></a>, then multiple by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.prod.html" rel="nofollow"><code>prod</code></a>. Last apply <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.max.html" rel="nofollow"><code>max</code></a>:</p>

<pre><code>a = df.filter(like='C1').prod(1)
b = df.filter(like='P1').prod(1)
df['max'] = pd.DataFrame({'a':a,'b':b}).max(1)
print (df)
   C1_IND  C1_PRICE  P1_IND  P1_PRICE  max
0       1        55       1        72   72
1       1        84       0         0   84
2       0         0       0         0    0
3       0         0       1        33   33
4       1       103       1        95  103
</code></pre>

<p>Or:</p>

<pre><code>df['a'] = df.filter(like='C1').prod(1)
df['b'] = df.filter(like='P1').prod(1)
df['max'] = df[['a','b']].max(1)
df = df.drop(['a','b'], axis=1)
print (df)
   C1_IND  C1_PRICE  P1_IND  P1_PRICE  max
0       1        55       1        72   72
1       1        84       0         0   84
2       0         0       0         0    0
3       0         0       1        33   33
4       1       103       1        95  103
</code></pre>

<p>It works with <code>NaN</code> also, but add parameter <code>skipna=False</code> to <code>prod</code>:</p>

<pre><code>data = {'C1_IND' : [1,1,0,0,1],
        'C1_PRICE' : [55,84,0,0,8],
        'P1_IND' : [1,0,0,1,10],
        'P1_PRICE' : [72,0,0,33,np.nan]}
df = pd.DataFrame(data)

print (df)
   C1_IND  C1_PRICE  P1_IND  P1_PRICE
0       1        55       1      72.0
1       1        84       0       0.0
2       0         0       0       0.0
3       0         0       1      33.0
4       1         8      10       NaN

a = df.filter(like='C1').prod(1, skipna=False)
b = df.filter(like='P1').prod(1, skipna=False)

print (pd.DataFrame({'a':a,'b':b}))
    a     b
0  55  72.0
1  84   0.0
2   0   0.0
3   0  33.0
4   8   NaN

df['max'] = pd.DataFrame({'a':a,'b':b}).max(1)
print (df)
   C1_IND  C1_PRICE  P1_IND  P1_PRICE   max
0       1        55       1      72.0  72.0
1       1        84       0       0.0  84.0
2       0         0       0       0.0   0.0
3       0         0       1      33.0  33.0
4       1         8      10       NaN   8.0
</code></pre>

python pandas dataframe max multiple-columns