Leading zeroes on CBG codes

I am looking at some data for Connecticut and noticed that poi CBGs begin with 90 while visitor CBGs begin 09. To my understanding 09 should be the correct state prefix. Am I missing something? Please advise. Thank you.

This topic was automatically generated from Slack. You can find the original thread here.

Hey @lsraabe - just doing a quick scan of CT POIs and not seeing any with poi_cbg starting with “90…” Can you share some example POIs that you’re seeing this with? Will help me dig deeper.

Sure, I just did a pull for CT patterns data for the month of January: if you look at column Q: “sg_mp_poi_cbg” all of the POI codes begin with 901. Attached are the first 10 rows of the export.

To be clear, it only seems to be an issue for POIs and not for home or daytime CBGs.

I found a similar issue when wrangling national pattern data for June 2020. I initially though that something in my R code was generating the error but now think it may be that something is going on with how CT poi CBG data is being output in the reports.

Please let me know if its something I’m messing up on my end. Thanks again.

Hey @lsraabe - thanks for your patience on this. Had to do some investigation into this and ask a few team members from our side. The POIs that you flagged as starting with ‘90…’ were 11-digit CBG codes. Since all values in poi_cbg should be 12 digit, I’m guessing what’s happening is that you’re not reading in the first zero.

To confirm, I fed the POIs you provided through our Match Service and downloaded Patterns for those POIs. When opening with Excel/Sheets, it indeed removes the first zero. When querying from our internal database for those same POIs, the leading zero is there.

TLDR - be sure to include the leading zero on those CBG codes and confirm that poi_cbg is 12 digits long.

:man-facepalming: I cant believe I missed that. I guess it serves me right for troubleshooting in excel rather than R.

Thank you for the help.

No worries! Helpful exercise for both of us :slightly_smiling_face: