Efficient way to calculate every columns in pandas?

user_3118602 0
Viewed 157

I have the following DataFrame:

            1-A-873   2-A-129   3-A-123
12/12/20      45        32        41
13/12/20      94        56        87
14/12/20      12        42        84
15/12/20      73        24        25

Each column represent an equipment. Each equipment has a size that is declared in the code:

1A = 5
2A = 3
3A = 7

Every column will need to be divided by this equipment size that is - (value / size)

This is what I am using:

df["1A-NewValue"] = df["1-A-873"] / 1A
df["2A-NewValue"] = df["2-A-129"] / 2A
df["3A-NewValue"] = df["3-A-123"] / 3A

End result:

            1-A-873   2-A-129   3-A-123   1A-NewValue   2A-NewValue   3A-NewValue
12/12/20      45        32        41           9           10.67         5.86    
13/12/20      94        56        87         18.8          18.67         12.43
14/12/20      12        42        84          2.4           14            12
15/12/20      73        24        25         14.6           8            3.57

This works perfectly and do what I want by having three extra columns at the end of the DataFrame.

However, this will be a tedious effort later on if my total number of equipment increases to 250 instead of 3; I will need to have 250 lines for equipment size and 250 lines for the formula.

Naturally the first thing that come to my mind is a for loop, but is there a more Pandas-way of doing this efficiently?

Thanks!

pandas python
1 Answers

You can create dictionary, rename columns names by split by - and join first 2 values for match and divide like:

d = {'1A': 5, '2A':3, '3A':7}
f = lambda x: ''.join(x.split('-')[:2])
df = df.join(df.rename(columns=f).div(d).add_suffix(' NewValue'))

print (df)
          1-A-873  2-A-129  3-A-123  1A NewValue  2A NewValue  3A NewValue
12/12/20       45       32       41          9.0    10.666667     5.857143
13/12/20       94       56       87         18.8    18.666667    12.428571
14/12/20       12       42       84          2.4    14.000000    12.000000
15/12/20       73       24       25         14.6     8.000000     3.571429
user_29010020
Related