Challenge #197: Customer Density Reporting

[Amin]

Spoiler

I'm intrigued to catch this movie...

[SeanAdams]

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

Spoiler

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]

Challenge 197 is done!

Spoiler

[alozano]

Here is my solution.

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

Spoiler

[cc000072]

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

[AngelosPachis]

Challenge #197 solved 

[KevinTang]

finished, interesting challenge

[atcodedog05]

On a spree to binge complete weekly challenges
Adrenaline rush

Spoiler

This is my solution

[patel_bm]

Here's my solution.

Spoiler

Spoiler

[johnemery]

Here you go:

Spoiler