Pivot table limitations

Question

I have started looking into the Pivot Table gp tool in Arcgis Pro and I am not satisfied with its performance... but it also might be that I am missing some essential clue on best practice. The following is an example of input: artefactamountmaterialcoordinateaxe1quartz100x100yaxe2silcrete100x100yaxe1quartz110x110yarrow1quartz100x100yarrow2silcrete100x100yarrow1quartz110x110yflake3quartz100x100yflake5silcrete100x100yflake10quartz110x110y

When doing pivot in excel I end up with something like this (ignoring material): coordinateaxearrowflake100x100y338110x110y1110

But doing the same in Arcgis i get something like this:  coordinateaxearrowflake100x100y113100x100y225110x110y225

I understand the reason (I think); it summarizes per material per coordinate, even though I only put coordinate as an input field. I have tested this with adding material and get the same output. Only difference is that it adds a column for material. Is this how it is supposed to work or is it bugged? If it is how it should work then I must say it does not do a good job...

danretired · Answer

You could also check out Panda pivot table to see if it works since you can use TableToNumPyArray to get a structured dataset which can be read by Pandas

pandas.pivot_table — pandas 1.3.5 documentation (pydata.org)

ErikKjellman · Answer

Thank you for looking into it. I suppose the easiest solution is to use excel pivot first, because it looks to me that the pivot  in ArcGIS Pro is not up to the task. I tried the summarize statistics tool, but it would not do complex summations across my table.

The example you did is not what I am after; I wanted the table to summarize amounts for a each artefact in each coordinate so that it would look like this:

coordinateartefactamount123x3210yNVAvslag10

I'll check with tech support if there could be an issue with Pivot.

Thanks for the assist!

danretired · Answer

Sorry for the delay, but every time I tried pivot table in Pro 2.9, it silently went away.

I decided to just do a summary using artefact and material, summing on amount as shown in the image

I am not sure what the issue is with the pivot table, but given my experience, you might want to Tech Support it.

I also noticed that you had a few blank cells in your excel