To The Point: The Python tool in Alteryx

The Python tool in Alteryx allows use Python on your data - with all the freedom that comes along with it - as long as you output a Pandas DataFrame. I will assume a basic understanding of Python for this blog.

Setup Basics

The Python tool takes one input and has up to 5 outputs

The default settings for the tool are:

This is in a Jupyter interface, which allows you to run your code in blocks. The line

#Package.installPackages(['pandas','numpy'])

Allows you to ensure that the Python packages you wish to import later are installed. If you are using any non-standard Python packages, make sure the line is uncommented and contains all the packages you need. You can then import the packages needed later as normal.

Warning: never delete the following lines:

from ayx import Package
from ayx import Alteryx

These lines are required to ensure you can input and output your data properly for Alteryx

How to interact with your data?

You can import the data as a Pandas DataFrame from the input anchor of the tool via the function:

Alteryx.read("#1")

Which will need to be assigned to a variable to be used. You can output a Pandas DataFrame via the function:

Alteryx.write(df,n)

Which outputs the Pandas DataFrame df to the output anchor n (allowed to be 1 to 5)

Because of this, almost every script will be of the form:

df = Alteryx.read("#1")
...
Alteryx.write(df,1)

How is the data imported?

A Pandas DataFrame is an object from the Pandas Library in Python, which can be interacted with similar syntax to that of a Python Dictionary object, where the keys are your column headers, and the values are arrays for the columns itself:

The line in the Python Alteryx tool

df = Alteryx.read("#1")

Outputs a DataFrame which has the columns and values the same as that of the input anchor. E.g. a column 'Value' could be called by df['Value'] after the above line.

An example script: get all permutations of the rows of your data

I first wrote a script which achieved my desired result outside of Alteryx:

import itertools
import pandas as pd

df = pd.DataFrame({
    'item':['a','b','c','d'],   # Dictionary item 1: column header 'item', values the first four letters
    'value':[10,20,30,40]       # Dictionary item 2: 'value', values first four multiples of 10
      })

print(df) # Full DataFrame
print(df['item']) # Call just the column 'item'


df["id_for_permutations"] = df.index


iters = list(itertools.permutations(df["id_for_permutations"]))

id_out = []
perm_id = []
for perm_num, id_tuple in enumerate(iters):
    for id in id_tuple:
        id_out.append(id)
        perm_id.append(perm_num)

df_out = pd.DataFrame({
    'Permutation_ID': perm_id,
    'Item_ID': id_out
    }).merge(df,how='inner',left_on='Item_ID',right_on='id_for_permutations')

del df_out['id_for_permutations']

Then to use this in Alteryx, I need to add it to the Python tool, ensuring that I

1. Ensure Pandas is installed in the installPackages line since I use this in the script
2. Replace my example Pandas DataFrame with the Alteryx input
3. Add the final writing line to an output anchor

My Python tool in Alteryx is therefore set up as the following two blocks:

# List all non-standard packages to be imported by your 
# script here (only missing packages will be installed)
from ayx import Package
Package.installPackages(['pandas'])

from ayx import Alteryx
import itertools
import pandas as pd

df = Alteryx.read("#1")

df["id_for_permutations"] = df.index


iters = list(itertools.permutations(df["id_for_permutations"]))

id_out = []
perm_id = []
for perm_num, id_tuple in enumerate(iters):
    for id in id_tuple:
        id_out.append(id)
        perm_id.append(perm_num)

df_out = pd.DataFrame({
    'Permutation_ID': perm_id,
    'Item_ID': id_out
    }).merge(df,how='inner',left_on='Item_ID',right_on='id_for_permutations')

del df_out['id_for_permutations']

Alteryx.write(df_out, 1)

An example usage can be found below - just by running the Alteryx workflow after setting up!