Extract specific part in a column using regex in pandas

Question

Hello I have a dataframe such as

COL1 
scaffold_6202_0_5660-8393_+__Apis_cerana
scaffold_27087_2-HSPs_+__Canis_lupus
LBMM01007576.1_2-HSPs_-__Lasius_niger
NW_019416736.1_1_2-HSPs_-__Cattus_felis
KQ415617.1_114142-115354_+__SPO_E
UXGB01011990.1_1481-2897_-__Apis_mellifera
CM010866.1_742312-745306_-__Cuniculus_griseus
scaffold_10628_4264-5914_-__Rattus_rattus 
IDBA_scaffold30_1_30-466_+__SP_A
IDBA_scaffold43_30-466_+__SP_B

and I would like to use a regex expression in order to extract only the part between :

[part to extract]_Number-HSPs_* or if there is not the HSPs pattern extract [part to extract]_Number*-Number_*

and save it into a COL2 Here I should get :

COL1                                          COL2
scaffold_6202_0_5660-8393_+__Apis_cerana      scaffold_6202_0
scaffold_27087_2-HSPs_+__Canis_lupus          scaffold_27087
LBMM01007576.1_2-HSPs_-__Lasius_niger         LBMM01007576.1
NW_019416736.1_1_2-HSPs_-__Cattus_felis       NW_019416736.1_1
KQ415617.1_114142-115354_+__SPO_E             KQ415617.1
UXGB01011990.1_1481-2897_-__Apis_mellifera    UXGB01011990.1
CM010866.1_742312-745306_-__Cuniculus_griseus CM010866.1
scaffold_10628_4264-5914_-__Rattus_rattus     scaffold_10628
IDBA_scaffold30_1_30-466_+__SP_A              IDBA_scaffold30_1
IDBA_scaffold43_30-466_+__SP_B                IDBA_scaffold43

So far I succeded to use

import re 

df['COL2'] = re.sub(r"_[^0-9]*-Number_", "", df['COL1'])

The fourth bird · Accepted Answer · 2021-01-19 07:35:38Z

3

For the example data, you might also match either word characters and dots until the last underscore it can match, as a word character also matches an underscore.

^([\w.]+)_

Regex demo

df['COL2'] = df["COL1"].str.extract(r'^([\w.]+)_')

edited Jan 19, 2021 at 7:35

answered Jan 19, 2021 at 7:31

The fourth bird

165k16 gold badges61 silver badges75 bronze badges

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

1 Comment

JvdV Over a year ago

Very clever, ++

Tim Biegeleisen · Accepted Answer · 2021-01-19 07:12:00Z

1

Using str.extract:

df["COL2"] = df["COL2"].str.extract('(^.*?(?=_[^_-]+-\w+))')

Demo

edited Jan 19, 2021 at 7:12

answered Jan 19, 2021 at 7:08

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

4 Comments

Grendel Over a year ago

I get the error message : ValueError: pattern contains no capture groups

Tim Biegeleisen Over a year ago

The answer should be working, check the demo

Tim Biegeleisen Over a year ago

@Grendel Sorry, I left out the mandatory capture group. Please try it again.

Grendel Over a year ago

It works now thank you for your time and help ! :-)

Collectives™ on Stack Overflow

Extract specific part in a column using regex in pandas

2 Answers 2

1 Comment

Demo

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related