topic Re: pivot table does not summarize properly in ArcGIS Pro Questions

pivot table does not summarize properly

Anne-MarieDubois — Tue, 30 Apr 2024 17:21:46 GMT

Me again,

I'm running into a weird issue. I'm trying to use the pivot table tool to summarize data from a feature class. The idea is to have main class as rows and subclasses as column and get area total at intersection.

Then, I use a cursor to sum all subclasses value into a new total main class column.

While trying to validate the cursor coding results, I noticed that I didn't get the same total values

I have exported my raw data to excel and tried to figure out what the problem is and from what I get, the pivot tool does not calculate correctly the area sums (or at least not for all classes??).

Does anyone have an idea about what causes this? There is no selection or subfiltrer on the input data to pivot table.

Thanks!

Re: pivot table does not summarize properly

DanPatterson — Tue, 30 Apr 2024 17:32:58 GMT

Pivot Table (Data Management)—ArcGIS Pro | Documentation

There are some warnings in the tool useage about uniqueness (Frequency) and nulls, which you have, but the tool didn't fail though

Re: pivot table does not summarize properly

Anne-MarieDubois — Tue, 30 Apr 2024 17:44:48 GMT

You mean that because certain class/subclass combination don't have a value it would explain the wrong totals?

Because I got no error while running the tool and it completed everytime.

Re: pivot table does not summarize properly

DanPatterson — Tue, 30 Apr 2024 17:55:39 GMT

Frequency (Analysis)—ArcGIS Pro | Documentation

The implication is that if a combination doesn't exist, you can't compare the individual totals via different methods

Re: pivot table does not summarize properly

MarkGilbert — Wed, 01 May 2024 17:58:58 GMT

I'm not certain that Pivot Table is the tool you should be using. I recently used it in a project and don't recall there being any summary operation. Pivot Table can be used to turn rows in a table into columns. Say, for example, you wanted to turn (or pivot) each of your main classes into columns.

Can you try and explain, in another way, what type of summary you are trying to accomplish? Maybe there is a more appropriate tool like Summary Statistics or Calculate Field.

Re: pivot table does not summarize properly

Anne-MarieDubois — Wed, 05 Jun 2024 16:12:57 GMT

Hi @MarkGilbert ,

Basically I needed the values in this column (TYPE_ARBRE)...

...to become new column and sum based on another column (TAILLE)

and give something like that:

I finally ended converting table to numpy array then to padas dataframe in order to get what I wanted without summing errors.

Re: pivot table does not summarize properly

MarkGilbert — Thu, 06 Jun 2024 17:51:33 GMT

I'm not sure if Pivot Table would have worked for your scenario since the combination of Input Field, Pivot Field, and Value Field have to be unique.

In my case, I needed one row for each GEOID with SHORT_DESC as the columns and VALUE as the value field. But there are duplicate combinations of my inputs and I don't believe Pivot Table has any aggregation methods. Might have to aggregate in some fashion before using Pivot Table.

I had to concatenate SHORT_DESC with DOMAIN_DESC and DOMAINCAT_DESC in order to get the required uniqueness for the tool. Ultimately I ran Pivot Table with GEOID as the Input Field, Long_Desc (concatenation of three text fields), as the Pivot Field, and VALUE as the Value Field.

I realize that you already have a solution, but I was so pleased with the Pivot Table tool that I wanted to share my experience in case others find it helpful.

Re: pivot table does not summarize properly

Emils — Tue, 20 May 2025 08:00:32 GMT

Have the same issue. ArcGIS Pivot table gives wrong SUM values (10..15% difference in cases I checked).