Dropping multilevel columns from lists of dictionaries in pandas
<p>I have a dataframe with multilevel columns, like the one in the following MWE:</p>

<pre><code>df = pd.DataFrame([[1,2],[3,4]], columns=[['a','c'],['b','d']], index=['one','two'])
df.columns.names = ['aa', 'bb']
</code></pre>

<p>Which looks like this:</p>

<pre><code>In [267]: df
Out[267]: 
aa   a  c
bb   b  d
one  1  2
two  3  4
</code></pre>

<p>I also have a list of dictionaries like the following:</p>

<pre><code>to_keep = [{'aa':'a', 'bb':'b'}, {'aa':'q', 'bb':'d'}]
</code></pre>

<p>What I'm looking to do:</p>

<p>If the multilevel column labels are in the <code>to_keep</code> list then keep them in <code>df</code>, otherwise drop them from the df.</p>

<p>So for <code>df</code> and <code>to_keep</code> above the resulting dataframe would look like:</p>

<pre><code>aa   a
bb   b
one  1
two  3
</code></pre>

<p>as <code>{'aa':'c', 'bb':'d'}</code> is not contained within <code>to_keep</code>. Is this possible?</p>

<p>Create a <code>DF</code> mapping the column names to it's levels of the multi-index <code>DF</code>:</p>

<pre><code>level_df = pd.DataFrame(df.columns.values.tolist(), columns=np.array(df.columns.names))
level_df
</code></pre>

<p><a href="http://i.stack.imgur.com/55E5e.png" rel="nofollow"><img src="http://i.stack.imgur.com/55E5e.png" alt="Image"></a></p>

<p>Create another <code>DF</code> which keeps our required mapping containing list of dicts:</p>

<pre><code>keep = pd.DataFrame(to_keep)
keep
</code></pre>

<p><a href="http://i.stack.imgur.com/rUQrD.png" rel="nofollow"><img src="http://i.stack.imgur.com/rUQrD.png" alt="Image"></a></p>

<p>Concatenate both <code>DFs</code> row-wise:</p>

<pre><code>df_concat = pd.concat([level_df, keep], ignore_index=True)
df_concat
</code></pre>

<p><a href="http://i.stack.imgur.com/0gFSj.png" rel="nofollow"><img src="http://i.stack.imgur.com/0gFSj.png" alt="Image"></a></p>

<p>Remove all duplicates from concatenated <code>DF</code>. Drop off the level after checking if the remaining values in the concatenated <code>DF</code> is present in <code>level_df</code>:</p>

<pre><code>cond = df_concat[~df_concat.duplicated(keep=False)]
df.drop([tuple(x) for x in cond[cond.isin(level_df).all(axis=1)].values], axis=1)
</code></pre>

<p><a href="http://i.stack.imgur.com/i3pfE.png" rel="nofollow"><img src="http://i.stack.imgur.com/i3pfE.png" alt="Image"></a></p>

<p>Note: Here it is assumed that the keys of the dictionaries match the multi-index column names.</p>

python pandas multi-index