topic Re: Pivot table limitations in ArcGIS Pro Questions

Pivot table limitations

ErikKjellman — Fri, 14 Jan 2022 13:25:52 GMT

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:

artefact	amount	material	coordinate
axe	1	quartz	100x100y
axe	2	silcrete	100x100y
axe	1	quartz	110x110y
arrow	1	quartz	100x100y
arrow	2	silcrete	100x100y
arrow	1	quartz	110x110y
flake	3	quartz	100x100y
flake	5	silcrete	100x100y
flake	10	quartz	110x110y

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

coordinate	axe	arrow	flake
100x100y	3	3	8
110x110y	1	1	10

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

coordinate	axe	arrow	flake
100x100y	1	1	3
100x100y	2	2	5
110x110y	2	2	5

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...

Re: Pivot table limitations

DanPatterson — Fri, 14 Jan 2022 13:44:53 GMT

Is your example a complete table or a sample?

What do you get with pivot field as artefact and value field as amount?

Re: Pivot table limitations

ErikKjellman — Fri, 14 Jan 2022 14:27:38 GMT

It's an simplified example of my data (much lareger and in norwegian).

AppoIogies that I wasn't clear enough in my example; the result is with artefact as pivot and amount as value.

I made a version with english headings if you care to have a look.

Re: Pivot table limitations

DanPatterson — Sat, 15 Jan 2022 04:11:28 GMT

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

Re: Pivot table limitations

ErikKjellman — Mon, 17 Jan 2022 09:00:32 GMT

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:

coordinate	artefact	amount
123x3210yNV	Avslag	10

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

Thanks for the assist!

Re: Pivot table limitations

DanPatterson — Mon, 17 Jan 2022 11:42:46 GMT

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)