Apply shift for comparison on multiple columns over groupby pandas
<p>I have a dataframe(raw_data) containing multiple columns:</p>

<pre><code>raw_data = {'one': ['A', 'B', 'A', 'C', 'B', 'B', 'A', 'C', 'A', 'B', 'C', 'B'],'two' : [3,4,5,6,2,7,9,12,1,10,11,8],'three': ['bcd','qpv', 'cba','klm','zfv','klm','abc','abc','abc','lmf','fly','zdb'],'four':['cba','klm','tcf','fly','zfb','zdb','pcd','tlm','bcd','klm','mvt','jbm'], 'five':['apple','lemon','banana','water','kiwi','lemon','orange','apple','apple','straw','water','pom']}

df = pd.DataFrame(raw_data, columns = ['one', 'two', 'three','four','five'])
</code></pre>

<p>From this df, I can generate new columns or directly shift by 1 for column three &amp; five.</p>

<p>Output:</p>

<p>1) Groupby 'one' </p>

<p>2) sort by 'two' </p>

<p>3) shift 'three' 1 level up, new column 'three_new'</p>

<p>4) shift 'five' 1 level, new column 'five_new'</p>

<p>5) compare 'four' with 'three_new' &amp; 'five' with 'five_new', if both match 
remove the first row &amp; continue</p>

<p>6) comparison on groupby</p>

<p>The output looks like this :</p>

<pre><code>one two three   four    five
A   3   bcd     cba     apple
A   5   cba     tcf     banana
A   9   abc     pcd     orange
B   2   zfv     zfb     kiwi
B   7   klm     zdb     lemon
B   8   zdb     jbm     pom
B   10  lmf     klm     straw
C   11  fly     mvt     water
C   12  abc     tlm     apple
</code></pre>

<p>Initial df has 12 rows &amp; output df has 9 rows, filtered on the above criteria. Please help.</p>

<p>Thanks</p>

<p>You could perform those steps as follows:</p>

<pre><code># Groupby column one and sort values by column two 
df_grouped = df.groupby('one').apply(lambda x: x.sort_values('two'))

# Shift by one level up and compare
comparison_1 = df_grouped['five']==df_grouped['five'].shift(-1)
comparison_2 = df_grouped['four']==df_grouped['three'].shift(-1)

# Select not of subset
df_grouped[~(comparison_1 &amp; comparison_2)].reset_index(drop=True)
</code></pre>

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

python pandas lambda