Splitting string column with uppercase and lowercase string into two separate columns Pyspark/Python/Sql?

user_11808907 0
Viewed 318

I have below Data in column Clicks:

    MEM-BEN-BTN-CLK-entertainment-audible
    MEM-BEN-LOC-MODAL-LOCATION-INPUT-Birmingham, AL, USA
    MEM-BEN-BTN-CLK-entertainment-games
    MEM-BEN-BTN-CLK-healthandwellness-love-and-meaning-after-50
    MEM-BEN-BTN-LRN-learn-more-aarp-travel-center-powered-by-expedia-10083
    MEM-BEN-BTN-LRN-learn-more-embassy-suites-by-hilton-1019

I want to split the column Click into two columns Click_Upper and Click_lower

Click_Upper (holds all uppercase characters)

    MEM-BEN-BTN-CLK
    MEM-BEN-LOC-MODAL-LOCATION-INPUT
    MEM-BEN-BTN-CLK
    MEM-BEN-BTN-CLK
    MEM-BEN-BTN-LRN
    MEM-BEN-BTN-LRN

Click_lower (holds all lowercase characters)

    entertainment-audible
    Birmingham, AL, USA
    entertainment-games
    healthandwellness-love-and-meaning-after-50
    learn-more-aarp-travel-center-powered-by-expedia-10083
    learn-more-embassy-suites-by-hilton-1019

I was trying to use split() function, but there are multiple delimiters (-) and the strings are of varying length so the code is not working for me. Also tried re but its breaking the string.

I would appreciate any guidance or help I could get on this.

pyspark python
2 Answers

I used a regex statement to split the strings. You can use the re.group(x) method to access the two groups. Here is some more information: https://docs.python.org/3/library/re.html

import re

strings = ["MEM-BEN-BTN-CLK-entertainment-audible",
    "MEM-BEN-LOC-MODAL-LOCATION-INPUT-Birmingham, AL, USA",
    "MEM-BEN-BTN-CLK-entertainment-games",
    "MEM-BEN-BTN-CLK-healthandwellness-love-and-meaning-after-50",
    "MEM-BEN-BTN-LRN-learn-more-aarp-travel-center-powered-by-expedia-10083",
    "MEM-BEN-BTN-LRN-learn-more-embassy-suites-by-hilton-1019"]

regex = "(?P<Click_Upper>[A-Z\-]+)-(?P<Click_Lower>.*)"

for string in strings:
    print(re.match(regex,string).groups())

Here is the output:

('MEM-BEN-BTN-CLK', 'entertainment-audible')
('MEM-BEN-LOC-MODAL-LOCATION-INPUT', 'Birmingham, AL, USA')
('MEM-BEN-BTN-CLK', 'entertainment-games')
('MEM-BEN-BTN-CLK', 'healthandwellness-love-and-meaning-after-50')
('MEM-BEN-BTN-LRN', 'learn-more-aarp-travel-center-powered-by-expedia-10083')
('MEM-BEN-BTN-LRN', 'learn-more-embassy-suites-by-hilton-1019')
user_164480980

Lets split the text with -followed by lower case or -followed with string Startingwithcaps but followed with lowercase letters.

After split, we can slice first element in list, that will give us the upper

Once we have upper, remove the upper from whole text to remain with lower.

Code below and enjoy coding

Data

data=[
  (1,"MEM-BEN-BTN-CLK-healthandwellness-love-and-meaning-after-50"),
  (2,"MEM-BEN-LOC-MODAL-LOCATION-INPUT-Birmingham, AL, USA")
  ]
df=spark.createDataFrame(data, ['id','text'])
df.show(truncate=False)

Code

df.withColumn('upper', F.split('text','\\-(?=[a-z]+)|(\\-[A-Z][a-z]+)')[0]).withColumn("lower",expr("regexp_replace(text,upper,'')")).show(truncate=False)
user_89869750
Related