<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/using-functions-to-be-more-efficient-march-28-2018/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://ibecav.github.io/Functionalize/"> Chuck Powell</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>In yesterday’s post I focused on the task of using R to “drive” MS<br>
Excel. I deliberately ended the post with a fully functioning (pun<br>
intended) but very ugly set of code. Why “ugly”? Well, because the last<br>
set of code wound up repeating 4 lines of code 12 times!</p>
<div>
<div>
<pre><code><span>### Manual and painful way</span><span>
</span><span>## Create a new workbook</span><span>
</span><span>wb</span><span> </span><span>&lt;-</span><span> </span><span>createWorkbook</span><span>()</span><span>
</span><span># education by each of the other 4 variables</span><span>
</span><span>NameofSheet</span><span> </span><span>&lt;-</span><span> </span><span>&quot;CoverageByEducation&quot;</span><span>
</span><span>TheData</span><span> </span><span>&lt;-</span><span> </span><span>table</span><span>(</span><span>OfInterest</span><span>$</span><span>EDUCATION</span><span>,</span><span>OfInterest</span><span>$</span><span>NOTCOV</span><span>)</span><span>
</span><span>addWorksheet</span><span>(</span><span>wb</span><span> </span><span>=</span><span> </span><span>wb</span><span>,</span><span> </span><span>sheetName</span><span> </span><span>=</span><span> </span><span>NameofSheet</span><span>)</span><span>
</span><span>writeData</span><span>(</span><span>wb</span><span> </span><span>=</span><span> </span><span>wb</span><span>,</span><span> </span><span>sheet</span><span> </span><span>=</span><span> </span><span>NameofSheet</span><span>,</span><span> </span><span>x</span><span> </span><span>=</span><span> </span><span>TheData</span><span>,</span><span> </span><span>borders</span><span> </span><span>=</span><span> </span><span>&quot;n&quot;</span><span>)</span><span>
</span><span>NameofSheet</span><span> </span><span>&lt;-</span><span> </span><span>&quot;MedbillByEducation&quot;</span><span>
</span><span>TheData</span><span> </span><span>&lt;-</span><span> </span><span>table</span><span>(</span><span>OfInterest</span><span>$</span><span>EDUCATION</span><span>,</span><span>OfInterest</span><span>$</span><span>MEDBILL</span><span>)</span><span>
</span><span>addWorksheet</span><span>(</span><span>wb</span><span> </span><span>=</span><span> </span><span>wb</span><span>,</span><span> </span><span>sheetName</span><span> </span><span>=</span><span> </span><span>NameofSheet</span><span>)</span><span>
</span><span>writeData</span><span>(</span><span>wb</span><span> </span><span>=</span><span> </span><span>wb</span><span>,</span><span> </span><span>sheet</span><span> </span><span>=</span><span> </span><span>NameofSheet</span><span>,</span><span> </span><span>x</span><span> </span><span>=</span><span> </span><span>TheData</span><span>,</span><span> </span><span>borders</span><span> </span><span>=</span><span> </span><span>&quot;n&quot;</span><span>)</span><span>
</span><span>### many repeating lines removed ###</span><span>
</span><span>NameofSheet</span><span> </span><span>&lt;-</span><span> </span><span>&quot;PNMED12MByAge&quot;</span><span>
</span><span>TheData</span><span> </span><span>&lt;-</span><span> </span><span>table</span><span>(</span><span>OfInterest</span><span>$</span><span>AGE</span><span>,</span><span>OfInterest</span><span>$</span><span>PNMED12M</span><span>)</span><span>
</span><span>addWorksheet</span><span>(</span><span>wb</span><span> </span><span>=</span><span> </span><span>wb</span><span>,</span><span> </span><span>sheetName</span><span> </span><span>=</span><span> </span><span>NameofSheet</span><span>)</span><span>
</span><span>writeData</span><span>(</span><span>wb</span><span> </span><span>=</span><span> </span><span>wb</span><span>,</span><span> </span><span>sheet</span><span> </span><span>=</span><span> </span><span>NameofSheet</span><span>,</span><span> </span><span>x</span><span> </span><span>=</span><span> </span><span>TheData</span><span>,</span><span> </span><span>borders</span><span> </span><span>=</span><span> </span><span>&quot;n&quot;</span><span>)</span><span>
</span><span>#</span><span>
</span><span>saveWorkbook</span><span>(</span><span>wb</span><span>,</span><span> </span><span>&quot;BetterExcelExample.xlsx&quot;</span><span>,</span><span> </span><span>overwrite</span><span> </span><span>=</span><span> </span><span>TRUE</span><span>)</span><span> </span><span>## save to working directory</span><span>
</span></code></pre>
</div>
</div>
<p>Now, to be honest, in a nice modern IDE environment like <code>RStudio</code>,<br>
cutting, pasting and making small edits 12 times is not all that<br>
difficult. I did do exactly that my first pass through. But as I looked<br>
at it I also knew I could do much better if I used automation. The code<br>
would be easier to read or modify in the future and I’d be much less<br>
likely to make a mistake.</p>
<h2>Background and catchup</h2>
<p>My colleague wanted to be able to do some simple analysis around health<br>
care using the Centers for Disease Control and Prevention<br>
(<a rel="nofollow" href="https://www.cdc.gov/" target="_blank">https://www.cdc.gov</a>), National Center for Health Statistics<br>
(<a rel="nofollow" href="https://www.cdc.gov/nchs/index.htm" target="_blank">https://www.cdc.gov/nchs/index.htm</a>), National Health Interview Survey<br>
(<a rel="nofollow" href="https://www.cdc.gov/nchs/nhis/nhis_2016_data_release.htm" target="_blank">https://www.cdc.gov/nchs/nhis/nhis_2016_data_release.htm</a>). They<br>
wanted a series of cross tabulated sets of summary data for variable<br>
pairings (for example whether or not the respondent had a formal health<br>
care provider by region of the country). They wanted one Excel<br>
“workbook” with 12 worksheets each one of which was the summary of<br>
counts for a pair of variables. From there they could use Excel’s native<br>
plotting tools to make the graphs they needed.</p>
<p>You can review everything that happened yesterday (which I recommend) or<br>
you can pick up here. To join us in progress make sure you load the<br>
right libraries and grab the dataset we wound up on which is called<br>
<code>OfInterest</code>.</p>
<div>
<div>
<pre><code><span>knitr</span><span>::</span><span>opts_chunk</span><span>$</span><span>set</span><span>(</span><span>echo</span><span> </span><span>=</span><span> </span><span>TRUE</span><span>,</span><span> </span><span>warning</span><span> </span><span>=</span><span> </span><span>FALSE</span><span>)</span><span>
</span><span>library</span><span>(</span><span>dplyr</span><span>)</span><span>
</span></code></pre>
</div>
</div>
<div>
<div>
<pre><code>## 
## Attaching package: 'dplyr'

## The following objects are masked from 'package:stats':
## 
##     filter, lag

## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
</code></pre>
</div>
</div>
<div>
<div>
<pre><code><span>library</span><span>(</span><span>ggplot2</span><span>)</span><span>
</span><span>theme_set</span><span>(</span><span>theme_bw</span><span>())</span><span> </span><span># set theme to my personal preference</span><span>
</span><span># install.packages(&quot;openxlsx&quot;)</span><span>
</span><span>require</span><span>(</span><span>openxlsx</span><span>)</span><span>
</span></code></pre>
</div>
</div>
<div>
<div>
<pre><code>## Loading required package: openxlsx
</code></pre>
</div>
</div>
<div>
<div>
<pre><code><span>OfInterest</span><span> </span><span>&lt;-</span><span> </span><span>read.csv</span><span>(</span><span>&quot;ofinterest.csv&quot;</span><span>)</span><span>
</span><span>### OfInterest &lt;-read.csv(&quot;https://raw.githubusercontent.com/ibecav/ibecav.github.io/master/Rmdfiles/ofinterest.csv&quot;) available through Github about 9Mb</span><span>
</span><span>str</span><span>(</span><span>OfInterest</span><span>)</span><span>
</span></code></pre>
</div>
</div>
<div>
<div>
<pre><code>## 'data.frame':    103789 obs. of  9 variables:
##  $ AGE      : Factor w/ 3 levels &quot;19 to 60&quot;,&quot;Less than 18&quot;,..: 1 1 1 2 2 1 1 1 3 1 ...
##  $ REGION   : Factor w/ 4 levels &quot;Midwest&quot;,&quot;Northeast&quot;,..: 3 4 4 4 4 4 3 3 4 3 ...
##  $ SEX      : Factor w/ 2 levels &quot;Female&quot;,&quot;Male&quot;: 2 1 2 2 1 1 1 2 2 2 ...
##  $ EDUCATION: Factor w/ 3 levels &quot;Bachelor's degree or higher&quot;,..: 3 2 1 2 NA 1 2 2 1 3 ...
##  $ EARNINGS : Factor w/ 3 levels &quot;$01-$34,999&quot;,..: 1 2 2 NA NA 3 1 NA 2 3 ...
##  $ PDMED12M : Factor w/ 2 levels &quot;No&quot;,&quot;Yes&quot;: 1 2 1 1 1 1 1 1 1 1 ...
##  $ PNMED12M : Factor w/ 2 levels &quot;No&quot;,&quot;Yes&quot;: 1 1 1 1 1 1 1 1 1 1 ...
##  $ NOTCOV   : Factor w/ 2 levels &quot;Covered&quot;,&quot;Not covered&quot;: 1 1 1 1 1 1 1 1 1 1 ...
##  $ MEDBILL  : Factor w/ 2 levels &quot;No&quot;,&quot;Yes&quot;: 1 2 2 2 2 1 1 1 1 1 ...
</code></pre>
</div>
</div>
<h2>Breaking the task down</h2>
<p>So as my tagline indicates I don’t consider myself a “programmer”. I<br>
love analyzing data and I love <code>R</code> but I approach programming slowly and<br>
cautiously. What I’m about to explain as my method will likely seem<br>
quaint and even antiquated to some but has the advantage of being very<br>
methodical and very practical. There are lots of places on the web to<br>
read about this stuff, I’m simply making the case I hope mine is slow<br>
enough and methodical enough for a beginner.</p>
<p>What do we have there in those 12 iterations? Well what we have are 3<br>
variables <code>EDUCATION</code>, <code>EARNINGS</code>, and <code>AGE</code> that we want to <strong>“cross”</strong><br>
with 4 other variables <code>NOTCOV</code>, <code>MEDBILL</code>, <code>PDMED12M</code>, and <code>PNMED12M</code><br>
to give us the total of 12. In my discipline we would call the first<br>
three the independent variables and the second four the dependent<br>
variables.</p>
<p>So as a first step in automating our work lets just make two lists that<br>
acknowledge that fact. We’ll even name the list elements so we can make<br>
use of those names in future steps. That means we can use the short hand<br>
<code>depvars$Coverage</code> instead of <code>depvars[[1]]</code> which I find difficult to<br>
keep track of. Notice that the list contains the actual data and is not<br>
just a pointer at the<br>
dataframe.</p>
<div>
<div>
<pre><code><span>depvars</span><span> </span><span>&lt;-</span><span> </span><span>list</span><span>(</span><span>Coverage</span><span> </span><span>=</span><span> </span><span>OfInterest</span><span>$</span><span>NOTCOV</span><span>,</span><span> </span><span>ProbPay</span><span> </span><span>=</span><span> </span><span>OfInterest</span><span>$</span><span>MEDBILL</span><span>,</span><span> </span><span>CareDelay</span><span> </span><span>=</span><span> </span><span>OfInterest</span><span>$</span><span>PDMED12M</span><span>,</span><span> </span><span>NeedNotGet</span><span> </span><span>=</span><span> </span><span>OfInterest</span><span>$</span><span>PNMED12M</span><span>)</span><span>
</span><span>indvars</span><span> </span><span>&lt;-</span><span> </span><span>list</span><span>(</span><span>Education</span><span> </span><span>=</span><span> </span><span>OfInterest</span><span>$</span><span>EDUCATION</span><span>,</span><span> </span><span>Earnings</span><span> </span><span>=</span><span> </span><span>OfInterest</span><span>$</span><span>EARNINGS</span><span>,</span><span> </span><span>Age</span><span> </span><span>=</span><span> </span><span>OfInterest</span><span>$</span><span>AGE</span><span>)</span><span>
</span><span># these two are identical use head because the list is more than 100,000 entries long</span><span>
</span><span>head</span><span>(</span><span>depvars</span><span>$</span><span>Coverage</span><span>)</span><span>
</span></code></pre>
</div>
</div>
<div>
<div>
<pre><code>## [1] Covered Covered Covered Covered Covered Covered
## Levels: Covered Not covered
</code></pre>
</div>
</div>
<div>
<div>
<pre><code><span>head</span><span>(</span><span>depvars</span><span>[[</span><span>1</span><span>]])</span><span>
</span></code></pre>
</div>
</div>
<div>
<div>
<pre><code>## [1] Covered Covered Covered Covered Covered Covered
## Levels: Covered Not covered
</code></pre>
</div>
</div>
<p>Okay, so far so good! Now what? Well <code>R</code> has a wonderful function<br>
called <code>lapply</code> which as the documentation <code>?lapply</code> and numerous<br>
websites will tell you applies a function to a list. It sequentially<br>
walks its way through a list and applies the function you tell it to<br>
use.</p>
<p>Let’s take a small step. We want tables. 12 of them. Each table is of<br>
the form <code>table(indvars,depvars)</code> like “education” by “coverage”. Let’s<br>
try <code>lapply</code> for just part of that process. The command becomes<br>
<code>lapply(depvars, function (x) table(OfInterest$EDUCATION,x))</code> which you<br>
can read as “Take the <strong>list</strong> of dependent variables <strong>depvars</strong>.<br>
<strong>Apply</strong> the function called <strong>table</strong> and wherever you see an <strong>x</strong><br>
substitute the current value of <strong>depvars</strong>”. So the very first thing it<br>
would do is <code>table(OfInterest$EDUCATION,OfInterest$NOTCOV))</code> and it<br>
would do it for all four variables in the list.</p>
<div>
<div>
<pre><code><span>lapply</span><span>(</span><span>depvars</span><span>,</span><span> </span><span>function</span><span> </span><span>(</span><span>x</span><span>)</span><span> </span><span>table</span><span>(</span><span>OfInterest</span><span>$</span><span>EDUCATION</span><span>,</span><span>x</span><span>))</span><span>
</span></code></pre>
</div>
</div>
<div>
<div>
<pre><code>## $Coverage
##                                   x
##                                    Covered Not covered
##   Bachelor's degree or higher        20802         895
##   High School Grad or less           43404        6846
##   Some college or Associate degree   20667        2313
## 
## $ProbPay
##                                   x
##                                       No   Yes
##   Bachelor's degree or higher      19994  1789
##   High School Grad or less         41375  9217
##   Some college or Associate degree 19231  3899
## 
## $CareDelay
##                                   x
##                                       No   Yes
##   Bachelor's degree or higher      20583  1219
##   High School Grad or less         47576  3155
##   Some college or Associate degree 21001  2185
## 
## $NeedNotGet
##                                   x
##                                       No   Yes
##   Bachelor's degree or higher      21108   694
##   High School Grad or less         48191  2532
##   Some college or Associate degree 21545  1645
</code></pre>
</div>
</div>
<p>Perfect! Just what we were looking for. We get back a list of 4 tables.<br>
Progress! No surprise it works the other way around as well. We can<br>
hold the second part of the table command constant and just vary the<br>
independent variable via <code>indvars</code>!</p>
<div>
<div>
<pre><code><span>lapply</span><span>(</span><span>indvars</span><span>,</span><span> </span><span>function</span><span> </span><span>(</span><span>y</span><span>)</span><span> </span><span>table</span><span>(</span><span>y</span><span>,</span><span>OfInterest</span><span>$</span><span>NOTCOV</span><span>))</span><span>
</span></code></pre>
</div>
</div>
<div>
<div>
<pre><code>## $Education
##                                   
## y                                  Covered Not covered
##   Bachelor's degree or higher        20802         895
##   High School Grad or less           43404        6846
##   Some college or Associate degree   20667        2313
## 
## $Earnings
##                   
## y                  Covered Not covered
##   $01-$34,999        16148        4121
##   $35,000-$74,999    12116         783
##   $75,000 and over    6253         158
## 
## $Age
##               
## y              Covered Not covered
##   19 to 60       47258        8623
##   Less than 18   24193        1312
##   More than 60   20730         571
</code></pre>
</div>
</div>
<p>What we need, of course, is both of those things. A <strong>“nested”</strong> set of<br>
calls to <code>lapply</code> to walk through both lists and give us 12 tables not 3<br>
or 4. So the next command is a bit ugly to read but hopefully if you<br>
have been following along it will make perfectly good sense. We’re going<br>
to call <code>lapply</code> and the function we will tell it to run is <code>lapply</code>!<br>
The second <code>lapply</code> will in turn call <code>table</code> and all we have to do is<br>
to keep our x’s and y’s correct!</p>
<div>
<div>
<pre><code><span>lapply</span><span>(</span><span>depvars</span><span>,</span><span> </span><span>function</span><span> </span><span>(</span><span>x</span><span>)</span><span> </span><span>lapply</span><span>(</span><span>indvars</span><span>,</span><span> </span><span>function</span><span> </span><span>(</span><span>y</span><span>)</span><span> </span><span>table</span><span>(</span><span>y</span><span>,</span><span>x</span><span>)))</span><span>
</span></code></pre>
</div>
</div>
<div>
<div>
<pre><code>## $Coverage
## $Coverage$Education
##                                   x
## y                                  Covered Not covered
##   Bachelor's degree or higher        20802         895
##   High School Grad or less           43404        6846
##   Some college or Associate degree   20667        2313
## 
## $Coverage$Earnings
##                   x
## y                  Covered Not covered
##   $01-$34,999        16148        4121
##   $35,000-$74,999    12116         783
##   $75,000 and over    6253         158
## 
## $Coverage$Age
##               x
## y              Covered Not covered
##   19 to 60       47258        8623
##   Less than 18   24193        1312
##   More than 60   20730         571
## 
## 
## $ProbPay
## $ProbPay$Education
##                                   x
## y                                     No   Yes
##   Bachelor's degree or higher      19994  1789
##   High School Grad or less         41375  9217
##   Some college or Associate degree 19231  3899
## 
## $ProbPay$Earnings
##                   x
## y                     No   Yes
##   $01-$34,999      16229  4139
##   $35,000-$74,999  11321  1596
##   $75,000 and over  6069   341
## 
## $ProbPay$Age
##               x
## y                 No   Yes
##   19 to 60     46924  9504
##   Less than 18 20841  4747
##   More than 60 19331  2031
## 
## 
## $CareDelay
## $CareDelay$Education
##                                   x
## y                                     No   Yes
##   Bachelor's degree or higher      20583  1219
##   High School Grad or less         47576  3155
##   Some college or Associate degree 21001  2185
## 
## $CareDelay$Earnings
##                   x
## y                     No   Yes
##   $01-$34,999      17937  2447
##   $35,000-$74,999  12049   877
##   $75,000 and over  6239   174
## 
## $CareDelay$Age
##               x
## y                 No   Yes
##   19 to 60     51621  5020
##   Less than 18 25058   612
##   More than 60 20307  1112
## 
## 
## $NeedNotGet
## $NeedNotGet$Education
##                                   x
## y                                     No   Yes
##   Bachelor's degree or higher      21108   694
##   High School Grad or less         48191  2532
##   Some college or Associate degree 21545  1645
## 
## $NeedNotGet$Earnings
##                   x
## y                     No   Yes
##   $01-$34,999      18481  1907
##   $35,000-$74,999  12403   522
##   $75,000 and over  6327    86
## 
## $NeedNotGet$Age
##               x
## y                 No   Yes
##   19 to 60     52841  3794
##   Less than 18 25262   402
##   More than 60 20638   782
</code></pre>
</div>
</div>
<p>Please note that it took me quite some time to get that nested lapply<br>
correct! A lot of searching on Stack Overflow, a lot of trial and<br>
error, but I won’t forget it now! If you want to test yourself try on<br>
your own to make the change necessary to invert the output to have the<br>
columns and rows the other way around.</p>
<p>Now that we have demonstrated we can do it, lets put our 12 tables<br>
someplace safe. Let’s call it <code>TablesList</code> and to ensure we know how to<br>
get these tables back out again let’s pull just one of them from our<br>
list of 12, this is where using names not numbers<br>
helps.</p>
<div>
<div>
<pre><code><span>TablesList</span><span> </span><span>&lt;-</span><span> </span><span>lapply</span><span>(</span><span>depvars</span><span>,</span><span> </span><span>function</span><span> </span><span>(</span><span>x</span><span>)</span><span> </span><span>lapply</span><span>(</span><span>indvars</span><span>,</span><span> </span><span>function</span><span> </span><span>(</span><span>y</span><span>)</span><span> </span><span>table</span><span>(</span><span>y</span><span>,</span><span>x</span><span>)))</span><span>
</span><span>TablesList</span><span>$</span><span>Coverage</span><span>$</span><span>Education</span><span>
</span></code></pre>
</div>
</div>
<div>
<div>
<pre><code>##                                   x
## y                                  Covered Not covered
##   Bachelor's degree or higher        20802         895
##   High School Grad or less           43404        6846
##   Some college or Associate degree   20667        2313
</code></pre>
</div>
</div>
<div>
<div>
<pre><code><span>TablesList</span><span>$</span><span>Coverage</span><span>
</span></code></pre>
</div>
</div>
<div>
<div>
<pre><code>## $Education
##                                   x
## y                                  Covered Not covered
##   Bachelor's degree or higher        20802         895
##   High School Grad or less           43404        6846
##   Some college or Associate degree   20667        2313
## 
## $Earnings
##                   x
## y                  Covered Not covered
##   $01-$34,999        16148        4121
##   $35,000-$74,999    12116         783
##   $75,000 and over    6253         158
## 
## $Age
##               x
## y              Covered Not covered
##   19 to 60       47258        8623
##   Less than 18   24193        1312
##   More than 60   20730         571
</code></pre>
</div>
</div>
<p>Okay we made a list of tables now let’s work on putting those tables<br>
where we want them in a series of sheets in a workbook.</p>
<h2>For a little more fun</h2>
<p>Pardon the pun (for those who got it) but yes we’re now going to use<br>
<code>for loops</code> to pull the tables out of the list and put them somewhere in<br>
an organized fashion. So the name of our list is <code>TablesList</code> and inside<br>
that list are sublists with names like <code>TablesList$Coverage</code> and the<br>
individual tables have names like <code>TablesList$Coverage$Education</code>.</p>
<p>So why don’t we walk down <code>TablesList$Coverage</code> and extract the 3 tables<br>
in there one by one? Many ways to do it but I’ll use a <code>for loop</code> with<br>
<code>for (i in seq_along(TablesList$Coverage))<br>
{print(TablesList$Coverage[[i]])}</code> which says sequence along the list<br>
<code>TablesList$Coverage</code> using <code>i</code> as a placeholder for where we are in the<br>
list. Then for each item in the list <code>i</code> <code>print</code> the<br>
table.</p>
<div>
<div>
<pre><code><span>for</span><span> </span><span>(</span><span>i</span><span> </span><span>in</span><span> </span><span>seq_along</span><span>(</span><span>TablesList</span><span>$</span><span>Coverage</span><span>))</span><span> </span><span>{</span><span>print</span><span>(</span><span>TablesList</span><span>$</span><span>Coverage</span><span>[[</span><span>i</span><span>]])}</span><span>
</span></code></pre>
</div>
</div>
<div>
<div>
<pre><code>##                                   x
## y                                  Covered Not covered
##   Bachelor's degree or higher        20802         895
##   High School Grad or less           43404        6846
##   Some college or Associate degree   20667        2313
##                   x
## y                  Covered Not covered
##   $01-$34,999        16148        4121
##   $35,000-$74,999    12116         783
##   $75,000 and over    6253         158
##               x
## y              Covered Not covered
##   19 to 60       47258        8623
##   Less than 18   24193        1312
##   More than 60   20730         571
</code></pre>
</div>
</div>
<p>Good. Once again though we want all 12 not just three so we need to nest<br>
again. So let’s walk down just <code>EDUCATION</code> first…</p>
<div>
<div>
<pre><code><span>for</span><span> </span><span>(</span><span>j</span><span> </span><span>in</span><span> </span><span>seq_along</span><span>(</span><span>TablesList</span><span>))</span><span> </span><span>{</span><span>print</span><span>(</span><span>TablesList</span><span>[[</span><span>j</span><span>]][[</span><span>1</span><span>]])}</span><span>
</span></code></pre>
</div>
</div>
<div>
<div>
<pre><code>##                                   x
## y                                  Covered Not covered
##   Bachelor's degree or higher        20802         895
##   High School Grad or less           43404        6846
##   Some college or Associate degree   20667        2313
##                                   x
## y                                     No   Yes
##   Bachelor's degree or higher      19994  1789
##   High School Grad or less         41375  9217
##   Some college or Associate degree 19231  3899
##                                   x
## y                                     No   Yes
##   Bachelor's degree or higher      20583  1219
##   High School Grad or less         47576  3155
##   Some college or Associate degree 21001  2185
##                                   x
## y                                     No   Yes
##   Bachelor's degree or higher      21108   694
##   High School Grad or less         48191  2532
##   Some college or Associate degree 21545  1645
</code></pre>
</div>
</div>
<h2>Driving Excel through automation</h2>
<p>Okay now we’re ready to make magic. Scroll back to the top of this post<br>
or review the last post and you’ll see the logic is:</p>
<ol>
<li>Create a new empty workbook object <code>wb &lt;- createWorkbook()</code> <strong>once</strong></li>
<li>Invent a name for the <strong>tab</strong> or worksheet inside the workbook<br>
<code>NameofSheet</code> <strong>12 times</strong></li>
<li>Make a <code>table</code> for a pair of variables like <code>TheData</code> <strong>12 times</strong></li>
<li>Add a worksheet (tab) into the workbook <code>addWorksheet</code> <strong>12 times</strong></li>
<li>Write the table we made onto the worksheet with <code>writeData</code> <strong>12<br>
times</strong></li>
<li>Save the workbook with the 12 sheets in it <strong>once</strong></li>
</ol>
<p>So steps 1 &amp; 6 occur once and steps 2-5 need to occur in our loop<br>
structure</p>
<ol>
<li><code>wb &lt;- createWorkbook()</code></li>
<li><code>for loop</code><br>
1) get table <code>TheData</code><br>
2) create <code>NameofSheet</code><br>
3) make an empty worksheet named <code>NameofSheet</code> with <code>addWorksheet</code><br>
4) <code>writeData</code> called <code>TheData</code> into <code>NameofSheet</code></li>
<li><code>saveWorkbook</code> or save it</li>
</ol>
<p></p>
<div>
<div>
<pre><code><span>## Create a new empty workbook</span><span>
</span><span>wb</span><span> </span><span>&lt;-</span><span> </span><span>createWorkbook</span><span>()</span><span>
</span><span>## nested for loop</span><span>
</span><span>for</span><span> </span><span>(</span><span>j</span><span> </span><span>in</span><span> </span><span>seq_along</span><span>(</span><span>TablesList</span><span>))</span><span> </span><span>{</span><span> </span><span>#top list with depvars</span><span>
</span><span>for</span><span> </span><span>(</span><span>i</span><span> </span><span>in</span><span> </span><span>seq_along</span><span>(</span><span>TablesList</span><span>[[</span><span>j</span><span>]]))</span><span> </span><span>{</span><span> </span><span>#for each depvar walk the indvars</span><span>
</span><span>TheData</span><span> </span><span>&lt;-</span><span> </span><span>TablesList</span><span>[[</span><span>j</span><span>]][[</span><span>i</span><span>]]</span><span>
</span><span>NameofSheet</span><span> </span><span>&lt;-</span><span> </span><span>paste0</span><span>(</span><span>names</span><span>(</span><span>TablesList</span><span>[</span><span>j</span><span>]),</span><span> </span><span>&quot;By&quot;</span><span>,</span><span> </span><span>names</span><span>(</span><span>TablesList</span><span>[[</span><span>j</span><span>]][</span><span>i</span><span>]))</span><span>
</span><span>addWorksheet</span><span>(</span><span>wb</span><span> </span><span>=</span><span> </span><span>wb</span><span>,</span><span> </span><span>sheetName</span><span> </span><span>=</span><span> </span><span>NameofSheet</span><span>)</span><span>
</span><span>writeData</span><span>(</span><span>wb</span><span> </span><span>=</span><span> </span><span>wb</span><span>,</span><span> </span><span>sheet</span><span> </span><span>=</span><span> </span><span>NameofSheet</span><span>,</span><span> </span><span>x</span><span> </span><span>=</span><span> </span><span>TheData</span><span>,</span><span> </span><span>borders</span><span> </span><span>=</span><span> </span><span>&quot;n&quot;</span><span>)</span><span>
</span><span>}</span><span>
</span><span>}</span><span>
</span><span>## Save our new workbook</span><span>
</span><span>saveWorkbook</span><span>(</span><span>wb</span><span>,</span><span> </span><span>&quot;newversion.xlsx&quot;</span><span>,</span><span> </span><span>overwrite</span><span> </span><span>=</span><span> </span><span>TRUE</span><span>)</span><span> </span><span>## save to working directory</span><span>
</span></code></pre>
</div>
</div>
<p>Breaking it down:</p>
<p>These two lines create the nested for loops to walk the 12 tables. <code>j</code><br>
is the outer loop of dependent variables and <code>i</code> is the inner loop of<br>
independent variables.</p>
<div>
<div>
<pre><code><span>for</span><span> </span><span>(</span><span>j</span><span> </span><span>in</span><span> </span><span>seq_along</span><span>(</span><span>TablesList</span><span>))</span><span> </span><span>{</span><span> </span><span>#top list with depvars</span><span>
</span><span>for</span><span> </span><span>(</span><span>i</span><span> </span><span>in</span><span> </span><span>seq_along</span><span>(</span><span>TablesList</span><span>[[</span><span>j</span><span>]]))</span><span> </span><span>{</span><span> </span><span>#for each depvar walk the indvars</span><span>
</span></code></pre>
</div>
</div>
<p><code>NameofSheet</code> is nice because since our list items are “named” we can<br>
put those names together with the word “By” to make the name of the<br>
worksheet sensible to a human.</p>
<div>
<div>
<pre><code><span> </span><span>TheData</span><span> </span><span>&lt;-</span><span> </span><span>TablesList</span><span>[[</span><span>j</span><span>]][[</span><span>i</span><span>]]</span><span>
</span><span>NameofSheet</span><span> </span><span>&lt;-</span><span> </span><span>paste0</span><span>(</span><span>names</span><span>(</span><span>TablesList</span><span>[</span><span>j</span><span>]),</span><span> </span><span>&quot;By&quot;</span><span>,</span><span> </span><span>names</span><span>(</span><span>TablesList</span><span>[[</span><span>j</span><span>]][</span><span>i</span><span>]))</span><span>
</span><span>addWorksheet</span><span>(</span><span>wb</span><span> </span><span>=</span><span> </span><span>wb</span><span>,</span><span> </span><span>sheetName</span><span> </span><span>=</span><span> </span><span>NameofSheet</span><span>)</span><span>
</span><span>writeData</span><span>(</span><span>wb</span><span> </span><span>=</span><span> </span><span>wb</span><span>,</span><span> </span><span>sheet</span><span> </span><span>=</span><span> </span><span>NameofSheet</span><span>,</span><span> </span><span>x</span><span> </span><span>=</span><span> </span><span>TheData</span><span>,</span><span> </span><span>borders</span><span> </span><span>=</span><span> </span><span>&quot;n&quot;</span><span>)</span><span>
</span></code></pre>
</div>
</div>
<h2>All done (not yet!)</h2>
<p>Hopefully this post helps you understand how to put automation in the<br>
guise of <code>lapply</code> and <code>for</code> for you. In my next post I’ll build on this<br>
scaffolding to discuss how to make these very same graphs in <code>ggplot2</code><br>
(which IMHO runs circles around Excel for scientific plotting), as well<br>
as making this all more efficient through the use of our own functions<br>
to take care of some of the more repetitive chores.</p>
<p>I hope you’ve found this useful. I am always open to comments,<br>
corrections and suggestions.</p>
<p>Chuck (ibecav at gmail dot<br>
com)</p>
<h3>License</h3>
<p><a rel="nofollow" href="http://creativecommons.org/licenses/by-sa/4.0/" target="_blank"><img alt="Creative Commons License" src="https://i1.wp.com/i.creativecommons.org/l/by-sa/4.0/88x31.png?w=456&ssl=1" data-recalc-dims="1"></a><br>This<br>
work is licensed under a<br>
<a rel="nofollow" href="http://creativecommons.org/licenses/by-sa/4.0/" target="_blank">Creative<br>
Commons Attribution-ShareAlike 4.0 International License</a>.</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://ibecav.github.io/Functionalize/"> Chuck Powell</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/ZAOduPmL_ns" height="1"></a></div>