CODEWITHSUNDEEP
pythonpandasdataframemaxmultiple-columns
Angus
Angus

Reputation: 167

Max of pandas dataframe columns multiplied together

Given this data:

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)

How can I create a variable in the same dataframe that is:

max(C1_IND*C1_PRICE,P1_IND*P1_PRICE)

Also, would there be any issues if there are nulls in that data?

Upvotes: 2

Views: 57

Answers (1)

jezrael
jezrael

Reputation: 863166

I think you can select columns by filter, then multiple by prod. Last apply max:

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

Or:

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

It works with NaN also, but add parameter skipna=False to prod:

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

Upvotes: 3

Related Questions