Conditional multiplication based on column value in Pandas

user_2753629 0
Viewed 531

I have a dataframe as follows:

index  client year  value
  1      A    2011    5
  2      A    2012    10
  ...
  8      A    2018    7
  9      B    2011    14
  10     B    2012    54
  ...
  ...    Z    2011    5

I need to multiply the values using year dependent values. I have a Series as follows:

2011  2
2012  2.5
2013  3
2014  3.5
...
2018  5.5

I need for all years the values to be multiplied by these year dependent values. E.g. the it would look like

index  client year  value
  1      A    2011    10
  2      A    2012    25
 ...etc

I wrote a loop to do this for now, but it is far from elegant and efficient. How to do this efficiently and elegant?

pandas-groupby pandas
2 Answers

Using reindex 

df.value*=s.reindex(df.year).values
df
Out[44]: 
   index client  year  value
0      1      A  2011   10.0
1      2      A  2012   25.0
user_79645270

If your series looks like:

>>> s
0
2011    2.0
2012    2.5
2013    3.0
2014    3.5
2018    5.5
Name: 1, dtype: float64

Then you can do this using map:

df['value'] = df['value'].mul(df.year.map(s))

And you get:

>>> df
   index client  year  value
0      1      A  2011   10.0
1      2      A  2012   25.0
2      8      A  2018   38.5
3      9      B  2011   28.0
4     10      B  2012  135.0
....
user_66711760
Related