topic Re: Joining attribute table to excel sheet in Python Questions

Joining attribute table to excel sheet

KelvinMwakomo — Thu, 10 Mar 2016 09:39:00 GMT

Hello guys,

I want to add a column in my dbase file which will have geometry data (Area) by joining the dbase file with attribute table.

I have tried to use "FARMER_UNI" from my dbase file to join with "FARMER_UNI" from my attribute table, but the result were not what i wanted.below is an attribute table,

And here is the results i get,

So what i want is the "name" field to be the same as "GPS_Coordi" field, but i cant use those field to do the joining without doing some changes because there is some spelling error such as removing the middle values(12345/6578/ST/1), adding "/" after ST, changing the lower case to upper case(st to ST), and removing spaces between values.

With thanks!

Kelvin.

Re: Joining attribute table to excel sheet

RebeccaStrauch__GISP — Thu, 10 Mar 2016 10:44:20 GMT

So what i want is the "name" field to be the same as "GPS_Coordi" field, but i cant use those field to do the joining without doing some changes because there is some spelling error such as removing the middle values(12345/6578/ST/1), adding "/" after ST, changing the lower case to upper case(st to ST), and removing spaces between values.

I think you have answered you own question with

"but i cant use those field to do the joining without doing some changes because there is some spelling error such as removing the middle"

Since those are basically the common unique field, but with slightly different formatting, I would suggest making a new field in each file and formatting or calculating the code to make those equivalent.

Since the "name" field does not always contain a valid GPS coordinate it would be hard to do a spatial join unless you have propery xy coordinate in field that isn't in your graphic, in which case you create an event theme etc. But if you dont, the unique field creation may be your only option.

there are others on this forum that are faster at providing string formatting code than me....i'll leave it to them to provide suggestions.

Re: Joining attribute table to excel sheet

KelvinMwakomo — Thu, 10 Mar 2016 10:56:03 GMT

Thanks Rebecca,

I am also looking for a field calculator expression which can help me to rectify those error, because i have a lot of data if i edit manually it can take days to complete.

Re: Joining attribute table to excel sheet

DanPatterson_Retired — Thu, 10 Mar 2016 11:26:09 GMT

you will have to add a new field

Python parser

new field: FarmerTxt make it text with an appropriate width

then in the new field do

str(!UniqueNo*!) or whatever the field is

So basically you are converting the unique numbers into text

Re: Joining attribute table to excel sheet

KelvinMwakomo — Thu, 10 Mar 2016 12:26:20 GMT

Thanks Dan,

But what i need now is to create a field both in a Table and dbase file which can have the values of "name" field,without the middle number eg.(181073/ST/1) instead of (181073/23792/ST/1), Also for the cases like 181073/23792/st1,181073/23792/st 1,181073/23792/ST/01 to become 181073/ST/1.

Kelvin.

Re: Joining attribute table to excel sheet

DanPatterson_Retired — Thu, 10 Mar 2016 13:24:22 GMT

So if this is a different question you should mark the answer that provide a solution correct.. then start a new question, otherwise the thread will become a long thread of needs that bear no relationship to the initial thread title.

Re: Joining attribute table to excel sheet

WesMiller — Thu, 10 Mar 2016 13:59:51 GMT

Is that all you have to worry about(because I'm guessing there's more)

Open your python interpretor and play with your various values till you start getting the desired result

>>> a = '181073/23792/ST/1'

>>> b = a.split('/')

>>> b[1]

'23792'

Below is a list of your examples and they all produce the same result.

a = ['181073/23792/ST/1',"181073/23792/st1","181073/23792/st","1,181073/23792/ST/01"]

#I'm walking through the list using the for statement.

#In field calculator you will not need to use for, you'll need each = str(!yourfield!)

for each in a:

#The if statement is testing to see if there is a comma in your each variable

if ',' in each:

#So if there is a comma we will split the each variable into pieces.

#The number of pieces are based on the number of commas

each = each.split(',')[-1]

#Now we'll split either what's left from the if statement or the original each value

each = each.split('/')[0]

#Now we'll add the proper ending back

each = each + '/ST/1'

#Finally we will print the value so we can see it.

#In the field calculator you'll use the return statement

#return each

print each