Pandas dataframe: Extracting float values from string in a column

Question

I'm trying to extract a floating value from a string for a particular column.

Original Output

DATE        strCondition
4/3/2018    2.9
4/3/2018    3.1, text
4/3/2018    2.6 text
4/3/2018    text, 2.7

and other variations. I've also tried regex but my knowledge here is limited, I've come up with:

clean = df['strCondition'].str.contains('\d+km')
df['strCondition'] = df['strCondition'].str.extract('(\d+)', expand = False).astype(float)

where the output ends up looking like this where it displays the main integer shown...

DATE        strCondition
4/3/2018    2.0
4/3/2018    3.0
4/3/2018    2.0
4/3/2018    2.0

My desired output would be along the lines of:

DATE        strCondition
4/3/2018    2.9
4/3/2018    3.1
4/3/2018    2.6
4/3/2018    2.7

I appreciate your time and inputs!

EDIT: I forgot to mention that in my original dataframe there are strCondition entries similar to

2.9(1.0) #where I would like both numbers to get returned
11/11/2018 #where this date as a string object can be discarded

Sorry for the inconvenience!

Try df['float'] = df['strCondition'].str.findall(r'\d+(?:\.\d+)?').apply(', '.join) — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Nov 11, 2019 at 21:36
To avoid matching digits in dates you may use df['float'] = df['strCondition'].str.findall(r'\b(?<!\d/)\d+(?:\.\d+)?\b(?!/\d)').apply(', '.join). Does it work like expected now? — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Dec 6, 2019 at 11:47

Scott Boston · Accepted Answer · 2019-11-11 19:39:30Z

10

Try:

df['float'] = df['strCondition'].str.extract(r'(\d+.\d+)').astype('float')

Output:

       DATE strCondition  float
0  4/3/2018          2.9    2.9
1  4/3/2018    3.1, text    3.1
2  4/3/2018     2.6 text    2.6
3  4/3/2018    text, 2.7    2.7

answered Nov 11, 2019 at 19:39

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

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

2 Comments

Scott Boston Over a year ago

So, you want the first float and not the ones inside parenthesis?

JQJQ Over a year ago

Ideally it would be cool to have it spit out both float values, even the one inside parenthesis. If we can only return only the float outside of the parenthesis that is a good solution as well.

user12097764 · Accepted Answer · 2019-11-11 19:50:41Z

0

A simple replace would be

Find (?m)^([\d/]+[ \t]+).*?(\d+\.\d+).*

Replace \1\2

https://regex101.com/r/pVC4jc/1

answered Nov 11, 2019 at 19:50

user12097764

2 Comments

JQJQ Over a year ago

That does work for some of the strings, but what about the dates as a string in my original data?

user12097764 Over a year ago

@JQJQ - when a determination is made on the complete sample text expected to match and what it will look like after substituted, then a complete solution can be made.

Matthias Luh · Accepted Answer · 2023-11-03 19:40:04Z

0

In case you also want to convert negative numbers with a minus sign at the beginning, this should do it:

df['strCondition'] = df['strCondition'].str.extract('(-{0,1}\d+\.\d+)', expand=False).astype(float)

answered Nov 3, 2023 at 19:40

Matthias Luh

6531 gold badge9 silver badges14 bronze badges

Collectives™ on Stack Overflow

Pandas dataframe: Extracting float values from string in a column

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related