topic Re: Delete text substring from longer text in Data Management Questions

Delete text substring from longer text

DanielBrenner — Tue, 02 Apr 2019 23:32:52 GMT

I have a field in a .dbf file with a long text string. I need to delete x amount of characters WITHIN the text string. I am also not very good with programming so I haven't looked into creating a script.

Essentially I have this:

aaa bbb ccc ddd eee fff ggg

What I need is this:

aaa bbb ccc fff ggg

I'm attaching the .dbf in MS Excel format (.xls). I have had some luck with functions in MS Excel but I can't get the consistency I need.

Re: Delete text substring from longer text

StephenM — Wed, 03 Apr 2019 19:30:36 GMT

The REPLACE function might work for this:

Excel formula: Remove text by position

Original text	Formula	New text
NAP NAP 012.039 DH 00.449 LINCOLN AVE OC 21-89	=REPLACE(A1,4,12,"")	NAP DH 00.449 LINCOLN AVE OC 21-89

Re: Delete text substring from longer text

DanielBrenner — Wed, 03 Apr 2019 20:10:39 GMT

Stephen,

Thanks for your efforts but I have been married to MS Excel for the last 4 days!!!

The main reason I was posting on the ArcGIS Forum is that I wanted to hope beyond all hope that maybe there is a VBA or Python script out there that would do what I’m trying to do in MS Excel?

Sheet 2 of Naplables0403 is closer to what I am wanting. I can do nested SUBSTITUTE functions to replace the text strings but I can’t get one set of SUBSTITUTE functions to repeat because somehow its text-string specific. i.e., DH 00.334 can only be deleted from the first text string, but even if I put an instance number of 2 (where I’m hoping the second string can be replaced), it doesn’t do that—it only replaces the text its referring to!

Perhaps a better way of putting this is how can I replace substrings of text of varying lengths with spaces between varying locations of spaces??

As in:

Aaa bbb ccc ddd eee fff

Would result in

Aaa bbb eee fff

But then

AAA BBB CCC DD EEE FF GGG

Could result in

AAA BBB CCC FF GG

Daniel T. Brenner

RESEARCH ANALYST I (GEOGRAPHIC INFORMATION SYSTEMS)

DIVISION OF RESEARCH, INNOVATION, and SYSTEM INFORMATION

Highway System Information and Performance

1120 N Street MS 38

Sacramento, CA 95814

(916) 657-4277

(510) 593-7547 cell

daniel.brenner@dot.ca.gov<mailto:daniel.brenner@dot.ca.gov>

“Consider the unconsidered.”

“Certainty is not a sure thing!”

Re: Delete text substring from longer text

StephenM — Wed, 03 Apr 2019 22:44:06 GMT

Are there a lot of different substrings you want to replace? I'm thinking maybe you could use a Python dictionary, but that would require explicit definition of substrings and their replacements. Since the substrings aren't always replaced with the same text, you'd also have to have some sort of decision-making in the code.

Re: Delete text substring from longer text

DanielBrenner — Wed, 03 Apr 2019 22:56:17 GMT

Yes there are a lot of different values I need to replace of varying lengths (see the parts of the text strings beginning with ‘DH’ or the one with ‘E DH’….)

I haven’t worked with Python Dictionaries. Could you give me an example of what you are talking about?

Re: Delete text substring from longer text

StephenM — Sat, 11 Dec 2021 15:12:17 GMT

Dictionaries are collections of key:value pairs, for example:

d = {"NAP NAP R": "NAP-029-R", "DH 00.334": ""}

Using this dictionary, d["NAP NAP R"] would give you the value "NAP-029-R", and d["00.334"] would give you an empty string. Using a dictionary you could loop through the rows and do replaces based on dictionary values.

Using the field calculator in ArcGIS Pro, for example:

Expression: mod = mod_substr(!orig!)

Code Block:

import re

def mod_substr(orig):
    d = {"NAP NAP R": "NAP-029-R",
         "DH 00.334": "",
         "NAP NAP 0": "NAP-121-0",
         "NAP YNTV 0": "NAP-121-0",
         "NAP R 006": "NAP-121-R 006"}
    tmp_str = orig

    # Replace substrings if they exist as a key in the dictionary
    for key in d.keys():
        if key in tmp_str:
            tmp_str = tmp_str.replace(key, d[key])

    # Remove duplicate spaces
    tmp_str = re.sub(' +', ' ', tmp_str)

    return tmp_str‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

May not be feasible though, since you'd have to define each substring and its replacement.

Re: Delete text substring from longer text

DanielBrenner — Thu, 04 Apr 2019 21:50:22 GMT

Stephen,

Thanks big time for this; this stuff is huge but I will do what I can to follow through on it.

But yes, your last line does get at one of the issues that Excel does much better on. That was one of (if not THE) core issues, was that what would have represented X number of characters at varying lengths and varying places in a text string. Like you’re saying, to have to put in each one of the text strings that I wanted to conver would be kind of bulky.

But at the same time some of what you’re doing is what I came up with in terms of character substrings and how to replace them—See attached!

My last questions to you are how did you learn this and where did you get the opportunity to do so? Was it on the job or in a class?

Thanks again and again,

Daniel T. Brenner

RESEARCH ANALYST I (GEOGRAPHIC INFORMATION SYSTEMS)

DIVISION OF RESEARCH, INNOVATION, and SYSTEM INFORMATION

Highway System Information and Performance

1120 N Street MS 38

Sacramento, CA 95814

(916) 657-4277

(510) 593-7547 cell

daniel.brenner@dot.ca.gov<mailto:daniel.brenner@dot.ca.gov>

“Consider the unconsidered.”

“Certainty is not a sure thing!”

Re: Delete text substring from longer text

JoshuaBixby — Sat, 11 Dec 2021 15:12:20 GMT

I don't understand what criteria/condition is driving the replacement text. For example, when

NAP NAP R 004.501 DH 00.334 VIA IMOLA AVE
‍

gets changed to

NAP-121-R 004.501 DH 00.334 VIA IMOLA AVE
‍

, " NAP " is getting changed to "-121-" .

However, when

NAP NAP 010.698 DH 00.501 OLD SONOMA RD OC 21 87
‍

gets changed to

NAP-029-010.698  OLD SONOMA RD OC 21 87
‍

, " NAP " is getting changed to "-029-".

How are you deciding when " NAP " gets changed to one value versus another?

Re: Delete text substring from longer text

DanielBrenner — Thu, 04 Apr 2019 23:31:47 GMT

Joshua,

See attached.

Its based on how the original text string describes a point location on a certain highway number network (like those little sticks along the side of the highway that have County-Route-Postmile stenciled on them) that’s in another field.

I did discuss the same matter with another respondent from a different forum and he was saying that the text strings couldn’t be created with out a logical scenario (IF location = x then label with Y) and I thought of using a drop down list of all the routes, and even joining them by another attribute, but all of that was going to be too much and given that I was going to be using the points layer’s dbf file in MS Excel I was going to be able to see the route number for the point location that needed the text string I’m trying to create and so it was just a matter of substituting when I had to.

Someone else emailed about using Data Dictionaries and I saw how they essentially did the same thing but the advantage for MS Excel is that I wouldn’t have had to include every record’s substring as part of one of them, and that the code to use was way simpler.

Hope this helps.

Daniel T. Brenner

RESEARCH ANALYST I (GEOGRAPHIC INFORMATION SYSTEMS)

DIVISION OF RESEARCH, INNOVATION, and SYSTEM INFORMATION

Highway System Information and Performance

1120 N Street MS 38

Sacramento, CA 95814

(916) 657-4277

(510) 593-7547 cell

daniel.brenner@dot.ca.gov<mailto:daniel.brenner@dot.ca.gov>

“Consider the unconsidered.”

“Certainty is not a sure thing!”

Re: Delete text substring from longer text

StephenM — Fri, 05 Apr 2019 00:02:46 GMT

Looking at that attachment, regular expressions might be useful. If you're looking to get rid of all text matching the pattern "DH ##.###", for example:

What I know of Python has been self-taught, but I did have a baseline established from studying other programming languages. I took a handful of programming classes in college, then started tinkering with Python when I was studying GIS. I've been able to continue tinkering in my job, using it to automate processes and try to solve problems.

Re: Delete text substring from longer text

DanielBrenner — Fri, 05 Apr 2019 00:09:35 GMT

Lucky you.

I’ve been married to MS Excel and watch my GIS career gently weep.

More soon.

Re: Delete text substring from longer text

JoshuaBixby — Fri, 05 Apr 2019 12:19:18 GMT

I agree with the other respondent from the different forum. The crux of your automation/scripting problem lies in:

Edited the "NAP" text string as need be to represent the correct character string (or spaces) as need be

You know what is correct "as need be," but you don't seem to be able to write out a rule set for it. If you are changing your script/formula every few rows, and not relying on a per-defined rule set of what needs to get changed to what, then you might as well just do it by hand.

Re: Delete text substring from longer text

DanielBrenner — Fri, 05 Apr 2019 19:31:19 GMT

Guys,

See attached and also refer to the other stuff.

The deal is is that NAP ‘xxx’ can a 3-4 letter character string or a space between spaces. It’s the variability of what needs to be changed to the even bigger variability in terms of the number of text strings that could go in front of the space between the 3 -4 character string or the space AND the R or L or other space before a 6 digit string representing a number.

I did come up with a partial solution, where I created a text string in MS Excel with a dash before and after the route number; this text string would replace the 2nd set of characters, but again, the variability of the size of the string is a kicker.

How does any of what you guys are suggesting deal with this?

Yes it would cover for some of what the bigger formula was getting at with the “ -121- “ part, but again, not one string fits all. I even had to edit 29 to become 029 because it wasn’t in a consistent 3 digit format like the other route.

The second string is nearly out the window in terms of variability of starting position and numbers of characters. But again, I didn’t have a problem editing the characters and the text strings as need be.

Seriously I do thank you for this.

Daniel T. Brenner

RESEARCH ANALYST I (GEOGRAPHIC INFORMATION SYSTEMS)

DIVISION OF RESEARCH, INNOVATION, and SYSTEM INFORMATION

Highway System Information and Performance

1120 N Street MS 38

Sacramento, CA 95814

(916) 657-4277

(510) 593-7547 cell

daniel.brenner@dot.ca.gov<mailto:daniel.brenner@dot.ca.gov>

“Consider the unconsidered.”

“Certainty is not a sure thing!”

Re: Delete text substring from longer text

StephenM — Sat, 11 Dec 2021 15:12:23 GMT

Maybe you could use regular expressions to replace substrings based on the character index of the first number in the ###.### sequence. Get the index of that character, then use string slices to replace all text between the third character (assuming all the rows start with "NAP") and that index.

Example in ArcGIS Pro using Python:

def replace_substr(s, rte):
    import re

    # Set pattern to replace beginning section. Optional
    # L or R and a space, then 3 numbers, a period, three
    # numbers, a space, 1 or 2 letters, and a space.
    ptn = re.compile("([LR] )?[0-9]{3}.[0-9]{3} [A-Z]{1,2} ")
    
    # Match the pattern in the string
    match = re.finditer(ptn, s)
    
    # Get the first match
    m = next(match)
    
    # Get the index where the match started
    idx = m.start()
    
    # Create new string by concatenating string slices and route
    new_str = s[:3] + rte + s[idx:]
    
    # Set pattern to replace the 'DH ##.###' section
    ptn = re.compile("DH [0-9]{2}.[0-9]{3}")
    
    # Replace the 'DH ##.###' section with nothing
    new_str = re.sub(" +", " ", re.sub(ptn, "", new_str))
    
    return new_str‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Delete text substring from longer text

DanielBrenner — Mon, 08 Apr 2019 21:26:33 GMT

I wouldn’t say that solves the issue because it feels like too much and I haven’t tried it out either; although I have gotten a few things to work, just had to do some editing in Excel to do so.