community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Calculate Proportional Data into Grid

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.

Alteryx Certified Partner

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.

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!

Highlighted
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