Refined Metadata

1 Introduction

This page holds a table of the 130 metrics that are used in the refined framework and in subsequent analyses. The metadata can be browsed below, and links to the source of each metric can be found by hitting the arrow on the left side of each row. You can also download the file as a .csv using the button provided.

# Load metrics data
sm_data <- readRDS('data/sm_data.rds')
metrics <- sm_data$metrics

# Load refined framework
raw_tree <- sm_data[['refined_tree']]

# Load refined framework
frame <- readRDS('data/frame.rds')

# Pull it from the actual metrics data
metrics <- sm_data$metrics %>% 
  dplyr::filter(
    variable_name %in% frame$variable_name,
    fips %in% sm_data$state_key$state_code
  )
get_str(metrics)
length(unique(metrics$variable_name))

# Filter to latest year for each metric, and pivot wider
# Also removing census participation - don't really have data at state level
# Note to aggregate counties for this at some point
metrics_df <- metrics %>%
  mutate(
    value = ifelse(value == 'NaN', NA, value),
    value = str_remove_all(value, ','),
    value = as.numeric(value)
  ) %>%
  get_latest_year() %>% 
  pivot_wider(
    names_from = 'variable_name',
    values_from = 'value'
  ) %>% 
  unnest(cols = !fips) %>% 
  unique()
get_str(metrics_df)

# Let's get rid of the years so they are easier to work with
names(metrics_df) <- str_split_i(names(metrics_df), '_', 1)
get_str(metrics_df)

# Also get rid of DC - too many missing values
metrics_df <- metrics_df %>% 
  dplyr::filter(fips != '11')

# Save this for use in subsequent pages
saveRDS(metrics_df, 'data/metrics_df.rds')

Below, the metrics are displayed in a table that lets you browse and explore them.

# Pull var names from metrics_df out of full metadata
vars <- unique(frame$variable_name) %>% 
  str_subset('NONE', negate = TRUE)


## Load metadata table, but keep framework from frame
metadata <- sm_data$metadata %>% 
  select(-c(dimension, index, indicator)) %>% 
  dplyr::filter(variable_name %in% vars)

# Grab the framework variables from the frame to combine with metadata
updated_framework <- frame %>% 
  dplyr::select(variable_name, dimension, index, indicator)

# Combine them
metadata <- inner_join(metadata, updated_framework, by = 'variable_name')


## Pick out variables to display
metadata <- metadata %>% 
  select(
    metric,
    'Variable Name' = variable_name,
    definition,
    dimension,
    index,
    indicator,
    units,
    years = year,
    'Year' = latest_year, # Renaming latest year as year, not including og year
    source,
    scope,
    updates,
    resolution,
    url
) %>% 
  setNames(c(str_to_title(names(.))))

# Save this for preso
saveRDS(metadata, 'preso/data/meta_for_table.rds')

###
htmltools::browsable(
  tagList(
    
    tags$div(
      style = "display: flex; gap: 16px; margin-bottom: 20px; justify-content: center;",
      
      tags$button(
        class = "btn btn-primary",
        style = "display: flex; align-items: center; gap: 8px; padding: 8px 12px;",
        tagList(fontawesome::fa("download"), "Show/hide more columns"),
        onclick = "Reactable.setHiddenColumns('metadata_table', prevColumns => {
          return prevColumns.length === 0 ? ['Definition', 'Scope', 'Resolution', 'Url'] : []
        })"
      ),
      
      tags$button(
        class = "btn btn-primary",
        style = "display: flex; align-items: center; gap: 8px; padding: 8px 12px;",
        tagList(fontawesome::fa("download"), "Download as CSV"),
        onclick = "Reactable.downloadDataCSV('metadata_table', 'sustainability_metadata.csv')"
      )
    ),
    
    reactable(
      metadata[, which(names(metadata) != 'Years')],
      sortable = TRUE,
      resizable = TRUE,
      filterable = TRUE,
      searchable = TRUE,
      pagination = TRUE,
      bordered = TRUE,
      wrap = TRUE,
      rownames = FALSE,
      onClick = 'select',
      striped = TRUE,
      pageSizeOptions = c(5, 10, 25, 50, 100),
      defaultPageSize = 5,
      showPageSizeOptions = TRUE,
      highlight = TRUE,
      style = list(fontSize = "14px"),
      compact = TRUE,
      fullWidth = TRUE,
      columns = list(
        Metric = colDef(
          minWidth = 200,
          sticky = 'left'
        ),
        'Variable Name' = colDef(
          minWidth = 150
        ),
        Definition = colDef(
          minWidth = 250
        ),
        'Latest Year' = colDef(minWidth = 75),
        Source = colDef(minWidth = 250),
        Scope = colDef(show = FALSE),
        Resolution = colDef(show = FALSE),
        Url = colDef(
          minWidth = 300,
          show = FALSE
        )
      ),
      defaultColDef = colDef(minWidth = 100),
      elementId = "metadata_table",
      details = function(index) {
        div(
          style = "padding: 15px; border: 1px solid #ddd; margin: 10px 0;
             background-color: #ecf4ed; border-radius: 10px; border-color: black;
             box-shadow: 2px 2px 10px rgba(0, 0, 0, 0.1);",
          
          tags$h4(
            strong("Details"), 
          ),
          tags$p(
            strong('Metric Name: '), 
            as.character(metadata[index, 'Metric']),
          ),
          tags$p(
            strong('Variable Name: '), 
            as.character(metadata[index, 'Variable Name']),
          ),
          tags$p(
            strong('Definition: '), 
            as.character(metadata[index, 'Definition']),
          ),
          tags$p(
            strong('Source: '), 
            as.character(metadata[index, 'Source'])
          ),
          tags$p(
            strong('Latest Year: '), 
            as.character(metadata[index, 'Year'])
          ),
          tags$p(
            strong('All Years (cleaned, wrangled, and included): '), 
            as.character(metadata[index, 'Years'])
          ),
          tags$p(
            strong('Updates: '), 
            str_to_title(as.character(metadata[index, 'Updates']))
          ),
          tags$p(
            strong('URL: '), 
            tags$a(
              href = as.character(metadata[index, 'Url']),
              target = '_blank',
              as.character(metadata[index, 'Url'])
            )
          )
        )
      }
    )
  )
)