Converting Characters To Numerical Values In A Dataframe

I have a df called 'XLK': Market Cap PE AAN 3.25B 23.6 AAPL 819.30B 18.44 ACFN 6.18M 2.1 ACIW 2.63B 103.15 I just want the mark

Solution 1:

Use replace with regex=True and use replacement strings that emulate scientific notation. Then use astype(float) or pd.to_numeric.

df[df.Market_Cap.replace(dict(B='E9', M='E6'), regex=True).astype(float) >= 100E6]

     Market_Cap      PE
AAN       3.25B   23.60
AAPL    819.30B   18.44
ACIW      2.63B  103.15

---

Equivalently

dct = dict(B='E9', M='E6')
num = pd.to_numeric(df.Market_Cap.replace(dct, regex=True), 'coerce')
df[num >= 100E6]

Solution 2:

Alternatively, specify a mapping and then substitute with str.map:

In [723]: mapping
Out[723]: {'B': 1000000000, 'K': 1000, 'M': 1000000}

In [724]: df[df['Market Cap'].str[:-1].astype(float) * df['Market Cap'].str[-1].map(mapping) > 100e6]
Out[724]: 
     Market Cap      PE
AAN       3.25B   23.60
AAPL    819.30B   18.44
ACIW      2.63B  103.15