Numpy Pivot: Summary Statistics Results

[PeterWilson]

!I recently posted the following discussion Convert Summary Table Structure (Python)​ which I thought I'd resolved with the help from Dan Patterson​. Please refer to my previous post for the explanation and the reasoning behind the following. Where I'm currently at is that I have been able to pivot the column Time and populate it with the values from the statistics count results.

The last hurdle that I'm trying to achieve is to merge the results so that there isn't a new record for each time interval as shown below:

Incorrect Pivot Results:

Correct Pivot Results:

'''
Created on April 6, 2016

Summarise Number of Buildings

per Time Interval

(5, 10, 15, 25, 30, 60)

@author: PeterW
'''
# import site-packages and modules
from pathlib import Path
import numpy.lib.recfunctions as rfn
import pandas as pd  # Pandas version 0.13.0
import arcpy


# set arguments
saa_stats_table = r"E:\Projects\2016\G112224\Models\Schools\Schools_Combined_160505.gdb\Botrivier_Prim_SAA_Stats"

# environment settings
arcpy.env.overwriteOutput = True
fgdb = Path(saa_stats_table).parents[0]


def pivot_table(saa_stats_table, fgdb):
    fields = [f.name for f in arcpy.ListFields(saa_stats_table)]
    table_recarray = arcpy.da.TableToNumPyArray(saa_stats_table, fields)  # @UndefinedVariable
    print table_recarray
    df = pd.DataFrame(table_recarray[fields])
    pivot = df.pivot(index="OBJECTID",
                     columns="TIME",
                     values="FREQUENCY").fillna(0, downcast="infer")
    pivot_fields = pivot.columns.values
    # rename pivot fields with prefix "TIME"
    pivot.columns = [("{0}{1}".format("TIME", field)) for field in pivot_fields]
    # convert pandas dataframe to record array
    pivot_recarray = pivot.to_records(index=False)
    pivot_type = pivot_recarray.dtype.descr
    pivot_type_new = [(x[0], x[1].replace(x[1], "<i2")) for x in pivot_type]
    # change pivot record array data type to short integer
    pivot_recarray = pivot_recarray.astype(pivot_type_new)
    fields2 = ["TOWN", "SETTLEMENTNAME", "NAME"]
    table_type_new = [(str(x), "<U25") for x in fields2]
    # change table array data type to unicode 50 characters
    table_recarray = table_recarray[fields2].astype(table_type_new)
    recarray_list = [table_recarray, pivot_recarray]
    # merge table and pivot record array
    summary_array = rfn.merge_arrays(recarray_list, flatten=True, usemask=False)
    summary_table = str(Path(fgdb, "SAA_Stats_Test"))
    # convert merged record array to file geodatabase table
    if arcpy.Exists(summary_table):
        arcpy.Delete_management(summary_table)
        arcpy.da.NumPyArrayToTable(summary_array, summary_table)  # @UndefinedVariable
    else:
        arcpy.da.NumPyArrayToTable(summary_array, summary_table)  # @UndefinedVariable

pivot_table(saa_stats_table, fgdb)
#

Python Code:

Any advice in how I can achieve the following. I suspect it would need to be done either before or after I have merged the two arrays (line 50). Any other alternative that won't require me having to change all my code would really be appreciated as time is a factor at the moment. I urgently need to get my results out.

NB. Please note I dont have an Advance Licence so Pivot_Table is not an option.

Thanks in advance.

[DanPatterson_Retired]

Peter, I will check when I get home, but on first look, that area is a lot of array slicing with conditions (on a wild guess).... this

row_match = sum_array[sum_array[['TOWN', 'SETTLEMENTNAME',  'NAME']] == unique_records]

means the row_match is the slice of the array sum_array, where the columns (town, sett.. name) in the array, are equal to those in the unique_records condition.  So in short it isn't a list comprehension but I wrote the slice in verbose form, matching the query to the unique conditions, doing them one condition at a time rather than all at once.  This is supposed to make it more readable (kindof didn't work as planned)  but I will try to lay out a flowchart then

[PeterWilson]

Thanks Dan, much appreciated