title: In R, trying to match and return PART of a string residing in a column for all the elements in a list and store the unique strings
body: <p>I'm stuck in processing multiple sheets of an excel file in which I would like to access the character information in a particular column per worksheet and wish to sort out the unique ones and return them.</p>
  <p>I have read in the excel file using read_excel function and sort each excel sheet into an element of a list. 15 sheets/elements in this list so far as tibbles. Each tibble varies in row numbers but they have the same column layouts. Luckily the column storing information of interest is called &quot;well_sample&quot;. Each row entry in the &quot;well_sample&quot; is following an ID_Time_Condition_sample_dose format.
  For example: S1041120_T4-R190118_3S_(-)-IndolactamV_1uM_none.</p>
  <p>I am interested in the S1041120 which is the ID (maybe later I will need the time T4-R190118).
  The trouble is there will be a few thousand rows in that column, perhaps 500 of them have the same ID, different treatments. The other few hundreds of them repeat the same pattern. Or they sometimes are the same ID, different time. So I want to isolate this column in each element of the list, split the strings, match and return only the ID. From there, I figured I could sort out the unique ID by using unique().</p>
  <p>Here is what I have:</p>
  <p>#build a mock data:</p>
  <pre><code>a&lt;- c(rep(&quot;S1041120_T4-R190118_3S_ab_1uM_none&quot;, 500), 
        rep(&quot;S1067120_T4-R190118_3S_ab_1uM_none&quot;, 50),
        rep(&quot;S5667890_T2-R191218_3S_amnb_10uM_none&quot;, 2800))
  b&lt;- c(rep(&quot;S1023120_T0-R190118_3S_ab_1uM_none&quot;, 1000), 
        rep(&quot;S6787120_T1-R190118_3S_ab_1uM_none&quot;, 1000),
        rep(&quot;S237890_T2-R191218_3S_amnb_10uM_none&quot;, 100))
  
  mysheets&lt;- list(a,b)
  
  #first set aside an empty list to store IDs
  ID.cleaned &lt;- list()
  
  #try to use a loop to go through all elements in the list
  for (i in 1:length(mysheets)) {
    #reminder the real list has my interest data in well_sample
    #mysheets[[i]]$well_sample should allow me to access that particular column
    #strsplit() to cut the string by &quot;_&quot;
  
    ID[[i]]&lt;- strsplit(mysheets[[i]]$well_sample, &quot;_&quot;, fixed = TRUE)
   
    #use lapply to apply the str_extract function
    #since the ID is always the first and the numbers after S is always between 4-10
    ID.cleaned[[i]]&lt;-lapply(ID[[i]], function(x){str_extract(x,&quot;^S\\d{4,10}&quot;)})
  }
  </code></pre>
  <p>I tried to run the code outside the loop line by line to see what each code is doing.</p>
  <pre><code> mysheets[[1]]$well_sample 
  </code></pre>
  <p>indeed gave me a character vector of [1:3350] from the 1st element in the list.</p>
  <pre><code> ID &lt;- strsplit(well_sample_column, &quot;_&quot;)
  </code></pre>
  <p>then</p>
  <pre><code>  str_extract(ID[[1]],&quot;^S\\d{4,10}&quot;)
  </code></pre>
  <p>Things are getting weird here, this gave me a list of [1:3350], I'm effectively blowing up my list. And the second line gave
  &quot;[1] &quot;S1041120&quot; NA         NA         NA         NA         NA   &quot; instead of just the S1041120.</p>
  <p>After running the codes, I got a ID.cleaned a list of 15 elements, corresponding to the 15 original excel sheets, but each element now contains a sub 3350 or so lists, 550 in a format to &quot;S1041120&quot; NA         NA         NA         NA         NA, other 2800 in &quot;S5667890&quot; NA         NA         NA         NA         NA</p>
  <p>I feel im in a dead loop. How am I going to take the IDs out and sort the unique ones? A little frustrated.</p>