Data360 Analyze

When working with categorical data, the GroupBy functionality of the Aggregate node can be used to count the number of occurrences of a particular categorical value in a data set. However, the Aggregate node will only generate counts for each category that was actually seen in the current data set.

In some situations you know the possible set of categorical values that can be present in the category field but all of them may not be present in a specific data set. In this case you can use the Transform node and some Python scripting to create the desired results.

For example, the default data in the Create Data node includes the 'type' field which comprises categorical data (i.e. 'primary', 'secondary', etc).

Suppose you wanted to count the number of occurrences of the following categories: 'primary', 'secondary' and 'tertiary'. In this case a Transform node would be connected to the output of the Create Data node and configured with the following scripts:

Transform node: 'ConfigureFields' property:

# Create the Output record metadata
out1.primary = int
out1.secondary = int
out1.tertiary = int

# Setup the local counters
primaryCount = 0
secondaryCount = 0
tertiaryCount = 0
otherTypeCount = 0

Transform node: 'ProcessRecords' property:

# For every record track if it is a primary, secondary or tertiary
if in1.type == 'primary':
  primaryCount += 1
elif in1.type == 'secondary':
  secondaryCount += 1
elif in1.type == 'tertiary':
  tertiaryCount += 1
else:
  otherTypeCount += 1

# This is the last record so output our results
if node.lastExec:
  out1.primary = primaryCount
  out1.secondary = secondaryCount
  out1.tertiary = tertiaryCount

The output from the Transform node is as follows.

Hi Adrian,

This is really useful, thanks for posting.

I've got a somewhat related question so hoping you can please help. I'm hitting my head against the wall trying to figure out how to replicate a SQL window function within the Transform node (python).

Essentially what I want to do is order by DATE ascending and count each instance of the ID that occurs. The rough code in SQL would be:

ROW_NUMBER() OVER(PARTITION BY "ID" ORDER BY "DATE") as Instance_Count

The easy option would be for me to adjust my code within the DB node however I know I will need similar functionality in future and figure there's definitely a way to do this within Dataverse/Python.

If you can assist that would be great - thanks.

There are a number of ways you can partition your data to allow you to then work with these sub-groups and apply custom window functions. In all cases you will need to sort your data using the group by fields before using the data in the Transform node.

One method is to explicitly create your own aggregate and reset the aggregate variable at the end of each sub-group. An example of this is shown in the top Transform node in the attached data flow file.

You can also leverage the GrouBy functionality in the Transform node and the Python API's 'grouping' functions. This functionality is described in the Dataverse Help Python scripting -> Python module support. It is also described in the online documentation here. An example of using the grouping functions is shown in the middle Transform node in the data flow.

If you just want to calculate the aggregate records you can also use the grouping functions in the Transform node. However, it may be more straightforward to use the Aggregate node - especially when using the Aggregate node's script-less configuration functionality that was introduced in Dataverse 3.1.6. An example of using the grouping functions to calculate aggregate values for each group is shown in the lower Transform node in the data flow. Typically this approach would be used if you wanted to subsequently join, say, the field containing the group average value to the original data set.

Regards,

Adrian

Attached files

Partitioning_Data_Using_a_Window_Function - 15 Nov 2017.lna