Pandas evaluate a string ratio into a float

Question

I have the following dataframe:

Date        Ratios
2009-08-23  2:1
2018-08-22  2:1
2019-10-24  2:1
2020-10-28  3:2

I want to convert the ratios into floats, so 2:1 becomes 2/1 becomes 0.5, 3:2 becomes 0.66667.

I used the following formula

df['Ratios'] = 1/pd.eval(df['Ratios'].str.replace(':','/'))

But I keep getting this error TypeError: unsupported operand type(s) for /: 'int' and 'list'

What's wrong with my code and how do I fix it?

Answer 1

Dont use pd.eval for Series, because if more like 100 rows it return ugly error, so need convert each value separately:

df['Ratios'] = 1/df['Ratios'].str.replace(':','/').apply(pd.eval)

But also your error seems some non numeric values together with :.

Error for 100+ rows:

AttributeError: 'PandasExprVisitor' object has no attribute 'visit_Ellipsis'

---

If not working and still error you can try test if data are correct in custom function:

print (df)
         Date Ratios
0  2009-08-23   2:1r
1  2018-08-22    2:1
2  2019-10-24    2:1
3  2020-10-28    3:2


def f(x):
    try:
        pd.eval(x)
        return False
    except:
        return True


df = df[df['Ratios'].str.replace(':','/').apply(f)]
print (df)
         Date Ratios
0  2009-08-23   2:1r