join two pandas pivot tables
<p>Considering two similar pandas pivot tables, how to join these two tables on their indexes e.g. <code>country</code>. For example: </p>

<pre><code>df1.pivot_table(index='country', columns='year', values=['rep','sales'], aggfunc='first')

          rep                       sales                  
year     2013  2014    2015  2016   2013  2014  2015  2016
country                                                  
fr       None  kyle  claire  None   None   10    20    None
uk       kyle  None    None  john    12   None  None   10
usa      None  None    None  john   None  None  None   21


df2.pivot_table(index='country', columns='year', values=['rep','sales'], aggfunc='first')

          rep                          sales                  
year     2013  2014    2015   2016     2013   2014  2015  2016
country                                                  
fr       120   marc   debbie  None     None    0    56   None
uk       marc  None    100    peter    45    None   65    10
ca       89    None    None   peter    None   33   None   78
</code></pre>

<blockquote>
  <p>How to join these tables</p>
</blockquote>

<p>Is vague.  There are many ways to join these tables.</p>

<h3>Setup</h3>

<pre><code>idx = pd.Index(['fr', 'uk', 'usa'], name='country')
col = pd.MultiIndex.from_product([['rep', 'sales'], range(2013, 2017)],
                                 names=[None, 'year'])

p1 = pd.DataFrame([
        [None, 'kyle', 'claire', None, None, 10, 20, None],
        ['kyle', None, None, 'john', 12, None, None, 10],
        [None, None, None, 'john', None, None, None, 21]
    ], idx, col, object)

p2 = pd.DataFrame([
        [120, 'marc', 'debbie', None, None, 0, 56, None],
        ['marc', None, 100, 'peter', 45, None, 65, 10],
        [89, None, None, 'peter', None, 33, None, 78]
    ], idx, col, object)
</code></pre>

<hr>

<h3>Likely Solutions</h3>

<pre><code>p1.combine_first(p2)
</code></pre>

<p><a href="http://i.stack.imgur.com/GqhE6.png" rel="nofollow"><img src="http://i.stack.imgur.com/GqhE6.png" alt="enter image description here"></a></p>

<hr>

<pre><code>pd.concat([p1, p2], axis=1, keys=['p1', 'p2'])
</code></pre>

<p><a href="http://i.stack.imgur.com/BcHEp.png" rel="nofollow"><img src="http://i.stack.imgur.com/BcHEp.png" alt="enter image description here"></a></p>

python pandas join