how to add subtract divide and multiply columns on a dataframe?

Question

i hope you can help me. i need to get this result: for item in each row, create Max(In/Out Util)-1Mbr column with result:

df['Max(In/Out Util)-1Mbr'] = [df['Max(In/Out Util)']/100*df['Egress Speed']] / [df['Egress Speed']-(df['Egress Speed']/df['Oper. Member Count'])]*100

Original DataFrame

Site Name           Oper. Member Count  Egress Speed    Max(In/Out Util)
KB201_XXX71_SR7                     2       6000000             11,1527 
KB201_XXX71_SR7                     2           N/A                 N/A 
KN092_XXX71_SR7                     4       4000000             9,60783

FinalDataFrame

Site Name           Oper. Member Count  Egress Speed    Max(In/Out Util)    Max(In/Out Util)-1Mbr
KB201_XXX71_SR7                     2       6000000             11,1527                     22,3
KB201_XXX71_SR7                     2           N/A                 N/A                     N/A
KN092_XXX71_SR7                     4       4000000             9,60783                     12,8

yes, but the name of columns are generic in this example.. I will give you a real example — juanpb12, Sep 10 '19 at 14:30
@anky_91 solution does use the dot notation shortcut that will not work with complex column naming. Use `df['column name']` notation. — Scott Boston, Sep 10 '19 at 14:56
@ScottBoston what´s your idea? can you explain me more? thanks!! — juanpb12, Sep 10 '19 at 15:00
Can you please put the expected number instead of "result" in the `final dataframe`? Otherwise I can't try and match my numbers with your expected output. — Celius Stingher, Sep 10 '19 at 15:03
Please use multiple parenthesis, not brackets I am finally making sense of your formula... — Celius Stingher, Sep 10 '19 at 15:14

Celius Stingher · Answer 1 · 2019-09-10T15:53:06.297

3

All you need to do is set up the math formula to express the result with the name of the columns:

   df['col4'] = (df['col1']+df['col2'])/df['col3']

EDIT: Newly requested formula:

df['Max(In/Out Util)-1Mbr'] = [df['Max(In/Out Util)']/100*df['Egress Speed']] / [df['Egress Speed']-(df['Egress Speed']/df['Oper. Member Count'])]*100

EDIT2: Based on the fact your numbers are coded with Latin notation (this is, commas being used to symbolize decimal places) you should start by using:

df = df.apply(lambda x: x.str.replace(',','.'))
df['Max(In/Out Util)-1Mbr'] = df['Max(In/Out Util)-1Mbr'].astype(int)
df['Egress Speed'] = df['Egress Speed'].astype(int)
df['Oper. Member Count'] = df['Oper. Member Count'].astype(int)

And then your proposed formula:

df['Max(In/Out Util)-1Mbr'] = (df['Max(In/Out Util)']*df['Egress Speed']) / (df['Egress Speed'] - df['Egress Speed'] / df['Oper. Member Count'])

Just use the final formula and it should work.

edited Sep 10 '19 at 15:53

answered Sep 10 '19 at 14:28

Celius Stingher

17,835
6
23
53

You are misusing brackets instead of parenthesis – Celius Stingher Sep 10 '19 at 14:55
Using the final formula, it gives me an error: TypeError: unsupported operand type(s) for /: 'str' and 'int' – juanpb12 Sep 10 '19 at 15:27
Are all your columns integers/floats? – Celius Stingher Sep 10 '19 at 15:39
1

This is because the datatype of the Egress and Max columns are strings instead of numbers. You need to use `pd.to_numeric` on those columns first, but first you need to remove the commas. – Scott Boston Sep 10 '19 at 15:39
1

True, he is using Latin notation where commas are used to represent decimal places in numbers . – Celius Stingher Sep 10 '19 at 15:42

score 1 · Answer 2 · answered Sep 10 '19 at 14:28

1

It's just:

df['col4']= (df['col1']+df['col2'])/df['col3']

answered Sep 10 '19 at 14:28

jottbe

4,228
1
15
31

1

Careful with the formula as it is read as df['col4'] = a + b/c – Celius Stingher Sep 10 '19 at 14:29

how to add subtract divide and multiply columns on a dataframe?

2 Answers2