Want to get involved? We're always looking for ideas and content for Weekly Challenges.
SUBMIT YOUR IDEAA nice challenge to get familiar with spatial tools!
not too bad in python when you find geopy.distance.
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
Hi,
Sharing my solution.
I don't have much experience with Spatial tools. Thanks to that this exercise was especialy interesting for me.