Standardize column names in the metadata file

Description

Reads an Excel metadata file, standardizes its column names to the expected field names using a JSON mapping configuration, and writes the result to a new Excel file.

Usage

change_colname(input_xlsx, output_xlsx, config_path, sheet = NULL)

Arguments

Details

The JSON configuration file must contain an aliases field, which is a named list mapping each canonical field name to a character vector of acceptable input column name variants. For example:

{
  "aliases": {
    "TTL1": ["Title 1", "Title_1"],
    "PGMNAME": ["Program Name", "program_name"]
  }
}

Before matching, input column names and aliases are normalized by converting to lowercase, trimming whitespace, and replacing underscores with spaces. Columns that do not match any alias are preserved unchanged. If multiple input columns map to the same canonical field name, output names are made unique via base::make.unique().

Value

Invisibly returns the path to the output Excel file.

See Also

read_tfile() to read the standardized metadata file.

Examples

input_file <- tempfile(fileext = ".xlsx")
output_file <- tempfile(fileext = ".xlsx")
config_file <- tempfile(fileext = ".json")

example_df <- data.frame(
  "Title_1" = "Summary of demographics",
  "Program Name" = "t_dm",
  check.names = FALSE
)

writexl::write_xlsx(example_df, input_file)

cfg <- paste0(
  "{",
  '  "aliases": {',
  '    "TTL1": ["Title 1"],',
  '    "PGMNAME": ["Program Name"]',
  "  }",
  "}"
)

writeLines(cfg, config_file)

change_colname(
  input_xlsx = input_file,
  output_xlsx = output_file,
  config_path = config_file
)

readxl::read_excel(output_file)

Get bookmark text for a table, listing, or figure

Description

Returns bookmark text from metadata for a specified output. The function first identifies the matching row in df using pname or tnumber. If a non-missing BOOKM value is available, that value is returned. Otherwise, the function falls back to get_title() and combines the returned title components into a single bookmark string.

Usage

get_bookm(
  df,
  tnumber = NULL,
  pname = NULL,
  oid = NULL,
  abbrev_file = NULL,
  max_length = 180
)

Arguments

Details

The bookmark text is sanitized by removing characters that are not suitable for bookmark use. If the result exceeds max_length, the function attempts to shorten it using abbreviation mappings from abbrev_file. If the bookmark is still too long, it is truncated at a word boundary up to max_length characters.

Value

A character string containing sanitized bookmark text.

See Also

read_tfile() to read metadata from Excel or CSV;

get_title(), get_footnote(), get_source(), get_pop(), get_byline(), get_pgmname(), get_ulheader(), and get_urheader() for retrieving individual annotation fields;

change_colname() to standardize column names in the metadata file.

Examples

# Example 1: return BOOKM when it is present
df1 <- data.frame(
  TTL1 = "Figure 1.1",
  PGMNAME = "f_km.R",
  BOOKM = "KM_PLOT"
)

get_bookm(df1, pname = "f_km.R")
get_bookm(df1, tnumber = "Figure 1.1")

# Example 2: fall back to title text when BOOKM is missing
df2 <- data.frame(
  TTL1 = "Adverse Events",
  TTL2 = "Safety Population",
  PGMNAME = "t_ae",
  BOOKM = NA
)

get_bookm(df2, pname = "t_ae")

# Example 3: invalid characters are removed
df3 <- data.frame(
  TTL1 = "Listing 3. Laboratory Results",
  PGMNAME = "l_lab",
  BOOKM = "Lab: ALT/AST * Overview?"
)

get_bookm(df3, pname = "l_lab")

Get Byline Metadata

Description

Retrieves byline fields (BYLINE1, BYLINE2, etc.) from metadata for a specified program name or TFL number.

Usage

get_byline(df, tnumber = NULL, pname = NULL, oid = NULL)

Arguments

Value

A data frame of the non-missing byline fields.

See Also

read_tfile() to read metadata from Excel or CSV;

get_title(), get_footnote(), get_source(), get_pop(), get_pgmname(), get_ulheader(), get_urheader(), and get_bookm() for retrieving individual annotation fields;

change_colname() to standardize column names in the metadata file.

Examples

meta <- data.frame(
  PGMNAME = c("t_dm", "t_ae"),
  TTL1 = c("Table 14.1.1", "Table 14.3.1"),
  SOURCE = c("ADSL", "ADAE"),
  BYLINE1 = c("Treatment Group", "System Organ Class"),
  BYLINE2 = c("N (%)", "Preferred Term"),
  FOOT1 = c("ITT Population", "Safety Population")
)

get_byline(meta, pname = "t_dm")
get_byline(meta, tnumber = "Table 14.3.1")

Get Footnote Metadata

Description

Retrieves footnote-related fields (columns beginning with "FOOT", such as FOOT1 and FOOT2) from a TFL metadata frame for a specified program name or TFL number.

Usage

get_footnote(
  df,
  tnumber = NULL,
  pname = NULL,
  oid = NULL,
  add_footr_tstamp = TRUE
)

Arguments

Value

A data frame of the non-missing footnote-related metadata fields.

See Also

read_tfile() to read metadata from Excel or CSV;

get_title(), get_source(), get_pop(), get_byline(), get_pgmname(), get_ulheader(), get_urheader(), and get_bookm() for retrieving individual annotation fields;

tflmetaR() for a single-call alternative.

Examples

meta <- data.frame(
  PGMNAME = c("t_dm", "t_ae"),
  TTL1 = c("Table 14.1.1", "Table 14.3.1"),
  TTL2 = c("Subject Disposition", "Adverse Events"),
  SOURCE = c("ADSL", "ADAE"),
  FOOT1 = c(
    "All Randomized Subjects",
    "Safety Population"
  ),
  FOOT2 = c(
    "Reference: Listing 11.3",
    "Adverse events coded using MedDRA"
  )
)

get_footnote(meta, pname = "t_dm", add_footr_tstamp = FALSE)
get_footnote(meta, tnumber = "Table 14.3.1", add_footr_tstamp = FALSE)

Get Program Name Metadata

Description

Retrieves the program name field (PGMNAME) from metadata for a specified program name or TFL number.

Usage

get_pgmname(df, tnumber = NULL, pname = NULL, oid = NULL)

Arguments

Value

A data frame of the non-missing program name field.

See Also

read_tfile() to read metadata from Excel or CSV;

get_title(), get_footnote(), get_source(), get_pop(), get_byline(), get_ulheader(), get_urheader(), and get_bookm() for retrieving individual annotation fields;

change_colname() to standardize column names in the metadata file.

Examples

meta <- data.frame(
  PGMNAME = c("t_dm", "t_ae"),
  TTL1 = c("Table 14.1.1", "Table 14.3.1"),
  SOURCE = c("ADSL", "ADAE"),
  FOOT1 = c("ITT Population", "Safety Population")
)

get_pgmname(meta, pname = "t_dm")
get_pgmname(meta, tnumber = "Table 14.3.1")

Get Population Metadata

Description

Retrieves the population field POPULATION from a TFL metadata data frame for a specified program name or TFL number.

Usage

get_pop(df, tnumber = NULL, pname = NULL, oid = NULL)

Arguments

Value

A data frame of the non-missing population field.

See Also

read_tfile() to read metadata from Excel or CSV;

get_title(), get_footnote(), get_source(), get_byline(), get_pgmname(), get_ulheader(), get_urheader(), and get_bookm() for retrieving individual annotation fields;

change_colname() to standardize column names in the metadata file.

Examples

meta <- data.frame(
  PGMNAME = c("t_dm", "t_ae"),
  TTL1 = c("Table 14.1.1", "Table 14.3.1"),
  SOURCE = c("ADSL", "ADAE"),
  POPULATION = c("ITT Population", "Safety Population"),
  FOOT1 = c(
    "Reference: Listing 11.3",
    "Adverse events coded using MedDRA"
  )
)

get_pop(meta, pname = "t_dm")
get_pop(meta, tnumber = "Table 14.3.1")

Get Source Metadata

Description

Retrieves source-related fields (for example, SOURCE) from metadata for a specified program name or TFL number.

Usage

get_source(df, tnumber = NULL, pname = NULL, oid = NULL)

Arguments

Value

A data frame of the non-missing source fields.

See Also

read_tfile() to read metadata from Excel or CSV;

get_title(), get_footnote(), get_pop(), get_byline(), get_pgmname(), get_ulheader(), get_urheader(), and get_bookm() for retrieving individual annotation fields;

tflmetaR() for a single-call alternative.

Examples

meta <- data.frame(
  PGMNAME = c("t_dm", "t_ae"),
  TTL1 = c("Table 14.1.1", "Table 14.3.1"),
  SOURCE = c("ADSL", "ADAE"),
  FOOT1 = c("ITT Population", "Safety Population")
)

get_source(meta, pname = "t_dm")
get_source(meta, tnumber = "Table 14.3.1")

Get Title Metadata

Description

Retrieves title-related fields (columns beginning with "TTL", such as TTL1, TTL2, and POPULATION if available) from a TFL metadata data frame for a specified program name or table number.

Usage

get_title(df, tnumber = NULL, pname = NULL, oid = NULL)

Arguments

Value

A data frame of the non-missing title-related metadata fields

See Also

read_tfile() to read metadata from Excel or CSV;

get_footnote(), get_source(), get_pop(), get_byline(), get_pgmname(), get_ulheader(), get_urheader(), and get_bookm() for retrieving individual annotation fields;

tflmetaR() for a single-call alternative.

Examples

meta <- data.frame(
  PGMNAME = c("t_dm", "t_ae"),
  TTL1 = c("Table 14.1.1", "Table 14.3.1"),
  TTL2 = c("Subject Disposition", "Adverse Events"),
  SOURCE = c("ADSL", "ADAE"),
  FOOT1 = c(
    "All Randomized Subjects",
    "Safety Population"
  ),
  FOOT2 = c(
    "Reference: Listing 11.3",
    "Adverse events coded using MedDRA"
  )
)

get_title(meta, pname = "t_dm")
get_title(meta, tnumber = "Table 14.3.1")

Get Upper-Left Header Text

Description

Retrieves upper-left header fields (columns beginning with "UL", such as UL1 and UL2) from metadata.

Usage

get_ulheader(df)

Arguments

Value

A data frame of the non-missing Upper-Left header metadata fields.

See Also

read_tfile() to read metadata from Excel or CSV;

get_title(), get_footnote(), get_source(), get_pop(), get_byline(), get_pgmname(), get_urheader(), and get_bookm() for retrieving individual annotation fields;

change_colname() to standardize column names in the metadata file.

Examples

meta <- data.frame(
  UL1 = "Drug X",
  UL2 = "Study 001",
  UR1 = "CONFIDENTIAL",
  UR2 = "VERSION: FINAL"
)

get_ulheader(meta)

Get Upper-Right Header Text

Description

Retrieves upper-right header fields (columns beginning with "UR", such as UR1 and UR2) from metadata.

Usage

get_urheader(df)

Arguments

Value

A data frame of the non-missing upper-right header metadata fields.

See Also

read_tfile() to read metadata from Excel or CSV;

get_title(), get_footnote(), get_source(), get_pop(), get_byline(), get_pgmname(), get_ulheader(), and get_bookm() for retrieving individual annotation fields;

change_colname() to standardize column names in the metadata file.

Examples

meta <- data.frame(
  UL1 = "Drug X",
  UL2 = "Study 001",
  UR1 = "CONFIDENTIAL",
  UR2 = "VERSION: FINAL"
)

get_urheader(meta)

Read metadata from an Excel or CSV file

Description

Reads metadata from an Excel (.xlsx, .xls) or CSV (.csv) file, standardizes column names to uppercase, and optionally validates that required metadata columns are present.

Usage

read_tfile(filename, sheetname = NULL, validate = TRUE, ...)

Arguments

Details

Column names in the returned data frame are converted to uppercase.

If validate = TRUE, the input metadata must contain the following required columns:

PGMNAME

Program name associated with the output.

TTL1

Primary title text.

FOOT1

Primary footnote text.

SOURCE

Source description for the output.

If any required column is missing, the function stops with an error.

Value

A data frame containing the imported metadata, with column names converted to uppercase.

See Also

get_title(), get_footnote(), get_source(), get_pop(), get_byline(), get_pgmname(), get_ulheader(), get_urheader(), and get_bookm() for retrieving individual annotation fields;

tflmetaR() for a single-call alternative.

change_colname() to standardize column names in the metadata file.

Examples

# Example 1: read a CSV metadata file
csv_file <- tempfile(fileext = ".csv")
write.csv(
  data.frame(
    pgmname = "t_dm",
    ttl1 = "Table 1. Demographics",
    foot1 = "Source: ADSL",
    foot2 = "*: Baseline record",
    source = "ADSL"
  ),
  csv_file,
  row.names = FALSE
)

read_tfile(csv_file)

# Example 2: read an Excel metadata file
xlsx_file <- tempfile(fileext = ".xlsx")
writexl::write_xlsx(
  data.frame(
    pgmname = "t_ae",
    ttl1 = "Table 2. Adverse Events",
    foot1 = "Source: ADAE",
    source = "ADAE"
  ),
  xlsx_file
)

read_tfile(xlsx_file)

Single-call interface for retrieving annotation metadata

Description

Reads annotation metadata from an Excel (.xls, .xlsx) or CSV (.csv) file and returns the requested metadata based on annotation.

Usage

tflmetaR(
  filename,
  sheetname = NULL,
  by_column = "PGMNAME",
  by_value,
  oid = NULL,
  annotation = NULL,
  add_footr_tstamp = TRUE
)

Arguments

Details

tflmetaR() provides a concise single-call interface for retrieving annotation metadata. The metadata file is filtered to a single row by matching by_value against by_column (default: "PGMNAME"). When multiple rows share the same by_column value, oid can be used for additional filtering. The returned metadata is then reduced to the columns specified by annotation.

For scripts that annotate multiple fields, the helper-function workflow is recommended to avoid repeated file I/O: read the metadata file once with read_tfile(), then retrieve individual annotations with get_title(), get_footnote(), and related helpers.

Value

A data frame containing the selected metadata.

See Also

read_tfile() to read metadata from Excel or CSV;

get_title(), get_footnote(), get_source(), get_pop(), get_byline(), get_pgmname(), get_ulheader(), get_urheader(), and get_bookm() for retrieving individual annotation fields;

change_colname() to standardize column names in the metadata file.

Examples

# Create a small example metadata file
csv_file <- tempfile(fileext = ".csv")
write.csv(
  data.frame(
    PGMNAME = "t_dm",
    TTL1 = "Table 14.1.1",
    TTL2 = "Subject Disposition",
    FOOT1 = "All Randomized Subjects",
    SOURCE = "ADSL"
  ),
  csv_file,
  row.names = FALSE
)

# Return title-related columns
tflmetaR(
  filename = csv_file,
  by_value = "t_dm",
  annotation = "TITLE"
)

# Return footnote-related columns
tflmetaR(
  filename = csv_file,
  by_value = "t_dm",
  annotation = "FOOTR",
  add_footr_tstamp = FALSE
)

# Return a specific column
tflmetaR(
  filename = csv_file,
  by_value = "t_dm",
  annotation = "SOURCE"
)

# Return the full selected row
tflmetaR(
  filename = csv_file,
  by_value = "t_dm"
)