How do I filter a multiindex dataframe based on columns that meet certain conditions?

Question

I have a multiindex dataframe. Columns 'partner', 'employer', and 'date' are the multiindex. enter image description here

partner	employer	date	ecom	sales
A	a	10/01/21	1	0
A	a	10/02/21	1	0
A	a	10/03/21	0	1
A	b	10/01/21	0	1
A	b	10/02/21	1	0
A	b	10/03/21	1	0
B	c	10/03/21	1	0
B	c	10/04/21	1	0
B	c	10/04/21	0	1

I'm trying to find which unique (parter, employer) pairs have 'ecom' BEFORE 'sales'. For example, I want to have the output to be. How do I filter through each (partner, employer) pair with these conditions in python? enter image description here

partner	employer	date	ecom	sales
A	a	10/01/21	1	0
A	a	10/02/21	1	0
A	a	10/03/21	0	1
B	c	10/03/21	1	0
B	c	10/04/21	1	0
B	c	10/04/21	0	1

Answer 1

Try this:

# Find the first date where ecom or sales is not 0
first_date = lambda col: col.first_valid_index()[-1]
tmp = df.replace(0, np.nan).sort_index().groupby(level=[0,1]).agg(
    first_ecom=('ecom', first_date),
    first_sales=('sales', first_date)
)

# The (partner, employer) pairs where ecom happens before sales
idx = tmp[tmp['first_ecom'] < tmp['first_sales']].index

# Condition to filter the original frame
cond = df.index.droplevel(-1).isin(idx)

# Result
df[cond]