Remove/move rows that have matching values in an other dataframe-CodePudding

I'm currently working on managing refunding of products after some systems errors occured. I have a huge xlxs list (table 1) with all pending refunds of a couple of weeks. However, some of the records in that table have been manually refunded and stored in a seperate file (table 2).

Here is an example of my data sets:

All refunds table:

number	ordernumber	Amount	Status
1	123456789	150.50	pending
2	235641458	250.30	pending
3	235984258	50.20	pending
4	283478566	102.45	pending

Manually refunded table

number	ordernumber	Amount	Status
1	123456789	150.50	refunded
2	235641458	250.30	refunded

What I now wish to do is delete (preferably move to a separate table) the rows in the 'all refunds table' whenever the ordernumber matches the ordernumber in the 'manually refunded table'. Could you guys help me out?

Thanks!

CodePudding user response：

With base R you can subset matching ordernumber's with following code:

all_refunds <- data.frame(
  ordernumber = c(123456789, 235641458,     235984258,  283478566),
  amount = c(150.50, 250.30, 50.20, 102.45),
  status = rep("pending", 4)
)

manual_refunds <- data.frame(
  ordernumber = c(123456789, 235641458),
  amount = c(150.50, 250.30)
)

matching <- all_refunds$ordernumber %in% manual_refunds$ordernumber #Find matching ordernumbers.

You can then make a new table with matching rows and remove the rows from all_refunds table, like this:

registered_refunds <- all_refunds[matching, ] #Select only matching from all_refunds
all_refunds <- all_refunds[!matching, ] #Select rows that DO NOT match in all_refunds and reassign the table.

Giving the output:

>all_refunds
  ordernumber amount  status
3   235984258  50.20 pending
4   283478566 102.45 pending
> registered_refunds
  ordernumber amount  status
1   123456789  150.5 pending
2   235641458  250.3 pending

CodePudding user response：

You may try

all_refund <- read.table(text = "number ordernumber Amount  Status
1   123456789   150.50  pending
2   235641458   250.30  pending
3   235984258   50.20   pending
4   283478566   102.45  pending", header = T)
manually <- read.table(text = "number   ordernumber Amount  Status
1   123456789   150.50  refunded
2   235641458   250.30  refunded", header = T)

all_refund[!(all_refund$ordernumber %in% manually$ordernumber),]

  number ordernumber Amount  Status
3      3   235984258  50.20 pending
4      4   283478566 102.45 pending