converting to census countyfip from the existing variable of US city and state names

Tariq Abdullah

Join Date: Apr 2021
Posts: 366

converting to census countyfip from the existing variable of US city and state names

24 Oct 2022, 16:01

Hello everyone,

I've the following data which is referenced by it's id variable. For each ID, it has city and statefip name.

Is there any easier way to convert it to USA countyfip code like census county code from the existing information of my data. Some of the data has information about international city which doesn't provide any statefip information.

Code:

* Example generated by -dataex-. For more info, type help dataex
clear
input str14 id strL city str3 statefip
"04840815" "POMONA"          "CA"
"05434252" "GLENVIEW"        "IL"
"05434252" "EVANSTON"        "IL"
"05603052" "NEW YORK"        "NY"
"05823902" "BUCYRUS"         "OH"
"05823902" "BUCYRUS"         "OH"
"06332032" "ITHACA"          "NY"
"06332032" "ITHACA"          "NY"
"06490502" "CROYDON"         "NH"
"06490502" "STRATFORD"       "CT"
"08211955" ""                ""  
"08379649" ""                ""  
"07965267" ""                ""  
"08392957" ""                ""  
"08564130" ""                ""  
"08464759" ""                ""  
"08433467" ""                ""  
"07098353" "WUPPERTAL"       ""  
"07098353" "KOELN"           ""  
"08522419" ""                ""  
"09092710" "CATANIA"         ""  
"09092710" "PALERMO"         ""
"08762555" "SINGAPORE"       ""  
"08762555" "SINGAPORE"       ""  
end

Last edited by Tariq Abdullah; 24 Oct 2022, 16:04.

Tags: None

Scott Merryman

Join Date: Mar 2014

Posts: 895
#2

24 Oct 2022, 17:34

This US cities data base also has US counties and FIPS codes as well
https://www.kaggle.com/datasets/serg...ities-database
2 likes
Comment

William Lisowski

Join Date: Dec 2014
Posts: 10150

24 Oct 2022, 18:06

Unfortunately, US city names are not necessariiy distinct within US states, only within US counties. Utilizing the data linked to in post #2

Code:

import delimited "~/Downloads/uscities.csv"
duplicates report city state_id
duplicates tag city state_id, generate(extras)
keep if extras>0
isid city state_id county_name
sort state_id city county_name
list city state_id county_name, sepby(city state_id) noobs

Code:

. import delimited "~/Downloads/uscities.csv"
(encoding automatically selected: ISO-8859-1)
(17 vars, 30,409 obs)

. duplicates report city state_id

Duplicates in terms of city state_id

--------------------------------------
   Copies | Observations       Surplus
----------+---------------------------
        1 |        30299             0
        2 |           92            46
        3 |           18            12
--------------------------------------

. duplicates tag city state_id, generate(extras)

Duplicates in terms of city state_id

. keep if extras>0
(30,299 observations deleted)

. isid city state_id county_name

. sort state_id city county_name

. list city state_id county_name, sepby(city state_id) noobs

  +---------------------------------------------+
  |            city   state_id      county_name |
  |---------------------------------------------|
  |       Oak Grove         AR          Carroll |
  |       Oak Grove         AR             Pope |
  |---------------------------------------------|
  |     Alta Sierra         CA             Kern |
  |     Alta Sierra         CA           Nevada |
  |---------------------------------------------|
  |     Centerville         CA           Fresno |
  |     Centerville         CA           Shasta |
  |---------------------------------------------|
  |      Cottonwood         CA             Kern |
  |      Cottonwood         CA           Shasta |
  |---------------------------------------------|
  |         Fairfax         CA             Kern |
  |         Fairfax         CA            Marin |
  |---------------------------------------------|
  |        Lakeside         CA             Kern |
  |        Lakeside         CA        San Diego |
  |---------------------------------------------|
  |        Lockwood         CA           Amador |
  |        Lockwood         CA         Monterey |
  |---------------------------------------------|
  |      Twin Lakes         CA             Mono |
  |      Twin Lakes         CA       Santa Cruz |
  |---------------------------------------------|
  |       Long Hill         CT        Fairfield |
  |       Long Hill         CT       New London |
  |---------------------------------------------|
  |          Midway         FL          Gadsden |
  |          Midway         FL       Santa Rosa |
  |          Midway         FL         Seminole |
  |---------------------------------------------|
  |        Buckhead         GA            Bryan |
  |        Buckhead         GA           Morgan |
  |---------------------------------------------|
  |        Kingston         IL            Adams |
  |        Kingston         IL           DeKalb |
  |---------------------------------------------|
  |        Lakewood         IL          McHenry |
  |        Lakewood         IL           Shelby |
  |---------------------------------------------|
  |     Silver Lake         NJ       Cumberland |
  |     Silver Lake         NJ            Essex |
  |---------------------------------------------|
  |    Arroyo Hondo         NM         Santa Fe |
  |    Arroyo Hondo         NM             Taos |
  |---------------------------------------------|
  |        La Cueva         NM         Sandoval |
  |        La Cueva         NM         Santa Fe |
  |---------------------------------------------|
  |       La Madera         NM       Rio Arriba |
  |       La Madera         NM         Sandoval |
  |---------------------------------------------|
  |   Mountain View         NM           Cibola |
  |   Mountain View         NM             Luna |
  |---------------------------------------------|
  |        Valencia         NM         Santa Fe |
  |        Valencia         NM         Valencia |
  |---------------------------------------------|
  |      White Lake         NY           Oneida |
  |      White Lake         NY         Sullivan |
  |---------------------------------------------|
  |        Woodbury         NY           Nassau |
  |        Woodbury         NY           Orange |
  |---------------------------------------------|
  |      Harrisburg         OH         Franklin |
  |      Harrisburg         OH            Stark |
  |---------------------------------------------|
  | Lincoln Heights         OH         Hamilton |
  | Lincoln Heights         OH         Richland |
  |---------------------------------------------|
  |         Oakwood         OH         Cuyahoga |
  |         Oakwood         OH       Montgomery |
  |         Oakwood         OH         Paulding |
  |---------------------------------------------|
  |   Salem Heights         OH       Columbiana |
  |   Salem Heights         OH         Hamilton |
  |---------------------------------------------|
  |          Shiloh         OH       Montgomery |
  |          Shiloh         OH         Richland |
  |---------------------------------------------|
  |           Caney         OK            Atoka |
  |           Caney         OK         Cherokee |
  |---------------------------------------------|
  |        Fairview         OR        Multnomah |
  |        Fairview         OR        Tillamook |
  |---------------------------------------------|
  |      Beavertown         PA            Blair |
  |      Beavertown         PA           Snyder |
  |---------------------------------------------|
  |        Franklin         PA          Cambria |
  |        Franklin         PA          Venango |
  |---------------------------------------------|
  |  Fredericksburg         PA            Blair |
  |  Fredericksburg         PA          Lebanon |
  |---------------------------------------------|
  |      Georgetown         PA           Beaver |
  |      Georgetown         PA        Lancaster |
  |      Georgetown         PA          Luzerne |
  |---------------------------------------------|
  |       Jefferson         PA           Greene |
  |       Jefferson         PA             York |
  |---------------------------------------------|
  |         Liberty         PA        Allegheny |
  |         Liberty         PA            Tioga |
  |---------------------------------------------|
  |     Marshallton         PA          Chester |
  |     Marshallton         PA   Northumberland |
  |---------------------------------------------|
  |      Middletown         PA          Dauphin |
  |      Middletown         PA      Northampton |
  |---------------------------------------------|
  |     Millerstown         PA            Blair |
  |     Millerstown         PA            Perry |
  |---------------------------------------------|
  |         Newburg         PA            Blair |
  |         Newburg         PA       Cumberland |
  |---------------------------------------------|
  |         Newtown         PA            Bucks |
  |         Newtown         PA       Schuylkill |
  |---------------------------------------------|
  |         Oakland         PA          Cambria |
  |         Oakland         PA         Lawrence |
  |         Oakland         PA      Susquehanna |
  |---------------------------------------------|
  |        Red Hill         PA            Blair |
  |        Red Hill         PA       Montgomery |
  |---------------------------------------------|
  |      Unionville         PA           Butler |
  |      Unionville         PA          Chester |
  |---------------------------------------------|
  |        Westwood         PA          Cambria |
  |        Westwood         PA          Chester |
  |---------------------------------------------|
  |       Palmarejo         PR          Corozal |
  |       Palmarejo         PR            Lajas |
  |---------------------------------------------|
  |         RincÃ³n         PR            Cidra |
  |         RincÃ³n         PR          RincÃ³n |
  |---------------------------------------------|
  |     San Antonio         PR        Aguadilla |
  |     San Antonio         PR           Dorado |
  |     San Antonio         PR     Quebradillas |
  |---------------------------------------------|
  |      Pine Ridge         SC       Darlington |
  |      Pine Ridge         SC        Lexington |
  |---------------------------------------------|
  |      Clear Lake         SD            Deuel |
  |      Clear Lake         SD         Marshall |
  |---------------------------------------------|
  |           Halls         TN             Knox |
  |           Halls         TN       Lauderdale |
  |---------------------------------------------|
  |     Chula Vista         TX          Cameron |
  |     Chula Vista         TX         Maverick |
  |     Chula Vista         TX           Zavala |
  |---------------------------------------------|
  |          Palmer         TX          Cameron |
  |          Palmer         TX            Ellis |
  |---------------------------------------------|
  |        Bull Run         VA          Fairfax |
  |        Bull Run         VA   Prince William |
  +---------------------------------------------+

.

Comment

Tariq Abdullah

Join Date: Apr 2021

Posts: 366
#4

24 Oct 2022, 19:43

This was a huge help for the next step of my research. Thanks a ton, Mr. Merryman for allowing me to have this wonderful dataset - which is going to be very handy given the type of research I do.

Thanks Mr. Lisowski again for always guiding me with the right set of instruction for any concern I have regarding my code.

Highly obliged to both of you for your generosity and time!
Comment

Announcement

converting to census countyfip from the existing variable of US city and state names

Comment

Comment

Comment