How can I solve this in a more pythonic way with pandas?

user_8011460 0
Viewed 46

I have the following column:

Strings
(costofitem:item0:99.93)
(costofitem:item1:2.64)
(costofitem:item2:6.43)
(costofitem:item3:9.34)
(costofitem:item4:12.63)
(costofitem:item5:13.63)
(costofitem:item6:14.63)
(number:3.3:sometext1)
(number:5.3:sometext2)
(number:5.3:sometext3)
(number:5.5:sometext3)

I want to achieve:

name number 
item0 99.93
item1 2.64 
item2 6.43
item3 9.34
item4 12.63 
item5 13.63 
item6 14.63 
number 3.3
number 5.3
number 5.3
number 5.5

I tried a lot with splitting but it got really messy really quick. df[['name', 'number']] = df['Strings'].str.split(':', expand=True) And split again, again ... drop things

Whats the most pythonic way to get these values in between or next to each other?

pandas python
1 Answers

You can use a regex with str.extract:

df[['name', 'number']] = df['Strings'].str.extract(r'([^\(:]+):(\d+\.\d+)')

output:

                     Strings    name number
0   (costofitem:item0:99.93)   item0  99.93
1    (costofitem:item1:2.64)   item1   2.64
2    (costofitem:item2:6.43)   item2   6.43
3    (costofitem:item3:9.34)   item3   9.34
4   (costofitem:item4:12.63)   item4  12.63
5   (costofitem:item5:13.63)   item5  13.63
6   (costofitem:item6:14.63)   item6  14.63
7     (number:3.3:sometext1)  number    3.3
8     (number:5.3:sometext2)  number    5.3
9     (number:5.3:sometext3)  number    5.3
10    (number:5.5:sometext3)  number    5.5

Regex:

([^\(:]+)   # capture anything but ( or :
:           # match :
(\d+\.\d+)  # capture a float (e.g. 1.23)
user_163434640
Related