Converting characters to numerical values in a dataframe

Question

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 market cap for values > 100 million, so expected output is:

       Market Cap   PE  
AAN     3.25B      23.6 
AAPL    819.30B    18.44    
ACIW    2.63B      103.15

I've tried converting the letters to the appropriate 0's with no success:

XLK['Market Cap'].replace('M','000000')
XLK.drop[XLK_quote['Market Cap'] < '100M'].index

Will you look at that... an exact duplicate. – cs95 Aug 03 '17 at 00:27 — cs95, Aug 03 '17 at 00:27

piRSquared · Accepted Answer · 2017-08-02T23:59:34.780

5

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]

edited Aug 02 '17 at 23:59

answered Aug 02 '17 at 23:30

piRSquared

285,575
57
475
624

I get ValueError: could not convert string to float: N/A .... is there a way to ignore Nan values? – thomas.mac Aug 02 '17 at 23:45
there are 500 rows in the df, guess theres one thats NA – thomas.mac Aug 02 '17 at 23:48
I updated the second version using `pd.to_numeric` to include a parameter setting that forces things it can't parse to `np.nan`. It should work with `NA` now. – piRSquared Aug 02 '17 at 23:53
I'm convinced regex is overkill here. Can we do without it? – cs95 Aug 02 '17 at 23:56
w/o regex it brings error: ValueError: invalid literal for float(): 3.08B – thomas.mac Aug 02 '17 at 23:58
One way to find out... you gonna test it... My 3 & 4 year olds have legos they want me to help them build – piRSquared Aug 02 '17 at 23:58
w/ the 'coerce' edit it now shows an empty df – thomas.mac Aug 02 '17 at 23:58
@thomas.mac Whoops, fixed – piRSquared Aug 02 '17 at 23:59

score 2 · Answer 2 · answered Aug 02 '17 at 23:33

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

Converting characters to numerical values in a dataframe

2 Answers2

Related