Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Weekly Challenges

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

Challenge #197: Customer Density Reporting

Amin
8 - Asteroid
Spoiler
197_Amin.PNG

I'm intrigued to catch this movie...

SeanAdams
17 - Castor
17 - Castor

Great fun way to use spatial tools, and also to earn my cubicle back after generating TPS Reports.

 

 

 

Spoiler

Solution.png


Did this challenge in 2 ways - one being using spatial tools, and the other being a Python solution.
Full text of the Python Solution is below.

#################################
# List all non-standard packages to be imported by your
# script here (only missing packages will be installed)
from ayx import Package
from ayx import Alteryx

Package.installPackages(['great-circle-calculator','openpyxl', 'pandas', 'numpy'])


#################################
import pandas as pd
import geopandas
import great_circle_calculator.great_circle_calculator as gcc
import math


#################################
#Bring in the data from the inputs - needs error trapping to handle zero row inputs
try:
dfOffice = Alteryx.read('#1')
except:
dfOffice = pd.Dataframe()

try:
dfCustomer = Alteryx.read('#2')
except:
dfCustomer = pd.Dataframe()

ptOffice = (dfOffice.Longitude[0], dfOffice.Latitude[0])


#################################
#work out the distance using the great circle calculator
#add on radious using pi* r squared
for i in range(len(dfCustomer)):
ptCust = (dfCustomer.loc[i,'Lon'],dfCustomer.loc[i,'Lat'])
distInMiles = gcc.distance_between_points(ptOffice, ptCust,unit='miles')


dfCustomer.loc[i, 'distance'] = abs(distInMiles)

if dfCustomer.loc[i, 'distance'] <5:
dfCustomer.loc[i,'RadiusSize'] = '0-5'
dfCustomer.loc[i,'Area'] = math.pi * 5*5
elif dfCustomer.loc[i, 'distance'] <8:
dfCustomer.loc[i,'RadiusSize'] = '5-8'
dfCustomer.loc[i,'Area'] = math.pi * 8*8 - math.pi * 5*5
else:
dfCustomer.loc[i,'RadiusSize'] = '8+'
dfCustomer.loc[i,'Area'] = -1


#################################
#filter out rows where it's outside the 8+ boundary
flt = dfCustomer['RadiusSize']!='8+'
dfCustomer = dfCustomer[flt]


#################################
#Group by the customer segment and radius
newdf = (
dfCustomer.groupby(
['RadiusSize','Customer Segment']).agg(
{'Area':[np.max, np.size]}))
newdf.columns=['area','custCount']
newdf.reset_index(inplace=True)


#################################
#figure out the density
newdf['density'] = newdf.area / newdf.custCount


#################################
#write out the two output files
writer = pd.ExcelWriter(r'D:\OneDrive\Documents\Alteryx data\Weekly Challenges\Week 197 (Intermediate)\Solution-0-5.xlsx')
newdf[newdf['RadiusSize']=='5-8'].to_excel(writer)
writer.save()

writer = pd.ExcelWriter(r'D:\OneDrive\Documents\Alteryx data\Weekly Challenges\Week 197 (Intermediate)\Solution-5-8.xlsx')
newdf[newdf['RadiusSize']=='5-8'].to_excel(writer)
writer.save()





#SnakingMyWayThruChallenges 
cc: @NicoleJohnson 



 

 

Jonathan-Sherman
15 - Aurora
15 - Aurora

Challenge 197 is done!

 

Spoiler
challenge 197 JMS solution.PNGReport Output 0-5.PNGReport Output 5-8.PNG
alozano
8 - Asteroid

Here is my solution.

 

It's the first time I use the geospatial tools so my solution is probably not optimal!

 

Spoiler
challenge_197_alozano.jpg
cc000072
8 - Asteroid

Here is my solution, this is fisrt time to use reporting function. Wonder how I can align PDF contents to center? 

AngelosPachis
16 - Nebula

Challenge #197 solved 

KevinTang
8 - Asteroid

finished, interesting challenge

atcodedog05
22 - Nova
22 - Nova

On a spree to binge complete weekly challenges
Adrenaline rush

 

Spoiler
This is my solution

atcodedog05_0-1585988823449.png

 

patel_bm
8 - Asteroid

Here's my solution.

 

Spoiler
Spoiler
patel_bm_0-1587109556929.png

 

johnemery
11 - Bolide

Here you go:

 

Spoiler
Capture.PNG