Read in Data and Inspect
- Use readLines() because this file is not a conventional CSV.
raw_biosample <- readLines("biosample_result (1).txt")
head(raw_biosample, 20)
## [1] "1: american gut project; 10317.X00185902\t"
## [2] "Identifiers: BioSample: SAMEA112990854; SRA: ERS14985877\t"
## [3] "Organism: human gut metagenome\t"
## [4] "Attributes:\t"
## [5] " /ENA-CHECKLIST\tERC000011"
## [6] " /ENA-FIRST-PUBLIC\t4/28/2023"
## [7] " /ENA-LAST-UPDATE\t4/28/2023"
## [8] " /External Id\tSAMEA112990854"
## [9] " /INSDC center alias\tUCSDMI"
## [10] " /INSDC center name\tUniversity of California San Diego Microbiome Initiative"
## [11] " /INSDC first public\t2023-04-28T16:20:26Z"
## [12] " /INSDC last update\t2023-04-28T16:20:26Z"
## [13] " /INSDC status\tpublic"
## [14] " /Submitter Id\tqiita_sid_10317:10317.X00185902"
## [15] " /acid_reflux\ti do not have this condition"
## [16] " /acne_medication\tno"
## [17] " /acne_medication_otc\tno"
## [18] " /add_adhd\ti do not have this condition"
## [19] " /age_cat\t30s"
## [20] " /alcohol_consumption\tyes"
length(raw_biosample)
## [1] 331
Locate the Beginning and End of the Attributes Section
attributes_start <- grep("Attributes:", raw_biosample)
attributes_end <- grep("Description:", raw_biosample)
biosample <- raw_biosample[
(attributes_start + 1):(attributes_end - 1)
]
head(biosample, 20)
## [1] " /ENA-CHECKLIST\tERC000011"
## [2] " /ENA-FIRST-PUBLIC\t4/28/2023"
## [3] " /ENA-LAST-UPDATE\t4/28/2023"
## [4] " /External Id\tSAMEA112990854"
## [5] " /INSDC center alias\tUCSDMI"
## [6] " /INSDC center name\tUniversity of California San Diego Microbiome Initiative"
## [7] " /INSDC first public\t2023-04-28T16:20:26Z"
## [8] " /INSDC last update\t2023-04-28T16:20:26Z"
## [9] " /INSDC status\tpublic"
## [10] " /Submitter Id\tqiita_sid_10317:10317.X00185902"
## [11] " /acid_reflux\ti do not have this condition"
## [12] " /acne_medication\tno"
## [13] " /acne_medication_otc\tno"
## [14] " /add_adhd\ti do not have this condition"
## [15] " /age_cat\t30s"
## [16] " /alcohol_consumption\tyes"
## [17] " /alcohol_frequency\toccasionally (1-2 times/week)"
## [18] " /alcohol_types_beercider\tTRUE"
## [19] " /alcohol_types_red_wine\tTRUE"
## [20] " /alcohol_types_sour_beers\tTRUE"
Tidy the data
- Remove “/” and the unnecessary spaces
biosample <- trimws(biosample)
biosample <- sub("^/", "", biosample)
- Split attributes and responses and turn into rows with rbind
biosample <- strsplit(
biosample,
"\t"
)
biosample <- do.call(
rbind,
biosample
)
Create a dataframe and export
biosample <- as.data.frame(
biosample,
stringsAsFactors = FALSE
)
names(biosample) <- c(
"Attribute",
"Response"
)
head(biosample, 20)
## Attribute
## 1 ENA-CHECKLIST
## 2 ENA-FIRST-PUBLIC
## 3 ENA-LAST-UPDATE
## 4 External Id
## 5 INSDC center alias
## 6 INSDC center name
## 7 INSDC first public
## 8 INSDC last update
## 9 INSDC status
## 10 Submitter Id
## 11 acid_reflux
## 12 acne_medication
## 13 acne_medication_otc
## 14 add_adhd
## 15 age_cat
## 16 alcohol_consumption
## 17 alcohol_frequency
## 18 alcohol_types_beercider
## 19 alcohol_types_red_wine
## 20 alcohol_types_sour_beers
## Response
## 1 ERC000011
## 2 4/28/2023
## 3 4/28/2023
## 4 SAMEA112990854
## 5 UCSDMI
## 6 University of California San Diego Microbiome Initiative
## 7 2023-04-28T16:20:26Z
## 8 2023-04-28T16:20:26Z
## 9 public
## 10 qiita_sid_10317:10317.X00185902
## 11 i do not have this condition
## 12 no
## 13 no
## 14 i do not have this condition
## 15 30s
## 16 yes
## 17 occasionally (1-2 times/week)
## 18 TRUE
## 19 TRUE
## 20 TRUE
write.csv(
biosample,
file = "American_Gut_Biosample.csv",
row.names = FALSE
)