Python Tool in Alteryx: Deduplicating Images

I can see that Alteryx.read() function allows me to read in data from a connection that I need to specify (Python tool can take multiple incoming connections) in '#1' format (a string). To be able to see the data, you need to run the workflow with the python tool on the canvas and connected to desired tools. I tried running the workflow with the python tool in a collapsed container but that didn't work, sadly. Which means that the Python tool has to be run as well. The data is brought in a pandas dataframe format.

Let's read in the data coming in incoming connection #1 and assign it to directory variable:

# Read in files datadirectory = Alteryx.read('#1')

It is exactly the same information going into the Python tool, so everything works great! One thing you might have noticed is that Python starts indexing (row numbers) from 0 instead 1.

Moving on, let's prepare a table that will hold the image data. What I will need:

• full path to access the image,
• filename to later create a new path if the image is a duplicate,
• duplicate flag so that I can store all images together whether they are duplicates or not,
• new path, which I will change for images flagged as duplicates,
• image to store the pixel values,
• width and height to know the size of the image (the images need to be of the same size to be compared)

# Initialise pandas DataFrame for storing image informationindex = directory.indexcolumns = ['FullPath','FileName','Duplicate','NewPath','image','width','height']images_df = pd.DataFrame(index=index, columns=columns)

On the second line, I am creating a new variable which is the same as the index of directory dataframe (data from incoming connection #1). That's because I want to have a table that has a single row for each image in my folder. Next, I'm creating a variable that has all of the columns I want to include in my table (you may notice they are very similar to what I've described above). Finally, I am initialising a pandas (pd) dataframe, with rows indexed after my new index variable and with columns I specified in my columns variable.

Now that I have a skeleton for my table, I can populate it with some default values:

# Populate that table with values from the input and some dummy valuesfor row in index:images_df['FullPath'][row] = directory['FullPath'][row]images_df['FileName'][row] = directory['FileName'][row]images_df['Duplicate'][row] = Falseimages_df['NewPath'][row] = directory['FullPath'][row]images_df['image'][row] = cv2.imread(images_df['FullPath'][row])images_df['width'][row] = images_df['image'][row].shape[0]images_df['height'][row] = images_df['image'][row].shape[1]

First thing you may notice is 'for row in index:'. This goes through all values within my index (0 through to 6) and holds the current index value in variable row. This approach is called for-loop and it is in a way similar to a batch macro where you repeat the same operation for a list of options.

Columns FullPath and NewPath are initialised to be the same as later I will be only changing NewPath for duplicate images. The default for Duplicate flag I decided to set to False as I expect only a small portion of the images to actually be duplicates. So far, I have either referenced my previous table directory or set a specific value (False). However, we're getting to the realm of images, and I'm using cv2.imread() function to open the image and store its pixel information in my images_df dataframe. For getting width and height of the image, I am accessing shape attribute of the image I opened on the previous line. shape returns the shape (wink wink) as an object with two positions, the first position is the width (index of 0), the second position is the height (index of 1).

Let's have a look if that worked:

I can see my table nicely populated with data and so I can continue the fun!

Identifying duplicates

Setting up

Alright, I have previously decided on using SSIM to identify duplicate images. Before we get on to running those comparisons let's consider the restrictions of that method:

1. you can only do a one-to-one comparison
2. the images that you are comparing have to be of the same size

Ok, what does it mean in practice? Let's start with point no. 2 because that has an easier answer. I will need all images to be the same size. There are fortunately functions to resize the image, but what size would be best? Finding the answer took me a while and, frankly, I could have dived deeper into that topic to find the best answer. I didn't do that because I got to a point where it was good enough and wanted to move on.

My initial thought was to find the maximum dimensions and resize all images to fit that so that the comparisons will be as precise as possible. The problem is if you have one massive image, all images will be massive. What about a minimum dimension? Similar issue, if you have a very tiny image, all images would be tiny and the comparisons may not make sense. Then I decided to use the median thinking that I wouldn't have to resize many images a lot. I was quite happy with this approach. The problem was that the comparisons were taking quite a lot of time. In the end, I settled on using 200x200 dimension for all comparisons and it seems to be working fine.

If I wanted to find the optimal size, I would need to run my code with a number of setting and see what is the minimum size for comparison that also has an acceptable level of comparison accuracy. That sounds like a lot of work for a small reward so I will save this for a rainy day.

First, I initialise the sizing. Having width and height as separate variables made it easier for me to tweak it between set values, min, max, and medians:

# Initialise the size for resizingwidth = 200height = 200 

Then I could move on to looping through the rows in images_df and use cv2.resize() function to make all images equal in size:

# Resize the imagesfor row in index:images_df['image'][row] = cv2.resize(images_df['image'][row],(width, height))

Back to restriction no. 1: only one-to-one comparisons mean I will need to do a lot of comparisons and I need to find a way to limit them as much as possible. The minimum would be not duplicating the comparisons. If we have two images: image1 and image2, image1-image1 comparison is pointless as we're comparing the same image with itself, image1-image2 comparison will have the same SSIM as image2-image1 so I only need to do one of those. Great. How do I translate this for many more images than two?

Let's 'draw' it out. Imagine I have four images with indices of 0, 1, 2, 3. I can split them into comparisons table with image1 and image2 columns (both identifying the indices of the images I want to compare within a single comparison), and ssim column for storing the results of that comparison. First, I want to make all possible and sensible comparisons with image 0, and so I compare 0-1, 0-2, 0-3. (I don't want to compare 0-0 because that's comparing the image with itself. Doesn't make sense.)

Then, I can do all remaining comparisons with image 1: compare 1-2, 1-3. (1-0 comparison has been already done previously as 0-1 so I don't need to worry about that.)

Now I'm left with 2-3 comparison because all of the other possible comparisons have been listed previously (or don't make sense as I would be repeating comparisons).

How do I turn it into something automated that will be built out depending on the number of images my app is dealing with? Well, I can see that starting with the lowest possible index for image1 (ind1), the values of image2 will vary from one index higher (ind1+1) until the maximum value of index (max(index)). Then I'm moving onto the next index for image1 and repeat the same process for image2:

With the above as my plan, I can initialise the comparisons table:

# Initialise data frame for comparisonstotal_comparisons = sum(index)comparisons_index = range(total_comparisons)comparisons_columns = ['image1', 'image2', 'ssim']comparisons_df = pd.DataFrame(index=comparisons_index, columns=comparisons_columns)

Based on my experience, the performance is better when you know how big of an object (here a data frame), you want to build in python. And so I needed to figure out the total number of comparisons I will need to be prepared for. Looking at my simple example with four images and indices of 0, 1, 2, 3, the number of comparisons I needed to do was 3 (for image1 index of 0) + 2 (for image1 index of 1) + 1 (for image1 index of 2). 3+2+1 for indices of 0, 1, 2, 3. Luckily the indices in python start at 0, because the total number of comparisons I need to do is the sum of all indices I have (3+2+1 = 3+2+1+0).

The first step, then, is to get the total number of comparisons. Now I can build the new index for comparisons table which is done by range(total_comparisons). range function, when given one argument, is creating a range from 0 to the given number - 1. So that the total number of steps in the created range is equal to the given number.

Next, I specify the columns I am interested in and initialise an empty dataframe.

Moving onto filling out the comparisons table with the image combinations I want for my comparisons:

row = 0for image1 in index[0:-1]:for image2 in range(image1+1,max(index)+1):comparisons_df['image1'][row] = image1comparisons_df['image2'][row] = image2row += 1

Previously, I was looping through the rows of the data frame. This time I found it easier to loop through indices for image1 and for image2, and change the row manually. To get it sorted, I initialise the row at 0. Then I start a loop through indices for image1. index[0:-1] will go through all values from 0 (the first one) to the penultimate one (noted in python as -1). Once I have the value for image1, I want to loop through indices for image2. The expression for this loop may look a bit complex but let's break it down.

range(image1+1, max(index)+1)

I am passing two arguments to range function, which means it will create a range between the first argument, to the second number - 1:

The first argument is the point where I want to start. Based on my previous analysis it will be the index above than for image1, so image1+1. The second argument will be where I want to end (max(index)) increased by 1, so max(index)+1.

Once I'm within the loop for image2, I can fill in the values of image1 and image2 for the current row. The final step is to move to the next row with row += 1. This way of writing it down is a bit faster than writing row = row+1.

What happens in the next row? The most inner loop will finish first, so python will iterate over the values for image2, and then move on to the next value in image1, create a new list for image2 and go over that again. Until it gets to the last value for image1 and the corresponding last value for image2.

Extracting the Threshold

One of the things I wanted my app to be able to do was to identify images that are very similar and not only identical duplicates. SSIM is returning values between -1 and 1, where 1 is an identical image. (I couldn't find what -1 is, but I assume it is exactly opposite, similar to the correlation of -1. Don't quote me on that though.) I decided to mark images as duplicate if the SSIM index was above a certain threshold. To give the user even more power (dangerous, I know), I added a numeric up and down so that they can specify the threshold whenever they run the app:

I use the Text Input tool (no. 1) to have a placeholder for the threshold (set it up to 0.9). Then I added Numeric Up Down (no. 2) with the range of -1 and 1 (possible values of SSIM index) and default value of 0.9. The Action tool (no. 3) updates the threshold value in the text input tool and passes it over to the Python tool as connection #2.

In the python tool, I need to read the threshold in with Alteryx.read function:

# Get the thresholdthreshold = Alteryx.read('#2')['threshold'][0]

I am accessing the value itself by taking out the content of threshold column and 0-th row, then assign it to threshold variable.

Running comparisons

Seems that I am finally ready to run my comparisons:

# Initialise duplicates list and run the SSIM comparisonsduplicates = []for comparison_index in comparisons_index:image1 = images_df['image'][comparisons_df['image1'][comparison_index]]image2 = images_df['image'][comparisons_df['image2'][comparison_index]]comparisons_df['ssim'][comparison_index] = ssim(image1,image2,multichannel=True)if comparisons_df['ssim'][comparison_index] >= threshold:duplicates.append(comparisons_df['image2'][comparison_index])

As I don't know how many duplicates I will have, I am starting with an empty list []. Then I'm looping through all comparisons, assign image from images_df to image1 and image2 based on the indices in the comparisons table. Moving onto running the function that returns the ssim index for two images, I have what I need to identify if the images are similar enough to be marked as duplicates.

The way I decided to handle or assign duplicate flag, was as follows: For every comparison with ssim value of greater than or equal to threshold, add the index of image2 to the duplicates list. If there are are more images that are similar to each other, all but the first one should be marked as duplicates.

Now that I'm writing it up, I think I could optimise the process and limit the number of comparisons even more. I could remove any future comparisons that reference images already known as duplicates. There's always something to improve on :)

In case some images would show up multiple times as duplicates, I wanted to make my duplicate list unique. I was pretty disappointed that it wasn't as straightforward in python as it is in Alteryx (or at least that I didn't find anything as straightforward as the unique tool). What I had to do was to change my list of duplicates to a set - this would result in a unique set of values - then back to list so that I could easily access the duplicate indices:

unique_duplicates = list(set(duplicates))

Create a new location

Before I update images_df I want to create the folder to move all duplicates. (I could do it after updating that table, but I wanted to update it only once the location existed.)

# Make Duplicates foldercurrent_dir = directory['Directory'][0]path = current_dir + 'Duplicates'try:os.mkdir(path)except OSError:print ('Creation of the directory %s failed' % path)else:print ('Successfully created the directory %s ' % path)

First, I assign the directory from connection #1 to the python tool (data from the directory tool) as current_dir. Adding 'Duplicates' to current_dir will build a path of the new folder, where I want to move the duplicate images to. I have to tell the local machine to actually create the folder (the path on its own is not enough). The try, except <error>, else is a way of handling errors in python. (To be honest, I googled creating a folder with python and found this code on stackoverflow.) Python will try to execute the code in try section (make the folder based on the path). If there will be the specified error (OSError), Python will execute the code in the except section (printing out message that the creation failed). If no such error is raised, Python will move on to else (printing out the message that the location has been created).

With the folder finally created, I can update my main table images_df:

# Create new file paths and update duplicate flagsfor row in unique_duplicates:dest_path = path + '\\\\' + images_df['FileName'][row]images_df['NewPath'][row] = dest_pathimages_df['Duplicate'][row] = True

This time, I am only going through the indices in the duplicates list. For each duplicate, I am building the destination path which is the Duplicates folder path combined with the file name, and use it to overwrite the value in NewPath column. I am also changing the duplicate flag to true.

The final step regarding the python tool, is to output the result back into the workflow:

# Output the data to alteryx workflowoutput = images_df[['FullPath','Duplicate','FileName','NewPath']]Alteryx.write(output, 1)

As I don't need all columns from images_df in my output, I create a new dataframe that has only FullPath, Duplicate, FileName, and NewPath. Then I am writing it to output anchor #1. (Notice Alteryx.write function takes numbers, not strings as the argument for identifying the output anchor. Something I found a little annoying...)

Move duplicates to the new location

The last step was to move the images from the original location to the newly created Duplicates folder. I thought it would be easy but it turned out to be a little challenging. My first try was to do it within the python tool (use it as much as I can!) Unfortunately, I was getting errors that the files I am trying to move are open by another process. I am not 100% sure that's the reason but my thinking was that because these files were open within the same python tool (to read the image information), they were considered as open files. Even though they were not open in a traditional way (as you would open a text file in python to edit its contents) and so I couldn't figure out how to enforce them being closed. As a result, I turned to The Information Lab and Ben came to the rescue once again, suggesting using bash script to get this part done.

I had a mixed reaction to this. On the one hand, I wasn't too happy about it as I had another step to do and another thing to learn before the app would work. On the other hand, I was very happy about it as I had another thing I could learn :)

To sort this one out, I had to figure out what are batch scripts, how to write a simple one, how to apply it to multiple images, then how to implement it in alteryx. Again, it took some googling, setting up a simple testing environment, and troubleshooting with my colleagues. Batch scripts are files with a code you would normally execute in the command line. The magic is that you can save them and run them from within Alteryx.

The script I had to build was not a very complex one. Nevertheless, it went down not without any technical problems... Thanks to Jonathan Sherman who shared his solution which helped me identify issues in my approach.

I decided to only do it for the duplicate images as for non-duplicates, NewPath (destination) was the same as FullPath (origin). (#1 the filter tool)

The command line cannot have any spaces within the path, and if it does it needs to be bookended by quotation marks. So in #2 (Formula tool, Update paths) I'm doing just that both for NewPath and FullPath:

''' + [NewPath] + '''

''' + [FullPath] + '''

In step #3 (Formula tool, script), I am building a script at the level of each image. Adding a column called script:

'MOVE ' + [FullPath] + ' ' + [NewPath]

The summarize tool (#4) is set to concatenate the script field with the separator of \ (newline character), so that scripts for all files are in a single cell, operation for each file starting on a new line.

The last step (#5, Run Command tool), saves the script into a file and then executes it. I set it up so that it's save to a temporary file (which should be cleaned after Alteryx is finished), and give it a meaningful name of moveFiles.bat

This is the place where I was running into issues. It turned out that the set up of output in Write Source [Optional] was the culprit:

I knew the file would need to be set to a text file, the bit I missed was that delimiters needed to be set to \\0.

Conclusion

Surprisingly it has been a short and a long project at the same time. A short one as I managed to get a lot done in a short amount of time. A long one as describing it take a long time, making me realise there was a lot of planning and learning involved (which is great!)

I really enjoyed building an application that combined the worlds of Python and Alteryx, actually making the most of the two, and learning extra bits such as batch scripts!

The final app sits on The Information Lab's public alteryx gallery and you can download it here.

As mentioned above, there are a few improvements I would like to make but I am still pretty happy with the current outcome. Let me know if you have other ideas on how to optimise this solution or on improvements I could add!

Have you used the python tool yourself? If so, what did you use it for? If not, what would you use it for? Let me know!