CODEWITHSUNDEEP
pythonpython-3.xpandasnumpy
excelguy
excelguy

Reputation: 1624

Numpy, multiply dataframe by number outputting NaN

I am trying to multiply dataframe 1 column a by dataframe 2 column b.

combineQueryandBookFiltered['pnlValue'] = np.multiply(combineQueryandBookFiltered['pnlValue'], df_fxrate['fx_rate'])

pnlValue column has many numbers and fx_rate column is just the one number.

The code executes but my end result ends up with tons of NaN .

Any help would be appreciated.

Upvotes: 1

Views: 2587

Answers (2)

Joe Ernst
Joe Ernst

Reputation: 179

-- Hi excelguy,

Is there a reason why you can't use the simple column multiplication?

df['C'] = df['A'] * df['B']

As was pointed out, multiplications of two series are based on their indices and it's likely that your fx_rate series does not have the same indices as the pnlValue series.

But since your fx_rate is only one value, I suggest multiplying your dataframe with a scalar instead:

fx_rate = df_fxrate['fx_rate'].iloc[0]
combineQueryandBookFiltered['pnlValue'] = combineQueryandBookFiltered['pnlValue'] * fx_rate

Upvotes: 3

ansev
ansev

Reputation: 30920

It is probably due to the index of your dataframe. You need to use df_fxrate['fx_rate'].values:

combineQueryandBookFiltered['pnlValue'] = np.multiply(combineQueryandBookFiltered['pnlValue'], df_fxrate['fx_rate'].values)

or better:

combineQueryandBookFiltered['pnlValue']=combineQueryandBookFiltered['pnlValue']*df_fxrate['fx_rate'].values

I show you an example:

df1=pd.DataFrame(index=[1, 2])
df2=pd.DataFrame(index=[0])
df1['col1']=[1,1]
print(df1)

 col1
1     1
2     1

df2['col1']=[1]
print(df2)

   col1
0     1

print(np.multiply(df1['col1'],df2['col1']))

0   NaN
1   NaN
2   NaN

as you can see the multiplication is done according to the index

So you need something like this:

np.multiply(df1['col1'],df2['col1'].values)

or 

df1['col1']*df2['col1'].values

Output:

1    1
2    1
Name: 1, dtype: int64

as you can see now only the df1['col1'] series index is used

Upvotes: 4

Related Questions