Weekly Challenge

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

We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!

Submit Feedback
We've recently made an accessibility improvement to the community and therefore posts without any content are no longer allowed. Please use the spoiler feature or add a short message in the message body in order to submit your weekly challenge.

Challenge #197: Customer Density Reporting

Highlighted
8 - Asteroid
Spoiler
197_Amin.PNG

I'm intrigued to catch this movie...

Highlighted
16 - Nebula
16 - Nebula

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 



 

 

Highlighted
Alteryx Certified Partner

Challenge 197 is done!

 

Spoiler
challenge 197 JMS solution.PNGReport Output 0-5.PNGReport Output 5-8.PNG
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
Highlighted
8 - Asteroid

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

Highlighted
Alteryx Partner

Challenge #197 solved 

Highlighted
Alteryx Partner

finished, interesting challenge

Highlighted
12 - Quasar

On a spree to binge complete weekly challenges
Adrenaline rush

 

Spoiler
This is my solution

atcodedog05_0-1585988823449.png

 

Highlighted
8 - Asteroid

Here's my solution.

 

Spoiler
Spoiler
patel_bm_0-1587109556929.png

 

Highlighted
Alteryx Partner

Here you go:

 

Spoiler
Capture.PNG