topic Re: Creating a table from values and export it in ModelBuilder Questions

Creating a table from values and export it

TimHaas — Wed, 31 Mar 2021 09:52:13 GMT

Hi everyone,

I´m trying to apply the Spatial Autocorrelation (Global Moran´s I) Tool for a big dataset. It contains values on a county level of several days. I want to generate an output file (e.g., excel/csv), that holds the scores produced by the Spatial Autocorrelation Tool for each individual day.

So far, I´ve managed to build an iteration loop for my fields, that are used as the input to the Spatial Autocorrelation Tool. When running my built model, it will output the Report files for all my cases.

My question is:

How can I combine the output (ZScore, Index, PValue) into one table file? I want the values from my iteration to be the column name and the values from the Spatial Autocorrelation Tool to be the data in the table.

Thanks and best regards,

Tim

Re: Creating a table from values and export it

curtvprice — Tue, 20 Apr 2021 14:28:40 GMT

This can be done by creating a table, adding fields, and adding data using a cursor. The easiest way is to do this in Python using the Calculate Value tool.

However, if you are in a hurry, you can use the Calculate Value tool to just spit out the values in to the messages stream, use the expression (the things in % are all model variables, %n% is a built in one that counts loops though you could use any model variable to identify results):

"Result {}: {} {} {}".format(%n%, %zscore%, %index%, %pvalue%)

and then copy and paste the messages stream into a text editor and clean it up to make a csv to load into excel. Sometimes brute force is easier than writing a bunch of code.

UPDATE: I fixed up the expression above and added another variable to identify the results for each loop.

Re: Creating a table from values and export it

TimHaas — Mon, 19 Apr 2021 09:05:09 GMT

Thank you for the answer. I´ve tried out both solutions.

By creating a table, did you mean to create an excel file, give it the desired column names and then implement it into the ModelBuilder tool? At his point I was not able to connect it to the values created, as I barely have any knowledge on python.

So I approached the brute force way and it worked. From there I´ll try to clean the text up and extract my wanted information. Thank you again!

One short follow up question: Is there a way to re-open the message stream once it has been colosed?

A note for everyone with the same problem, wanting to try out curtvprice ´s solution: make sure to add the extra % after pvalue in the expression.

Re: Creating a table from values and export it

curtvprice — Mon, 19 Apr 2021 13:17:19 GMT

No, the first solution was purely python based, where you write a python function with the calculate value tool to write the values from the tool output (I believe they are exposed as output parameters) using an arcpy cursor - pretty involved if you haven't used much Python yet, but not too difficult.
If you run the tool in model edit mode, no, you can't access the message stream, it's gone. However, if you run the model as a tool from the Geoprocessing pane, the messages are captured to the geoprocessing results window (ArcMap) or the geoprocessing history messages (ArcGIS Pro) so you can get them there.
Hope this helps you out!

Re: Creating a table from values and export it

TimHaas — Tue, 20 Apr 2021 14:19:05 GMT

Yes, it absolutely does. Thanks once again!

Re: Creating a table from values and export it

curtvprice — Tue, 20 Apr 2021 14:24:55 GMT

I fixed the bug (missing %) above, sorry about that. Please mark my answer as the solution to help others with the same question! (Honestly I don't care about the points but it helps people find answers!)