Remove random amount of rows based on matching columns of another dataframe

Question

I have two dataframes; orders and returns.

Orders:

Index | OrderID |TransactionID | ProductID | BuyerID |     Date    | TotalOrder | ProductPrice
-----------------------------------------------------------------------------------------------
  0   |    A    |      A-1     |     05    |    1    |  dd-mm-yyy  |     140    |     50
  1   |    A    |      A-2     |     45    |    1    |  dd-mm-yyy  |     140    |     90
  2   |    B    |      B-1     |     33    |    1    |  dd-mm-yyy  |     15     |     10
  3   |    B    |      B-2     |     01    |    1    |  dd-mm-yyy  |     15     |     5
  4   |    C    |      C-1     |     45    |    1    |  dd-mm-yyy  |     90     |     90
  5   |    D    |      D-1     |     45    |    1    |  dd-mm-yyy  |     90     |     90
  6   |    E    |      E-1     |     45    |    1    |  dd-mm-yyy  |     90     |     90
  7   |    F    |      F-1     |     45    |    2    |  dd-mm-yyy  |     90     |     90

Returns:

ProductID | BuyerID | ProductPrice | Amount
------------------------------------------------------------------------------------------------
   33     |    1    |     10      |   1
   45     |    1    |     90      |   2
   01     |    1    |     5       |   1

For every row in returns, a row with a matching ProductID, BuyerID and ProductPrice in orders should be removed n (= returns['Amount']) times. So I'll end up with only the rows with index 0, 7 and two of either 1, 4, 5 or 6.

Index | OrderID |TransactionID | ProductID | BuyerID |     Date    | TotalOrder | ProductPrice
------------------------------------------------------------------------------------------------
  0   |    A    |      A-1     |     05    |    1    |  dd-mm-yyy  |     140    |     50
  7   |    F    |      F-1     |     45    |    2    |  dd-mm-yyy  |     90     |     90
 -----------------------------------------------------------------------------------------
 | 1  |    A    |      A-2     |     45    |    1    |  dd-mm-yyy  |     140    |     90 |
 | 4  |    C    |      C-1     |     45    |    1    |  dd-mm-yyy  |     90     |     90 |+ 2 out 
 | 5  |    D    |      D-1     |     45    |    1    |  dd-mm-yyy  |     90     |     90 |of these
 | 6  |    E    |      E-1     |     45    |    1    |  dd-mm-yyy  |     90     |     90 |
 -----------------------------------------------------------------------------------------

Is there any way I can do this?

Welcome to SO! It really helps user in finding an answer if you provide a [minimal reproducible-(pandas) examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples). — Timus, Nov 26 '20 at 11:55

score 0 · Accepted Answer · answered Nov 26 '20 at 11:20

This should work:

import pandas as pd

orders = pd.DataFrame(
    {
        'orderId': ['a', 'a', 'b', 'b', 'c', 'd', 'e', 'f'],
        'pid': [5, 45, 33, 1, 45, 45, 45, 45],
        'bid': [1, 1, 1, 1, 1, 1, 1, 2],
        'torder': [140, 140, 15, 15, 90, 90, 90, 90],
        'px': [50, 90, 10, 5, 90, 90, 90, 90]
    }
)

returns = pd.DataFrame(
    {
        'pid': [33, 45, 1],
        'bid': [1, 1, 1],
        'px': [10, 90, 5],
        'amount': [1, 2, 1]
    }
)

orders['temp'] = 1
orders['rid'] = orders.groupby(['pid', 'bid', 'px'])['temp'].transform(pd.Series.cumsum)
orders = orders.merge(returns, on=['pid', 'bid', 'px'], how='outer').fillna(0)
left_orders = orders[orders.rid > orders.amount].drop(columns=['temp', 'rid', 'amount'])

print(left_orders)

Output:

  orderId  pid  bid  torder  px
0       a    5    1     140  50
3       d   45    1      90  90
4       e   45    1      90  90
7       f   45    2      90  90

Remove random amount of rows based on matching columns of another dataframe

1 Answers1