Skip to content
Advertisement

How can I scale a pandas dataframe based on row/column scaling factors defined in another dataframe?

So, I have extracted 2 dataframes as shown below:
DF1:enter image description here

DF2: enter image description here

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

Desired Output: enter image description here

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
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement