I have a CSV file of Postal Codes containing average energy usage information. Furthermore, I have Feature Classes (both points and polygons) of Postal Code areas in a File Geodatabase. I wish to join these files based on Zip Code. However, due to privacy reasons, some of the Postal Codes are reported as Postal Code ranges in one row in the CSV file. The CSV file is structured as shown in the picture.
Note that Postal Codes AC, AD and AE form a range, as well as Postal Codes AH to AK. So in essence, it is a CSV file of Postal Code ranges.
In order to Join the data from the CSV file to the Postal Code Area geometry, I require a unique field on which to base the join. Simply joining on postal code will not work because the CSV data concern postal code ranges and not actual postal codes. Of course the ranges have a unique ID (index), but I cannot join these to any attribute in the Postal Code area Feature Classes.
The solution is probably to assign the unique ID of the postal code ranges to the postal code Feature Classes, and then dissolve on that unique ID. However, there is no attribute on which to base a join in the first place, so an alternative solution is required.
I have a theoretical solution, but I do not know yet how to implement it. It involves the following steps:
- Export the CSV to a File Geodatabase table in the same GDB that contains the Postal Code area Feature Class.
- Each Postal code area in the Feature Class receives a unique number from 1..n. We call this unique number indexNum.
- Table join is performed on ZipCodeFrom and the joined table is exported to a new table. The new table is again joined on ZipCodeTo and again exported to a new table. This results in two attribute fields that contain the unique numbers from both the ZipCodeFrom and ZipCodeTo fields.
- A new attribute (called indexRange) is made in the range table which combines both unique numbers in a semi-colon separated value, as such: 1;1, 2;2, 3;3, or, in the case of a range: 4;6.
- The next step would involve creating a new, separate table which splits the numerical ranges, but which preserves the range as an index, called indexRange. The picture below illustrates this.
- This new table is joined to the Postal Code areas based on both indexNum and splitRange. This way the rangeID attribute is transferred to the Postal Code areas.
- The Postal Code areas are dissolved on the rangeID field.
- Finally, the dissolved Postal Code areas can be joined to the Postal Code ranges using the rangeID field in the Postal Code feature class and the indexRange field in the Postal Code ranges.
I think I can execute all steps except step 5: how to create a new table which splits the ranges and retains the ranges as an index? I imagine this might be possible using a Python script, but I do not know Python well enough to write it myself.
I work with ArcGIS 10.3.1 for Desktop.