dataframe multiply some columns with a series

Question

I have a dataframe df1 where the index is a DatetimeIndex and there are 5 columns, col1, col2, col3, col4, col5.

I have another df2 which has an almost equal datetimeindex (some days of df1 may be missing from df1), and a single 'Value' column.

I would like to multiply df1 in-place by the Value from df2 when the dates are the same. But not for all columns col1...col5, only col1...col4

I can see it is possible to multiply col1*Value, then col2*Value and so on... and make up a new dataframe to replace df1.

Is there a more efficient way?

JAB JAB · Accepted Answer · 2015-02-07T16:51:47

You an achieve this, by reindexing the second dataframe so they are the same shape, and then using the dataframe operator mul:

Create two data frames with datetime series. The second one using only business days to make sure we have gaps between the two. Set the dates as indices.

import pandas as pd
# first frame
rng1 = pd.date_range('1/1/2011', periods=90, freq='D')
df1 = pd.DataFrame({'value':range(1,91),'date':rng1})
df1.set_index('date', inplace =True)

# second frame with a business day date index
rng2 = pd.date_range('1/1/2011', periods=90, freq='B')
df2 = pd.DataFrame({'date':rng2}) 
df2['value_to_multiply'] = range(1-91)
df2.set_index('date', inplace =True)

reindex the second frame with the index from the first. Df1 will now have gaps for non-business days filled with the first previous valid observation.

# reindex the second dataframe to match the first
df2 =df2.reindex(index= df1.index, method = 'ffill')

Multiple df2 by df1['value_to_multiply_by']:

# multiple filling nans with 1 to avoid propagating nans
# nans can still exists if there are no valid previous observations such as at the beginning of a dataframe 
df1.mul(df2['value_to_multiply_by'].fillna(1), axis=0)

dataframe multiply some columns with a series

1 Answers