So, I have extracted 2 dataframes as shown below:
DF1:
And I wish to apply a factor onto different parts of column pricedata 1 and pricedata2 for DF1 based on the conditional matching in another dataframe.
For instance, for row 0 in DF1, I hope to apply a factor onto pricedata1 value 100.5 by multiplying 2.5 which is derived from DF2 based on the condition where DF1 column year value == DF2 column name and DF1 column name == DF2 pricename column value. And then for year 2007 for pricedata1, to apply another factor of 5 instead.
I know about using df.apply for a entire column, I’m pretty lost on how to partially apply it to a column based on different if conditions
Thanks in advance
Advertisement
Answer
A concise solution is to reindex()
your df2
on df1
. First reshape df2
to match df1
(years as rows, price names as columns), then reindex()
and multiply the scaling factors element-wise.
Note: This relies on both indexes having the same dtype, so convert year.astype(...)
as needed.
df2 = df2.set_index('pricename').T.reindex(df1.year) df1.pricedata1 = df1.pricedata1 * df2.pricedata1.values df1.pricedata2 = df1.pricedata2 * df2.pricedata2.values # date year pricedata1 pricedata2 # 2006-01-02 2006 251.25 169.5 # 2006-01-03 2006 251.25 169.5 # 2006-01-04 2006 251.25 169.5 # 2006-01-05 2006 251.25 169.5 # 2006-01-06 2006 251.25 169.5 # 2006-01-07 2006 251.25 169.5 # 2006-01-08 2006 251.25 169.5 # 2006-01-09 2006 251.25 169.5 # 2006-01-10 2006 251.25 169.5 # 2006-01-11 2006 251.25 169.5 # 2006-01-12 2006 251.25 169.5 # 2006-01-13 2006 251.25 169.5 # 2006-01-14 2006 251.25 169.5 # 2006-01-15 2006 251.25 169.5 # 2007-01-02 2007 502.50 339.0 # 2007-01-03 2007 502.50 339.0 # 2007-01-04 2007 502.50 339.0 # 2007-01-05 2007 502.50 339.0