Converting string of numbers and letters to int/float in pandas dataframe

Question

I feel like there has to be a quick solution to my problem, I hacked out a poorly implemented solution using multiple list comprehensions which is not ideal whatsoever. Maybe someone could help out here.

I have a set of values which are strings (e.g. 3.2B, 1.5M, 1.1T) where naturally the last character denotes million, billion, trillion. Within the set there are also NaN/'none' values which should remain untouched. I wish to convert these to floats or ints, so in the given example (3200000000, 1500000, 1100000000000)

TIA

what have you tried so far? can we see an example data set? This question is really poor and does not supply much info to help you... — oz123
– oz123, Commented Jan 8, 2013 at 15:52

Andy Hayden · Accepted Answer · 2013-07-08 23:15:49Z

6

You could create a function: and applymap it to every entry in the dataframe:

powers = {'B': 10 ** 9, 'M': 10 ** 6, 'T': 10 ** 12}
# add some more to powers as necessary

def f(s):
    try:
        power = s[-1]
        return int(s[:-1]) * powers[power]
    except TypeError:
        return s

df.applymap(f)

edited Jul 8, 2013 at 23:15

answered Jan 8, 2013 at 15:55

Andy Hayden

378k110 gold badges640 silver badges546 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zelazny7 Over a year ago

Nice solution! I think the only adjustment might be to change return int() to return float().

piRSquared · Accepted Answer · 2017-10-05 02:04:19Z

3

Setup
Borrowing @MaxU's pd.DataFrame

df = pd.DataFrame({'col': ['123.456', '78M', '0.5B']})

Solution
Replace strings with scientific notation then use astype(float)

d = dict(M='E6', B='E9', T='E12')

df.replace(d, regex=True).astype(float)

            col
0  1.234560e+02
1  7.800000e+07
2  5.000000e+08

edited Oct 5, 2017 at 2:04

answered Oct 4, 2017 at 22:37

piRSquared

296k68 gold badges509 silver badges654 bronze badges

1 Comment

MaxU - stand with Ukraine Over a year ago

This is really smart !

MaxU - stand with Ukraine · Accepted Answer · 2017-10-04 22:25:58Z

2

Demo:

In [58]: df
Out[58]:
       col
0  123.456
1      78M
2     0.5B

In [59]: d = {'B': 10**9, 'M': 10**6}

In [60]: df['new'] = \
    ...: df['col'].str.extract(r'(?P<val>[\d.]+)\s*?(?P<mult>\D*)', expand=True) \
    ...:   .replace('','1') \
    ...:   .replace(d, regex=True) \
    ...:   .astype(float) \
    ...:   .eval('val * mult')
    ...:

In [61]: df
Out[61]:
       col           new
0  123.456  1.234560e+02
1      78M  7.800000e+07
2     0.5B  5.000000e+08

answered Oct 4, 2017 at 22:25

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

2 Comments

cs95 Over a year ago

Wow, what a coincidence. I just happened to see this page when you posted!

MaxU - stand with Ukraine Over a year ago

@cᴏʟᴅsᴘᴇᴇᴅ, yeah, i didn't expect it to be upvoted a few seconds after posting in such an old question :-)

Collectives™ on Stack Overflow

Converting string of numbers and letters to int/float in pandas dataframe

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related