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 <SPAN class="keyword token">import</SPAN> numpy <SPAN class="keyword token">as</SPAN> np a <SPAN class="operator token">=</SPAN> <SPAN class="comment token"># your array goes here</SPAN> a_s <SPAN class="operator token">=</SPAN> a<SPAN class="punctuation token">[</SPAN>np<SPAN class="punctuation token">.</SPAN>argsort<SPAN class="punctuation token">(</SPAN>a<SPAN class="punctuation token">,</SPAN> order<SPAN class="operator token">=</SPAN><SPAN class="string token">"Class"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN> a_u<SPAN class="punctuation token">,</SPAN> idx <SPAN class="operator token">=</SPAN> np<SPAN class="punctuation token">.</SPAN>unique<SPAN class="punctuation token">(</SPAN>a_s<SPAN class="punctuation token">[</SPAN><SPAN class="string token">"Class"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> return_inverse<SPAN class="operator token">=</SPAN><SPAN class="token boolean">True</SPAN><SPAN class="punctuation token">)</SPAN> bin_cnt <SPAN class="operator token">=</SPAN> np<SPAN class="punctuation token">.</SPAN>bincount<SPAN class="punctuation token">(</SPAN>idx<SPAN class="punctuation token">,</SPAN>weights<SPAN class="operator token">=</SPAN>a_s<SPAN class="punctuation token">[</SPAN><SPAN class="string token">'Count'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> ans <SPAN class="operator token">=</SPAN> np<SPAN class="punctuation token">.</SPAN>array<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">(</SPAN>a_u<SPAN class="punctuation token">,</SPAN> bin_cnt<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> dtype<SPAN class="operator token">=</SPAN><SPAN class="string token">'object'</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"a_u\n{}\nidx {}\nanswer\n{}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>a_u<SPAN class="punctuation token">,</SPAN> idx<SPAN class="punctuation token">,</SPAN> ans<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> rot90 <SPAN class="operator token">=</SPAN> np<SPAN class="punctuation token">.</SPAN>rot90<SPAN class="punctuation token">(</SPAN>ans<SPAN class="punctuation token">,</SPAN> k<SPAN class="operator token">=</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">)</SPAN>&nbsp; and_flipud <SPAN class="operator token">=</SPAN> np<SPAN class="punctuation token">.</SPAN>flipud<SPAN class="punctuation token">(</SPAN>rot90<SPAN class="punctuation token">)</SPAN> <SPAN class="comment token">#np.flipud(np.rot90(a,k=1))))</SPAN> frmt <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">"pivot table... rotate 90, flip up/down\n{}"</SPAN>&nbsp; <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN>frmt<SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>and_flipud<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN> 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 <SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> range<SPAN class="punctuation token">(</SPAN><SPAN class="number token">4</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> &nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"\nrotated {}\n{}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN><SPAN class="number token">90</SPAN><SPAN class="operator token">*</SPAN>i<SPAN class="punctuation token">,</SPAN> np<SPAN class="punctuation token">.</SPAN>rot90<SPAN class="punctuation token">(</SPAN>a<SPAN class="punctuation token">,</SPAN> k<SPAN class="operator token">=</SPAN>i<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> range<SPAN class="punctuation token">(</SPAN><SPAN class="number token">4</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> &nbsp;&nbsp;&nbsp; f <SPAN class="operator token">=</SPAN> <SPAN class="string token">"\nrotate by {} and flip up/down\n{}"</SPAN> &nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN>f<SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN><SPAN class="number token">90</SPAN><SPAN class="operator token">*</SPAN>i<SPAN class="punctuation token">,</SPAN> np<SPAN class="punctuation token">.</SPAN>flipud<SPAN class="punctuation token">(</SPAN>np<SPAN class="punctuation token">.</SPAN>rot90<SPAN class="punctuation token">(</SPAN>a<SPAN class="punctuation token">,</SPAN> k<SPAN class="operator token">=</SPAN>i<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> range<SPAN class="punctuation token">(</SPAN><SPAN class="number token">5</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> &nbsp;&nbsp;&nbsp; f <SPAN class="operator token">=</SPAN> <SPAN class="string token">"\nrotate by {} and flip left/right\n{}"</SPAN> &nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN>f<SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN><SPAN class="number token">90</SPAN><SPAN class="operator token">*</SPAN>i<SPAN class="punctuation token">,</SPAN> np<SPAN class="punctuation token">.</SPAN>fliplr<SPAN class="punctuation token">(</SPAN>np<SPAN class="punctuation token">.</SPAN>rot90<SPAN class="punctuation token">(</SPAN>a<SPAN class="punctuation token">,</SPAN> k<SPAN class="operator token">=</SPAN>i<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Input table with the following fields 'ID', 'X', 'Y', 'Class', 'Count' <SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> input array<SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">6.0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">0.0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'a'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">10</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">7.0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">9.0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'c'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">1</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">8.0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">6.0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'b'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">2</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="number token">3</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">3.0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">2.0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'a'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">5</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="number token">4</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">6.0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">0.0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'a'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">5</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="number token">5</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">2.0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">5.0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'b'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">2</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="number token">6</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">3.0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">2.0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'a'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">10</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="number token">7</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">8.0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">6.0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'b'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">2</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="number token">8</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">7.0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">9.0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'c'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">1</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="number token">9</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">6.0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">0.0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'a'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">10</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="comment token"># the results</SPAN> a_u&nbsp; <SPAN class="comment token"># unique values</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'a'</SPAN> <SPAN class="string token">'b'</SPAN> <SPAN class="string token">'c'</SPAN><SPAN class="punctuation token">]</SPAN> idx <SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN> <SPAN class="number token">0</SPAN> <SPAN class="number token">0</SPAN> <SPAN class="number token">0</SPAN> <SPAN class="number token">0</SPAN> <SPAN class="number token">1</SPAN> <SPAN class="number token">1</SPAN> <SPAN class="number token">1</SPAN> <SPAN class="number token">2</SPAN> <SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN> answer <SPAN class="comment token"># the sums</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="string token">'a'</SPAN> <SPAN class="string token">'b'</SPAN> <SPAN class="string token">'c'</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="number token">40.0</SPAN> <SPAN class="number token">6.0</SPAN> <SPAN class="number token">2.0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">]</SPAN> pivot table<SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN> rotate <SPAN class="number token">90</SPAN><SPAN class="punctuation token">,</SPAN> flip up<SPAN class="operator token">/</SPAN>down <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="string token">'a'</SPAN> <SPAN class="number token">40.0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'b'</SPAN> <SPAN class="number token">6.0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'c'</SPAN> <SPAN class="number token">2.0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">]</SPAN> ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

DanPatterson_Retired

Xander... Very true.  Pandas is available now...if you are using ArcGIS Pro, since they installed the SciPy stack.  I will be 'addendum'-ing any relavent numpy posts to reflect alternatives.  Thanks