Pivot table limitations

216
5
01-14-2022 05:25 AM
Labels (1)
ErikKjellman
New Contributor III
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):
 
coordinateaxearrowflake
100x100y
3
3
8
110x110y
1
1
10
 
But doing the same in Arcgis i get something like this:
 
coordinateaxearrowflake
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...
 
0 Kudos
5 Replies
DanPatterson
MVP Esteemed Contributor

Is your example a complete table or a sample?

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


... sort of retired...
0 Kudos
ErikKjellman
New Contributor III

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.

0 Kudos
DanPatterson
MVP Esteemed Contributor

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

z1.png

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


... sort of retired...
0 Kudos
ErikKjellman
New Contributor III

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: 

coordinateartefactamount
123x3210yNVAvslag10

 

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

Thanks for the assist!

0 Kudos
DanPatterson
MVP Esteemed Contributor

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)


... sort of retired...
0 Kudos