nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to convert a python "collection" to a gdb table? (1-to-many)

RebeccaStrauch__GISP

I have a attribute table that contains the results of a SpatialJoin giving me multiple records for each of my target polygon.

I'm trying to convert the above to a one-to-many table; one record for each unique HuntID w/a field listing all the uniqid.e.g.

'RL450', '50, 57, 70, 71, 81, 175, 177'

'DM448, '72, 76, ...'

etc.

Actual formatting (i.e. comma or space separated, quotes, etc, ) is yet tbd and not important at this point

I've done quite a bit of searching and have found two different simple functions to give me unique values for one field, and and other method using collections for giving me the 1-to-many "collection", but I haven't been able to figure out a simple way to get this back into a table.

I think I am missing something obvious, and although I have done quite a few searchs and looked thru many of the /blogs/dan_patterson/2016/05/09/the-links?sr=search&searchId=ceb1d9a4-61d3-4411-b4b9-2e06544edf3c&searchIndex=0‌ I haven't figure it out.

<SPAN class="comment token">#&nbsp; the various snippets I've narrowed it down to so far</SPAN>
<SPAN class="keyword token">import</SPAN> arcpy
<SPAN class="keyword token">import</SPAN> os
<SPAN class="keyword token">import</SPAN> numpy

inDS <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'Database Connections\__me@wcgis_test2.sde\sde_wcgis_test2.DBO.FandA'</SPAN>
inDB <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'Database Connections\__me@wcgis_test2.sde'</SPAN>
inFC <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'Database Connections\__me@wcgis_test2.sde\sde_wcgis_test2.DBO.FandA\sde_wcgis_test2.DBO.SJ_hunt2flat'</SPAN>

outTable <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'Database Connections\__me@wcgis_test2.sde\testtable'</SPAN>

huntFld <SPAN class="operator token">=</SPAN> <SPAN class="string token">'HuntID'</SPAN>
uniqfld <SPAN class="operator token">=</SPAN> <SPAN class="string token">'uniqid'</SPAN>

<SPAN class="comment token">#&nbsp; Function to output a list of of unique values for a field</SPAN>
<SPAN class="keyword token">def</SPAN> <SPAN class="token function">unique_values</SPAN><SPAN class="punctuation token">(</SPAN>table<SPAN class="punctuation token">,</SPAN> field<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>&nbsp; 
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>table<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN>field<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">return</SPAN> sorted<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">{</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">}</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
<SPAN class="comment token">#&nbsp; Function to output a array of unique values for a field</SPAN>
<SPAN class="keyword token">def</SPAN> <SPAN class="token function">unique_values_2</SPAN><SPAN class="punctuation token">(</SPAN>table<SPAN class="punctuation token">,</SPAN> field<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; data <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>TableToNumPyArray<SPAN class="punctuation token">(</SPAN>table<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN>field<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">return</SPAN> numpy<SPAN class="punctuation token">.</SPAN>unique<SPAN class="punctuation token">(</SPAN>data<SPAN class="punctuation token">[</SPAN>field<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>

huntList <SPAN class="operator token">=</SPAN> unique_values<SPAN class="punctuation token">(</SPAN>inFC<SPAN class="punctuation token">,</SPAN> huntFld<SPAN class="punctuation token">)</SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
huntList2 <SPAN class="operator token">=</SPAN> unique_values_2<SPAN class="punctuation token">(</SPAN>inFC<SPAN class="punctuation token">,</SPAN> huntFld<SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># ###&nbsp; Collection portion</SPAN>
<SPAN class="keyword token">import</SPAN> collections
uniqvals <SPAN class="operator token">=</SPAN> collections<SPAN class="punctuation token">.</SPAN>defaultdict<SPAN class="punctuation token">(</SPAN>list<SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># output a collection of unique Field1 and the its unique Field2 values</SPAN>
<SPAN class="keyword token">for</SPAN> field1<SPAN class="punctuation token">,</SPAN> field2 <SPAN class="keyword token">in</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>inFC<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">(</SPAN>huntFld<SPAN class="punctuation token">,</SPAN> uniqfld<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; uniqvals<SPAN class="punctuation token">[</SPAN>field1<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>append<SPAN class="punctuation token">(</SPAN>field2<SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># sample output</SPAN>
test <SPAN class="operator token">=</SPAN> uniqvals<SPAN class="punctuation token">.</SPAN>items<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="number token">95</SPAN><SPAN class="punctuation token">]</SPAN>
test
<SPAN class="comment token"># (u'DM448', [72, 76, 77, 78, 79, 80, 81, 83, 86, 87, 88, 89, 90, 91, 92, 93, 105, 112, 116, 122])</SPAN>
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

BTW - I have tried various combinations of arcpy summary, frequency, pivot tables, Pro's (yes Pro) "summarize within" -- instead of SpatialJoin, but open to other suggestions (although this python solution will be fast enough, I believe).

Thanks

This seems right up your alley Dan Patterson‌

Find more posts tagged with

Accepted answers

JoshuaBixby

Although I work with 9.7. itertools — Functions creating iterators for efficient looping — Python 2.7.13 documentation quite a bit, and hence would lean towards an itertools.groupby solution, I think the best approach is using a pandas data frame now that pandas is bundled with the ArcGIS Desktop Python distribution:

<SPAN class="keyword token">import</SPAN> pandas
tbl <SPAN class="operator token">=</SPAN> <SPAN class="comment token"># path to spatial join table</SPAN>
csv <SPAN class="operator token">=</SPAN> <SPAN class="comment token"># path to output csv</SPAN>

idx<SPAN class="punctuation token">,</SPAN> rollup <SPAN class="operator token">=</SPAN> <SPAN class="string token">"HuntID"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"uniqid"</SPAN>

df <SPAN class="operator token">=</SPAN> pandas<SPAN class="punctuation token">.</SPAN>DataFrame<SPAN class="punctuation token">.</SPAN>from_records<SPAN class="punctuation token">(</SPAN>
&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>TableToNumPyArray<SPAN class="punctuation token">(</SPAN>tbl<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN>idx<SPAN class="punctuation token">,</SPAN> rollup<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="punctuation token">)</SPAN>
df<SPAN class="punctuation token">[</SPAN>rollup<SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> df<SPAN class="punctuation token">[</SPAN>rollup<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>apply<SPAN class="punctuation token">(</SPAN>str<SPAN class="punctuation token">)</SPAN>
df<SPAN class="punctuation token">.</SPAN>groupby<SPAN class="punctuation token">(</SPAN>idx<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN>rollup<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>apply<SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">lambda</SPAN> x<SPAN class="punctuation token">:</SPAN> <SPAN class="string token">", "</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>x<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>to_csv<SPAN class="punctuation token">(</SPAN>csv<SPAN class="punctuation token">,</SPAN> header<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">[</SPAN>rollup<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

All comments

JoshuaBixby

Most of the SciPy stack, including NumPy and Pandas, started getting bundled with ArcGIS 10.4. Esri initially trumpeted SciPy modules would be bundled with 10.3.1, but it ended up not making that release.

RebeccaStrauch__GISP

Thanks bixb0012 . I'll switch the "correct answer" to you, so others will know that it works and is a much better way to go, assuming two things:

you are using a version where Pandas is included like 10.5.x (not included with 10.3.1) or you download/install manually, and
you have a GDB table that is compatible with that version. (i.e. SDE for SQL 2012+ or a fgdb)

Not only is your code more efficient, the output has both the HuntID and the uniqid polys within each sorted, my code did not.

Unfortunately, I'm still using 10.3.1 and SQL 2008 so the initial test would not work. However, I did test on a ArcCatalog 10.5.1 install with my table copied to a fgdb, and it worked slick. So, I'll keep this solution for the future since we are looking to upgrade soon(er or later) and can definitely use it for my prototype testing!

Thanks for taking the time!

JoshuaBixby

<SPAN class="keyword token">import</SPAN> pandas
tbl <SPAN class="operator token">=</SPAN> <SPAN class="comment token"># path to spatial join table</SPAN>
csv <SPAN class="operator token">=</SPAN> <SPAN class="comment token"># path to output csv</SPAN>

idx<SPAN class="punctuation token">,</SPAN> rollup <SPAN class="operator token">=</SPAN> <SPAN class="string token">"HuntID"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"uniqid"</SPAN>

df <SPAN class="operator token">=</SPAN> pandas<SPAN class="punctuation token">.</SPAN>DataFrame<SPAN class="punctuation token">.</SPAN>from_records<SPAN class="punctuation token">(</SPAN>
&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>TableToNumPyArray<SPAN class="punctuation token">(</SPAN>tbl<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN>idx<SPAN class="punctuation token">,</SPAN> rollup<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="punctuation token">)</SPAN>
df<SPAN class="punctuation token">[</SPAN>rollup<SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> df<SPAN class="punctuation token">[</SPAN>rollup<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>apply<SPAN class="punctuation token">(</SPAN>str<SPAN class="punctuation token">)</SPAN>
df<SPAN class="punctuation token">.</SPAN>groupby<SPAN class="punctuation token">(</SPAN>idx<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN>rollup<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>apply<SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">lambda</SPAN> x<SPAN class="punctuation token">:</SPAN> <SPAN class="string token">", "</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>x<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>to_csv<SPAN class="punctuation token">(</SPAN>csv<SPAN class="punctuation token">,</SPAN> header<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">[</SPAN>rollup<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

RebeccaStrauch__GISP

Not perfect, and not in a GDB attribute table, but I was able to get the "rolled up" list to a comma delimited file with two fields, one with my key field (huntID) and the other with a list of the unique polygon id's as a string. For the purposes of this workflow development and test, I think this will work.

The issue with getting it into a GDB attribute file is the length of the polygon ID list. Right now I have a list of integers, but in production, the id's will be much larger and possibly using the list of globids. In that case, I may revisit Domenico Ciavarella 's SQL sample and/or get the list into a variable length field.

Although I did not use the SQL solution, it did give me the "roll-up" term which I then searched and found Advice on "roll up" / consolidation tool which might be helpful for others. So between that post, and the advice from both Domenico and Joshua Bixby , I decided the fastest solution was writing the output of collection.defaultdict to a .csv with a modified version of Joshua's formatting string.

Sample output:

Here is the code I used to produce that,

<SPAN class="keyword token">import</SPAN> arcpy
<SPAN class="keyword token">import</SPAN> os
<SPAN class="keyword token">import</SPAN> collections 
uniqvals <SPAN class="operator token">=</SPAN> collections<SPAN class="punctuation token">.</SPAN>defaultdict<SPAN class="punctuation token">(</SPAN>list<SPAN class="punctuation token">)</SPAN>

theWorkspace <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"c:\__temp"</SPAN>&nbsp;&nbsp;&nbsp; 
arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>workspace <SPAN class="operator token">=</SPAN> theWorkspace
arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>overwriteOutput <SPAN class="operator token">=</SPAN> <SPAN class="token boolean">True</SPAN>

inFC <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'Database Connections\__me@wcgis_test2.sde\sde_wcgis_test2.DBO.FandA\sde_wcgis_test2.DBO.SJ_hunt2flat'</SPAN>


<SPAN class="keyword token">for</SPAN> field1<SPAN class="punctuation token">,</SPAN> field2 <SPAN class="keyword token">in</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>inFC<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">(</SPAN>huntFld<SPAN class="punctuation token">,</SPAN> uniqfld<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; uniqvals<SPAN class="punctuation token">[</SPAN>field1<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>append<SPAN class="punctuation token">(</SPAN>field2<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="comment token">#print("hunt {0} poly {1}".format(field1, field2))</SPAN>

outFile <SPAN class="operator token">=</SPAN> <SPAN class="string token">"hunt2poly"</SPAN> 
outFileCSV <SPAN class="operator token">=</SPAN> os<SPAN class="punctuation token">.</SPAN>path<SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>theWorkspace<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">"{0}.csv"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>outFile<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># open the file, verify it opened, and write the field/header names</SPAN>
csvFile <SPAN class="operator token">=</SPAN> open<SPAN class="punctuation token">(</SPAN>outFileCSV<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'w'</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN>&nbsp; <SPAN class="string token">"File {0} is open? {1}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>str<SPAN class="punctuation token">(</SPAN>outFileCSV<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> str<SPAN class="punctuation token">(</SPAN><SPAN class="operator token">not</SPAN> csvFile<SPAN class="punctuation token">.</SPAN>closed<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
csvFile<SPAN class="punctuation token">.</SPAN>write<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"huntID, polys\n"</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="keyword token">for</SPAN> hunt<SPAN class="punctuation token">,</SPAN> plist <SPAN class="keyword token">in</SPAN> uniqvals<SPAN class="punctuation token">.</SPAN>items<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; csvFile<SPAN class="punctuation token">.</SPAN>write<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'{0},"{1}"\n'</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>hunt<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">", "</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>str<SPAN class="punctuation token">(</SPAN>pID<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> pID <SPAN class="keyword token">in</SPAN> plist<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># close the file and verify it closed properly</SPAN>
csvFile<SPAN class="punctuation token">.</SPAN>close<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN>&nbsp; <SPAN class="string token">"File {0} is open? {1}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>str<SPAN class="punctuation token">(</SPAN>outFileCSV<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> str<SPAN class="punctuation token">(</SPAN><SPAN class="operator token">not</SPAN> csvFile<SPAN class="punctuation token">.</SPAN>closed<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

I'm marking this comment as correct to close the post and hope it may help others.

RebeccaStrauch__GISP

Thanks Domenico. This may work for my needs. I'm working on a very small subset right now for testing, and I think a pure SQL process might work in this case to get my table. Then I can read it back into my gdb if needed. I'll take a look at the sample and give it a try.

RebeccaStrauch__GISP

Thanks Joshua. I may go that route, although I was hoping for a NumpyArrayToTable type solution, but with my initial test of that it didn't like the format of the array. Thought maybe, since it was a nice function to create my collection, maybe there was a simple function to do the last step. My head was spinning by the end of the day yesterday, but I might give it another shot/look. Although Domenico's SQL solution might also work for my purposes.

nicogis

It isn't in python but you can also use sql creating a view.

See for example in sql server : https://www.mssqltips.com/sqlservertip/2914/rolling-up-multiple-rows-into-a-single-row-and-column-for-sql-server-data/

JoshuaBixby

It seems like your current approach works, you just need to convert the list of items to a string. What about join to create the string field you are looking for:

<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> l <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="number token">72</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">76</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">77</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">78</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">79</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">80</SPAN><SPAN class="punctuation token">]</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="string token">", "</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>str<SPAN class="punctuation token">(</SPAN>i<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> l<SPAN class="punctuation token">)</SPAN>
<SPAN class="string token">'72, 76, 77, 78, 79, 80'</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.