Challenge #15: Warehouse Shipped Miles

[CailinS]

Like a little puzzle - fun!

[mulvihi11]

A nice challenge to get familiar with spatial tools!

Spoiler

[VegasBeans]

Completed!

Thrown off by the distance goal of each item, but multiplying distance by Assigned and then taking the Sum worked.

[sumanthskumar]

This was fun 🙂

[jarrod]

not too bad in python when you find geopy.distance. 

Spoiler

and the python code:

from ayx import Alteryx
import pandas as pd
from geopy.distance import geodesic
import numpy as np

store = Alteryx.read('#1')
item = Alteryx.read('#2')
warehouse = Alteryx.read('#3')

# found helpful tips from following site:
# https://gis.stackexchange.com/questions/273304/how-to-measure-distance-between-2-gps-points-in-pandas

# append all warehouses to stores to find closest
storeWarehouse = pd.merge(store.assign(key=0), warehouse.assign(key=0), on='key').drop('key', axis=1)

# calculate distance between stores and warehouses
storeWarehouse['MILES'] = storeWarehouse.apply(
    (lambda row: geodesic(
        (row['Lat_x'], row['Lon_x']),
        (row['Lat_y'], row['Lon_y'])
    ).miles),
    axis=1
)

# find closest warehouse
closest = storeWarehouse.loc[storeWarehouse.groupby(["Store"])["MILES"].idxmin()].drop({'Lat_x','Lon_x','Lat_y','Lon_y','City_x','City_y','State_x','State_y','Priority','Warehouse'}, axis=1)

# join list to items
items = pd.merge(closest, item, on='Store')

# calculate total distance for each item per store
items['Total Distance'] = items['MILES']*items['Assigned']

# Grouping by item, summarize total distance
summary = items.groupby(by='Item').agg({'Total Distance':'sum'}).reset_index()

Alteryx.write(summary,1)

#SnakingMyWayThruChallenges

[loganmjung]

I wasn't aware of the find nearest tool until I saw someone's spoiler so I came up with this convolution flow involving the Distance Tool. 

[Emil_Kos]

Hi,

Sharing my solution.

I don't have much experience with Spatial tools. Thanks to that this exercise was especialy interesting for me.

Spoiler

[DavidLane]

Nice straightforward one this time 🙂

[DMContente]

My solution

[edavilac]

My solution attached, very cool challenge!

Spoiler