title: Merging two DataFrames based on indexes from two other DataFrames
body: <p>I'm new to pandas have tried going through the docs and experiment with various examples, but this problem I'm tacking has really stumped me.</p>
  
  <p>I have the following two dataframes (DataA/DataB) which I would like to merge on a per global_index/item/values basis.</p>
  
  <pre><code>DataA                      DataB
  row  item_id  valueA       row    item_id  valueB
  0    x        A1           0      x        B1
  1    y        A2           1      y        B2
  2    z        A3           2      x        B3
  3    x        A4           3      y        B4
  4    z        A5           4      z        B5
  5    x        A6           5      x        B6
  6    y        A7           6      y        B7
  7    z        A8           7      z        B8
  </code></pre>
  
  <p>The list of items(item_ids) is finite and each of the two dataframes represent a the value of a trait (trait A, trait B) for an item at a given global_index value.</p>
  
  <p>The global_index could roughly be thought of as a unit of "time"</p>
  
  <p>The mapping between each data frame (DataA/DataB) and the global_index is done via the following two mapper DFs:</p>
  
  <pre><code>DataA_mapper
  global_index  start_row  num_rows
  0             0          3
  1             3          2
  3             5          3
  
  
  DataB_mapper
  global_index  start_row  num_rows
  0             0          2
  2             2          3
  4             5          3
  </code></pre>
  
  <p>Simply put for a given global_index (eg: 1) the mapper will define a list of rows into the respective DFs (DataA or DataB) that are associated with that global_index.</p>
  
  <p>For example, for a global_index value of 0:</p>
  
  <ul>
  <li>In DF DataA rows 0..2 are associated with global_index 0</li>
  <li>In DF DataB rows 0..1 are associated with global_index 0</li>
  </ul>
  
  <p>Another example, for a global_index value of 2:</p>
  
  <ul>
  <li>In DF DataB rows 2..4 are associated with global_index 2</li>
  <li>In DF DataA there are no rows associated with global_index 2</li>
  </ul>
  
  <p>The ranges [start_row,start_row + num_rows) presented do not overlap each other and represent a unique sequence/range of rows in their respective dataframes (DataA, DataB)</p>
  
  <p>In short no row in either DataA or DataB will be found in more than one range.</p>
  
  <hr>
  
  <p>I would like to merge the DFs so that I get the following dataframe:</p>
  
  <pre><code>row   global_index  item_id   valueA   valueB
  0     0             x         A1        B1
  1     0             y         A2        B2
  2     0             z         A3        NaN
  3     1             x         A4        B1
  4     1             z         A5        NaN
  5     2             x         A4        B3
  6     2             y         A2        B4
  7     2             z         A5        NaN
  8     3             x         A6        B3
  9     3             y         A7        B4
  10    3             z         A8        B5
  11    4             x         A6        B6
  12    4             y         A7        B7
  13    4             z         A8        B8
  </code></pre>
  
  <p>In the final datafram any pair of <strong>global_index/item_id</strong> there will ever be either:</p>
  
  <ol>
  <li>a value for both valueA and valueB </li>
  <li>a value only for valueA </li>
  <li>a value only for valueB</li>
  </ol>
  
  <p>With the requirement being if there is only one value for a given global_index/item (eg: valueA but no valueB) for the last value of the missing one to be used.</p>