Summary or Pivot Tables using NumPy

[DanPatterson_Retired]

It is really a pain that certain highly used functions are only available an advanced license level.  This is an alternate to the options of using Excel to produce a pivot table from ArcMap tabular data.

Flipping rows and columns in data generally works smoothly when the table contains one data type, whether it be integer, float or text.  Problems arise when you add stuff to Excel is that it allows you do so without regard to the underlying data.  So, columns get mixed data types and rows do as well. Uniformity by column is the rule.

In NumPy, each column has a particular data type.  The data type controls the operations that can be performed on it.  Numeric fields can have all the number type operations used...similarly for string/text fields.  It is possible to cast a field as an "object" type allowing for mixed type entries.  The nice thing about this type, is that you can't really do anything with it unless it is recast into a more useful form...but it does serve as a conduit to other programs or just for presentation purposes.

In the following example, the line

a = # your array goes here

can be derived using

a = arcpy.FeatureClasstoNumPyArray(....)  FeatureClassToNumPyArray

The nature of np.unique change in version 1.9 to get the number of unique classes as well.  So if you are using ArcGIS Pro, then you can use the newer version if desired by simply changing line 04 below.

a_u, idx, counts = np.unique(a_s, return_inverse=True, unique_counts=True)

Array conversion to summary table or pivot table

Input and output

Well... who needs an advanced license or excel ... Assume we have an array of the format shown in the Input section.  We can determine the counts or sums of unique values in a field, using the following. sort the array on a field, get unique values in that field, sum using the values in another field as weights rotate if desired import numpy as np a = # your array goes here a_s = a[np.argsort(a, order="Class")] a_u, idx = np.unique(a_s["Class"], return_inverse=True) bin_cnt = np.bincount(idx,weights=a_s['Count']) ans = np.array((a_u, bin_cnt), dtype='object') print("a_u\n{}\nidx {}\nanswer\n{}".format(a_u, idx, ans)) rot90 = np.rot90(ans, k=1)  and_flipud = np.flipud(rot90) #np.flipud(np.rot90(a,k=1)))) frmt = ("pivot table... rotate 90, flip up/down\n{}"  print(frmt.format(and_flipud))‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ The trick is to set the data type to 'object'. You just use FeatureClassToNumPyArray or TableToNumPyArray and their inverses to get to/from array format.  Ergo....pivot table should NOT be just for an advanced license For all-ish combos, you can just add the desired lines to the above for i in range(4):     print("\nrotated {}\n{}".format(90*i, np.rot90(a, k=i))) for i in range(4):     f = "\nrotate by {} and flip up/down\n{}"     print(f.format(90*i, np.flipud(np.rot90(a, k=i)))) for i in range(5):     f = "\nrotate by {} and flip left/right\n{}"     print(f.format(90*i, np.fliplr(np.rot90(a, k=i)))) ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Input table with the following fields 'ID', 'X', 'Y', 'Class', 'Count' >>> input array... [[(0, 6.0, 0.0, 'a', 10)] [(1, 7.0, 9.0, 'c', 1)] [(2, 8.0, 6.0, 'b', 2)] [(3, 3.0, 2.0, 'a', 5)] [(4, 6.0, 0.0, 'a', 5)] [(5, 2.0, 5.0, 'b', 2)] [(6, 3.0, 2.0, 'a', 10)] [(7, 8.0, 6.0, 'b', 2)] [(8, 7.0, 9.0, 'c', 1)] [(9, 6.0, 0.0, 'a', 10)]] >>> # the results a_u  # unique values ['a' 'b' 'c'] idx [0 0 0 0 0 1 1 1 2 2] answer # the sums [['a' 'b' 'c'] [40.0 6.0 2.0]] pivot table... rotate 90, flip up/down [['a' 40.0] ['b' 6.0] ['c' 2.0]] ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍