This README.txt file was generated on <20221114> by ------------------- GENERAL INFORMATION ------------------- 1. Title of Dataset: Panel Data Preparation and Models for Social Equity of Bridge Management 2. Author Information Cari Gandy Engineering and Public Policy, Carnegie Mellon University 5000 Forbes Avenue, Pittsburgh, PA, 15213, USA cgandy@andrew.cmu.edu Daniel Armanios, Ph.D Saïd Business School, University of Oxford Park End Street, Oxford, OX1 1HP, UK Constantine Samaras, Ph.D Civil and Environmental Engineering, Carnegie Mellon University 5000 Forbes Avenue, Pittsburgh, PA, 15213, USA --------------------- DATA & FILE OVERVIEW --------------------- Directory of Files: A. Panel.csv Resulting panel of spatially matched NBI structure, demographic, and climate data from panel preparation script. B. Panel_Prep.R This R script requires downloaded and unzipped 1992-2020 NBI ASCII files, downloaded secondary climate attributes, and a U.S. Census Bureau API key to create a panel of spatially matched NBI structure, demographic, and climate data. To replicate panel preparation from raw data, call Panel_Prep.R to initialize the function and call the function with the following arguments: panel_prep(input_wd, output_wd, api_key, cejst_file, climate_file) Where: "input_wd" is the file path to the downloaded and unzipped input files "output_wd" is the file path to an output folder "api_key" is the user's Census API Key "cejst_file" is the CEJST CSV file name "climate_file" is the Climate CSV file name For example: panel_prep("/Documents/input","/Documents/output","abcdefg", "cejst.csv","climate.csv") C. Models_OrderedProbit.R R script specifying ordered probit models with random effects to produce main analysis and 'pglm' robustness checks from "Social Equity of Bridge Management." Input is the panel of NBI, census, and climate data. Requires R packages PGLM and texreg. D. Robustness_BinomialProbit.R R script specifying binomial probit models with random effects to produce robustness checks from "Social Equity of Bridge Management." Input is the panel of NBI, census, and climate data. Requires R packages PGLM and texreg. E. Robustness_NoRandomEffects.R R script specifying ordered probit models to produce robustness checks from "Social Equity of Bridge Management." Input is the panel of NBI, census, and climate data. Requires R packages MASS and texreg. F. Robustness_OLS.R R script specifying panel ordinary least squares models to produce robustness checks from "Social Equity of Bridge Management." Input is the panel of NBI, census, and climate data. Requires R packages pls and texreg. G. Extended_Supplemental.pdf Our Supplemental Materials document describes the panel preparation steps, variable selection, and ordered probit model implementation in detail, provides descriptive statistics and exploratory analysis for the uncensored NBI data and the final panel, and replicates our analysis for subsets of the data and alternative explanatory variables to demonstrate the robustness of our results. Further, we evaluated our modeling assumptions by conducting the same analysis with Ordinary Least Squares, binomial probit with random effects, and ordered probit without random effects. Source Data Files Naming Conventions: NBI (https://www.fhwa.dot.gov/bridge/nbi/ascii.cfm) File Naming Convention: "YYYYAllRecordsDelimitedAllStates.txt" (i.e., 2020AllRecordsDelimitedAllStates.txt) CEJST (https://screeningtool.geoplatform.gov/en/downloads) File Naming Convention: "communities-YYYY-MM-DD-HHMM.csv" (i.e., "communities-2022-05-31-1915.csv") ----------------------------------------- DATA DESCRIPTION FOR: Panel.csv ----------------------------------------- 1. Number of variables: 54 2. Number of cases/rows: 6396168 3. Missing data codes: Not applicable for this dataset 4. Variable List See National Bridge Inventory NBI) Recording and Coding Guide for detailed descriptions of NBI Variables: A. Name: BRIDGE_KEY Description: Unique identifier for each bridge structure B. Name: INSPECT_YR Description: Year of Inspection Date (NBI Item 90) C. Name: BRIDGE_COND Description: Minimum of component conditions (1-9) D. Name: DECK_COND_058 Description: NBI Item 58 (1-9) E. Name: SUPERSTRUCTURE_COND_059 Description: NBI Item 59 (1-9) F. Name: SUBSTRUCTURE_COND_060 Description: NBI Item 60 (1-9) G. Name: DECK_COND_STATE Description: Recoded NBI Item 58 to 1-4 H. Name: SUPERSTRUCTURE_COND_STATE Description: Recoded NBI Item 59 to 1-4 I. Name: SUBSTRUCTURE_COND_STATE Description: Recoded NBI Item 60 to 1-4 J. Name: STATE_CODE_001 Description: State FIPS Code (NBI Item 1) K. Name: REGION Description: Census Region L. Name: AGE Description: INSPECT_YR - YEAR_BUILT_027 (or YEAR_RECONSTRUCTED_106) M. Name: URBAN Description: NBI Item 26 indicates bridge is in an urban area (binary) N. Name: INTERSTATE Description: NBI Item 26 indicates interstate bridge (binary) O. Name: ADT_029 Description: NBI Item 029 P. Name: PERCENT_ADT_TRUCK_109 Description: NBI Item 109 Q. Name: DETOUR_KILOS_019 Description: NBI Item 019 R. Name: DECK_PROTECTION Description: NBI Item 108C indicates deck has a protective layer (binary) S. Name: STEEL_STRUCT Description: NBI Item 43B indicates the structure is steel (binary) T. Name: WATERWAY Description: NBI Item 42B indicates the bridge is over a waterway (binary) U. Name: WARM_REGION Description: Average annual air temperature greater than 18 deg C (binary) Source: Liao, T., Kepley, P., Kumar, I., and Labi, S. (2020). “Revisiting the secondary climate attributes for transportation infrastructure management: A Redux and Update for 2020.” 1(765). V. Name: HIGH_FREEZE_THAW Description: Average of over 60 freeze-thaw cycles per year (binary) Source: Liao, T., Kepley, P., Kumar, I., and Labi, S. (2020). “Revisiting the secondary climate attributes for transportation infrastructure management: A Redux and Update for 2020.” 1(765). W. Name: HIGH_PRECIP Description: Average of over 127 cm of rainfall annually (binary) Source: Liao, T., Kepley, P., Kumar, I., and Labi, S. (2020). “Revisiting the secondary climate attributes for transportation infrastructure management: A Redux and Update for 2020.” 1(765). X. Name: MINORITY_ABOVE_NAT_AVG_2020 Description: Tract percent minority (non-White and/or Hispanic or Latino) greater than the national average (42 percent) in 2020 (binary) Y. Name: MINORITY_ABOVE_NAT_AVG_2010 Description: Tract percent minority (non-White and/or Hispanic or Latino) greater than the national average (36 percent) in 2010 (binary) Z. Name: MINORITY_ABOVE_NAT_AVG_2000 Description: Tract percent minority (non-White and/or Hispanic or Latino) greater than the national average (31 percent) in 2000 (binary) AA. Name: MINORITY_ABOVE_50_PERCENT_2020 Description: Tract percent minority (non-White and/or Hispanic or Latino) greater than the 50 percent in 2020 (binary) AB. Name: MINORITY_ABOVE_50_PERCENT_2010 Description: Tract percent minority (non-White and/or Hispanic or Latino) greater than the 50 percent in 2010 (binary) AC. Name: MINORITY_ABOVE_50_PERCENT_2000 Description: Tract percent minority (non-White and/or Hispanic or Latino) greater than the 50 percent in 2000 (binary) AD. Name: MINORITY_ABOVE_60_PERCENT_2020 Description: Tract percent minority (non-White and/or Hispanic or Latino) greater than the 60 percent in 2020 (binary) AE. Name: MINORITY_ABOVE_60_PERCENT_2010 Description: Tract percent minority (non-White and/or Hispanic or Latino) greater than the 60 percent in 2010 (binary) AF. Name: MINORITY_ABOVE_60_PERCENT_2000 Description: Tract percent minority (non-White and/or Hispanic or Latino) greater than the 60 percent in 2000 (binary) AG. Name: BLACK_AFAM_ABOVE_NAT_AVG_2020 Description: Tract percent Black or African American greater than national average (13 percent) in 2020 (binary) AH. Name: BLACK_AFAM_ABOVE_NAT_AVG_2010 Description: Tract percent Black or African American greater than national average (13 percent) in 2010 (binary) AI. Name: BLACK_AFAM_ABOVE_NAT_AVG_2000 Description: Tract percent Black or African American greater than national average (13 percent) in 2000 (binary) AJ. Name: BLACK_AFAM_ABOVE_50_PERCENT_2020 Description: Tract percent Black or African American greater than 50 percent in 2020 (binary) AK. Name: BLACK_AFAM_ABOVE_50_PERCENT_2010 Description: Tract percent Black or African American greater than 50 percent in 2010 (binary) AL. Name: BLACK_AFAM_ABOVE_50_PERCENT_2000 Description: Tract percent Black or African American greater than 50 percent in 2000 (binary) AM. Name: BLACK_AFAM_ABOVE_60_PERCENT_2020 Description: Tract percent Black or African American greater than 60 percent in 2020 (binary) AN. Name: BLACK_AFAM_ABOVE_60_PERCENT_2010 Description: Tract percent Black or African American greater than 60 percent in 2010 (binary) AO. Name: BLACK_AFAM_ABOVE_60_PERCENT_2000 Description: Tract percent Black or African American greater than 60 percent in 2000 (binary) AP. Name: HISP_LATINO_ABOVE_NAT_AVG_2020 Description: Tract percent Hispanic or Latino greater than national average (19 percent) in 2020 (binary) AQ. Name: HISP_LATINO_ABOVE_NAT_AVG_2010 Description: Tract percent Hispanic or Latino greater than national average (16 percent) in 2010 (binary) AR. Name: HISP_LATINO_ABOVE_NAT_AVG_2000 Description: Tract percent Hispanic or Latino greater than national average (13 percent) in 2000 (binary) AS. Name: HISP_LATINO_ABOVE_50_PERCENT_2020 Description: Tract percent Hispanic or Latino greater than 50 percent in 2020 (binary) AT. Name: HISP_LATINO_ABOVE_50_PERCENT_2010 Description: Tract percent Hispanic or Latino greater than 50 percent in 2010 (binary) AU. Name: HISP_LATINO_ABOVE_50_PERCENT_2000 Description: Tract percent Hispanic or Latino greater than 50 percent in 2000 (binary) AV. Name: HISP_LATINO_ABOVE_60_PERCENT_2020 Description: Tract percent Hispanic or Latino greater than 60 percent in 2020 (binary) AW. Name: HISP_LATINO_ABOVE_60_PERCENT_2010 Description: Tract percent Hispanic or Latino greater than 60 percent in 2010 (binary) AX. Name: HISP_LATINO_ABOVE_60_PERCENT_2000 Description: Tract percent Hispanic or Latino greater than 60 percent in 2020 (binary) AY. Name: MED_INCOME.2020.acs Description: Tract household median income in 2020 (USD) AZ. Name: MED_INCOME.2010.acs Description: Tract household median income in 2010 (USD) BA. Name: MED_INCOME.2000.dec Description: Tract household median income in 200 (USD) BB. Name: DISADVANTAGED Description: Tract identified as Disadvantaged Community in the Climate and Economic Screening Tool (CEJST Ver 1.0) -------------------------- METHODOLOGICAL INFORMATION -------------------------- 1. Software-specific information: The included R scripts require the following open source R packages from CRAN: Croissant, Y. (2021). Package ‘pglm’(0.2-3). CRAN. . Croissant, Y., Millo, G., Tappe, K., Toomet, O., Kleiber, C., Zeilis, A., Henningsen, A., Andronic, L., and Schoenfelder, N. (2022). “Package ’plm’, . Dowle, M., Srinivasan, A., Gorecki, J., Chirico, M., Stetsenko, P., Short, T., Lianoglou, S., Antonyan, E., Bonsch, M., Parsonage, H., Ritchie, S., Ren, K., and Tan, X. (2021). “Package ‘data.table’: Extension of ‘data.frame‘, Hijmans, R. J., Phillips, S., Leathwick, J., and Elith, J. (2021). “Package ‘dismo’: Species Distribution Modeling, . Leifeld, P. (2013). “Texreg: Conversion of statistical model output in R to LATEX and HTML tables.” Journal of Statistical Software, 55(8). Pebesma, E., Bivand, R., Racine, E., Sumner, M., Cook, I., Keitt, T., Lovelace, R., Wickham, H., Ooms, J., Müller, K., Pedersen, T. L., Baston, D., Dunnington, D. (2022). Package ‘sf’: Simple Features for R (1.0-7). CRAN. https://r-spatial.github.io/sf/, https://github.com/r-spatial/sf/ Pebesma, E., Bivand, R., Rowlingson, B., Gomez-Rubio, V., Hijmans, R., Sumner, M., Macqueen, D., Lindgren, F., O’Brien, J., and O’Rourke, J. (2021). “Package ‘sp’: Classes and Methods for Spatial Data, . Ripley, B., Venables, B., Bates, D. M., Hornik, K., Gebhardt, A., and Firth, D. (2022). “Package ’MASS’, . Turner, R. (2021). Package ‘deldir’: Delaunay Triangulation and Dirichlet (Voronoi) Tessellation (1.0-6). CRAN. https://cran.r-project.org/package=deldir Walker, K., Herman, M., and Eberwein, K. (2022). “Package ‘tidycensus’: Load US Census Boundary and Attribute Data, . (Requires Census API Key: https://api.census.gov/data/key_signup.html) Walker, K. and Rudis, B. (2022). “Package ‘tigris’: Load Census TIGER/Line Shapefiles, . Wickham H, Averick M, Bryan J, Chang W, McGowan LD, François R, Grolemund G, Hayes A, Henry L, Hester J, Kuhn M, Pedersen TL, Miller E, Bache SM, Müller K, Ooms J, Robinson D, Seidel DP, Spinu V, Takahashi K, Vaughan D, Wilke C, Woo K, Yutani H (2019). “Welcome to the tidyverse.” Journal of Open Source Software, 4(43), 1686. doi:10.21105/joss.01686. 2. Equipment-specific information: 100Gb memory required 3. Date of data collection: NBI ASCII files obtained 20210815 CEJST Version 1.0 data file was dated 20220531 Census Data obtained via API on 20220817. Includes 2000 Decennial Census Summary File 3 2010 Decennial Redistricting Data (PL 94-171) Table P2 2020 Decennial Redistricting Data (PL 94-171) Table P2 2006-2010 American Community Survey B19025 2016-2020 American Community Survey B19025