Separate column data based on comma characters using pandas regex

user_10829044 0
Viewed 295

I have a dataframe like as shown below

df = pd.DataFrame({'val': ['V583 ,ATTENTION, PRIMARY','Y9207,INDOOR LIVING, sEcondary',' z526, liver,primary ','12345678, test, secondary',',project,']})

I would like to split/segregate column val data based on comma character.

For instance, All characters before 1st comma should go to first column Similarly, All characters after 1st comma and before 2nd comma should go to second column likewise, All characters after 2nd comma and before 3rd comma should go to third column

I tried the below

df['val'].astype(str).str.extract(r'\s*([a-zA-Z0-9\s]*)',expand=True)

I expect my output to be like as shown below

enter image description here

pandas series dataframe python regex
4 Answers

With your shown samples, please try following. Using extract function of Pandas here. Simple explanation would be: mentioning regex in extract to create 3 new columns in DataFrame. Its basically creating 3 capturing groups for each new fields as per shown samples.

df[["first", "second", "third"]] =  df['val'].str.extract(r'^([^,]*),([^,]*),(.*)$',expand=True)

Here is online demo of above regex

df's output will be as follows:

                              val     first         second       third
0        V583 ,ATTENTION, PRIMARY     V583       ATTENTION     PRIMARY
1  Y9207,INDOOR LIVING, sEcondary     Y9207  INDOOR LIVING   sEcondary
2            z526, liver,primary       z526          liver    primary 
3       12345678, test, secondary  12345678           test   secondary
4                       ,project,                  project
user_58665800

Series.str.extractall

We can extract all the occurrences of capturing group specified as regex pattern then unstack to reshape.

df['val'].str.extractall(r'([^,]+)(?:\s*,\s*|$)')[0].unstack()

match         0              1          2
0         V583       ATTENTION    PRIMARY
1         Y9207  INDOOR LIVING  sEcondary
2          z526          liver   primary 
3      12345678           test  secondary
4       project            NaN        NaN

Regex details:

  • ([^,]+) : First capturing group
    • [^,]+ : Matches any character not present in the list [,] one or more times.
  • (?:\s*,\s*|$) : Non capturing group
    • \s*,\s* : First alternative
      • \s* : Matches any white space character zero or more times
      • , : Matches the character comma literally
      • \s* : Matches any white space character zero or more times
    • $ : Second alternative asserts position at the end of line

PS: This method can also work if you have any number of comma delimited strings.

user_128331660

You could use str.extract here as follows:

df["first"] = df["val"].str.extract(r'^\s*(.*?)\s*,')
df["second"] = df["val"].str.extract(r',\s*(.*?)\s*,')
df["third"] = df["val"].str.extract(r',\s*([^,]*)$')
user_18632290

Use str.split(expand=True)

  df.join(df['val'].str.split(',', expand=True).rename(columns={0:'first',1:'second',2:'third'}))

                              val     first         second       third
0        V583 ,ATTENTION, PRIMARY     V583       ATTENTION     PRIMARY
1  Y9207,INDOOR LIVING, sEcondary     Y9207  INDOOR LIVING   sEcondary
2            z526, liver,primary       z526          liver    primary 
3       12345678, test, secondary  12345678           test   secondary
4                       ,project,                  project
user_89869750
Related