Divide rows of a dataframe conditional on the value of one column

user_13067389 0
Viewed 119

I have a dataframe:

import pandas as pd

df1 = pd.DataFrame({"Line":['Sales', 'Volume', 'ANR', 'EBT'], "a":[10, 4, 64, 32],
         "b":[4, 8, 32, 48],
         "c":[16, 4, 8, 24]})

I want to divide each row by a different constant. So I want to divide "Sales" by 10, "Volume" by 1, "ANR" by 100, "EBT" by 1. I can do the following:

df2 = pd.DataFrame({"A":[10, 1, 100, 1]})
df3 = df1.iloc[:, 1:].div(df2.A, axis=0)

This gets me to the right answer for this particular example. But I want the operation to be smarter. I want it to look up the value in the Line column. If it is "Sales", then divide by 10 etc.

The reason is that the dataframe that I am working with has 1000s of rows. I don't want to create and maintain a series like df2.

For the above example, the answer should look like:

df3 = pd.DataFrame({"Line":['Sales', 'Volume', 'ANR', 'EBT'], "a":[1, 4, 0.64, 32],
         "b":[0.4, 8, 0.32, 48],
         "c":[1.6, 4, 0.8, 24]})
pandas conditional-statements dataframe
1 Answers

You can collect your divisor rules into a dictionary:

div = {'Sales' : 10, 'Volume' : 1, 'ANR' : 100, 'EBT':1}

and then divide columns 'a','b','c' by a column obtained by a map of 'Line' with div:

df1[['a','b','c']] = df1[['a','b','c']].div( df1['Line'].map(div), axis = 0)
df1

to obtain

    Line    a       b       c
0   Sales   1.00    0.40    1.60
1   Volume  4.00    8.00    4.00
2   ANR     0.64    0.32    0.08
3   EBT     32.00   48.00   24.00
user_145514260
Related