Anybody has experience converting 12-digit cbg code to 5-digit county level FIPS code? I thought the first 5 digits of the cbg code is the county level FIPS code, but turns out I got 3500+ unique first 5 digits of the cbg code, while there are only 3100+ counties.
The first 5 digits is correct (with leading zeros as necessary). Do you have Canadian locations included by any chance?
No. The Canadian locations begin with a ‘CA’. Here are these excessive 5 digits code I got from the CBG code.
[10010, 10030, 10059, 10070, 10090, 10119, 10139, 10150, 10159, 10179, 10199, 10210, 10239, 10259, 10279, 10299, 10310, 10330, 10359, 10379, 10399, 10419, 10439, 10450, 10479, 10499, 10510, 10539, 10550, 10570, 10599, 10610, 10630, 10650, 10670, 10690, 10719, 10730, 10750, 10770, 10799, 10810, 10830, 10857, 10872, 10890, 10919, 10939, 10950, 10970, 10990, 11010, 11030, 11056, 11070, 11091, 11110, 11130, 11150, 11170, 11179, 11190, 11210, 11239, 11250, 11270, 11290, 11310, 11339, 20130, 20160, 20200, 20500, 20600, 20680, 20700, 20900, 21000, 21050, 21100, 21220, 21300, 21500, 21580, 21640, 21700, 21800, 21850, 21880, 21950, 21980, 21989, 22200, 22300, 22400, 22610, 22750, 22820, 22900, 40030, 40050, 40070, 40120, 40130, 40132, 40134, 40136, 40138, 40159, 40179, 40190, 40199, 40210, 40219, 40239, 40250, 40270, 40279, 50014, 50039, 50059, 50070, 50097, 50119, 50134, 50159, 50170, 50199, 50219, 50234, 50259, 50279, 50299, 50310, 50330, 50350, 50379, 50399, 50419, 50434, 50450, 50479, 50495, 50510, 50534, 50554, 50574, 50590, 50619, 50634, 50659, 50674, 50690, 50719, 50734, 50754, 50774, 50799, 50810, 50839, 50850, 50879, 50899, 50910, 50919, 50930, 50959, 50979, 50990, 51039, 51074, 51114, 51174, 51190, 51219, 51239, 51250, 51279, 51299, 51310, 51330, 51354, 51379, 51399, 51414, 51430, 51450, 51474, 51499, 60010, 60014, 60019, 60030, 60050, 60070, 60090, 60110, 60133, 60150, 60170, 60190, 60210, 60230, 60239, 60250, 60259, 60270, 60290, 60310, 60319, 60330, 60350, 60371, 60372, 60373, 60374, 60375, 60376, 60377, 60378, 60379, 60390, 60411, 60430, 60450, 60470, 60490, 60510, 60530, 60539, 60552, 60570, 60590, 60591, 60599, 60610, 60630, 60650, 60659, 60670, 60679, 60690, 60710, 60719, 60730, 60739, 60750, 60759, 60770, 60790, 60816, 60819, 60830, 60839, 60855, 60871, 60890, 60910, 60930, 60952, 60959, 60971, 60990, 61010, 61030, 61050, 61070, 61090, 61099, 61110, 61119, 61130, 61150, 66010, 72001, 72003, 72005, 72007, 72009, 72011, 72013, 72015, 72017, 72019, 72021, 72023, 72025, 72027, 72029, 72031, 72033, 72035, 72037, 72039, 72041, 72043, 72045, 72047, 72049, 72051, 72053, 72054, 72055, 72057, 72059, 72061, 72063, 72065, 72067, 72069, 72071, 72073, 72075, 72077, 72079, 72081, 72083, 72085, 72087, 72089, 72091, 72093, 72095, 72097, 72099, 72101, 72103, 72105, 72107, 72109, 72111, 72113, 72115, 72117, 72119, 72121, 72123, 72125, 72127, 72129, 72131, 72133, 72135, 72137, 72139, 72141, 72143, 72145, 72147, 72149, 72151, 72153, 78010, 80010, 80019, 80039, 80050, 80079, 80099, 80119, 80130, 80140, 80150, 80179, 80190, 80219, 80239, 80259, 80279, 80299, 80310, 80319, 80330, 80350, 80370, 80399, 80410, 80439, 80459, 80470, 80490, 80519, 80539, 80559, 80579, 80590, 80599, 80619, 80639, 80659, 80679, 80690, 80710, 80739, 80759, 80770, 80799, 80810, 80839, 80859, 80870, 80899, 80919, 80930, 80959, 80970, 80990, 81010, 81039, 81059, 81070, 81099, 81119, 81139, 81159, 81170, 81190, 81219, 81230, 81259, 90010, 90011, 90012, 90033, 90034, 90035, 90039, 90052, 90053, 90054, 90075, 90076, 90091, 90093, 90116, 90117, 90118, 90119, 90135, 90138, 90158, 90159]
And you’re sure the leading zero has been read in? Looking at the extras you have starting with 90, if instead those started with 090, and then the last digit was dropped, they match the 8 counties of CT (09001, 09003, 09005, 09007, 09009, 09011, 09013, 09015)
Thank you! That makes a lot of sense! I directly read the first 5 digits of the cbg cod in the patterns dataset. So in order to count the leading zero, I need to first check the cbg code is 1 digits short or not. If so, I need to prepend a ‘0’ in the beginning. Is my understanding correct?
My solution is usually to ensure that the cbg are read in as character instead of numeric which preserves the leading zero (I’m working in R, assume this is true regardless of program). But if you already have numeric, your solution should work.
Problem solved! Thank you for the advice, super helpful!
I divide the CBG ID by 10000000 then truncate to get the county. (Works if you don’t mind losing the leading “0” of the county FIPS)