Debugging public data - county codes in Texas

If you sort a list of Texas counties by county name, the counties will not be in order by county number.
Texas Center for Healthcare Statistics

Every county in the United States has a FIPS code. These standards codes are usful for mapping and joining additional datasets.

Texas state agencies do not bother with these FIPS codes. Instead, each state agency publishes data using their own county codes. The county codes of one state agency may not match other agencies’ county codes. I recently spent a few hours extremely confused by this, because I used the formula published by the Texas Department of State Health Services on data published by the Texas Health and Human Services, resulting in incorrect data.

I understand that imperfect solutions often come about because the success criteria is something that works, not something that meets everyone’s needs in every situation.

And since they work, the additional amount of time needed to make them better often isn’t worth it given all the things that don’t work. For many tasks, that is probably fine - if you know the limitations of your solution and adjust accordingly.

But each Texas state agency using its own subtly different coding system for counties is the type of issue that deserves fixing because it can lead to bigger problems. Because they publish these codes to the public, and people disseminate data without guidance from the original authors. And so it surprises me that rather than settle on a standard set of county codes that could be used across all Texas state agencies, they each implement their own guidance.

How do they assign codes?

The majority sort counties alphabetically by county name and use the row index number as the ID. You’ll see this on the Texas Comptroller codes, and in the data file in question: the Medicaid and CHIP Enrollment by Risk Group by County, Preliminary from the Healthcare Statistics page on the Texas Health and Human Services website (HHS).

This data file is released at hhs.texas.gov, under the Texas Health and Human Services website.

However, this convenient solution doesn’t give an easy conversion to FIPs codes.

When I found these county codes and wanted to get match them to the FIPs codes to join Census data, I found a page at the Texas Department of State Health Services (DSHS) website dshs.texas.gov that gives a helpful formula.

DSHS gives a formula to translate county IDs to FIPs codes:

The county FIPS code can be calculated from the Texas county number: FIPS_code = 48000 + (county_number * 2) - 1.

However, the County IDs published in the HHS Medicaid data file do not match the county numbers on the DSHS conversion table. This is because an alphabetic sort index doesn’t match the order of the IDs:

• spaces change the order (Ellis after El Paso)
• prefixes (“Mc..”) are sorted before other “M…” counties

So if I use the DSHS formula on the HHS data, I will get the incorrect FIPs code since the input County ID is not correct. In the published file, Martin county had the county ID of 156, which suggests a FIPS code of 48 + (156 * 2) - 1 = 48311. The FIPS code 48311 actually corresponds with McMullen county.

I made the mistake of thinking that the advice published on the DSHS website applied to the data on the HHS website. From their logos, I thought the DSHS was a sub-office of the HHS.

Since this sorting error only switches a few of the counties, a majority of the data county IDs are correct. As someone not familiar with the data practices of various Texas state agencies, it was only when I normalized enrollment counts against population (from Census data) that I realized some counties had enrollment over 100%.

Intuitively I worked backwards, which cost me a few hours of work - I thought my join must be wrong, or the Census data I pulled, or when I calculated the FIPs codes. I also foolishly checked other state agency data - which only further confused me, because since the other agency did use the alphabetic sort method, the data across both files matched. I also checked the data in the file by county name, which held up, since the county names and counts were always correct!

Correcting this meant pulling the published HHS Healthcare Statistics numbers and joining (based on name) to the DSHS table online to pull the correct ID and FIPs codes.

Overall

Texas state agencies use county IDs that do not align with the federal standard. I am curious as to why this was implemented - it is easy to think that doing the alphabetic sort by name for them was just easier and they didn’t think about the consequences, but perhaps I am being cynical.

Alphabetic Texas County IDs to FIPs Codes

The following table links the Texas County IDs (obtained through the row index from a standard alphabetic sort) to their county FIPs codes.

Thanks to TablesGenerator through https://www.tablesgenerator.com/markdown_tables.