Pandas: extract or split char from a numeric string

Question

I have a dataframe selected from a sql table that looks like this:

   id shares_float
0   1      621.76M
1   2      329.51M

in another word,

[(1, '621.76M'), (2, '329.51M')]

I want to split share_float so that if it is “B”, multiply 1,000,000,000, and if it is “M”, multiply 1,000,000, and if it is not or does not have a trailing character, just convert and assign the number.

The result should be a float type.

   ticker_id  shares_float     float_value
0          1       621.76M    621760000.00
1          2         3.51B   3510000000.00

I am new to pandas. Is there any way to do this in pandas? or do I need to convert the data to a list and do my manipulations in a loop and then convert it back to a pandas DataFrame?

Note added: The answer works great! Thank. By the way, how does this feature work?

Answer 1

, , 624540000:

In [9]:

D={'M':'*1e6', 'B':'*1e9'}
df['float_value']=df.shares_float.apply(lambda x: eval(x[:-1]+D[x[-1]]))
In [10]:

print df
   ticker_id shares_float  float_value
0          1      621.76M   621760000
1          2        3.51B  3510000000

[2 rows x 3 columns]
In [11]:

df.dtypes
Out[11]:
ticker_id         int64
shares_float     object
float_value     float64
dtype: object

Answer 2

; , , :

>>> df
   id shares_float
0   1            5
1   2           6M
2   3           7B

[3 rows x 2 columns]

:

>>> sh = df.shares_float.str.extract(r'(?P<val>[0-9.]*)(?P<unit>[MB]{0,1})')
>>> sh
  val unit
0   5
1   6    M
2   7    B

[3 rows x 2 columns]

:

>>> unit_map = {'':1, 'M':1e6, 'B':1e9}
>>> df['float_value'] = sh.val.astype(np.float64) * sh.unit.map(unit_map)
>>> df
   id shares_float  float_value
0   1            5            5
1   2           6M      6000000
2   3           7B   7000000000

[3 rows x 3 columns]