Pandas Dataframe: Split mixed float-string column into separate float and string columns

Question

I have a Pandas dataframe column named 'VALUE' which has string data like this: '-1.459NS' I want to create 2 new columns -> 'VALUE' must have a float -1.459 and UNIT must have a string 'NS'

Is there a Regex and/or Non Regex way of doing this? What is the fastest way of doing this? I have maybe a million + lines over which I want to do this.

>>> d = {'VALUE': ['-1.234NS','0.22MH']}
>>> df=pd.DataFrame(data=d)
>>> df
      VALUE
0  -1.234NS
1    0.22MH

I want:

    VALUE    UNIT
0  -1.234    NS
1    0.22    MH

Where VALUE is float and UNIT is string

Are units always 2-characters long?

rafaelc
– rafaelc

2018-08-27 22:11:20 +00:00
Commented Aug 27, 2018 at 22:11 — rafaelc
– rafaelc, Commented Aug 27, 2018 at 22:11
The units can be any number of characters

ZenBear
– ZenBear

2018-08-27 23:14:34 +00:00
Commented Aug 27, 2018 at 23:14 — ZenBear
– ZenBear, Commented Aug 27, 2018 at 23:14

BallpointBen · Accepted Answer · 2018-08-28 13:30:40Z

6

df.column.str.extract will produce a dataframe with one column per matched group in the regex, indexed by int position. Then you can use rename to rename the columns.

>>> df.VALUE.str.extract(r'([-]?[\d.]*)([\w\D]*)').rename(columns={0:'VALUE', 1:'UNIT'})

    VALUE UNIT
0  -1.234   NS
1    0.22   MH

edited Aug 28, 2018 at 13:30

answered Aug 27, 2018 at 22:06

BallpointBen

15.6k2 gold badges46 silver badges81 bronze badges

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

1 Comment

SpghttCd Over a year ago

My approach would have been df.VALUE.str.extract('([+-.0-9]+)(.*)'), then plus sign is allowed and UNIT could have numbers, too - however, I'm not a regex expert, but at least for the sample above it would also work.

Joseph Seung Jae Dollar · Accepted Answer · 2018-08-27 22:21:01Z

0

Here is another non-regex way to solve this if the following units aren't fixed lengths. This will split the string into float numbers and string type units.

s = '-1.234NS'
a,b = map(str, s.split("."))
a = a + '.'
unit = ''
for num in b:
    try:
        float(num)
        a += num
    except ValueError:
        unit += num
fl = float(a)

answered Aug 27, 2018 at 22:21

Joseph Seung Jae Dollar

1,0864 gold badges14 silver badges29 bronze badges

Collectives™ on Stack Overflow

Pandas Dataframe: Split mixed float-string column into separate float and string columns

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related