# National Animal Nutrition Program (https://animalnutrition.org) # Modeling Committee (R code written by Veridiana Daley) # The objective of this R script is to import, transpose, and export the dataset from the NANP website # Clean up your environment. rm(list = ls(all = TRUE)) # remove all R object graphics.off() # Delete all open graphics # Install the libraries install.packages("readr") install.packages("reshape2") ############################# Importing Data to R ########################################################## # Download the data from the NANP website into a folder in your computer, unzip the files, and import it into the R ############################################################################################################# # Create a new folder named NANP on your computer # Set working directory (specific location of your folder) setwd("C:/Users/veridi7/Downloads/csv-tables (2)/csv-tables") # Attention: change this example using your working directory. In R you should use a forward slash # Download the NANP database as a .zip file from the NANP website (https://animalnutrition.org/modeling-database) # Frequently the .zip file is on the "Downloads" folder in your PC # Save the zipped file into the NANP folder (make sure if this file is saved on your folder) # Extract .zip files into the NANP folder (.csv files) ####### 1. Only RUN the code below to read the CSV files if you downloaded "All Database" (You Did Not Use "Filter" Option) library(readr) StudyDescriptors <- read_csv("study_descriptors.csv", col_types = cols(VarValue = col_character())) DietaryIngredients <- read_csv("dietary_ingredients.csv", col_types = cols(VarValue = col_character())) DietaryNutrients <- read_csv("dietary_nutrients.csv", col_types = cols(VarValue = col_character())) Subjects <- read_csv("subjects.csv", col_types = cols(VarValue = col_character())) Performance <- read_csv("performance_datas.csv", col_types = cols(VarValue = col_character())) Infusion <- read_csv("infusions.csv", col_types = cols(VarValue = col_character())) Invitro <- read_csv("in_vitro_datas.csv", col_types = cols(VarValue = col_character())) Genome <- read_csv("genome_transcripts.csv", col_types = cols(VarValue = col_character())) ####### 2. Only RUN the code below to read the CSV files if you downloaded "filtered data" (After a search on the NANP database, You Used "Filter" Option) library(readr) StudyDescriptors <- read_csv("studydescriptors.csv", col_types = cols(VarValue = col_character())) DietaryIngredients <- read_csv("dietaryingredients.csv", col_types = cols(VarValue = col_character())) DietaryNutrients <- read_csv("dietarynutrients.csv", col_types = cols(VarValue = col_character())) Subjects <- read_csv("subjects.csv", col_types = cols(VarValue = col_character())) Performance <- read_csv("performances.csv", col_types = cols(VarValue = col_character())) Infusion <- read_csv("infusions.csv", col_types = cols(VarValue = col_character())) Invitro <- read_csv("invitrodata.csv", col_types = cols(VarValue = col_character())) Genome <- read_csv("genome.csv", col_types = cols(VarValue = col_character())) ############################# Transposing (rotate) data in R ######################################################## # You may want to transpose the datasets, in this case, run the script below. # All Transposed files are named "Trs" + Table name ##################################################################################################################### # Loading reshape2 package library(reshape2) # 1) Transpose Study Descriptors # Unique ID (test the data for unique ID) StudyDescriptors <- unique(StudyDescriptors) # Transpose the data Trs_StudyDescriptors <- dcast(StudyDescriptors, DataSet + PubID + TrialID ~ VarName, value.var = 'VarValue') # 2) Transpose Dietary Ingredients (Not working once the UID and RepUID is missing from the website) # Unique ID (test the data for unique ID) DietaryIngredients <- unique(DietaryIngredients) # Transpose the data Trs_DietaryIngredients <- dcast(DietaryIngredients, DataSet + TrialID + PubID + TrtID + UID + RepUID ~ VarName, value.var = 'Varvalue') # 3) Transpose Dietary Nutrients # Unique ID (test the data for unique ID) DietaryNutrients <- unique(DietaryNutrients) # Transpose the data Trs_DietaryNutrients <- dcast(DietaryNutrients, DataSet + PubID + TrialID + TrtID + SubjectID ~ VarName, value.var = 'Varvalue') # 4) Transpose Subjects # Unique ID (test the data for unique ID) Subjects <- unique(Subjects) # Transpose the data Trs_Subjects <- dcast(Subjects, DataSet + PubID + TrialID + TrtID + SubjectID ~ VarName, value.var = 'Varvalue') # 5) Transpose Performance # Unique ID (test the data for unique ID) Performance <- unique(Performance) # Transpose the data Trs_Performance <- dcast(Performance, DataSet + PubID + TrialID + TrtID + SubjectID + Day_Sample + Time_Sample ~ VarName, value.var = 'VarValue') # 6) Transpose Infusion # Unique ID (test the data for unique ID) Infusion <- unique(Infusion) # Transpose the data Trs_Infusion <- dcast(Infusion, DataSet + PubID + TrialID + TrtID + SubjectID + InfusionLocation + DayofPeriodStart + DayofPeriodStop + TimeofDayStart + TimeofDayStop ~ VarName, value.var = 'VarValue') # 7) Transpose In vitro (No data uploaded in this moment) # Unique ID (test the data for unique ID) Invitro <- unique(Invitro) # Transpose the data Trs_Invitro <- dcast(Invitro, DataSet + PubID + TrialID + TrtID + SubjectID + PlateID + WellID + SubTrtID + DaySampleofPeriod_InVivo + DaySampleofPeriod_InVitro + TimeSampleofPeriod_InVivo + TimeSampleofPeriod_InVitro ~ VarName, value.var = 'VarValue') # 8) Transpose Genome (No data uploaded in this moment) # Unique ID (test the data for unique ID) Genome <- unique(Genome ) # Transpose the data Trs_Genome <- dcast(Genome, DataSet + PubID + TrialID + TrtID + SubjectID + PlateID + WellID + SubTrtID + DaySampleofPeriod_InVivo + DaySampleofPeriod_InVitro + Time_Sample_InVivo + Time_Sample_InVitro ~ VarName, value.var = 'VarValue') ############################# Export the Transposed Data ################################################# # Transposed (rotated) data can be saved into your NANP folder. # All Transposed files are named "Trs" + Table name ############################################################################################### # Set your working directory (the folder where the files will be saved). In R you should use a forward slash setwd("C:/Users/veridi7/Desktop/NANP Downloaded data") # Attention: Change this example with your working directory # Export the transposed data in CSV files (Trs_ = transposed data) write.csv(Trs_StudyDescriptors, file = "Trs_StudyDescriptors.csv",row.names=FALSE, na="") write.csv(Trs_DietaryIngredients, file = "Trs_DietaryIngredients.csv",row.names=FALSE, na="") write.csv(Trs_DietaryNutrients, file = "Trs_DietaryNutrients.csv",row.names=FALSE, na="") write.csv(Trs_Subjects, file = "Trs_Subjects.csv",row.names=FALSE, na="") write.csv(Trs_Performance, file = "Trs_Performance.csv",row.names=FALSE, na="") write.csv(Trs_Infusion, file = "Trs_Infusion.csv",row.names=FALSE, na="") write.csv(Trs_Invitro, file = "Trs_Invitro.csv",row.names=FALSE, na="") write.csv(Trs_Genome , file = "Trs_Genome.csv",row.names=FALSE, na="") ############################# Summary of the Transposed Data ################################################# # Observe the summary of each tables in the "Viewer" R page ############################################################################################### install.packages("summarytools") library(summarytools) Trs_Performance <- data.frame(lapply(Trs_Performance, function(x) as.numeric(as.character(x)))) # transform data as numeric view(dfSummary(Trs_Performance)) # See output in the R Viewer