import time

import pandas as pd

import jecfa

pd.set_option("display.width", 220)
pd.set_option("display.max_columns", 30)
pd.set_option("display.max_rows", 200)
pd.set_option("display.max_colwidth", 90)

started = time.time()
data = jecfa.load()
df = data["rds"]
print(f"Figshare item {jecfa.ARTICLE_ID} version {jecfa.VERSION} ({jecfa.DOI}), "
      f"read in {time.time() - started:.1f} s")

Figshare item 26877211 version 1 (10.6084/m9.figshare.26877211.v1), read in 6.8 s

meta = data["meta"]
print("Title:", meta["title"])
print("Published:", meta["published_date"], " version:", meta["version"], " DOI:", meta["doi"])
print("Related:", [(r["relation"], r["identifier"]) for r in meta["related_materials"]])
print("\nFiles (name, id, size in bytes, MD5 of the bytes read, archive computed_md5, equal):")
for f in meta["files"]:
    got, expected = data["md5"][f["name"]]
    print(f"  {f['name']:10s} {f['id']}  {f['size']:>8d}  {got}  {expected}  {got == expected}")
versions = pd.read_json(jecfa.API_URL.rsplit("/", 1)[0])
print("\nPublished versions of the item:", versions["version"].tolist())
print("\nThe item's description, which documents each field:\n")
print(meta["description"])

Title: Unveiling Insights from the Joint FAO/WHO Expert Committee on Food Additives (JECFA) Portal
Published: 2024-12-29T08:01:38Z  version: 1  DOI: 10.6084/m9.figshare.26877211.v1
Related: [('IsSupplementTo', '10.1038/s41597-024-04294-w')]

Files (name, id, size in bytes, MD5 of the bytes read, archive computed_md5, equal):
  jecfa.rds  48922360    360432  5bfd89459ec77a152e5ee1f55e124f3d  5bfd89459ec77a152e5ee1f55e124f3d  True
  JECFA.csv  48922363   3484607  dee04993aaa56ca1f3844ded5c472764  dee04993aaa56ca1f3844ded5c472764  True

Published versions of the item: [1]

The item's description, which documents each field:

This database is a result of web scraping techniques applied to the Joint FAO/WHO Expert Committee on Food Additives (JECFA) database. It contains detailed toxicological and evaluation information on various chemicals assessed by JECFA. Below is a description of each field in the database: Report: Identifier for the report number and session in which the chemical was evaluated. Report_sourcelink: URL link to the full text of the report. Tox.Monograph: Identifier for the toxicological monograph related to the chemical. Tox.Monograph_sourcelink: URL link to the toxicological monograph. Specification: Identifier for the specification document related to the chemical. Specification_sourcelink: URL link to the specification document. Synonyms: Alternative names for the chemical. CAS.number: The Chemical Abstracts Service (CAS) registry number, a unique numerical identifier for the chemical. Functional.Class: The functional classification of the chemical (e.g., Flavouring Agent, Food Contaminant). Evaluation.year: The year in which the chemical was evaluated by JECFA. Chemical.Names: The chemical names as listed in the JECFA evaluation. JECFA.number: The unique identifier assigned to the chemical by JECFA. COE.number: The Council of Europe (COE) number for the chemical, if applicable. FEMA.number: The Flavor and Extract Manufacturers Association (FEMA) number, if applicable. JECFA_name: The name used by JECFA for the chemical. URL: URL link to the JECFA database entry for the chemical. FAS: FAO/WHO Food Additive Specification identifier.

csv = data["csv"]
print("jecfa.rds:", df.shape, " JECFA.csv:", csv.shape)
print("Same column names in the same order:", list(df.columns) == list(csv.columns))
same = (df.fillna("<NA>").to_numpy() == csv.fillna("<NA>").to_numpy())
print("Cells that differ between the two files:", int((~same).sum()))
print("\nColumns of jecfa.rds, next to the field of Table 1 and the name in R/process_df.R:")
cols = pd.DataFrame({"table1_field": list(jecfa.TABLE1_FIELDS),
                     "deposit_column": list(jecfa.TABLE1_FIELDS.values())})
code_names = {c.lower().replace(".", "_"): c for c in jecfa.CODE_COLUMNS}
cols["process_df_name"] = cols.deposit_column.map(code_names)
cols["in_deposit"] = cols.deposit_column.isin(df.columns)
print(cols.to_string(index=False))
print("Deposit columns not in Table 1:", sorted(set(df.columns) - set(cols.deposit_column)))

jecfa.rds: (6552, 17)  JECFA.csv: (6552, 17)
Same column names in the same order: True
Cells that differ between the two files: 0

Columns of jecfa.rds, next to the field of Table 1 and the name in R/process_df.R:
            table1_field           deposit_column          process_df_name  in_deposit
                     URL                      url                      URL        True
              JECFA name               jecfa_name               JECFA_name        True
          Chemical names           chemical_names           Chemical.Names        True
                Synonyms                 synonyms                 Synonyms        True
              CAS number               cas_number               CAS.number        True
            JECFA number             jecfa_number             JECFA.number        True
             FEMA number              fema_number              FEMA.number        True
              COE number               coe_number               COE.number        True
        Functional class         functional_class         Functional.Class        True
         Evaluation year          evaluation_year          Evaluation.year        True
                  Report                   report                   Report        True
       Report sourcelink        report_sourcelink        Report_sourcelink        True
           Tox monograph            tox_monograph            Tox.Monograph        True
Tox monograph sourcelink tox_monograph_sourcelink Tox.Monograph_sourcelink        True
           Specification            specification            Specification        True
Specification sourcelink specification_sourcelink Specification_sourcelink        True
                     FAS                      fas                      FAS        True
Deposit columns not in Table 1: []

present = df.apply(jecfa.present)
summary = pd.DataFrame({
    "NA": df.isna().sum(),
    "empty_string": (df.notna() & (df.fillna("").apply(lambda s: s.str.strip()) == "")).sum(),
    "present": present.sum(),
    "distinct": df.nunique(),
    "example": [df[c].dropna().iloc[0][:60] for c in df.columns],
})
print(summary.to_string())

                            NA  empty_string  present  distinct                                                       example
report                     156             0     6396       664                                           TRS 776-JECFA 33/22
report_sourcelink          156             0     6396       231  http://apps.who.int/iris/bitstream/10665/39252/1/WHO_TRS_776
tox_monograph              468             0     6084       625                                            FAS 24-JECFA 33/97
tox_monograph_sourcelink   468             0     6084       583    http://www.inchem.org/documents/jecfa/jecmono/v024je06.htm
specification              536             0     6016       429       FAO Combined Compendium of Food Additive Specifications
specification_sourcelink   536             0     6016        71  https://www.fao.org/food/food-safety-quality/scientific-advi
synonyms                  1422             0     5130      1907  Karaya; Gum sterculia; Sterculia; Kadaya; Katilo; Kullo; Kut
cas_number                4235             0     2317      1295                                                     9000-36-6
functional_class           110             0     6442       215                   Food AdditivesEMULSIFIERSTABILIZERTHICKENER
evaluation_year            118             0     6434        62                                                          1988
chemical_names            1142             0     5410      2467                                     1-(4-Methylphenyl)ethanol
jecfa_number              1913             0     4639      2285                                                           805
coe_number                3521             0     3031      1255                                                         10197
fema_number               2294             0     4258      1939                                                          3139
jecfa_name                   0             0     6552      6551                                                    KARAYA GUM
url                          0             0     6552      6552  https://apps.who.int/food-additives-contaminants-jecfa-datab
fas                        468           752     5332        82                                                            24

pid = jecfa.page_id(df)
print("URLs of the form", jecfa.URL_PREFIX + "<n>:", int(pid.notna().sum()), "of", len(df))
print("Page numbers from", int(pid.min()), "to", int(pid.max()), "; duplicated URLs:", int(df.url.duplicated().sum()),
      "; rows sorted by page number:", bool(pid.is_monotonic_increasing))
missing_ids = sorted(set(range(1, int(pid.max()) + 1)) - set(pid.astype(int)))
print(f"Page numbers between 1 and {int(pid.max())} with no row: {len(missing_ids)}:", missing_ids)

year = pd.to_numeric(df.evaluation_year, errors="coerce")
print("\nevaluation_year: all present values are integers:", bool(year[df.evaluation_year.notna()].notna().all()),
      "; range", int(year.min()), "to", int(year.max()))
for c in ["jecfa_number", "fema_number", "coe_number"]:
    s = df[c].dropna()
    odd = s[~s.str.fullmatch(r"\d+")]
    print(f"{c}: {len(s)} present, {len(odd)} not a plain integer, e.g. {odd.head(4).tolist()}")
    print(f"  of these {len(odd)}, {int(odd.str.strip().str.fullmatch(r'\d+').sum())} are plain integers once leading "
          f"and trailing spaces are removed")
cas = df.cas_number.dropna()
plain = cas[cas.str.fullmatch(r"\d{2,7}-\d{2}-\d")]
print(f"cas_number: {len(cas)} present, {len(plain)} of the plain form NNNNNNN-NN-N, of which "
      f"{int(plain.map(jecfa.cas_check_digit_ok).sum())} have a valid check digit; "
      f"{len(cas) - len(plain)} carry more than one number or a note, e.g. {cas[~cas.isin(plain)].head(3).tolist()}")
print("Plain CAS numbers with a wrong check digit:", plain[~plain.map(jecfa.cas_check_digit_ok)].tolist())
print("\nValues starting with the characters '\">' (Usage Notes): report",
      int(df.report.str.startswith('">').sum()), ", tox_monograph", int(df.tox_monograph.str.startswith('">').sum()),
      ", specification", int(df.specification.str.startswith('">').sum()))

URLs of the form https://apps.who.int/food-additives-contaminants-jecfa-database/Home/Chemical/<n>: 6552 of 6552
Page numbers from 1 to 6593 ; duplicated URLs: 0 ; rows sorted by page number: True
Page numbers between 1 and 6593 with no row: 41: [1798, 4905, 5959, 5960, 6003, 6183, 6202, 6207, 6211, 6217, 6222, 6229, 6286, 6294, 6295, 6302, 6303, 6304, 6305, 6309, 6317, 6318, 6320, 6321, 6322, 6323, 6324, 6325, 6327, 6328, 6362, 6381, 6382, 6397, 6459, 6464, 6527, 6528, 6530, 6548, 6553]

evaluation_year: all present values are integers: True ; range 1958 to 2023
jecfa_number: 4639 present, 31 not a plain integer, e.g. ['506a,b', '506a,b', '632.1', '631.1']
  of these 31, 1 are plain integers once leading and trailing spaces are removed
fema_number: 4258 present, 33 not a plain integer, e.g. ['4126  ', '4161  ', '4209  ', '3957  ']
  of these 33, 33 are plain integers once leading and trailing spaces are removed
coe_number: 3031 present, 1 not a plain integer, e.g. ['2983-38-2']
  of these 1, 0 are plain integers once leading and trailing spaces are removed
cas_number: 2317 present, 2127 of the plain form NNNNNNN-NN-N, of which 2081 have a valid check digit; 190 carry more than one number or a note, e.g. ['38966-29-9; 3387-37-9 (1:1 SALT)', "860-22-0 (5,5' ISOMER)", '13270-61-6 (Delphinidin)']
Plain CAS numbers with a wrong check digit: ['22389-47-0', '76-36-3', '5284-04-9', '2353-43-9', '7783-54-0', '7783-54-0', '2412-24-1', '5284-04-9', '61597-96-6', '2353-43-9', '7783-54-0', '1229-55-9', '5284-04-9', '2353-43-9', '3943-58-3', '22389-47-0', '1229-55-9', '1229-55-9', '2412-24-1', '5284-04-9', '5284-04-9', '58-92-1', '5284-04-9', '6833-79-9', '5284-04-9', '45706-73-7', '5807-68-8', '76-36-3', '3943-58-3', '1005-86-2', '3943-58-3', '7796-47-2', '23696-28-2', '141-40-0', '76-36-3', '76-36-3', '76-36-3', '76-36-3', '76-36-3', '5284-04-9', '15514-30-0', '71437-64-0', '0079-19-5', '79797-16-1', '1440-67-0', '86366-64-3']

Values starting with the characters '">' (Usage Notes): report 602 , tox_monograph 349 , specification 398

fas_rederived = jecfa.fas_process_df(df.tox_monograph)
print("fas equals the derivation of R/process_df.R from tox_monograph in every row:",
      bool((fas_rederived.fillna("<NA>") == df.fas.fillna("<NA>")).all()))
empty_fas = df.fas.fillna("x").str.strip() == ""
print("\nTox monographs of the rows with an empty fas, by the code's abbreviation (R/extract_jecfa_tox_abbr.R):")
print(df.tox_monograph[empty_fas].map(jecfa.tox_abbr).value_counts().to_string())
print("Of these, Tox monographs starting with '\">':", int(df.tox_monograph[empty_fas].str.startswith('">').sum()))
print("\nDistinct fas values:", sorted(df.fas.dropna().unique(), key=lambda s: (len(s), s)))

fas equals the derivation of R/process_df.R from tox_monograph in every row: True

Tox monographs of the rows with an empty fas, by the code's abbreviation (R/extract_jecfa_tox_abbr.R):
tox_monograph
FAS 60                                                  339
NOT PREPARED                                            273
NOT A FAS                                               120
FAS 74                                                   10
FAS79                                                     4
NOT PREPARED. See FAS 17                                  3
FAS 27                                                    2
NOT PREPARED (SEE ENVIRONMENTAL HEALTH CRITERIA 140)      1
Of these, Tox monographs starting with '">': 349

Distinct fas values: ['', '1', '5', '6', '8', '10', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '36', '37', '38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52', '53', '54', '55', '56', '57', '58', '59', '60', '62', '64', '65', '69', '70', '71', '72', '74', '78', '80', '82', '83', '86', '617', '24.65', '67.29', '68.33', '70.36', '70.39', '65 JECFA 74', '67 JECFA 76', '68 JECFA 77', '71 JECFA 80', '73 JECFA 82', '75 JECFA 84', '77 JECFA 86', '80 JECFA 89']

import inspect

import re as _re

def r_function(path, name):
    """The definition of one R function in a file of the paper's code, from its name to the closing brace."""
    text = jecfa.r_source(path)
    start = text.index(f"{name} <- function")
    end = _re.search(r"^}", text[start:], _re.M).end()
    return text[start:start + end]

print(f"Paper's code at commit {jecfa.CODE_COMMIT}, next to the Python ports of jecfa.py\n")
for path, name, port in [("R/process_df.R", "process_df", jecfa.fas_process_df),
                         ("R/extract_jecfa_tox_abbr.R", "extract_jecfa_tox_abbr", jecfa.tox_abbr),
                         ("R/extract_jecfa_tox_abbr.R", "extract_initial_part", None),
                         ("R/extract_jecfa_tox_abbr.R", "extract_initial_part_bis", None),
                         ("R/extract_jecfa_tox_abbr.R", "extract_initial_part_ter", None),
                         ("R/add_metadata.R", "add_metadata", jecfa.fas_add_metadata)]:
    r = r_function(path, name)
    if name == "process_df":
        r = r[:r.index("dplyr::rename")]  # the FAS step; the rest renames and selects columns
    print(f"----- {path}: {name}\n{r}")
    if port is not None:
        print(f"----- jecfa.py: {port.__name__}\n{inspect.getsource(port)}")

Paper's code at commit 2d8a5340d5322cbae28eb3095d948313932f6c7e, next to the Python ports of jecfa.py

----- R/process_df.R: process_df
process_df <- function(df_clean) {
  df_clean |>
    dplyr::mutate(
      FAS = dplyr::if_else(
        stringr::str_detect(`Tox Monograph1`, "^FAS .*$"),
        stringr::str_sub(`Tox Monograph1`, 5),
        ""
      ),
      FAS = stringr::str_split(FAS, "/", simplify = TRUE)[, 1],
      FAS = stringr::str_split(FAS, "-", simplify = TRUE)[, 1]
    ) |>

----- jecfa.py: fas_process_df
def fas_process_df(tox_monograph: pd.Series) -> pd.Series:
    """FAS as R/process_df.R derives it from the Tox monograph (NA stays NA)."""
    def one(t):
        if pd.isna(t):
            return pd.NA
        if not re.match(r"^FAS .*$", t):
            return ""
        return t[4:].split("/")[0].split("-")[0]
    return tox_monograph.map(one).astype("string")

----- R/extract_jecfa_tox_abbr.R: extract_jecfa_tox_abbr
extract_jecfa_tox_abbr <- function(tox_monograph) {
  aux <- tox_monograph |>
    sapply(extract_initial_part) |>
    sapply(extract_initial_part_bis) |>
    sapply(extract_initial_part_ter)

  aux <- ifelse(
    substr(aux, 1, 3) == "See",
    substr(aux, 5, nchar(aux)),
    aux
  )

  aux <- ifelse(
    substr(aux, 1, 2) == '">',
    substr(aux, 3, nchar(aux)),
    aux
  )

  aux <- ifelse(
    substr(aux, 1, 3) != "FAS" &
      substr(aux, 1, 12) != "NOT PREPARED",
    "NOT A FAS",
    aux
  )

  aux
}
----- jecfa.py: tox_abbr
def tox_abbr(t) -> object:
    """R/extract_jecfa_tox_abbr.R for one Tox monograph value."""
    if pd.isna(t):
        return pd.NA
    a = t.split("-")[0].split("/")[0].split("JECFA")[0]
    if a[:3] == "See":
        a = a[4:]
    if a[:2] == '">':
        a = a[2:]
    if a[:3] != "FAS" and a[:12] != "NOT PREPARED":
        a = "NOT A FAS"
    return a

----- R/extract_jecfa_tox_abbr.R: extract_initial_part
extract_initial_part <- function(input_string) {
  unlist(strsplit(input_string, "-"))[1]
}

----- R/extract_jecfa_tox_abbr.R: extract_initial_part_bis
extract_initial_part_bis <- function(input_string) {
  unlist(strsplit(input_string, "/"))[1]
}
----- R/extract_jecfa_tox_abbr.R: extract_initial_part_ter
extract_initial_part_ter <- function(input_string) {
  unlist(strsplit(input_string, "JECFA"))[1]
}

----- R/add_metadata.R: add_metadata
add_metadata <- function(jecfa) {
  jecfa |>
    dplyr::mutate(
      type = get_jecfa_type(Tox.Monograph_sourcelink),
      host = get_jecfa_url_parts(Tox.Monograph_sourcelink)[, 2],
      Tox_monograph_abbr = extract_jecfa_tox_abbr(Tox.Monograph),
      Report_clean = extract_jecfa_report_clean(Report),
      FAS = dplyr::case_when(
        nchar(FAS) > 3 ~ substr(FAS, 1, 2),
        FAS != "" ~ FAS,
        # From here FAS == ""
        substr(Tox_monograph_abbr, 1, 6) == "FAS 74" ~ "74",
        substr(Tox_monograph_abbr, 1, 6) == "FAS 60" ~ "60",
        substr(Tox_monograph_abbr, 1, 6) == "FAS 27" ~ "27",
        TRUE ~ FAS
      ),
      ref_id = dplyr::row_number()
    )
}
----- jecfa.py: fas_add_metadata
def fas_add_metadata(fas: pd.Series, tox_monograph: pd.Series) -> pd.Series:
    """FAS after the case_when of R/add_metadata.R."""
    def one(f, t):
        if pd.isna(f):
            return pd.NA
        if len(f) > 3:
            return f[:2]
        if f != "":
            return f
        abbr = tox_abbr(t)
        if not pd.isna(abbr):
            for code in ("74", "60", "27"):
                if abbr[:6] == f"FAS {code}":
                    return code
        return f
    return pd.Series([one(f, t) for f, t in zip(fas, tox_monograph)], index=fas.index,
                     dtype="string")

cls = jecfa.leading_class(df.functional_class)
print("Functional class strings, by leading class (the rest of the string is the sub-class):")
print(cls.value_counts(dropna=False).to_string())
print("Strings that start with none of the four class names:",
      df.functional_class[cls.isna() & df.functional_class.notna()].tolist())
print("Spellings of the flavouring class:",
      df.functional_class.str.extract(r"^(Flavou?ring Agent)")[0].value_counts().to_dict())
print("\nCOE number present, by class:")
print(pd.crosstab(cls.fillna("unknown"), present.coe_number).to_string())
print("\nFEMA number present, by class:")
print(pd.crosstab(cls.fillna("unknown"), present.fema_number).to_string())

Functional class strings, by leading class (the rest of the string is the sub-class):
leading_class
Flavoring Agent     4635
Food Additives      1453
Veterinary Drug      277
<NA>                 110
Food Contaminant      77
Strings that start with none of the four class names: []
Spellings of the flavouring class: {'Flavouring Agent': 4635}

COE number present, by class:
coe_number        False  True 
leading_class                 
Flavoring Agent    1613   3022
Food Additives     1444      9
Food Contaminant     77      0
Veterinary Drug     277      0
unknown             110      0

FEMA number present, by class:
fema_number       False  True 
leading_class                 
Flavoring Agent     400   4235
Food Additives     1443     10
Food Contaminant     77      0
Veterinary Drug     277      0
unknown              97     13

by_url = df.drop(columns="url")
print("Rows identical to another row in every field but the URL:", int(by_url.duplicated(keep=False).sum()))
dup_names = df.jecfa_name[df.jecfa_name.duplicated(keep=False)]
print("JECFA names used by more than one row:", dup_names.unique().tolist(), "at pages",
      pid[dup_names.index].astype(int).tolist())
print("Rows named TEST:", pid[df.jecfa_name.str.strip().str.upper() == "TEST"].astype(int).tolist())
print("\nFields present in each of these rows:")
show = df.index[pid.isin([5883, 6316, 6319])]
print(present.loc[show].assign(page=pid[show].astype(int)).set_index("page").T.to_string())
print(f"\nData check done in {time.time() - started:.1f} s")

Rows identical to another row in every field but the URL: 0
JECFA names used by more than one row: ['(E)-N-[2-(1,3-BENZODIOXOL-5-YL)ETHYL]-3- (3,4-DIMETHOXYPHENYL)PROP-2-ENAMIDE'] at pages [6316, 6319]
Rows named TEST: [5883]

Fields present in each of these rows:
page                       5883   6316   6319
report                    False  False   True
report_sourcelink         False  False   True
tox_monograph             False  False   True
tox_monograph_sourcelink  False  False   True
specification             False  False   True
specification_sourcelink  False  False   True
synonyms                  False  False  False
cas_number                False  False   True
functional_class          False  False   True
evaluation_year           False  False   True
chemical_names            False  False   True
jecfa_number              False  False   True
coe_number                False  False  False
fema_number               False  False   True
jecfa_name                 True   True   True
url                        True   True   True
fas                       False  False   True

Data check done in 8.3 s

n_rows = len(df)
fields_found = cols.in_deposit.sum()
print(f"Rows of jecfa.rds: {n_rows} (expected 6552, tolerance 3; within: {abs(n_rows - 6552) <= 3})")
print(f"Table 1 fields with a column: {fields_found} of {len(cols)}; missing: {cols.table1_field[~cols.in_deposit].tolist()}")
print(f"JECFA.csv: {csv.shape[0]} rows and {csv.shape[1]} columns; cells that differ from jecfa.rds: {int((~same).sum())}")

Rows of jecfa.rds: 6552 (expected 6552, tolerance 3; within: True)
Table 1 fields with a column: 17 of 17; missing: []
JECFA.csv: 6552 rows and 17 columns; cells that differ from jecfa.rds: 0

name_6316 = df.jecfa_name[pid == 6316].iloc[0]
d1 = df.url.nunique()
d2 = df.loc[pid != 5883, "jecfa_name"].nunique()
d3 = len(df) - int((pid == 5883).sum()) - (int((df.jecfa_name == name_6316).sum()) - 1)
readings = pd.DataFrame({"reading": ["D1", "D2", "D3"], "unique_pages": [d1, d2, d3]})
readings["within_2_of_6549"] = (readings.unique_pages - 6549).abs() <= 2
print(readings.to_string(index=False))
print(f"Rows with page 6316's chemical ({name_6316}): {int((df.jecfa_name == name_6316).sum())}, "
      f"at pages {pid[df.jecfa_name == name_6316].astype(int).tolist()}")
print("Variant, removing both rows of that chemical and page 5883:",
      len(df) - int((pid == 5883).sum()) - int((df.jecfa_name == name_6316).sum()))

reading  unique_pages  within_2_of_6549
     D1          6552             False
     D2          6550              True
     D3          6550              True
Rows with page 6316's chemical ((E)-N-[2-(1,3-BENZODIOXOL-5-YL)ETHYL]-3- (3,4-DIMETHOXYPHENYL)PROP-2-ENAMIDE): 2, at pages [6316, 6319]
Variant, removing both rows of that chemical and page 5883: 6549

expected = pd.Series({"URL": 100, "JECFA name": 100, "Chemical names": 82, "Synonyms": 79, "CAS number": 35,
                      "JECFA number": 71, "FEMA number": 65, "COE number": 46, "Functional class": 98,
                      "Evaluation year": 98, "Report": 97, "Report sourcelink": 97, "Tox monograph": 93,
                      "Tox monograph sourcelink": 93, "Specification": 92, "Specification sourcelink": 92,
                      "FAS": 93}, dtype=float)
fas_b = jecfa.fas_add_metadata(df.fas, df.tox_monograph)
cleaned = (pid != 5883) & ~((df.jecfa_name == name_6316) & df.jecfa_name.duplicated(keep="last"))
table = pd.DataFrame({"expected": expected, "tolerance": expected * 0.10})
for label, rows in [("all_6552", pd.Series(True, index=df.index)), ("cleaned_6550", cleaned)]:
    pa = present.loc[rows].mean() * 100
    table[f"A_{label}"] = [pa[jecfa.TABLE1_FIELDS[f]] for f in table.index]
    table[f"B_{label}"] = table[f"A_{label}"]
    table.loc["FAS", f"B_{label}"] = jecfa.present(fas_b[rows]).mean() * 100
table = table.round(2)
print(f"Rows used: all {len(df)}; cleaned {int(cleaned.sum())}")
print(table.to_string())
for c in [c for c in table.columns if c[:2] in ("A_", "B_")]:
    within = ((table[c] - table.expected).abs() <= table.tolerance + 1e-9)
    exact = (table[c].round(0) == table.expected)
    print(f"{c}: within tolerance {int(within.sum())} of 17 (outside: {table.index[~within].tolist()}); "
          f"equal to Table 1 when rounded to whole numbers {int(exact.sum())} of 17")
print("\nVariant: FAS counted as present wherever it is not NA (the 752 empty strings counted):",
      round(df.fas.notna().mean() * 100, 2), "%")

Rows used: all 6552; cleaned 6550
                          expected  tolerance  A_all_6552  B_all_6552  A_cleaned_6550  B_cleaned_6550
URL                          100.0       10.0      100.00      100.00          100.00          100.00
JECFA name                   100.0       10.0      100.00      100.00          100.00          100.00
Chemical names                82.0        8.2       82.57       82.57           82.60           82.60
Synonyms                      79.0        7.9       78.30       78.30           78.32           78.32
CAS number                    35.0        3.5       35.36       35.36           35.37           35.37
JECFA number                  71.0        7.1       70.80       70.80           70.82           70.82
FEMA number                   65.0        6.5       64.99       64.99           65.01           65.01
COE number                    46.0        4.6       46.26       46.26           46.27           46.27
Functional class              98.0        9.8       98.32       98.32           98.35           98.35
Evaluation year               98.0        9.8       98.20       98.20           98.23           98.23
Report                        97.0        9.7       97.62       97.62           97.65           97.65
Report sourcelink             97.0        9.7       97.62       97.62           97.65           97.65
Tox monograph                 93.0        9.3       92.86       92.86           92.89           92.89
Tox monograph sourcelink      93.0        9.3       92.86       92.86           92.89           92.89
Specification                 92.0        9.2       91.82       91.82           91.85           91.85
Specification sourcelink      92.0        9.2       91.82       91.82           91.85           91.85
FAS                           93.0        9.3       81.38       86.74           81.40           86.76
A_all_6552: within tolerance 16 of 17 (outside: ['FAS']); equal to Table 1 when rounded to whole numbers 12 of 17
B_all_6552: within tolerance 17 of 17 (outside: []); equal to Table 1 when rounded to whole numbers 12 of 17
A_cleaned_6550: within tolerance 16 of 17 (outside: ['FAS']); equal to Table 1 when rounded to whole numbers 12 of 17
B_cleaned_6550: within tolerance 17 of 17 (outside: []); equal to Table 1 when rounded to whole numbers 12 of 17

Variant: FAS counted as present wherever it is not NA (the 752 empty strings counted): 92.86 %

bins = jecfa.year_bin(df.evaluation_year)
counts = bins.value_counts().reindex(jecfa.YEAR_BINS)
known = int(bins.notna().sum())
res = pd.DataFrame({"count": counts, "share_of_known_pct": (counts / known * 100).round(2),
                    "expected_count": [2357, 3099, 778, 200], "expected_pct": [37, 48, 12, 3.1]})
res["count_tolerance"] = [236, 310, 78, 20]  # the claims file's tolerances
res["count_within"] = (res["count"] - res.expected_count).abs() <= res.count_tolerance
res["pct_within"] = (res.share_of_known_pct - res.expected_pct).abs() <= res.expected_pct * 0.1 + 1e-9
print(res.to_string())
unknown_y = int(df.evaluation_year.isna().sum())
print(f"Unknown year: {unknown_y} (expected 118, tolerance 12; within: {abs(unknown_y - 118) <= 12}); known {known}; "
      f"years outside 1958 to 2023: {int((bins.isna() & df.evaluation_year.notna()).sum())}")
print("Shares over all 6552 rows:", (counts / len(df) * 100).round(2).tolist())

             count  share_of_known_pct  expected_count  expected_pct  count_tolerance  count_within  pct_within
[1958-2000)   2357               36.63            2357          37.0              236          True        True
[2000-2010)   3099               48.17            3099          48.0              310          True        True
[2010-2020)    778               12.09             778          12.0               78          True        True
[2020-2023]    200                3.11             200           3.1               20          True        True
Unknown year: 118 (expected 118, tolerance 12; within: True); known 6434; years outside 1958 to 2023: 0
Shares over all 6552 rows: [35.97, 47.3, 11.87, 3.05]

cc = cls.value_counts().reindex(jecfa.CLASSES)
known_c = int(cls.notna().sum())
res = pd.DataFrame({"count": cc, "share_of_known_pct": (cc / known_c * 100).round(3),
                    "expected_count": [4635, 1453, 77, 277], "expected_pct": [72, 23, 1.2, 4.3]})
res["count_tolerance"] = [464, 145, 7.7, 28]  # the claims file's tolerances
res["count_within"] = (res["count"] - res.expected_count).abs() <= res.count_tolerance
res["pct_within"] = (res.share_of_known_pct - res.expected_pct).abs() <= res.expected_pct * 0.1 + 1e-9
print(res.to_string())
unknown_c = int(cls.isna().sum())
print(f"Unknown class: {unknown_c} (expected 110, tolerance 11; within: {abs(unknown_c - 110) <= 11}); known {known_c}; "
      f"strings matching none of the four names: {int((cls.isna() & df.functional_class.notna()).sum())}")
print("Largest class:", cc.idxmax())

                  count  share_of_known_pct  expected_count  expected_pct  count_tolerance  count_within  pct_within
leading_class                                                                                                       
Flavoring Agent    4635               71.95            4635          72.0            464.0          True        True
Food Additives     1453              22.555            1453          23.0            145.0          True        True
Food Contaminant     77               1.195              77           1.2              7.7          True        True
Veterinary Drug     277                 4.3             277           4.3             28.0          True        True
Unknown class: 110 (expected 110, tolerance 11; within: True); known 6442; strings matching none of the four names: 0
Largest class: Flavoring Agent

others = [c for c in df.columns if c not in ("url", "jecfa_name")]
name_only = ~present[others].any(axis=1)
for label, mask in [("unknown functional class", cls.isna()), ("unknown evaluation year", df.evaluation_year.isna())]:
    n, k = int(mask.sum()), int((mask & name_only).sum())
    print(f"{label}: {n} rows, {k} with only URL and JECFA name ({k / n * 100:.1f}%)")
    print("   fields present in the other", n - k, "rows:",
          {str(c): int(v) for c, v in present.loc[mask & ~name_only, others].sum().items() if v > 0})
    rest = df.index[mask & ~name_only]
    print("   their pages:", pid[rest].astype(int).tolist())
    print("   their evaluation years:", {("NA" if pd.isna(y) else int(y)): int(v) for y, v in year[rest].value_counts(dropna=False).sort_index().items()})
    print("   their functional classes:", {("NA" if pd.isna(c) else c): int(v) for c, v in cls[rest].value_counts(dropna=False).items()})
print("\nUnknown class and unknown year together:", int((cls.isna() & df.evaluation_year.isna()).sum()),
      "; rows with only URL and JECFA name in the whole table:", int(name_only.sum()))
print("Pages of the rows with only URL and JECFA name, from", int(pid[name_only].min()), "to", int(pid[name_only].max()))

unknown functional class: 110 rows, 95 with only URL and JECFA name (86.4%)
   fields present in the other 15 rows: {'report': 15, 'report_sourcelink': 15, 'tox_monograph': 14, 'tox_monograph_sourcelink': 14, 'specification': 13, 'specification_sourcelink': 13, 'synonyms': 1, 'cas_number': 15, 'evaluation_year': 15, 'chemical_names': 14, 'jecfa_number': 15, 'fema_number': 13, 'fas': 1}
   their pages: [6025, 6385, 6392, 6417, 6426, 6439, 6440, 6441, 6442, 6445, 6446, 6447, 6448, 6450, 6554]
   their evaluation years: {2008: 13, 2010: 1, 2023: 1}
   their functional classes: {'NA': 15}
unknown evaluation year: 118 rows, 95 with only URL and JECFA name (80.5%)
   fields present in the other 23 rows: {'synonyms': 14, 'cas_number': 6, 'functional_class': 23, 'chemical_names': 15, 'jecfa_number': 1, 'fema_number': 1}
   their pages: [699, 864, 1233, 1630, 1797, 1861, 2691, 2692, 2693, 2739, 2851, 5575, 6476, 6496, 6497, 6498, 6499, 6500, 6501, 6502, 6503, 6506, 6533]
   their evaluation years: {'NA': 23}
   their functional classes: {'Food Additives': 13, 'Food Contaminant': 9, 'Flavoring Agent': 1}

Unknown class and unknown year together: 95 ; rows with only URL and JECFA name in the whole table: 95
Pages of the rows with only URL and JECFA name, from 219 to 6316

named = ["40", "42", "44", "46", "48", "50", "52"]
for label, fas in [("A (deposited fas)", df.fas), ("B (after add_metadata.R)", fas_b)]:
    has = jecfa.present(fas)
    vc = fas[has].value_counts()
    in_named = int(fas[has].isin(named).sum())
    f1, f2 = in_named / len(df) * 100, in_named / int(has.sum()) * 100
    top7 = vc.index[:7].tolist()
    print(f"Reading {label}: {int(has.sum())} rows with a FAS code, {vc.size} distinct codes (at least 77: {vc.size >= 77})")
    print(f"   rows in the seven named codes: {in_named}; F1 {f1:.2f}% (within 45 to 55: {abs(f1 - 50) <= 5}), "
          f"F2 {f2:.2f}% (within: {abs(f2 - 50) <= 5})")
    print(f"   seven most frequent: {top7}; the named seven: {set(top7) == set(named)}; "
          f"ranks of the named codes: {[int(vc.index.get_loc(c)) + 1 for c in named]}")
    ranked = pd.DataFrame({"code": vc.index[:12], "rows": vc.values[:12],
                           "cum_share_F2_pct": (vc.cumsum() / has.sum() * 100).round(1).values[:12],
                           "cum_share_F1_pct": (vc.cumsum() / len(df) * 100).round(1).values[:12]})
    print(ranked.to_string(index=False))
    integer_codes = sorted({int(c) for c in vc.index if c.isdigit() and 1 <= int(c) <= 85})
    print(f"   distinct values that are whole numbers from 1 to 85: {len(integer_codes)} (at least 77: "
          f"{len(integer_codes) >= 77}); missing: {sorted(set(range(1, 86)) - set(integer_codes))}")
    print(f"   other values: {sorted(c for c in vc.index if not (c.isdigit() and 1 <= int(c) <= 85))}\n")

Reading A (deposited fas): 5332 rows with a FAS code, 81 distinct codes (at least 77: True)
   rows in the seven named codes: 3036; F1 46.34% (within 45 to 55: True), F2 56.94% (within: False)
   seven most frequent: ['48', '44', '50', '46', '42', '40', '59']; the named seven: False; ranks of the named codes: [6, 5, 2, 4, 1, 3, 9]
       code  rows  cum_share_F2_pct  cum_share_F1_pct
         48   607              11.4               9.3
         44   490              20.6              16.7
         50   477              29.5              24.0
         46   407              37.2              30.2
         42   399              44.6              36.3
         40   370              51.6              42.0
         59   351              58.2              47.3
         54   287              63.5              51.7
         52   286              68.9              56.1
          5   172              72.1              58.7
77 JECFA 86   131              74.6              60.7
         17   122              76.9              62.6
   distinct values that are whole numbers from 1 to 85: 66 (at least 77: False); missing: [2, 3, 4, 7, 9, 11, 61, 63, 66, 67, 68, 73, 75, 76, 77, 79, 81, 84, 85]
   other values: ['24.65', '617', '65 JECFA 74', '67 JECFA 76', '67.29', '68 JECFA 77', '68.33', '70.36', '70.39', '71 JECFA 80', '73 JECFA 82', '75 JECFA 84', '77 JECFA 86', '80 JECFA 89', '86']

Reading B (after add_metadata.R): 5683 rows with a FAS code, 73 distinct codes (at least 77: False)
   rows in the seven named codes: 3036; F1 46.34% (within 45 to 55: True), F2 53.42% (within: True)
   seven most frequent: ['48', '44', '50', '46', '42', '40', '60']; the named seven: False; ranks of the named codes: [6, 5, 2, 4, 1, 3, 10]
code  rows  cum_share_F2_pct  cum_share_F1_pct
  48   607              10.7               9.3
  44   490              19.3              16.7
  50   477              27.7              24.0
  46   407              34.9              30.2
  42   399              41.9              36.3
  40   370              48.4              42.0
  60   353              54.6              47.4
  59   351              60.8              52.7
  54   287              65.8              57.1
  52   286              70.9              61.5
   5   172              73.9              64.1
  67   133              76.2              66.1
   distinct values that are whole numbers from 1 to 85: 71 (at least 77: False); missing: [2, 3, 4, 7, 9, 11, 61, 63, 66, 76, 79, 81, 84, 85]
   other values: ['617', '86']

with_name = int(present.jecfa_name.sum())
print(f"Rows with a JECFA name present: {with_name} (expected 6551, tolerance 655; within: {abs(with_name - 6551) <= 655})")
for r, v in zip(readings.reading, readings.unique_pages):
    print(f"After cleaning, reading {r}: {v}; difference from 6547: {v - 6547} (within 2: {abs(v - 6547) <= 2})")
print("The paper's own numbers: 6551 - 2 = 6549, a difference of 2 from 6547")
print("\nRows with a JECFA name other than TEST:", int((present.jecfa_name & (df.jecfa_name.str.strip().str.upper() != "TEST")).sum()))

Rows with a JECFA name present: 6552 (expected 6551, tolerance 655; within: True)
After cleaning, reading D1: 6552; difference from 6547: 5 (within 2: False)
After cleaning, reading D2: 6550; difference from 6547: 3 (within 2: False)
After cleaning, reading D3: 6550; difference from 6547: 3 (within 2: False)
The paper's own numbers: 6551 - 2 = 6549, a difference of 2 from 6547

Rows with a JECFA name other than TEST: 6551

Claim	Tested
data-records-rows (link): 6552 rows with the fields of Table 1	tested
unique-pages: 6549 unique pages after removing a duplicate and a test page	tested
table1-availability: share of pages with each field	tested
table2-evaluation-year: evaluations by year bin	tested
table2-functional-class: substances by functional class	tested
unknowns-name-only: unknowns have only a JECFA name	tested
fas-codes: FAS 40 to 52 (even) cover 50% of records	tested
validation-failures: discrepancies in the 700-page manual check	blocked (data_missing)
portal-search-count: portal count 6547 in line with pages with a JECFA name	tested (dataset's part)

Claim	Outcome
data-records-rows (link)	reproduced
unique-pages	reproduced
table1-availability	reproduced
table2-evaluation-year	reproduced
table2-functional-class	reproduced
unknowns-name-only	not reproduced
fas-codes	partially reproduced
validation-failures	blocked (data_missing)
portal-search-count	not reproduced (dataset's part only; the portal count is untested)

Reproducing Ocagli et al. (2024) from Figshare item 26877211¶

Data check¶

data-records-rows (link)¶

unique-pages¶

table1-availability¶

table2-evaluation-year¶

table2-functional-class¶

unknowns-name-only¶

fas-codes¶

validation-failures¶

portal-search-count¶

Summary¶