Using Alteryx to find the nearest alternative

by Daniel Watt

This week at Data School we’ve been building a new viz each day from a different data source.  The topics varied from weapons confiscated at airports to the British National Food survey. My personal favourite was visualising farmer’s markets in the US.  We were given free reign to come up with our own question to ask of the data, so I asked “Where do I go if my local farmer’s market closes?”

Here’s a sample of the data, once I’d already transposed the food items into rows (rather than several columns) using Alteryx.

MarketName x y Name Value
Caledonia Farmers Market Association – Danville -72.140305 44.411013 Organic Y
Caledonia Farmers Market Association – Danville -72.140305 44.411013 Bakedgoods Y
Caledonia Farmers Market Association – Danville -72.140305 44.411013 Cheese Y
Caledonia Farmers Market Association – Danville -72.140305 44.411013 Crafts Y
Caledonia Farmers Market Association – Danville -72.140305 44.411013 Flowers Y
Caledonia Farmers Market Association – Danville -72.140305 44.411013 Eggs Y

The data contained 30 different products that a farmers market could sell.  I didn’t just want to visualise where the nearest market but specifically where the nearest market was that sold specific products that had previously been available at your local market. In Alteryx I created points from the longitude and latitude fields then filtered by the products as you can see in the workflow below.  This meant I had a list for all the farmers markets along with their spatial points for each possible product.

filter-and-create-point

Creating points then filtering by product

The next step was to use the find nearest tool to find the nearest market that sold a particular product.  In this case the target and universe inputs are linked to the same data.

find-nearest

The nearest market to a market is itself, so it is vital to tick ‘ignore 0 distance matches’.

nearest-config

Once I had repeated this action for each product I unioned the data together.   Further reshaping was required to get the home stores and nearest alternative stores on different rows as the find nearest tool creates new columns for the nearest rather than new rows. I also used the summarise tool to concatenate together items that were available at the same market.  Here is what the viz looked like in Tableau:

map

With British supermarkets currently having standoffs with some of their suppliers, perhaps finding the nearest alternative is something that could be useful to a lot of consumers.

If I walked this path again

2 mins read

Fri 06 Jan 2017

How to succeed in the Data School

2 mins read

Thu 05 Jan 2017

Uphill battles at the Data School

1 mins read

Wed 04 Jan 2017

Using the Include function in Tableau calculations

2 mins read

Tue 13 Dec 2016