Map pandas dataframe on multiple keys as columns or multiIndex
<p>Setup: two pandas dataframes; data from df2 needs to be added to df1, as explained below:</p>

<ul>
<li>df1 and df2 are multiIndexed with the same four levels</li>
<li>df1 contains more rows than df2</li>
<li>df1 has three copies (in rows) of a value per unique combination of three out of the four levels of the index; that is, each row differs only with respect to the 4th level</li>
<li>df2 only partially aligns with df1 on the other 3 levels (df2 contains extraneous rows)</li>
<li>df2 contains only one column</li>
</ul>

<p>I want to add values from the one column of df2 to all three copies of the rows in df1 where the three corresponding levels match.</p>

<p>Having learned that 'merging with more than one level overlap on a multiIndex is not implemented' in pandas, I propose to map the values, but have not found a way to map on (multiple) index levels, or multiple columns, if reset index levels to columns:</p>

<pre><code>df1 = pd.DataFrame(np.array([['Dec', 'NY', 'Ren', 'Q1', 10],
   ['Dec', 'NY', 'Ren', 'Q2', 12],
   ['Dec', 'NY', 'Ren', 'Q3', 14],
   ['Dec', 'FL', 'Mia', 'Q1', 6],
   ['Dec', 'FL', 'Mia', 'Q2', 8],
   ['Dec', 'FL', 'Mia', 'Q3', 17],
   ['Apr', 'CA', 'SC', 'Q1', 1],
   ['Apr', 'CA', 'SC', 'Q2', 2],
   ['Apr', 'CA', 'SC', 'Q3', 3]]), columns=['Date', 'State', 'County', 'Quarter', 'x'])

df1.set_index(['Date', 'State', 'County', 'Quarter'], inplace=True)

df2 = pd.DataFrame(np.array([['Dec', 'NY', 'Ren', 0.4],
   ['Dec', 'FL', 'Mia', 0.3]]), columns=['Date', 'State', 'County', 'y'])

df2.set_index(['Date', 'State', 'County', 'y'], inplace=True)

df_combined = df1['Date', 'State', 'County'].map(df2)
</code></pre>

<p>You can temporarily change <code>df1</code> to change the index to do the join:</p>

<pre><code>df_combined = df1.reset_index(3).join(df2,how='left')

&gt;&gt;&gt; df_combined
           level_3   x    y
Apr CA SC       Q1   1  NaN
       SC       Q2   2  NaN
       SC       Q3   3  NaN
Dec FL Mia      Q1   6  0.3
       Mia      Q2   8  0.3
       Mia      Q3  17  0.3
    NY Ren      Q1  10  0.4
       Ren      Q2  12  0.4
       Ren      Q3  14  0.4

df_combined.set_index('level_3',append=True, inplace=True)
df_combined.index.rename(None,3,inplace=True)

&gt;&gt;&gt; df_combined
                x    y
Apr CA SC  Q1   1  NaN
           Q2   2  NaN
           Q3   3  NaN
Dec FL Mia Q1   6  0.3
           Q2   8  0.3
           Q3  17  0.3
    NY Ren Q1  10  0.4
           Q2  12  0.4
           Q3  14  0.4
</code></pre>

<p>The reset_index method is used to temporarily turn the index that isn't in <code>df2</code> into a column so that you can do a normal join.  Then turn the column back into an index when you're done.</p>

python pandas