I have a pandas data frame
| Column A | Column B |
|---|---|
| Report 1 | Vendor A |
| Report 1 | Vendor B |
| Report 2 | Vender C |
| Report 2 | Vendor B |
| Report 3 | Vendor A |
| Report 3 | Vendor C |
I’m looking for a way to exclude any reports that Vender A appears on. The correct output should be
| Column A | Column B |
|---|---|
| Report 2 | Vendor C |
| Report 2 | Vendor B |
So far, I’ve tried using multi-index and groupby, but whenever I filter, I’m only seeing the rows that exclude Vendor A, not the multi-level.
import pandas as pd
df = pd.read_excel('Expense Data.xlsx', sheet_name='FY23')
cols = ['Report ID', 'Vendor']
grouped_df = df.set_index(cols)
print(grouped_df.head)
grouped_df.xs('Vendor A', level='Vendor', drop_level=False)
print(grouped_df.loc[~grouped_df.index.get_level_values(1).str.contains("Vendor A", na=False)])
New contributor
Ryan Mucha is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.