Loading Required Libraries

First, we’ll load the necessary R packages.

library(rio)
library(readxl)
library(foreign)

File Path Specification

Define the paths for input Excel files and output DBF files.

# Specify the file paths
xlsx_file1 <- "C:/Projects/2024_09_SR 37/Model/ZSED/2050/ZSED50_MAZ_02132024.dbf.xlsx"
xlsx_file2 <- "C:/Projects/2024_09_SR 37/Model/ZSED/2050/ZSED50_TAZ_02132024.dbf.xlsx"
dbf_file1 <- "C:/Projects/2024_09_SR 37/Model/ZSED/2050/ZSED50_MAZ_10302024.dbf"
dbf_file2 <- "C:/Projects/2024_09_SR 37/Model/ZSED/2050/ZSED50_TAZ_10302024.dbf"

Reading Excel Files

Read the data from both Excel files.

# Read the Excel files
data1 <- read_excel(xlsx_file1, sheet = 1)
data2 <- read_excel(xlsx_file2, sheet = 1)

# Display the structure of the input data
str(data1)
## tibble [19,431 × 38] (S3: tbl_df/tbl/data.frame)
##  $ ID      : num [1:19431] 20001 20002 20003 20004 20005 ...
##  $ Acres   : num [1:19431] 43.5 993.1 148.2 22.6 15.6 ...
##  $ Area    : num [1:19431] 0.068 1.5517 0.2316 0.0353 0.0243 ...
##  $ TAZ     : num [1:19431] 1876 1874 1910 1877 1877 ...
##  $ POP     : num [1:19431] 5 41 39 15 80 662 8 5 135 41 ...
##  $ POP18   : num [1:19431] 2 5 9 1 20 145 1 0 26 7 ...
##  $ POP_GQ  : num [1:19431] 0 0 0 4 3 0 0 0 10 1 ...
##  $ GQ_INS  : num [1:19431] 0 0 0 3 2 0 0 0 8 1 ...
##  $ GQ_UNV  : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
##  $ GQ_OTH  : num [1:19431] 0 0 0 1 1 0 0 0 2 0 ...
##  $ WORKERS : num [1:19431] 2 20 17 4 38 295 7 4 58 23 ...
##  $ TOT_HH  : num [1:19431] 2 14 13 5 35 235 4 2 45 18 ...
##  $ HHINCOME: num [1:19431] 68026 71524 84089 71745 71745 ...
##  $ NAICS11 : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
##  $ NAICS21 : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
##  $ NAICS22 : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
##  $ NAICS23 : num [1:19431] 0 0 0 0 0 0 8 0 0 0 ...
##  $ NAICS31 : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
##  $ NAICS32 : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
##  $ NAICS33 : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
##  $ NAICS42 : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
##  $ NAICS44 : num [1:19431] 0 5 0 2 0 1 1 0 1 0 ...
##  $ NAICS45 : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
##  $ NAICS48 : num [1:19431] 0 2 4 1 0 0 4 0 2 0 ...
##  $ NAICS49 : num [1:19431] 0 0 0 1 0 0 0 0 16 0 ...
##  $ NAICS51 : num [1:19431] 0 0 0 0 0 0 0 0 5 0 ...
##  $ NAICS52 : num [1:19431] 0 0 0 0 0 0 0 11 0 0 ...
##  $ NAICS53 : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
##  $ NAICS54 : num [1:19431] 0 0 0 5 0 0 0 0 2 0 ...
##  $ NAICS55 : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
##  $ NAICS56 : num [1:19431] 0 0 0 0 0 11 0 0 0 0 ...
##  $ NAICS61 : num [1:19431] 0 5 0 3 6 53 1 0 1 0 ...
##  $ NAICS62 : num [1:19431] 0 0 0 0 0 0 17 0 0 0 ...
##  $ NAICS71 : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
##  $ NAICS72 : num [1:19431] 0 1 0 0 1 0 0 10 0 0 ...
##  $ NAICS81 : num [1:19431] 0 0 0 0 0 0 1 0 0 0 ...
##  $ NAICS92 : num [1:19431] 0 46 0 1 1 0 0 0 6 0 ...
##  $ NAICS99 : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
str(data2)
## tibble [2,186 × 60] (S3: tbl_df/tbl/data.frame)
##  $ TAZ       : num [1:2186] 1 2 3 4 5 6 7 8 9 10 ...
##  $ MPO_ID    : num [1:2186] 1 2 3 4 5 6 7 8 9 10 ...
##  $ Acres     : num [1:2186] 69.2 22.8 24.6 11.6 17.5 ...
##  $ Area      : num [1:2186] 0.1081 0.0357 0.0385 0.0181 0.0273 ...
##  $ MPO       : num [1:2186] 1 2 3 4 5 6 7 8 9 10 ...
##  $ county_ch : chr [1:2186] "FRA" "FRA" "FRA" "FRA" ...
##  $ PUMA_2010 : num [1:2186] 4105 4105 4105 4105 4105 ...
##  $ DISTRICT  : num [1:2186] 0 0 0 0 0 0 0 0 0 0 ...
##  $ POP       : num [1:2186] 606 266 883 41 1173 ...
##  $ POP18     : num [1:2186] 70 0 61 0 150 280 244 136 419 0 ...
##  $ POP_GQ    : num [1:2186] 0 0 0 0 0 0 179 0 66 0 ...
##  $ GQ_INS    : num [1:2186] 0 0 0 0 0 0 92 0 0 0 ...
##  $ GQ_UNV    : num [1:2186] 0 0 0 0 0 0 0 0 0 0 ...
##  $ GQ_OTH    : num [1:2186] 0 0 0 0 0 0 87 0 67 0 ...
##  $ WORKERS   : num [1:2186] 377 202 585 28 616 ...
##  $ HHINCOME  : num [1:2186] 12616 34506 49643 69466 62527 ...
##  $ TOT_HH    : num [1:2186] 290 189 517 31 458 588 896 796 740 0 ...
##  $ PARK_H    : num [1:2186] 300 396 200 188 393 ...
##  $ PARK_D    : num [1:2186] 500 1190 1000 1684 1162 ...
##  $ PARK_M    : num [1:2186] 7500 17856 15000 9452 6062 ...
##  $ parktot   : num [1:2186] 495 1162 1083 2074 3015 ...
##  $ parklong  : num [1:2186] 395 246 575 1799 1214 ...
##  $ parkfree  : num [1:2186] 0.798 0.212 0.538 0.875 0.407 ...
##  $ K8ENRPUB  : num [1:2186] 0 0 0 0 0 0 0 0 0 0 ...
##  $ HSENRPUB  : num [1:2186] 0 0 0 0 0 0 0 0 0 0 ...
##  $ ENRUNV    : num [1:2186] 0 0 0 0 0 0 0 0 0 0 ...
##  $ SCHOOL_D  : chr [1:2186] "COLUMBUS" "COLUMBUS" "COLUMBUS" "COLUMBUS" ...
##  $ NAICS11   : num [1:2186] 2 0 0 0 0 0 0 0 2 0 ...
##  $ NAICS21   : num [1:2186] 1 0 0 0 0 0 0 0 0 0 ...
##  $ NAICS22   : num [1:2186] 0 0 1206 0 0 ...
##  $ NAICS23   : num [1:2186] 0 0 0 0 0 0 0 107 3 0 ...
##  $ NAICS31   : num [1:2186] 0 0 0 0 0 1 0 2 1 0 ...
##  $ NAICS32   : num [1:2186] 0 0 0 0 0 0 0 9 6 0 ...
##  $ NAICS33   : num [1:2186] 1 0 0 27 0 1 0 15 0 0 ...
##  $ NAICS42   : num [1:2186] 0 0 0 5 0 12 0 15 46 0 ...
##  $ NAICS44   : num [1:2186] 6 0 109 0 33 61 26 9 57 0 ...
##  $ NAICS45   : num [1:2186] 1 0 2 1 5 8 385 0 21 0 ...
##  $ NAICS48   : num [1:2186] 3 0 0 0 0 79 24 3 3 3 ...
##  $ NAICS49   : num [1:2186] 15 1 0 0 6 0 0 65 0 0 ...
##  $ NAICS51   : num [1:2186] 0 35 7 26 0 28 9 0 27 0 ...
##  $ NAICS52   : num [1:2186] 146 236 919 931 18 29 0 46 228 0 ...
##  $ NAICS53   : num [1:2186] 1 31 119 2 21 23 242 0 91 0 ...
##  $ NAICS54   : num [1:2186] 192 341 369 20 105 278 0 0 84 0 ...
##  $ NAICS55   : num [1:2186] 10 10 97 0 17 13 0 0 10 0 ...
##  $ NAICS56   : num [1:2186] 9 30 9 456 6 62 7 18 20 0 ...
##  $ NAICS61   : num [1:2186] 32 4 7 0 35 11 0 15 181 184 ...
##  $ NAICS62   : num [1:2186] 20 396 22 0 28 23 0 69 240 0 ...
##  $ NAICS71   : num [1:2186] 0 4 204 1 3 83 0 0 5 0 ...
##  $ NAICS72   : num [1:2186] 0 27 87 230 187 173 503 0 86 0 ...
##  $ NAICS81   : num [1:2186] 7 79 6 26 187 63 0 35 30 0 ...
##  $ NAICS92   : num [1:2186] 0 27 0 0 1613 ...
##  $ NAICS99   : num [1:2186] 0 0 0 0 0 0 0 0 0 0 ...
##  $ WAGE_AVG  : num [1:2186] 69721 62741 70880 59569 59004 ...
##  $ AIRPASS   : num [1:2186] 0 0 0 0 0 0 0 0 0 0 ...
##  $ COUNTY    : num [1:2186] 3 3 3 3 3 3 3 3 3 3 ...
##  $ MAJUNIVENR: num [1:2186] 0 0 0 0 0 0 0 0 0 0 ...
##  $ OTHUNIVENR: num [1:2186] 0 0 0 0 0 0 0 0 0 0 ...
##  $ TERM_TIME : num [1:2186] 0 0 0 0 0 0 0 0 0 0 ...
##  $ REGION    : num [1:2186] 1 1 1 1 1 1 1 1 1 1 ...
##  $ PUMA_2000 : num [1:2186] 3105 3105 3105 3105 3105 ...

Data Processing

Process both datasets to ensure compatibility with DBF format.

# Process data1
data1[] <- lapply(data1, function(x) {
  if(is.numeric(x)) {
    return(as.numeric(format(round(x, 6), scientific = FALSE)))
  } else {
    return(substr(as.character(x), 1, 254))
  }
})

# Process data2
data2[] <- lapply(data2, function(x) {
  if(is.numeric(x)) {
    return(as.numeric(format(round(x, 6), scientific = FALSE)))
  } else {
    return(substr(as.character(x), 1, 254))
  }
})

# Ensure column names meet DBF requirements for both datasets
names(data1) <- substr(names(data1), 1, 10)
names(data2) <- substr(names(data2), 1, 10)

# Display the processed data structure
str(data1)
## tibble [19,431 × 38] (S3: tbl_df/tbl/data.frame)
##  $ ID      : num [1:19431] 20001 20002 20003 20004 20005 ...
##  $ Acres   : num [1:19431] 43.5 993.1 148.2 22.6 15.6 ...
##  $ Area    : num [1:19431] 0.068 1.5517 0.2316 0.0353 0.0243 ...
##  $ TAZ     : num [1:19431] 1876 1874 1910 1877 1877 ...
##  $ POP     : num [1:19431] 5 41 39 15 80 662 8 5 135 41 ...
##  $ POP18   : num [1:19431] 2 5 9 1 20 145 1 0 26 7 ...
##  $ POP_GQ  : num [1:19431] 0 0 0 4 3 0 0 0 10 1 ...
##  $ GQ_INS  : num [1:19431] 0 0 0 3 2 0 0 0 8 1 ...
##  $ GQ_UNV  : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
##  $ GQ_OTH  : num [1:19431] 0 0 0 1 1 0 0 0 2 0 ...
##  $ WORKERS : num [1:19431] 2 20 17 4 38 295 7 4 58 23 ...
##  $ TOT_HH  : num [1:19431] 2 14 13 5 35 235 4 2 45 18 ...
##  $ HHINCOME: num [1:19431] 68026 71524 84089 71745 71745 ...
##  $ NAICS11 : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
##  $ NAICS21 : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
##  $ NAICS22 : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
##  $ NAICS23 : num [1:19431] 0 0 0 0 0 0 8 0 0 0 ...
##  $ NAICS31 : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
##  $ NAICS32 : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
##  $ NAICS33 : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
##  $ NAICS42 : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
##  $ NAICS44 : num [1:19431] 0 5 0 2 0 1 1 0 1 0 ...
##  $ NAICS45 : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
##  $ NAICS48 : num [1:19431] 0 2 4 1 0 0 4 0 2 0 ...
##  $ NAICS49 : num [1:19431] 0 0 0 1 0 0 0 0 16 0 ...
##  $ NAICS51 : num [1:19431] 0 0 0 0 0 0 0 0 5 0 ...
##  $ NAICS52 : num [1:19431] 0 0 0 0 0 0 0 11 0 0 ...
##  $ NAICS53 : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
##  $ NAICS54 : num [1:19431] 0 0 0 5 0 0 0 0 2 0 ...
##  $ NAICS55 : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
##  $ NAICS56 : num [1:19431] 0 0 0 0 0 11 0 0 0 0 ...
##  $ NAICS61 : num [1:19431] 0 5 0 3 6 53 1 0 1 0 ...
##  $ NAICS62 : num [1:19431] 0 0 0 0 0 0 17 0 0 0 ...
##  $ NAICS71 : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
##  $ NAICS72 : num [1:19431] 0 1 0 0 1 0 0 10 0 0 ...
##  $ NAICS81 : num [1:19431] 0 0 0 0 0 0 1 0 0 0 ...
##  $ NAICS92 : num [1:19431] 0 46 0 1 1 0 0 0 6 0 ...
##  $ NAICS99 : num [1:19431] 0 0 0 0 0 0 0 0 0 0 ...
str(data2)
## tibble [2,186 × 60] (S3: tbl_df/tbl/data.frame)
##  $ TAZ       : num [1:2186] 1 2 3 4 5 6 7 8 9 10 ...
##  $ MPO_ID    : num [1:2186] 1 2 3 4 5 6 7 8 9 10 ...
##  $ Acres     : num [1:2186] 69.2 22.8 24.6 11.6 17.5 ...
##  $ Area      : num [1:2186] 0.1081 0.0357 0.0385 0.0181 0.0273 ...
##  $ MPO       : num [1:2186] 1 2 3 4 5 6 7 8 9 10 ...
##  $ county_ch : chr [1:2186] "FRA" "FRA" "FRA" "FRA" ...
##  $ PUMA_2010 : num [1:2186] 4105 4105 4105 4105 4105 ...
##  $ DISTRICT  : num [1:2186] 0 0 0 0 0 0 0 0 0 0 ...
##  $ POP       : num [1:2186] 606 266 883 41 1173 ...
##  $ POP18     : num [1:2186] 70 0 61 0 150 280 244 136 419 0 ...
##  $ POP_GQ    : num [1:2186] 0 0 0 0 0 0 179 0 66 0 ...
##  $ GQ_INS    : num [1:2186] 0 0 0 0 0 0 92 0 0 0 ...
##  $ GQ_UNV    : num [1:2186] 0 0 0 0 0 0 0 0 0 0 ...
##  $ GQ_OTH    : num [1:2186] 0 0 0 0 0 0 87 0 67 0 ...
##  $ WORKERS   : num [1:2186] 377 202 585 28 616 ...
##  $ HHINCOME  : num [1:2186] 12616 34506 49643 69466 62527 ...
##  $ TOT_HH    : num [1:2186] 290 189 517 31 458 588 896 796 740 0 ...
##  $ PARK_H    : num [1:2186] 300 396 200 188 393 ...
##  $ PARK_D    : num [1:2186] 500 1190 1000 1684 1162 ...
##  $ PARK_M    : num [1:2186] 7500 17856 15000 9452 6062 ...
##  $ parktot   : num [1:2186] 495 1162 1083 2074 3015 ...
##  $ parklong  : num [1:2186] 395 246 575 1799 1214 ...
##  $ parkfree  : num [1:2186] 0.798 0.212 0.538 0.875 0.407 ...
##  $ K8ENRPUB  : num [1:2186] 0 0 0 0 0 0 0 0 0 0 ...
##  $ HSENRPUB  : num [1:2186] 0 0 0 0 0 0 0 0 0 0 ...
##  $ ENRUNV    : num [1:2186] 0 0 0 0 0 0 0 0 0 0 ...
##  $ SCHOOL_D  : chr [1:2186] "COLUMBUS" "COLUMBUS" "COLUMBUS" "COLUMBUS" ...
##  $ NAICS11   : num [1:2186] 2 0 0 0 0 0 0 0 2 0 ...
##  $ NAICS21   : num [1:2186] 1 0 0 0 0 0 0 0 0 0 ...
##  $ NAICS22   : num [1:2186] 0 0 1206 0 0 ...
##  $ NAICS23   : num [1:2186] 0 0 0 0 0 0 0 107 3 0 ...
##  $ NAICS31   : num [1:2186] 0 0 0 0 0 1 0 2 1 0 ...
##  $ NAICS32   : num [1:2186] 0 0 0 0 0 0 0 9 6 0 ...
##  $ NAICS33   : num [1:2186] 1 0 0 27 0 1 0 15 0 0 ...
##  $ NAICS42   : num [1:2186] 0 0 0 5 0 12 0 15 46 0 ...
##  $ NAICS44   : num [1:2186] 6 0 109 0 33 61 26 9 57 0 ...
##  $ NAICS45   : num [1:2186] 1 0 2 1 5 8 385 0 21 0 ...
##  $ NAICS48   : num [1:2186] 3 0 0 0 0 79 24 3 3 3 ...
##  $ NAICS49   : num [1:2186] 15 1 0 0 6 0 0 65 0 0 ...
##  $ NAICS51   : num [1:2186] 0 35 7 26 0 28 9 0 27 0 ...
##  $ NAICS52   : num [1:2186] 146 236 919 931 18 29 0 46 228 0 ...
##  $ NAICS53   : num [1:2186] 1 31 119 2 21 23 242 0 91 0 ...
##  $ NAICS54   : num [1:2186] 192 341 369 20 105 278 0 0 84 0 ...
##  $ NAICS55   : num [1:2186] 10 10 97 0 17 13 0 0 10 0 ...
##  $ NAICS56   : num [1:2186] 9 30 9 456 6 62 7 18 20 0 ...
##  $ NAICS61   : num [1:2186] 32 4 7 0 35 11 0 15 181 184 ...
##  $ NAICS62   : num [1:2186] 20 396 22 0 28 23 0 69 240 0 ...
##  $ NAICS71   : num [1:2186] 0 4 204 1 3 83 0 0 5 0 ...
##  $ NAICS72   : num [1:2186] 0 27 87 230 187 173 503 0 86 0 ...
##  $ NAICS81   : num [1:2186] 7 79 6 26 187 63 0 35 30 0 ...
##  $ NAICS92   : num [1:2186] 0 27 0 0 1613 ...
##  $ NAICS99   : num [1:2186] 0 0 0 0 0 0 0 0 0 0 ...
##  $ WAGE_AVG  : num [1:2186] 69721 62741 70880 59569 59004 ...
##  $ AIRPASS   : num [1:2186] 0 0 0 0 0 0 0 0 0 0 ...
##  $ COUNTY    : num [1:2186] 3 3 3 3 3 3 3 3 3 3 ...
##  $ MAJUNIVENR: num [1:2186] 0 0 0 0 0 0 0 0 0 0 ...
##  $ OTHUNIVENR: num [1:2186] 0 0 0 0 0 0 0 0 0 0 ...
##  $ TERM_TIME : num [1:2186] 0 0 0 0 0 0 0 0 0 0 ...
##  $ REGION    : num [1:2186] 1 1 1 1 1 1 1 1 1 1 ...
##  $ PUMA_2000 : num [1:2186] 3105 3105 3105 3105 3105 ...

Writing DBF Files

Save the processed data to DBF format.

# Write to DBF files
write.dbf(as.data.frame(data1), dbf_file1)
write.dbf(as.data.frame(data2), dbf_file2)

Verification

Verify that the files were created successfully.

# Check if files exist
file.exists(dbf_file1)
## [1] TRUE
file.exists(dbf_file2)
## [1] TRUE