title: r - Merge two dataframes in master/detail hierarchy
body: <p>I have two data.frames, one represents the directed connection of two sites in my system, for example below site a1 (looking at the rows) is connected to site d1 but not the other way around. And d1 is connected to itself and d2.</p>
  
  <pre><code>&gt;connections=read.table("file1")
  &gt;connections
    V1 V2 V3 V4 V5 Site
  1  0  1  0  0  0   a1
  2  0  1  1  0  0   d1
  3  0  1  1  1  1   d2
  4  1  0  0  0  0   d3
  5  0  0  0  0  0   f1
  </code></pre>
  
  <p>What has been really hard for me is to write this connections in terms of the subunits of each site, each sites are composed of 5 subunits, as seen in my summary file below, each subunit id is unique.</p>
  
  <pre><code>  &gt; subunits=read.table("file2")
    &gt; subunits
        Site minID maxId
      1   a1     0     4
      2   d1     5     9
      3   d2    10    14
      4   d3    15    19 
      5   f1    20    24
  </code></pre>
  
  <p>My goal is to create a detail table of connections by subunit , the result should look something like</p>
  
  <pre><code>site subunit numconnections conectionids…
  a1 0 5 5 6 7 8 9
  a1 1 5 5 6 7 8 9
  a1 2 5 5 6 7 8 9
  a1 3 5 5 6 7 8 9
  a1 4 5 5 6 7 8 9
  d1 5 10 5 6 7 8 9 10 11 12 13 14
  d1 6 10 5 6 7 8 9 10 11 12 13 14
  d1 7 10 5 6 7 8 9 10 11 12 13 14
  d1 8 10 5 6 7 8 9 10 11 12 13 14
  d1 9 10 5 6 7 8 9 10 11 12 13 14
  d2 10 20 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
  d2 11 20 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
  d2 12 20 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
  d2 13 20 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
  d2 14 20 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
  d3 15 5 0 1 2 3 4
  d3 16 5 0 1 2 3 4
  d3 17 5 0 1 2 3 4
  d3 18 5 0 1 2 3 4
  d3 19 5 0 1 2 3 4
  f1 20 0
  f1 21 0
  f1 22 0
  f1 23 0
  f1 24 0
  
  …
  </code></pre>
  
  <p>This is file1 and file2</p>
  
  <p>file1</p>
  
  <pre><code>"V1" "V2" "V3" "V4" "V5" "Site"
  "1" 0 1 0 0 0 "a1"
  "2" 0 1 1 0 0 "d1"
  "3" 0 1 1 1 1 "d2"
  "4" 1 0 0 0 0 "d3"
  "5" 0 0 0 0 0 "f1"
  </code></pre>
  
  <p>file2</p>
  
  <pre><code>"Site" "minID" "maxId"
  "1" "a1" 0 4
  "2" "d1" 5 9
  "3" "d2" 10 14
  "4" "d3" 15 19
  "5" "f1" 20 24
  </code></pre>