How can I convert FIPS Code to GISJOIN for census tracts

I have two datasets from two different agencies that report census tracts in two different ways, namely FIPSCode and GISJOIN. I have to frequently interchange these two and hence looking for a way to see if anyone knows how to effectively do it as my dataset includes some 70,000 census tracts and doing it manually is out of question.

For example, Fipscode 1073011803 = GISJOIN G0100730011803. The logic is simple where

1 = 01 (state code)
073 = 0073 (county code)
011803 = 0011803 (census tract number)

It seems that padding with 0 for each of the three elements in a fipscode gives the GISJOIN, however, I am unsure how to convert it. I am using excel but can work with R if there is a way.

Thank you for your time!

Solution

After giving it a few tries, I have found a solution to this.

We need to know about the GISJOIN a bit more! It is a geoidentifier unique to US census geographies and NHGIS provided a standard structure using a combination of 13 digit alphanumeric IDs. For demonstration purposes, I selected five random census tracts from the HUD data with the fips2010 and converted them into the prescribed GISJOIN style.

data <- data.frame(State = c("Alabama", "Alabama", "Delaware", 
                             "Texas", "Wisconsin"),
                   County = c("Jefferson County", "Montgomary County", "Kent County", 
                              "Travis County", "Milwaukee County"),
                   Tract = c("118.03", "1.00", "433.00", "13.07", "86.00"),
                   fips2010 = c("1073011803", "1101000100", "10001043300",
                                "48453001307", "55079008600"))

print(data)
##Output
     State            County  Tract    fips2010
1   Alabama  Jefferson County 118.03  1073011803
2   Alabama Montgomary County   1.00  1101000100
3  Delaware       Kent County 433.00 10001043300
4     Texas     Travis County  13.07 48453001307
5 Wisconsin  Milwaukee County  86.00 55079008600

Following the logic established in the NHGIS documentation, the code below converts the fips2010 column to appropriate GISJOIN standard.

for (i in 1:nrow(data)) {
  fips2010 <- data$fips2010[i]
  if (nchar(fips2010) == 10) {
    data$fips2010[i] <- paste0("G0", substr(fips2010, 1, 1), "0", substr(fips2010, 2, 4), "0", substr(fips2010, 5, 10))
  } else if (nchar(fips2010) == 11) {
    data$fips2010[i] <- paste0("G", substr(fips2010, 1, 2), "0", substr(fips2010, 3, 5), "0", substr(fips2010, 6, 11))
  }
}

print(data)
##Output
      State            County  Tract       fips2010
1   Alabama  Jefferson County 118.03 G0100730011803
2   Alabama Montgomary County   1.00 G0101010000100
3  Delaware       Kent County 433.00 G1000010043300
4     Texas     Travis County  13.07 G4804530001307
5 Wisconsin  Milwaukee County  86.00 G5500790008600

A side by side comparison: fips2010 and GISJOIN

      State            County  Tract    fips2010        GISJOIN
1   Alabama  Jefferson County 118.03  1073011803 G0100730011803
2   Alabama Montgomary County   1.00  1101000100 G0101010000100
3  Delaware       Kent County 433.00 10001043300 G1000010043300
4     Texas     Travis County  13.07 48453001307 G4804530001307
5 Wisconsin  Milwaukee County  86.00 55079008600 G5500790008600

I hope this helps anyone dealing with a similar issue.