0

I have a df that I'm trying populate a new column based on a calculation. Here is an example below.

import pandas as pd 
import numpy as np
df = pd.DataFrame(
        {"Quantity" :[4721,1647],
         "Total" : [236.05,82.35]},
        index = [1,2])

df["CPS Gross"]= (df["Total"]/df["Quantity"])

conditions = [df["CPS Gross"] == 0.05]
values = [0.03]

df["CPS Calc"] = np.select(conditions,values)

print(df)

Can someone explain to me why the second row does not meet the condition test but the first row does even though they both meet the criteria of 0.05?

Any help would be much appreciated

mozway
  • 194,879
  • 13
  • 39
  • 75
Pedro Schneider
  • 3
  • 2

2 Answers2

3

I cannot comment your original post so I'll leave you an answer.

The reason why the second row does not meet the condition is because floats are not a precise type. Despite the real result of the operation 82.35/1647 is 0.05, if you check it on Python you will see that the result it gives is 0.049999999999999996, that is not exactly 0.05. That's the reason why 82.35/1647 == 0.05 is False... because it is really False! (in Python)

One solution you could try is using some method to take in consideration this behaviour like math.isclose(). It does not check if two values are exactly the same but if two values are close to each other in a certain range (called tolerance).

from math import isclose

...

conditions = [ws['New Gross'].apply(lambda x: isclose(x, 0.05, abs_tol=1e-8))]
values = [0.03]

df["CPS Calc"] = np.select(conditions, values)

If you check that you will see like now both rows are selected!

tdy
  • 36,675
  • 19
  • 86
  • 83
rmoret
  • 133
  • 2
  • 10
  • I've used `isclose` in an apply function because `ws[New Gross]` is a Series so you cannot put it in the first param of `isclose` just like that. – rmoret Nov 02 '21 at 23:17
  • 1
    good answer, but note that [`np.isclose`](https://numpy.org/doc/stable/reference/generated/numpy.isclose.html) should be faster: `conditions = np.isclose(df['CPS Gross'], 0.05)` – tdy Nov 02 '21 at 23:30
0

You can replace your np.select function with the np.where function as it can have a singular value as input:

ws["CPS Calc"] = np.where(ws["New Gross"]==0.05,0.03,np.nan)

The last value is the default when the condition is not true (I assumed it to be NaN but can be any value).

jhso
  • 3,103
  • 1
  • 5
  • 13
  • I'm confused as to why the second row in my example fails to meet the conditions I've set. Can you explain to me why the second row fails the condition but the first one passes? – Pedro Schneider Nov 02 '21 at 22:55
  • When I run `np.select` with your values I get `ValueError: list of cases must be same length as list of conditions`. It's exactly as I said: `np.select` selects values from an array according to a list of conditions. You are selecting values from a single array so of course it wouldn't work. – jhso Nov 02 '21 at 22:58
  • Hey can you take another look at the question? Sorry i don't think i was clear the first time around. That code in the question should not error out – Pedro Schneider Nov 02 '21 at 23:15
  • Ok, next time please provide the values in the question to help us answer it, although I think @rmoret has answered your question. – jhso Nov 02 '21 at 23:22