title: pandas merge dataframe and pivot creating new columns
body: <p>I've got two input dataframes</p>
  
  <p><strong>df1</strong> (note, this DF could have more columns of data)</p>
  
  <pre><code>   Sample Animal  Time     Sex
  0       1      A   one    male
  1       2      A   two    male
  2       3      B   one  female
  3       4      C   one    male
  4       5      D   one  female
  </code></pre>
  
  <p>and <strong>df2</strong></p>
  
  <pre><code>          a    b    c
  Sample               
  1       0.2  0.4  0.3
  2       0.5  0.7  0.2
  3       0.4  0.1  0.9
  4       0.4  0.2  0.3
  5       0.6  0.2  0.4
  </code></pre>
  
  <p>and I'd like to combine them so that I get the following:</p>
  
  <pre><code>        one_a  one_b  one_c  two_a  two_b  two_c     Sex
  Animal                                                  
  A         0.2    0.4    0.3    0.5    0.7    0.2    male
  B         0.4    0.1    0.9    NaN    NaN    NaN  female
  C         0.4    0.2    0.3    NaN    NaN    NaN    male
  D         0.6    0.2    0.4    NaN    NaN    NaN  female
  </code></pre>
  
  <p>This is how I'm doing things:</p>
  
  <pre><code>df2.reset_index(inplace = True)
  df3 = pd.melt(df2, id_vars=['Sample'], value_vars=list(cols))
  df4 = pd.merge(df3, df1, on='Sample')
  df4['moo'] = df4['Group'] + '_' + df4['variable']
  df5 = pd.pivot_table(df4, values='value', index='Animal', columns='moo')
  df6 = df1.groupby('Animal').agg('first')
  pd.concat([df5, df6], axis=1).drop('Sample',1).drop('Group',1)
  </code></pre>
  
  <p>This works just fine, but could potentially be slow for large datasets.  I'm wondering if any panda-pros see a better (<em>read</em> faster, more efficient)?  I'm new to pandas and can imagine there are some shortcuts here that I don't know about.</p>