CODEWITHSUNDEEP
pythonpandasdataframe
jenny
jenny

Reputation: 811

How to multiply two dataframes if they have the same index value along the corresponding row?

Suppose I have something like this (which may have the forecast_date index repeated):

df1: 
forecast_date    value

2015-04-11       18952
2015-04-12       18938
2015-04-13       18940
2015-04-14       18949
2015-04-15       18955
2015-04-16       18956
...
2015-04-02       18950
2015-04-03       18968

I also have another dataframe that is like this (indices here are never duplicated):

df2:
date           value
2015-04-01     1.3
2015-04-02     1.35
2015-04-03     1.34
2015-04-04     1.45
....

I want to multiple the df1 row value by the df2 row value if their indices match. What is an elegant way to do this in pandas? This is probably really easy and I am just overlooking it.

Thanks.

Upvotes: 0

Views: 3080

Answers (3)

muon
muon

Reputation: 14037

you could use

df1.multiply(df2)

check pandas.DataFrame.multiply

Upvotes: 0

EdChum
EdChum

Reputation: 394071

If you set the index to be the dates for both df's then multiplication will align where the indices match:

In [46]:

df['value'] * df1['value']
Out[46]:
2015-04-01         NaN
2015-04-02    25582.50
2015-04-03    25417.12
2015-04-04         NaN
2015-04-11         NaN
2015-04-12         NaN
2015-04-13         NaN
2015-04-14         NaN
2015-04-15         NaN
2015-04-16         NaN
Name: value, dtype: float64

The question is whether you want NaN values where the rows are missing or not.

EDIT

If you have duplicate date values then what you could do is left merge the other df's value column and then multiply the 2 columns so the following should work:

In [58]:

df1.rename(columns={'value':'other_value'}, inplace=True)
merged = df.merge(df1, left_on='forecast_date', right_on='date', how='left')
merged['new_value'] = merged['value'] * merged['other_value']
merged
Out[58]:
  forecast_date  value        date  other_value  new_value
0    2015-04-11  18952         NaN          NaN        NaN
1    2015-04-12  18938         NaN          NaN        NaN
2    2015-04-13  18940         NaN          NaN        NaN
3    2015-04-14  18949         NaN          NaN        NaN
4    2015-04-15  18955         NaN          NaN        NaN
5    2015-04-16  18956         NaN          NaN        NaN
6    2015-04-02  18950  2015-04-02         1.35   25582.50
7    2015-04-03  18968  2015-04-03         1.34   25417.12

The above assumes that the date columns have not been set as the index already.

Upvotes: 1

Tenzin
Tenzin

Reputation: 2505

You could make use of a array to store the values. And then look through arrayA if the same value occurs in arrayB. If yes, do a calculation.

Upvotes: 0

Related Questions