Hello, I have a dataframe with a column named 'InvoiceNumber' (invoiceNo, example 541431). And another column named ' Stockcode'.
When InvoiceNo starts by a C (example C541433) and it matches a previous StockCode I have to cancel the 'Quantity' and/or 'Unitprice' from my analysis. (it means a customer RETURNED the item). I'm trying to do an RFM analysis and a Market Basket Analysis but first, I need to take this into consideration first.
How can I solve this problem? I have 500K+ rows of transactional data.
| InvoiceNo | StockCode | Description | Quantity | InvoiceDate | UnitPrice | CustomerID | Country |
| 541431 | 23166 | MEDIUM CERAMIC TOP STORAGE JAR | 74215 | 18/1/2011 10:01 | 1.04 | 12346 | United Kingdom |
| C541433 | 23166 | MEDIUM CERAMIC TOP STORAGE JAR | -74215 | 18/1/2011 10:17 | 1.04 | 12346 | United Kingdom |
| 537626 | 85116 | BLACK CANDELABRA T-LIGHT HOLDER | 12 | 7/12/2010 14:57 | 2.1 | 12347 | Iceland |
| 537626 | 22375 | AIRLINE BAG VINTAGE JET SET BROWN | 4 | 7/12/2010 14:57 | 4.25 | 12347 | Iceland |
| 537626 | 71477 | COLOUR GLASS. STAR T-LIGHT HOLDER | 12 | 7/12/2010 14:57 | 3.25 | 12347 | Iceland |
| 537626 | 22492 | MINI PAINT SET VINTAGE | 36 | 7/12/2010 14:57 | 0.65 | 12347 | Iceland |
| 537626 | 22771 | CLEAR DRAWER KNOB ACRYLIC EDWARDIAN | 12 | 7/12/2010 14:57 | 1.25 | 12347 | Iceland |
| 537626 | 22772 | PINK DRAWER KNOB ACRYLIC EDWARDIAN | 12 | 7/12/2010 14:57 | 1.25 | 12347 | Iceland |
| 537626 | 22773 | GREEN DRAWER KNOB ACRYLIC EDWARDIAN | 12 | 7/12/2010 14:57 | 1.25 | 12347 | Iceland |
| 537626 | 22774 | RED DRAWER KNOB ACRYLIC EDWARDIAN | 12 | 7/12/2010 14:57 | 1.25 | 12347 | Iceland |
| 537626 | 22775 | PURPLE DRAWERKNOB ACRYLIC EDWARDIAN | 12 | 7/12/2010 14:57 | 1.25 | 12347 | Iceland |
| 537626 | 22805 | BLUE DRAWER KNOB ACRYLIC EDWARDIAN | 12 | 7/12/2010 14:57 | 1.25 | 12347 | Iceland |
| 537626 | 22725 | ALARM CLOCK BAKELIKE CHOCOLATE | 4 | 7/12/2010 14:57 | 3.75 | 12347 | Iceland |
| 537626 | 22726 | ALARM CLOCK BAKELIKE GREEN | 4 | 7/12/2010 14:57 | 3.75 | 12347 | Iceland |
| 537626 | 22727 | ALARM CLOCK BAKELIKE RED | 4 | 7/12/2010 14:57 | 3.75 | 12347 | Iceland |
| 537626 | 22728 | ALARM CLOCK BAKELIKE PINK | 4 | 7/12/2010 14:57 | 3.75 | 12347 | Iceland |
| 537626 | 22729 | ALARM CLOCK BAKELIKE ORANGE | 4 | 7/12/2010 14:57 | 3.75 | 12347 | Iceland |
It doesn't look like it's a duplicate. It looks like the quantity is negative so it seems like it's already compensated.
