Python Pandas - filtering df by the number of unique values within a group
<p>Here is an example of data I'm working on. (as a pandas df)</p>

<pre><code>    index   inv Rev_stream  Bill_type   Net_rev
       1    1   A           Original    -24.77
       2    1   B           Original    -24.77
       3    2   A           Original    -409.33
       4    2   B           Original    -409.33
       5    2   C           Original    -409.33
       6    2   D           Original    -409.33
       7    3   A           Original    -843.11
       8    3   A           Rebill       279.5
       9    3   B           Original    -843.11
      10    4   A           Rebill       279.5
      11    4   B           Original    -843.11
      12    5   B           Rebill       279.5
</code></pre>

<p>How could I filter this df, in a way to only get the lines where invoice/Rev_stream combo has both original and rebill kind of Net_rev. In the example above it would be only lines with index 7 and 8. </p>

<p>Is there an easy way to do it, without iterating over the whole dataframe and building dictionaries of invoice+RevStream : Bill_type?</p>

<p>What I'm looking for is some kind of</p>

<pre><code>df = df[df[['inv','Rev_stream']]['Bill_type'].unique().len() == 2]
</code></pre>

<p>Unfortunately the code above doesn't work.</p>

<p>Thanks in advance.</p>

<p>You can group your data by <code>inv</code> and <code>Rev_stream</code> columns and then check for each group if both <code>Original</code> and <code>Rebill</code> are in the <code>Bill_type</code> values and filter based on the condition:</p>

<pre><code>(df.groupby(['inv', 'Rev_stream'])
   .filter(lambda g: 'Original' in g.Bill_type.values and 'Rebill' in g.Bill_type.values))
</code></pre>

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

python pandas dataframe filtering