Remove text based on string in another column

Support and Helpdesk Data cleaning and transformations
1

Hello!

I am not sure if there is a way to do this, but thought I would ask.
tl;dr is there a way to use the values of another column as the string for a function (like chomp)?

Background: I am trying to create structured data out of a list of materials in an archival collection. Right now I have rows that say things like "09/08/1995 Letter from Wong Foo to Julie Newmar, with thanks for everything" or "Feb 9, 1942 Letter from Screwtape to Wormwood regarding questions of theology, and dinner plans for the weekend."

I'm trying to separate these into date, title, and content. Because the formatting isn't consistent, I can't just slice on commas. I've been using filter on the action words (with thanks, regarding) to split out the information after the action word into an 'About' column.Ex Regarding" + cells.Title.value.split("regarding")[1]

The question is whether I could use the strings in the 'About' column to strip out the 'about' information from the titles? Something like value.chomp(cells.About.value) (which does nothing)

2

I guess this does "nothing" because you replaced the original lower case action word to title case.

So "regarding questions of theology, and dinner plans for the weekend." would become "Regarding questions of theology, and dinner plans for the weekend." in the About column.

You could add a little more GREL to your chomp expression to make the action word lower case again:

value.chomp(
    with(
        cells.About.value,
        v,
        v[0].toLowercase()+v.substring(1, v.length())
    )
).trim()

But depending on how much more transformations you already did on your About column this probably will not solve all the problems.

1 Like
3

Thank you for pointing out why it wasn't working! I often forget about case sensitivity.

When I try the expression you suggest, I get the error "toLowercase expects a string". However, I'm going to take what you've suggested and play with it to get to a solution.

Thank you!

4

This may be overly clunky, but it works:

value.chomp(
with(cells.About.value, v, v.split(" ")[0].toLowercase()) 
 + " " + 
 cells.About.value.split(" ").slice(1).join(" ")
).trim()
5

So what the first solution did was taking the first character of the sentence, make it lowercase and then join it with the rest of the sentence.

Not sure why in your case making a character lower case did not work.

The second solution is splitting the "sentence" into words, makes the first word lowercase and then joins it with the rest of the sentence back together.

Here is a cleaned version of the second version, that assigns the word array to a variable named "words" to make the code a bit more readable.

value.chomp(
    with(
        cells.About.value.split(" "),
        words,
        words[0].toLowercase() + " " + words.slice(1).join(" ")
    )
).trim()