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.
Solved! Go to Solution.
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'....
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?
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.
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.
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.
splitString would be a list which looks like this
['John', 'Smith']
splitString[0] would only return the first item 'John'. The code should work.
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'....
Thank you Ken! That was it. I used OccupantFirstname = !OccupantLabel!.rstrip(!OccupantLastName!)
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.
R_