<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/parse-an-online-table-into-an-r-dataframe-westgards-biological-variation-database/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://labrtorian.com/2017/08/14/parse-an-online-table-into-an-r-dataframe-westgards-biological-variation-database/"> The Lab-R-torian</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<h2>Background</h2>
<p>From time to time I have wanted to bring an online table into an R dataframe. While in principle, the data can be cut and paste into Excel, sometimes the table is very large and sometimes the columns get goofed up in the process. Fortunately, there are a number of R tools for accomplishing this. I am just going to show one approach using the <a rel="nofollow" href="https://cran.r-project.org/web/packages/rvest/index.html" target="_blank">rvest</a> package. The rvest package also makes it possible to interact with forms on webpages to request specific material which can then be scraped. I think you will see the potential if you look <a rel="nofollow" href="https://stat4701.github.io/edav/2015/04/02/rvest_tutorial/" target="_blank">here</a>.</p>
<p>In our (simple) case, we will apply this process to Westgard's desirable assay specifications as shown on his <a rel="nofollow" href="https://www.westgard.com/biodatabase1.htm" target="_blank">website</a>. The goal is to parse out the biological variation tables, get them into a dataframe and the write to csv or xlsx.</p>
<h2>Reading in the Data</h2>
<p>The first thing to do is to load the <code>rvest</code> and <code>httr</code> packages and define an html session with the <code>html_session()</code> function.</p>

<pre>library(rvest)
library(httr)
wg &lt;- html_session(&quot;https://www.westgard.com/biodatabase1.htm&quot;, user_agent(&quot;LabRtorian&quot;))</pre>

<p>Now looking at the <a rel="nofollow" href="https://www.westgard.com/biodatabase1.htm" target="_blank">webpage</a>, you can see that there are 8 columns in the tables of interest. So, we will define an empty dataframe with 8 columns.</p>

<pre>#define empty table to hold all the content
biotable = data.frame(matrix(NA,0, 8))</pre>

<p>We need to know which part of the document to scrape. This is a little obscure, but following the instructions in <a rel="nofollow" href="https://www.r-bloggers.com/using-rvest-to-scrape-an-html-table/" target="_blank">this post</a>, we can determine that the xpaths we need are:</p>
<p><strong>/html/body/div[1]/div[3]/div/main/article/div/table[1]</strong></p>
<p><strong>/html/body/div[1]/div[3]/div/main/article/div/table[2]</strong> </p>
<p><strong>/html/body/div[1]/div[3]/div/main/article/div/table[3]</strong> </p>
<p>…</p>
<p>etc.</p>
<p>There are 8 such tables in the whole webpage. We can define a character vector for these as such:</p>

<pre>xpaths &lt;- paste0(&quot;/html/body/div[1]/div[3]/div/main/article/div/table[&quot;, 1:8, &quot;]&quot;)</pre>

<p>Now we make a loop to scrape the 8 tables and with each iteration of the loop, append the scraped <code>subtable</code> to the main dataframe called <code>biotable</code> using the <code>rbind()</code> function. We have to use the parameter <code>fill = TRUE</code> in the <code>html_table()</code> function because the table does not happen to always a uniform number of columns.</p>

<pre>for (j in 1:8){                
  subtable &lt;- wg %>%
  read_html() %>%
  html_nodes(xpath =  xpaths[j]) %>%
  html_table(., fill = TRUE) 
  subtable &lt;- subtable[[1]]
  biotable &lt;- rbind(biotable,subtable)
}</pre>

<h2>Clean Up</h2>
<p>Now that we have the raw data out, we can have a quick look at it:</p>
<table>
<thead>
<tr>
<th align="left">X1</th>
<th align="left">X2</th>
<th align="left">X3</th>
<th align="left">X4</th>
<th align="left">X5</th>
<th align="left">X6</th>
<th align="left">X7</th>
<th align="left">X8</th>
</tr>
</thead>
<tbody>
<tr>
<td align="left"></td>
<td align="left">Analyte</td>
<td align="left">Number of Papers</td>
<td align="left">Biological Variation</td>
<td align="left">Biological Variation</td>
<td align="left">Desirable specification</td>
<td align="left">Desirable specification</td>
<td align="left">Desirable specification</td>
</tr>
<tr>
<td align="left"></td>
<td align="left">Analyte</td>
<td align="left">Number of Papers</td>
<td align="left">CVI</td>
<td align="left">CVg</td>
<td align="left">I(%)</td>
<td align="left">B(%)</td>
<td align="left">TE(%)</td>
</tr>
<tr>
<td align="left">S-</td>
<td align="left">11-Desoxycortisol</td>
<td align="left">2</td>
<td align="left">21.3</td>
<td align="left">31.5</td>
<td align="left">10.7</td>
<td align="left">9.5</td>
<td align="left">27.1</td>
</tr>
<tr>
<td align="left">S-</td>
<td align="left">17-Hydroxyprogesterone</td>
<td align="left">2</td>
<td align="left">19.6</td>
<td align="left">50.4</td>
<td align="left">9.8</td>
<td align="left">13.5</td>
<td align="left">29.7</td>
</tr>
<tr>
<td align="left">U-</td>
<td align="left">4-hydroxy-3-methoximandelate (VMA)</td>
<td align="left">1</td>
<td align="left">22.2</td>
<td align="left">47.0</td>
<td align="left">11.1</td>
<td align="left">13.0</td>
<td align="left">31.3</td>
</tr>
<tr>
<td align="left">S-</td>
<td align="left">5' Nucleotidase</td>
<td align="left">2</td>
<td align="left">23.2</td>
<td align="left">19.9</td>
<td align="left">11.6</td>
<td align="left">7.6</td>
<td align="left">26.8</td>
</tr>
<tr>
<td align="left">U-</td>
<td align="left">5'-Hydroxyindolacetate, concentration</td>
<td align="left">1</td>
<td align="left">20.3</td>
<td align="left">33.2</td>
<td align="left">10.2</td>
<td align="left">9.7</td>
<td align="left">26.5</td>
</tr>
<tr>
<td align="left">S-</td>
<td align="left">α1-Acid Glycoprotein</td>
<td align="left">3</td>
<td align="left">11.3</td>
<td align="left">24.9</td>
<td align="left">5.7</td>
<td align="left">6.8</td>
<td align="left">16.2</td>
</tr>
<tr>
<td align="left">S-</td>
<td align="left">α1-Antichymotrypsin</td>
<td align="left">1</td>
<td align="left">13.5</td>
<td align="left">18.3</td>
<td align="left">6.8</td>
<td align="left">5.7</td>
<td align="left">16.8</td>
</tr>
<tr>
<td align="left">S-</td>
<td align="left">α1-Antitrypsin</td>
<td align="left">3</td>
<td align="left">5.9</td>
<td align="left">16.3</td>
<td align="left">3.0</td>
<td align="left">4.3</td>
<td align="left">9.2</td>
</tr>
</tbody>
</table>
<p>We can see that we need define column names and we need to get rid of some rows containing extraneous column header information. There are actually 8 such sets of headers to remove.</p>

<pre>table.header &lt;- c(&quot;Sample&quot;, &quot;Analyte&quot; ,&quot;NumPapers&quot;, &quot;CVI&quot;, &quot;CVG&quot;, &quot;I&quot;, &quot;B&quot;,&quot;TE&quot;)
names(biotable) &lt;- table.header</pre>

<p>Let's now find rows we don't want and remove them. </p>

<pre>for.removal &lt;- grep(&quot;Analyte&quot;, biotable$Analyte)
biotable &lt;- biotable[-for.removal,]</pre>

<p>You will find that the table has missing data which is written as “- – -”. This should be now replaced by <code>NA</code> and the column names should be assigned to sequential integers. Also, we will remove all the minus signs after the specimen type. I'm not sure what they add.</p>

<pre>biotable[biotable == &quot;---&quot;] &lt;- NA
row.names(biotable) &lt;- 1:nrow(biotable)
biotable$Sample &lt;- gsub(&quot;-&quot;, &quot;&quot;, biotable$Sample, fixed = TRUE)</pre>

<h2>Check it Out</h2>
<p>Just having another look at the first 10 rows:</p>
<table>
<thead>
<tr>
<th align="left">Sample</th>
<th align="left">Analyte</th>
<th align="left">NumPapers</th>
<th align="left">CVI</th>
<th align="left">CVG</th>
<th align="left">I</th>
<th align="left">B</th>
<th align="left">TE</th>
</tr>
</thead>
<tbody>
<tr>
<td align="left">S</td>
<td align="left">11-Desoxycortisol</td>
<td align="left">2</td>
<td align="left">21.3</td>
<td align="left">31.5</td>
<td align="left">10.7</td>
<td align="left">9.5</td>
<td align="left">27.1</td>
</tr>
<tr>
<td align="left">S</td>
<td align="left">17-Hydroxyprogesterone</td>
<td align="left">2</td>
<td align="left">19.6</td>
<td align="left">50.4</td>
<td align="left">9.8</td>
<td align="left">13.5</td>
<td align="left">29.7</td>
</tr>
<tr>
<td align="left">U</td>
<td align="left">4-hydroxy-3-methoximandelate (VMA)</td>
<td align="left">1</td>
<td align="left">22.2</td>
<td align="left">47.0</td>
<td align="left">11.1</td>
<td align="left">13.0</td>
<td align="left">31.3</td>
</tr>
<tr>
<td align="left">S</td>
<td align="left">5' Nucleotidase</td>
<td align="left">2</td>
<td align="left">23.2</td>
<td align="left">19.9</td>
<td align="left">11.6</td>
<td align="left">7.6</td>
<td align="left">26.8</td>
</tr>
<tr>
<td align="left">U</td>
<td align="left">5'-Hydroxyindolacetate, concentration</td>
<td align="left">1</td>
<td align="left">20.3</td>
<td align="left">33.2</td>
<td align="left">10.2</td>
<td align="left">9.7</td>
<td align="left">26.5</td>
</tr>
<tr>
<td align="left">S</td>
<td align="left">α1-Acid Glycoprotein</td>
<td align="left">3</td>
<td align="left">11.3</td>
<td align="left">24.9</td>
<td align="left">5.7</td>
<td align="left">6.8</td>
<td align="left">16.2</td>
</tr>
<tr>
<td align="left">S</td>
<td align="left">α1-Antichymotrypsin</td>
<td align="left">1</td>
<td align="left">13.5</td>
<td align="left">18.3</td>
<td align="left">6.8</td>
<td align="left">5.7</td>
<td align="left">16.8</td>
</tr>
<tr>
<td align="left">S</td>
<td align="left">α1-Antitrypsin</td>
<td align="left">3</td>
<td align="left">5.9</td>
<td align="left">16.3</td>
<td align="left">3.0</td>
<td align="left">4.3</td>
<td align="left">9.2</td>
</tr>
<tr>
<td align="left">S</td>
<td align="left">α1-Globulins</td>
<td align="left">2</td>
<td align="left">11.4</td>
<td align="left">22.6</td>
<td align="left">5.7</td>
<td align="left">6.3</td>
<td align="left">15.7</td>
</tr>
<tr>
<td align="left">U</td>
<td align="left">α1-Microglobulin, concentration, first morning</td>
<td align="left">1</td>
<td align="left">33.0</td>
<td align="left">58.0</td>
<td align="left">16.5</td>
<td align="left">16.7</td>
<td align="left">43.9</td>
</tr>
</tbody>
</table>
<p>Now examining the structure:</p>

<pre>str(biotable)</pre>

<pre>## 'data.frame':    370 obs. of  8 variables:
##  $ Sample   : chr  &quot;S&quot; &quot;S&quot; &quot;U&quot; &quot;S&quot; ...
##  $ Analyte  : chr  &quot;11-Desoxycortisol&quot; &quot;17-Hydroxyprogesterone&quot; &quot;4-hydroxy-3-methoximandelate (VMA)&quot; &quot;5' Nucleotidase&quot; ...
##  $ NumPapers: chr  &quot;2&quot; &quot;2&quot; &quot;1&quot; &quot;2&quot; ...
##  $ CVI      : chr  &quot;21.3&quot; &quot;19.6&quot; &quot;22.2&quot; &quot;23.2&quot; ...
##  $ CVG      : chr  &quot;31.5&quot; &quot;50.4&quot; &quot;47.0&quot; &quot;19.9&quot; ...
##  $ I        : chr  &quot;10.7&quot; &quot;9.8&quot; &quot;11.1&quot; &quot;11.6&quot; ...
##  $ B        : chr  &quot;9.5&quot; &quot;13.5&quot; &quot;13.0&quot; &quot;7.6&quot; ...
##  $ TE       : chr  &quot;27.1&quot; &quot;29.7&quot; &quot;31.3&quot; &quot;26.8&quot; ...</pre>

<p>It's kind-of undesirable to have numbers as characters so…</p>

<pre>#convert appropriate columns to numeric
biotable[,3:8] &lt;- lapply(biotable[3:8], as.numeric)</pre>

<h2>Write the Data</h2>
<p>Using the <code>xlsx</code> package, you can output the table to an Excel file in the current working directory.</p>

<pre>library(xlsx)
write.xlsx(biotable,
            file = &quot;Westgard_Biological_Variation.xlsx&quot;,
            row.names = FALSE)</pre>

<p>If you are having trouble getting <code>xlsx</code> to install, then just write as csv.</p>

<pre>write.csv(biotable,
            file = &quot;Westgard_Biological_Variation.csv&quot;,
            row.names = FALSE)</pre>

<h2>Conclusion</h2>
<p>You can now use the same general approach to parse any table you have web access to, no mater how small or big it is. Here is a complete script in one place:</p>

<pre>library(httr)
library(rvest)
library(xlsx)

wg &lt;- html_session(&quot;https://www.westgard.com/biodatabase1.htm&quot;, user_agent(&quot;yournamehere&quot;))
xpaths &lt;- paste0(&quot;/html/body/div[1]/div[3]/div/main/article/div/table[&quot;, 1:8, &quot;]&quot;)

#define empty dataframe
biotable = data.frame(matrix(NA,0, 8))

#loop over the 8 html tables
for (j in 1:8){                
  subtable &lt;- wg %>%
  read_html() %>%
  html_nodes(xpath =  xpaths[j] ) %>%
  html_table(., fill = TRUE) 
  subtable &lt;- subtable[[1]]
  biotable &lt;- rbind(biotable,subtable)
}

table.header &lt;- c(&quot;Sample&quot;, &quot;Analyte&quot; ,&quot;NumPapers&quot;, &quot;CVI&quot;, &quot;CVG&quot;, &quot;I&quot;, &quot;B&quot;,&quot;TE&quot;)
names(biotable) &lt;- table.header

#remove extraneous rows
for.removal &lt;- grep(&quot;Analyte&quot;, biotable$Analyte)
biotable &lt;- biotable[-for.removal,]

#make missing data into NA
biotable[ biotable == &quot;---&quot; ] &lt;- NA
row.names(biotable) &lt;- 1:nrow(biotable)

#convert appropriate columns to numeric
biotable[,3:8] &lt;- lapply(biotable[3:8], as.numeric)

#get rid of minus signs in column 1
biotable$Sample &lt;- gsub(&quot;-&quot;, &quot;&quot;, biotable$Sample, fixed = TRUE)

write.xlsx(biotable,
            file = &quot;Westgard_Biological_Variation.xlsx&quot;,
            row.names = FALSE)

write.csv(biotable,
            file = &quot;Westgard_Biological_Variation.csv&quot;,
            row.names = FALSE)</pre>

<p><strong>Parting Thought on Tables</strong></p>
<p><em>You prepare a table before me in the presence of my enemies. You anoint my head with oil; my cup overflows.</em></p>
<p>(Prov 23:5)</p>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="http://labrtorian.com/2017/08/14/parse-an-online-table-into-an-r-dataframe-westgards-biological-variation-database/"> The Lab-R-torian</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/uC1hQ7f0pnQ" height="1"></a></div>