topic Re: Select records by number of spaces in field in ArcGIS Pro Questions

Select records by number of spaces in field

Ona_Tim — Fri, 12 May 2023 16:11:19 GMT

I have a graves hosted feature layer that has Ownerfullname, Firstname, lastname, OccupantLabel, OccupantFirstName, OccupantLastName. Somewhere down the line some of the Ownerfirstname was placed in the OccupantFirstName which is different from the OccupantLabel. I cannot use a simple strip from the end of the string due to multiple last names and suffixes. I am looking for a way to select records from the OccupantLabel field by the number of spaces and haven't found a way. The field has been trimmed to remove any leading or ending spaces.

I have tried the following ways in the select by records window just to find 1 space:

OccupantLabel.Count(" ") = 1

OccupantLabel.Count(' ') = 1

(LEN(column1 + ';')-LEN(REPLACE(column1,' ','')) = 1)

OccupantLabel like '%" "%'

Any help would be apprciated.

Thank you.

Re: Select records by number of spaces in field

bbaker_tngeo — Fri, 12 May 2023 16:33:43 GMT

If I understand correctly, you're trying to remove 2 spaces from records, such that "BOB SMITH" (2 spaces between BOB and SMITH) would be "BOB SMITH". Is that correct?

If so, I have used Find and Replace within the attribute table in ArcGIS Pro to Find: <space bar x2> and Replace: <space bar x1>. No brackets or quotes are needed. You can test is first by just finding a record and verifying it's what you're looking for.

If you're wanting to find where a first name has been , could you construct a variable by concatenating the occupant Firstname + " " + lastname and then checking that against the OccupantLabel to see which records don't match?

Re: Select records by number of spaces in field

Ona_Tim — Fri, 12 May 2023 16:52:38 GMT

Nope.

I have the following example

OccupantLablel OccupantFirstName(Exisiting) OccupantFirstName(What I need)

John Smith Alice John

John J Smith John John J

John "Joe" Van Smith Jr Joe John "Joe"

I am try to select records so I only select John Smith (one space) so I can strip the correct first name or select by 2 spaces in the case of John J Smith and so on.

Re: Select records by number of spaces in field

DavidPike — Fri, 12 May 2023 18:29:03 GMT

Calculate Field, Python.

splitString = yourString.split() oneSpace = splitString[0] + " " + splitString[1]

note - edited original post to use .split() default whitespace method which would be more robust than .split(" ") which would include >=2 consecutive whitespaces.

Re: Select records by number of spaces in field

Ona_Tim — Fri, 12 May 2023 17:33:34 GMT

That would work for John J Smith but not for John Smith.

As John Smith would need to be

splitstring = yourstring.split(" ")

onespace = splitstring[0]

That is why I need to select the records base on the number of spaces before Calculating them.

Re: Select records by number of spaces in field

KenBuja — Fri, 12 May 2023 18:10:22 GMT

If you have OccupantLastName field, subtract that from the OccupantLabel to get OccupantFirstName. Assuming that for 'John "Joe" Van Smith Jr', the OccupantLastName is 'Van Smith Jr'....

Re: Select records by number of spaces in field

DavidPike — Fri, 12 May 2023 18:20:41 GMT

splitString would be a list which looks like this

['John', 'Smith']

splitString[0] would only return the first item 'John'. The code should work.

Re: Select records by number of spaces in field

RhettZufelt — Mon, 15 May 2023 15:28:08 GMT

With leading/trailing spaces already stripped:

>>> s = 'John Smith' >>> splitString = s.split(" ") >>> splitString ['John', 'Smith'] >>> len(splitString) 2 >>> >>> >>> s = 'John J Smith' >>> splitString = s.split(" ") >>> splitString ['John', 'J', 'Smith'] >>> len(splitString) 3 >>>

You can also iterate through the split string and count the number of 'spaces'. this would be more robust if there could be more than one space in between owner names:

numSpaces = 0 for splstr in splitString: if splstr == '' or splstr is None: numSpaces +=1 print(numSpaces)

You can split the string by " " (space) and then see the length of the resultant string. Won't really help if there are multiple spaces in a row, but the len() will let you know if there is more than two components to the name.

Not sure if it works for you, but at least a coule ideas of "counting" spaces in the strings.

Re: Select records by number of spaces in field

Ona_Tim — Mon, 15 May 2023 16:40:18 GMT

Thank you Ken! That was it. I used OccupantFirstname = !OccupantLabel!.rstrip(!OccupantLastName!)