title: Looping loading spreadsheets, making a new dataframe from specific cells, and then merging into one dataframe - R
body: <p>Goals:</p>
  <ol>
  <li>Read in all xlsm files from a folder (my working directory)</li>
  <li>Pull specific cells from each file and make a new, clean dataframe OR pull the values into a vector</li>
  <li>Combine these into one dataframe</li>
  </ol>
  <p>I have a large number of excel &quot;forms&quot; that I need to import into r for analysis. Unfortunately, the form was not designed with this goal in mind, so when reading it into r, the dataframe is not in any shape for analysis. Here is an example dataframe:</p>
  <pre><code>library(tidyverse)
  
  df_ex &lt;- data.frame(Form.Title = c(NA, &quot;Name:&quot;, &quot;ID:&quot;, NA, NA, &quot;Result 1:&quot;, &quot;Result 2:&quot;, &quot;Result 3:&quot;, NA, NA, NA),
                   X = c(NA, &quot;a&quot;, 12345, NA, NA, 4, 7, 2, NA, &quot;Count 1:&quot;, &quot;Count 3:&quot;),
                   Additional.Form.Title = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, 9, 3),
                   X.1 = c(NA, &quot;Title:&quot;, &quot;Phone Number:&quot;, &quot;email:&quot;, NA, NA, NA, NA, NA, NA, NA),
                   X.2 = c(NA, &quot;x&quot;, &quot;123-456-7890&quot;, &quot;ex@x.com&quot;, NA, NA, NA, NA, NA, &quot;Count2:&quot;, &quot;Count4:&quot;),
                   X.3 = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, 16, 12)
  )
  </code></pre>
  <p>Which would look like:</p>
  <pre><code>   Form.Title        X Additional.Form.Title           X.1          X.2 X.3
  1        &lt;NA&gt;     &lt;NA&gt;                    NA          &lt;NA&gt;         &lt;NA&gt;  NA
  2       Name:        a                    NA        Title:            x  NA
  3         ID:    12345                    NA Phone Number: 123-456-7890  NA
  4        &lt;NA&gt;     &lt;NA&gt;                    NA        email:     ex@x.com  NA
  5        &lt;NA&gt;     &lt;NA&gt;                    NA          &lt;NA&gt;         &lt;NA&gt;  NA
  6   Result 1:        4                    NA          &lt;NA&gt;         &lt;NA&gt;  NA
  7   Result 2:        7                    NA          &lt;NA&gt;         &lt;NA&gt;  NA
  8   Result 3:        2                    NA          &lt;NA&gt;         &lt;NA&gt;  NA
  9        &lt;NA&gt;     &lt;NA&gt;                    NA          &lt;NA&gt;         &lt;NA&gt;  NA
  10       &lt;NA&gt; Count 1:                     9          &lt;NA&gt;      Count2:  16
  11       &lt;NA&gt; Count 3:                     3          &lt;NA&gt;      Count4:  12
  </code></pre>
  <p>My goal is to take certain cells and move them into a proper dataframe. For example, &quot;Name:&quot; [2,1] would become a column name and &quot;a&quot; [2,2] would be a value in the row under that column. I would then want to loop this process for the rest of the forms and merge the rows into one dataframe.</p>
  <p>I started with this, but it did not work</p>
  <pre><code>library(readxl)
  library(tidyverse)
  
  test &lt;- list.files(pattern = &quot;*xlsm&quot;) %&gt;% 
    map(., ~ read_excel(.x, sheet = 1)) %&gt;% 
    map(., ~ data.frame(Name = .x[2,2], Result1 = .x[6,2], Result2 = .x[7,2], Result3 = .x[8,2])) %&gt;% 
    bind_rows()
  </code></pre>
  <p>Trying a different way, but stuck at the attempted loop. Any help with how to proceed or a better route is greatly appreciated.</p>
  <pre><code>library(readxl)
  library(tidyverse)
  
  #Read in all spreadsheets in working directory
  list_xlsms &lt;- list.files(pattern = &quot;*.xlsm&quot;) %&gt;% 
    map(., ~ read_excel(.x, sheet = 1))
  
  #Make empty dataframe. I'll add rows later
  df &lt;- data.frame(matrix(ncol = 3, nrow = 0))
  colnames(df) &lt;- c('Name', 'Result1', 'Result2')
  
  #Pull specific values from spreadsheets and create vector to add row to empty dataframe. 
  #Need to do this with all spreadsheets in list_xlsms
  #Will then add all the vectors as rows to the empty dataframe
  for (i in list_xlsms)
  {
    c(i$X[2], i$X[6], i$X[7])
  }
  </code></pre>