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!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Calculate Proportional Data into Grid

huntermcg
5 - Atom

Hello!

 

I am trying to append zip code labor data to a 1 mile grid. I'd like to weight the data so some grids receive a higher value according to the population. 

Right now I am finding the percentage of the population the grid has relative to the zip code and using that percentage and multiplying it by my labor values. 

 

One problem is when a grid overlaps and touches two different zip codes. I am very new to Alteryx so my attached workflow may be the wrong way to go about this. 

The first "section" of tools is really just to normalize my data (which was really just a test for now and not needed). It is the 2nd and 3rd section that is tricky.

For reference I am putting the grid as a shapefile into Tableau to be visualized.

 

Thanks everyone for your help on this. Any advice would be very appreciated.

3 REPLIES 3
CharlieS
17 - Castor
17 - Castor

It sounds like you're on the right track. I don't have time to build out an example, but here are a few things to think about:

 

- One way to ensure a single ZIP match for each grid would be to use a Spatial Info tool to assign a centroid to each grid. Then a Spatial Match could be used to match those grid centroids to ZIP polygons. 

 

- An alternate matching method would be to find the ZIP code that captures the greatest percent of land area of each grid and assign that grid to that ZIP code. Use the Spatial Match tool to output intersection objects and measure land area of each using the Spatial Info tool. 

 

- Many data providers have different polygons for ZIP codes. It appears that your data specifically identifies "ZCTA" or the ZIP Code Tabulation Areas produced by the Census Bureau. I would suggest using the Census Bureau's TIGER line polygons found at the link below for spatial applications in case there are any discrepancies between their polygons and those provided by Experian. 

https://www.census.gov/geo/maps-data/data/tiger-line.html

 

- Some ZCTAs in your data specify "TX Portion" meaning these polygons cross the state line. Consider using a polygon of the state of Texas to cut (Spatial Process tool) the polygons you use to only the Texas portion so your map only falls within Texas state lines. 

 

- Depending on the Allocate data you have access to, consider using different base to distribute your employees. Rather than the entire population, maybe only the adult population of a working age is relevant here. Also, if you know the year this employee data was measured, use the same data vintage of Allocate data.

huntermcg
5 - Atom

Charlie, 

 

These are some great tips. I'm thinking your first two suggestions will fix my matching problem so I don't have weird joins when a grid overlaps multiple zip codes.

 

That is also a very helpful suggestion on the different kinds of zip codes I am using. I have some mismatched zip codes and I'm hoping that will help. 

 

All great ideas - I will test these out as soon as I can. Thank you!

EricM
Alteryx
Alteryx

Hi @huntermcg

 

Another way you could do this would be to use census block data for population weighting.  The US Census Bureau publishes population and household counts at the Census Block level, in total there are about 11 million blocks across the US. 

 

All you would need to do is generate the centroid for each census block, then intersect the block centroids, ZCTAs, and the grid. Find the total population of the ZCTA (looks like you already have that), as well as the proportion of the ZCTA population that resides in each grid cell (using the block centroids), then multiply your ZCTA labor data against the proportion of the ZCTA population in each grid cell. That should give you a fairly accurate way to weight data from ZCTAs to your grid.  

 

This is exactly how the Allocate engine works in Alteryx, and you can build out the same functionality easily in a workflow.  

 

Labels