pivot table does not summarize properly

[Anne-MarieDubois]

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!

[DanPatterson]

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

[Anne-MarieDubois]

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.

[DanPatterson]

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

[MarkGilbert]

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.

[Anne-MarieDubois · Accepted solution]

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.

[MarkGilbert]

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.