Creating a Flat File Version of the MIDRC Data Dictionary

---

This notebook will walk you through converting MIDRC's data node-based dictionary into a flat-file version.

Getting started

---

Setting up directory and expansion path

#Set directory and expansion path
import wget
import sys, os, webbrowser

home_dir = ""  #set home directory here (eg. /Users/username)
demo_dir = f"{home_dir}/Desktop"

os.chdir(demo_dir)

Import libraries

import gen3
import pandas as pd
import requests

from gen3.submission import Gen3Submission
from gen3.auth import Gen3Auth
from gen3.index import Gen3Index
from IPython.display import display
from gen3.query import Gen3Query

Set up local variables and define Gen3 classes

The location of your MIDRC credential should be downloaded from https://data.midrc.org/identity by clicking the "Create API key" button and saving the credentials.json locally to your downloads folder.

api = "https://data.midrc.org"
cred = f"{home_dir}/Downloads/midrc-credentials.json"
auth = Gen3Auth(api, refresh_file=cred)
sub = Gen3Submission(api, auth)
query = Gen3Query(auth)

Accessing the data dictionary

#Viewing list of endpoints
headers = {"Authorization": f"Bearer {auth._access_token}"}

url = f"{api}/api/v0/submission/_dictionary"
resp = requests.get(url, headers=headers)

dictionary = resp.json()

dictionary

url = f"{api}/api/v0/submission/_dictionary/_all"
resp = requests.get(url, headers=headers)

#Will error out if incomplete values are generated
resp.raise_for_status()

dictionary = resp.json()

rows = []

#Flatten dictionary
for node_name, node_data in dictionary.items():
    if not isinstance(node_data, dict):
        continue

    props = node_data.get("properties", {})
    required = node_data.get("required", [])
    links = node_data.get("links", [])

    #Build link map
    link_map = {}
    for link in links or []:
        targets = link.get("target_type", [])
        if isinstance(targets, list):
            for target in targets:
                link_map[link.get("name")] = target

    for prop_name, prop_data in props.items():
        if not isinstance(prop_data, dict):
            continue

        prop_type = prop_data.get("type")
        if isinstance(prop_type, list):
            prop_type = ", ".join(prop_type)

        prop_enum = prop_data.get("enum")
        if isinstance(prop_enum, list):
            prop_enum = ", ".join(prop_enum)

        row = {
            "node": node_name,
            "property": prop_name,
            "type": prop_type or "",
            "description": prop_data.get("description", ""),
            "required": prop_name in required if isinstance(required, list) else False,
            "enum": prop_enum or "",
            "link_to": link_map.get(prop_name, None)
        }

        rows.append(row)

df = pd.DataFrame(rows)
df.head()

#Viewing raw/unflattened nodes
for node_name, node_data in dictionary.items():
    props = node_data.get("properties", {})
    for prop_name, prop_data in props.items():
        term = prop_data.get("term")
        if term:
            print(node_name, prop_name, type(term), term)

A quick view of the standards and the summary statistics

---

Taking a preliminary view of the data dictionary

#List all properties in df
all_properties = df['property'].unique()
list(all_properties)

Basic summary statistics of data model

In this section we will generate simple summary statistics of the model. After running the below cells we can see that that there are 30 unique nodes with 214 unique properties in the data model. The nodes with the larges number of variables will be imaging series nodes since they include DICOM standard tags.

#General structure
df.info()
df.describe(include="all")

This cell displays the number of properties per node. As described above, we can see that the nodes with the most properties are the imaging series nodes with the most containing 52 properties.

#Count of properties per node (all nodes)
df.groupby("node")["property"].count().sort_values(ascending=False)

#Removing system nodes from count
exclude = ["data_release", "metaschema", "root"]

(
    df[~df["node"].isin(exclude)]
      .groupby("node")["property"]
      .count()
      .sort_values(ascending=False)
)

Lastly, the cell below shows a table consisting of a few remaining summary statistics for the 5 nodes with the most properties.

#Summary statisitics of nodes
summary = df.groupby("node").agg(
    n_properties=("property", "count"),
    n_required=("required", "sum"),
    pct_required=("required", "mean"),
    n_enum=("enum", lambda x: (x != "").sum()),
    n_links=("link_to", lambda x: x.notna().sum())
).sort_values("n_properties", ascending=False)

summary.head()

Generating a flat file version of the standards

---

#Repeating process but including standards
rows = []

for node_name, node_data in dictionary.items():
    if not isinstance(node_data, dict):
        continue

    props = node_data.get("properties", {})
    required = node_data.get("required", [])
    links = node_data.get("links", [])

    link_map = {}
    for link in links or []:
        targets = link.get("target_type", [])
        if isinstance(targets, list):
            for target in targets:
                link_map[link.get("name")] = target

    for prop_name, prop_data in props.items():
        if not isinstance(prop_data, dict):
            continue

        prop_type = prop_data.get("type")
        if isinstance(prop_type, list):
            prop_type = ", ".join(prop_type)

        prop_enum = prop_data.get("enum")
        if isinstance(prop_enum, list):
            prop_enum = ", ".join(prop_enum)

        #Including standards here
        term_def = prop_data.get("termDef", {})

        term_id = term_def.get("cde_id") if isinstance(term_def, dict) else None
        term_url = term_def.get("term_url") if isinstance(term_def, dict) else None
        term_source = term_def.get("source") if isinstance(term_def, dict) else None

        row = {
            "node": node_name,
            "property": prop_name,
            "type": prop_type or "",
            "description": prop_data.get("description", ""),
            "required": prop_name in required if isinstance(required, list) else False,
            "enum": prop_enum or "",
            "link_to": link_map.get(prop_name, None),
            "term_id": term_id,
            "term_url": term_url,
            "term_source": term_source
        }

        rows.append(row)

df = pd.DataFrame(rows)

#Creating list of standards
standards_df = df[
    (df["enum"] != "") |
    (df["term_id"].notna()) |
    (df["term_url"].notna())
].copy()

standards_df = standards_df[
    ["node", "property", "type", "enum", "description"] #"term_id", "term_url", "term_source" will be generated later
]

standards_df.sort_values(["node", "property"]).head(20)

#Display all standards for browsing
from IPython.display import display, HTML

display(HTML(standards_df.to_html(escape=False)))

#Save file
standards_df.to_csv("MIDRC_flatfile_dict.csv", index=False)

Generating list of links to standards

---

#Gathering links
import pandas as pd

rows = []

def extract_termdefs(node_name, current_dict):
    for prop_name, prop_data in current_dict.items():
        if isinstance(prop_data, dict):
            # If termDef exists, capture it
            term_def = prop_data.get("termDef")
            if isinstance(term_def, dict):
                rows.append({
                    "node": node_name,
                    "property": prop_name,
                    "term_id": term_def.get("cde_id"),
                    "term_name": term_def.get("term"),
                    "term_source": term_def.get("source"),
                    "term_url": term_def.get("term_url"),
                    "description": prop_data.get("description") if isinstance(prop_data.get("description"), str) else ""
                })
            # Recurse in case there are nested dicts
            extract_termdefs(node_name, prop_data)

# Run over all top-level nodes
for node_name, node_data in dictionary.items():
    if isinstance(node_data, dict):
        extract_termdefs(node_name, node_data)

links_df = pd.DataFrame(rows)
links_df.head(10)

#Generate list of clickable links to standards for browsing
def make_clickable(url):
    if pd.isna(url):
        return ""
    return f'<a href="{url}" target="_blank">{url}</a>'

links_df["term_url"] = links_df["term_url"].apply(make_clickable)

display(HTML(links_df.to_html(escape=False)))

#Save file
links_df.to_csv("MIDRC_flatfile_links.csv", index=False)

Combining list of standards and links into one dataframe

---

combined_df = pd.concat([standards_df, links_df[['node', 'property', 'term_url', 'description']]]).reset_index()
display(combined_df)

#Save combined file
combined_df.to_csv("MIDRC_master_flatfile.csv", index=False)