topic Re: How do I calculate a field using data from Excel in ArcGIS Spatial Analyst Questions

How do I calculate a field using data from Excel

jamesluscher — Fri, 14 Feb 2020 21:24:34 GMT

Ran Ordinary Least Squares and the output table is in Excel. I am using the data from the 5 variables to predict the value in another data set with the same variables. I was hoping to have a calculated field where I could multiply the coefficient times the whole column for all 5 variables. Coefficient 1 times Variable 1, Coefficient 2 times variable 2, etc. Does anyone have any experience doing this?

Re: How do I calculate a field using data from Excel

DavidPike — Fri, 14 Feb 2020 21:51:53 GMT

You could add the xls as a gdb table then use search and update cursors to do the calculations.

Re: How do I calculate a field using data from Excel

jamesluscher — Mon, 17 Feb 2020 18:35:02 GMT

Thank you. I am not well steeped in Python.

Re: How do I calculate a field using data from Excel

DavidPike — Mon, 17 Feb 2020 18:44:36 GMT

If you send some pictures of the tables and a comprehensive explanation of what you want to achieve, I can write it out.

Re: How do I calculate a field using data from Excel

jamesluscher — Mon, 17 Feb 2020 19:28:37 GMT

I am trying to calculate the tracts.Value field by using the below equation. I can do it once but, as soon as I run a different regression, I have to change all of the coefficients for each Variable. Furthermore, I have 20+ variables to use and, therefore, the number of variables can fluctuate.

Re: How do I calculate a field using data from Excel

DavidPike — Mon, 17 Feb 2020 19:58:36 GMT

I'm sorry it's easily codeable but I still don't get the whole picture.

Re: How do I calculate a field using data from Excel

jamesluscher — Mon, 17 Feb 2020 20:15:50 GMT

When I run the Ordinary Least Squares tool the result is a regression that is reported in OLS_Coefficients. The regression is based on home sales with a known sales price. I am trying to take the Coefficient of each variable and apply it to homes that we don't have sales data on. We have the same Variable but just don't know the price.

Re: How do I calculate a field using data from Excel

DavidPike — Mon, 17 Feb 2020 20:29:58 GMT

So theres an OLS table for each row in the table in the second image?

Re: How do I calculate a field using data from Excel

jamesluscher — Mon, 17 Feb 2020 22:45:16 GMT

There is one OLS table for the whole "tracts" table. It is a one-to-many relationship.

Re: How do I calculate a field using data from Excel

DavidPike — Mon, 17 Feb 2020 22:53:06 GMT

Are you saying you have multiple tables such as tracts and an ols table for each of them?

Re: How do I calculate a field using data from Excel

jamesluscher — Mon, 17 Feb 2020 23:10:46 GMT

There is one tracts table and one OLS table. there are 10,000 rows in the tracts table.

Re: How do I calculate a field using data from Excel

DavidPike — Mon, 17 Feb 2020 23:26:33 GMT

Then I just dont get why the field calculation isnt appropriate?