title: Union all type query with python pandas
body: <p>I am attempting to use pandas to perform data analysis on a flat source of data. Specifically, what I'm attempting to accomplish is the equivalent of a Union All query in SQL.</p>
  
  <p>I am using the read_csv() method to input the data and the output has unique integer indices and approximately 30+ columns.</p>
  
  <p>Of these columns, several contain identifying information, whilst others contain data.</p>
  
  <p>In total, the first 6 columns contain identifying informations which uniquely identifies an entry. Following these 6 columns there are a range of columns (A,B... etc) which reference the value. Some of these columns are linked together in sets, for example (A,B,C) belong together, as do (D,E,F).</p>
  
  <p>However, (D,E,F) are also related to (A,B,C) as follows ((A,D),(B,E),(C,F)).
  What I am attempting to do is take my data set which has as follows:</p>
  
  <pre><code>(id1,id2,id3,id4,id5,id6,A,B,C,D,E,F) 
  </code></pre>
  
  <p>and return the following</p>
  
  <pre><code>((id1,id2,id3,id4,id5,id6,A,B,C),
   (id1,id2,id3,id4,id5,id6,D,E,F))
  </code></pre>
  
  <p>Here, as A and D are linked they are contained within the same column.</p>
  
  <p>(Note, this is a simplification, there are approximately 12 million unique combinations in the total dataset)</p>
  
  <p>I have been attempting to use the merge, concat and join functions to no avail. I feel like I am missing something crucial as in an SQL database I can simply perform a union all query (which is quite slow admittedly) to solve this issue.</p>
  
  <p>I have no working sample code at this stage.</p>
  
  <p>Another way of writing this problem based upon some of the pandas docs.</p>
  
  <pre><code>left = key lval
  right = key rval
  merge(left, right, on=key) = key, lval, rval
  </code></pre>
  
  <p>Instead I want:</p>
  
  <pre><code>left = kev, lval
  right = key, lval
  union(left, right) = key, lval
                       key, rval
  </code></pre>
  
  <p>I'm not sure if a new indexing key value would need to be created for this.</p>