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

danicahui
8 - Asteroid
Spoiler
Challenge 197 2020-05-12.jpgChallenge 197 table 2020-05-12.jpg
balajilolla
8 - Asteroid
Spoiler
Solution Attached

balajilolla_0-1589298755663.png

 

jarrod
ACE Emeritus
ACE Emeritus

Spatial Donut works well for this one:

 

Spoiler
jarrod_0-1589315699190.png

and Python:

#################################
from ayx import Alteryx
import pandas as pd
from geopy import distance
import great_circle_calculator.great_circle_calculator as gcc
import math

office = Alteryx.read('Office')
customers = Alteryx.read('Customers')



def dfappend(df1,df2):
    df1['dfappkey'] = 1
    df2['dfappkey'] = 1
    df = pd.merge(df1, df2, on='dfappkey')
    del df['dfappkey']
    return df

customers = dfappend(customers,office)

customers['point1'] = '('+customers['Lat'].map(str)+', '+customers['Lon'].map(str)+')'
customers['point2'] = '('+customers['Latitude'].map(str)+', '+customers['Longitude'].map(str)+')'

def calc_dist(x):
    point1 = (x.Lat, x.Lon)
    point2 = (x.Latitude, x.Longitude)
    return distance.distance(point1, point2).miles

customers['milesFrom'] = customers.apply(calc_dist, axis=1)    
customers_0to5 = customers.query('milesFrom < 5')
customers_0to5 = customers_0to5.groupby('Customer Segment').agg({'Lon': 'count'})
customers_0to5 = customers_0to5.rename(columns={'Lon':'Number of Customers'}).reset_index()
customers_0to5['Customer Density'] = customers_0to5['Number of Customers']/(math.pi*5**2)

customers_5to8 = customers.query('milesFrom >= 5 and milesFrom < 8')
customers_5to8 = customers_5to8.groupby('Customer Segment').agg({'Lon': 'count'})
customers_5to8 = customers_5to8.rename(columns={'Lon':'Number of Customers'}).reset_index()
customers_5to8['Customer Density'] = customers_5to8['Number of Customers']/((math.pi*8**2)-(math.pi*5**2))

Alteryx.write(customers_0to5,1)
Alteryx.write(customers_5to8,2)



#################################

#SnakingMyWayThruChallenges

 

bsanders2009
8 - Asteroid

Sweet, spatial analytics and report tools--two things I haven't needed to use at all so far, so glad to learn more.

 

Spoiler
bsanders2009_0-1589400148529.png

 

Spoiler
bsanders2009_1-1589400183136.png

 

Spoiler
bsanders2009_2-1589400208240.png

 

deviseetharaman
11 - Bolide
Spoiler
 
Mengdee
8 - Asteroid

good times!! that's was fun.. if anyone actually looks sorry for the messy workflow!

wwatson
12 - Quasar

my solution

paulfound
11 - Bolide
Spoiler
PaulFound_0-1595491113703.png

 

If you could just go ahead and like, that'll be great!!

Sntrada
11 - Bolide

My solution is attached!

 

I feel like I have way too many tools. 

 

Spoiler
End product 

Sntrada_0-1596993294757.png

 

Sashikumar
8 - Asteroid

I like it.

Spoiler
ch197.PNGch197_1.PNGch197_2.PNG