DOI: 10.5281/zenodo.5758925
title: HMDB Clean

description: This is a cleaned and condensed version of the HMDB XML metabolites file from https://hmdb.ca/downloads, current version 5.0. The original zipped file (~1GB) extracts to 6.49 GB xml file. The full is then cleaned and then only small sub-files are exported. This is too big to extract from the zipped file on the server, and gets clipped (to about 4GB). As a result, the XML to DF function fails because of missing data. This repo containing 4 files is an alternative pointer for only the data needed. Code to process it from the hmdb_full file is pasted in the notes below. These 4 files are used on www.datadrivenhypothesis.org
description: #read current release information to set parameters for download source(here::here("code", "current_release.R")) #LOAD BIG data temp &lt;- tempfile() download.file(metabolites_url, temp) hmdb_full &lt;- xmlToDataFrame(unzip(temp)) %&gt;% #extracting XML took a long time for a 4GB XML file! janitor::clean_names() unlink(temp) unlink("hmdb_metabolites.xml") #hmdb_full &lt;- xmlToDataFrame(here::here("tmp", "hmdb_metabolites.xml")) %&gt;% janitor::clean_names() #for getting metabolite classes class_finder &lt;- function(string) { if(stringr::str_detect(string, "class\\b")) { class_compounds &lt;- function(string) { tmp &lt;- purrr::map(string, ~ unlist(str_split(., pattern = "\\."))) %&gt;% purrr::map(., ~keep(.x, ~ str_detect(.x, "class"))) %&gt;% flatten_chr(.) class &lt;- stringr::str_split(tmp, "class of organic compounds known as ") return(stringr::str_to_sentence(class[[1]][[2]]))} tryCatch(class_compounds(string), error = function(x){"Organic compound"}) } else if(stringr::str_detect(string, "classified\\b")) { classified_compounds &lt;- function(string) { tmp &lt;- purrr::map(string, ~ unlist(str_split(., pattern = "\\."))) %&gt;% purrr::map(., ~keep(.x, ~ str_detect(.x, "classified"))) %&gt;% flatten_chr(.) class &lt;- stringr::str_split(tmp, "classified as a member of the ") return(stringr::str_to_sentence(class[[1]][[2]]))} tryCatch(classified_compounds(string), error = function(x){"Organic compound"}) } else if(stringr::str_detect(string, "steroid")) { return("Steroid hormone") } else if(stringr::str_detect(string, "nucleoside")) { return("Nucleoside") } else if(stringr::str_detect(string, "acid")) { return("Organic acid") } else { return("Organic compound") } } #purrr::map(hmdb_class$description, ~ class_finder(.)) hmdb_class &lt;- hmdb_full %&gt;% #slice(1:10) %&gt;% #for testing dplyr::select(name, description) %&gt;% dplyr::mutate(class = purrr::map(description, ~ class_finder(.))) %&gt;% dplyr::select(-description) hmdb_names &lt;- hmdb_full %&gt;% #slice(1:10) %&gt;% #for testing dplyr::select(name, synonyms, cid = pubchem_compound_id) %&gt;% dplyr::left_join(hmdb_class, by = "name") hmdb_meta &lt;- hmdb_full %&gt;% dplyr::select(name, synonyms, cid = pubchem_compound_id, accession, description, chemical_formula, average_molecular_weight, wikipedia_id) %&gt;% dplyr::left_join(hmdb_class, by = "name") temp &lt;- tempfile() download.file(metabolite_proteins_url, temp) hmdb_proteins_raw &lt;- XML::xmlToDataFrame(unzip(temp)) %&gt;% janitor::clean_names() unlink(temp) unlink("hmdb_proteins.xml") censor_proteins &lt;- c("") censor_metabolites &lt;- c("","Water", "Hydrogen Ion") hmdb_proteins_long &lt;- hmdb_proteins_raw %&gt;% #dplyr::slice(1:20) %&gt;% #for testing dplyr::mutate(metabolite_associations = stringr::str_replace_all(metabolite_associations, "HMDB", "\\.HMDB")) %&gt;% tidyr::separate_rows(metabolite_associations, sep = "\\.") %&gt;% dplyr::filter(metabolite_associations != "") %&gt;% tidyr::separate(metabolite_associations, into = c("metabolite_accession", "metabolite_name"), sep = 11) %&gt;% dplyr::select(gene_name, metabolite_name, gene_accession = accession, metabolite_accession) %&gt;% dplyr::filter(!gene_name %in% censor_proteins, !metabolite_name %in% censor_metabolites, stringr::str_detect(metabolite_accession, "HMDB")) # tmp &lt;- "Water" # tmp &lt;- "Glucose-6-Phosphate" # tmp &lt;- "3-Carbamoyl-2-phenylpropionaldehyde" # tmp &lt;- "-1(11),7,9-trien-11-ol" # metabolite_string &lt;- "R-95913" #collapse metabolite name if it's long collapse_metabolites &lt;- function(metabolite_string) { # #skip if missing # if(stringr::str_length(metabolite_string) == 0 | is.na(metabolite_string)) { # return(metabolite_string) # } #skip if too short if(stringr::str_length(metabolite_string) &lt; 7) { return(metabolite_string) } #get first word new_metabolite_string &lt;- stringr::str_extract(metabolite_string, "[[:alpha:]]\\w+") #alpha omits numbers and punct, w gets word character, + gets one or more #if new_string is NA b/c it has a bizarre name (like a drug name), code breaks; so return original string if(is.na(new_metabolite_string)) { return(metabolite_string) } #add plus to indicate that it got collapsed if(metabolite_string != new_metabolite_string) { new_metabolite_string &lt;- glue::glue("{new_metabolite_string} +")} return(new_metabolite_string) } # collapse_metabolites(tmp) # simplify_metabolites &lt;- function(df) { # # if (nrow(df) &lt; 20) { # # return(df) # # } # new_df &lt;- # df %&gt;% # mutate(metabolite_name_simple = map_chr(.x = df[[2]], .f = collapse_metabolites)) # return(new_df) # } hmdb_proteins_full &lt;- hmdb_proteins_long %&gt;% dplyr::group_by(fav_gene = gene_name) %&gt;% tidyr::nest() %&gt;% dplyr::filter(!fav_gene %in% censor_proteins) %&gt;% dplyr::mutate(original_num_rows = map_int(data, nrow)) hmdb_proteins &lt;- hmdb_proteins_long %&gt;% dplyr::mutate(metabolite_name_simple = map_chr(metabolite_name, collapse_metabolites)) %&gt;% dplyr::distinct(gene_name, metabolite_name_simple, .keep_all = TRUE) %&gt;% dplyr::group_by(fav_gene = gene_name) %&gt;% tidyr::nest() %&gt;% dplyr::filter(!fav_gene %in% censor_proteins) %&gt;% dplyr::mutate(num_rows = map_int(data, nrow)) %&gt;% dplyr::left_join(hmdb_proteins_full, by = "fav_gene", suffix = c("_collapsed", "_original")) %&gt;% dplyr::arrange(desc(num_rows)) #colnames(hmdb_proteins) are fav_gene, data_collapsed, num_rows, data_original, original_num_rows hmdb_metabolites &lt;- hmdb_proteins_long %&gt;% #dplyr::mutate(metabolite_name = stringr::str_to_lower(metabolite_name)) %&gt;% dplyr::group_by(fav_metabolite = metabolite_name) %&gt;% tidyr::nest() %&gt;% dplyr::filter(!fav_metabolite %in% censor_metabolites) %&gt;% dplyr::mutate(num_rows = map_int(data, nrow)) %&gt;% dplyr::arrange(desc(num_rows)) #save files #saveRDS(hmdb_full, file = here::here("data", paste0(release, "_hmdb_full.Rds"))) saveRDS(hmdb_names, file = here::here("data", paste0(release, "_hmdb_names.Rds"))) saveRDS(hmdb_meta, file = here::here("data", paste0(release, "_hmdb_meta.Rds"))) saveRDS(hmdb_proteins, file = here::here("data", paste0(release, "_hmdb_proteins.Rds"))) saveRDS(hmdb_metabolites, file = here::here("data", paste0(release, "_hmdb_metabolites.Rds")))