Filter Pandas DF

Question

I got a Dataframe with three columns: "Company", "Cost" and "Refund".

raw_df = pd.DataFrame({
'Company': ['A', 'A', 'B', 'A', 'A'],
'Cost': [10, 10, 10, 0, 158],
'Refund': [0, 0, 0, 10, 0]})

If there has been a refund, then my goal is to remove the corresponding cost. For the above example, the solution would be:

filtered_df = pd.DataFrame({
'Company': ['A', 'B', 'A'],
'Cost': [10, 10, 158],
'Refund': [0, 0, 0]})

How can you code it?

Clarification notes:

A row can never have a cost and refund. It is always a cost OR a refund
If there has been a refund, there will ALWAYS be a matching cost with a matching company.
A single refund cancels only a single cost. Like in the example above.

Doesn't really matter, but would be "nice" if it is the closest one. — crystal, Apr 01 '22 at 15:39
@wjandrea. If there is a refund, then there is ALWAYS a corresponding company with the EXACT corresponding cost. — crystal, Apr 01 '22 at 15:49
Noting the cost is in a different transaction than the refund would have been helpful to understand what you need. Try using pd.change.shift(-1) to access a previous row. — juntunen, Apr 01 '22 at 15:50
@crystal Sorry, I mean, can a refund ever have a cost in the same row? — wjandrea, Apr 01 '22 at 15:50
@wjandrea, no they are always separated. A row can never have a cost and refund. It is always a cost OR a refund. — crystal, Apr 01 '22 at 15:52
Does this answer your question? [Pandas: Filtering multiple conditions](https://stackoverflow.com/questions/48978550/pandas-filtering-multiple-conditions) — itprorh66, Apr 01 '22 at 16:11
@itprorh66 No, OP only needs one condition, but they don't know what it would be — wjandrea, Apr 01 '22 at 16:26

anky · Accepted Answer · 2022-04-01T16:42:01.560

You can try this:

self join the dataframes renaming refund to cost. then filter each company to keep the last index and drop them.

tmp = raw_df.drop("Cost",1).rename(columns={"Refund":"Cost"}).query("Cost > 0")
tmp1 = raw_df.merge(tmp.reset_index(),on=['Company','Cost'],how='left')\
            .dropna(subset=['index']).drop_duplicates(["Company","index"],keep='last')
filtered_df = raw_df.drop(sum(map(list,tmp1['index'].items()),[]))

print(filtered_df)

  Company  Cost  Refund
0       A    10       0
2       B    10       0
4       A   158       0

Filter Pandas DF

1 Answers1