How do I sum certain partially-duplicated rows but not all duplicated rows (based on a conditional statement)?

Admin 1
Viewed 23

My current dataset looks something like this:

INVOICE_ID  MONTH  AMOUNT
1            2     -500
1            2     -500
1            2     1000
1            3     30
1            3     10
1            3     -30
2            5     40
2            5     60
3            4     50
....

I want to convert this to look like:

INVOICE_ID  MONTH  AMOUNT
1            2      0
1            3     10
2            5      40
2            5      60
3            4      50

So for rows that have an INVOICE_ID and MONTH that match a row with the same INVOICE_ID and MONTH and a negative AMOUNT, I want to consolidate these rows into one row that is the sum of the AMOUNTs in all matching rows. However, for rows that have an INVOICE_ID and MONTH that match rows with the same INVOICE_ID and MONTH but only positive AMOUNTs, I want to leave this data untouched.

I'm not a great R user, so this feels above my level and I'm not sure how to approach it. Any help would be greatly appreciated!

data-cleaning if-statement duplicates r
2 Answers

With dplyr you could do:

library(dplyr)

x <- data.frame(INVOICE_ID = c(1, 1,1,1,1,1,2,2,3), 
                MONTH = c(2, 2,2,3,3,3,5,5,4), 
                AMOUNT = c(-500, -500,1000,30,10,-30,40,60,50))

x %>% group_by(INVOICE_ID, MONTH) %>% 
      mutate(check = ifelse(any(AMOUNT < 0), 1, 0),
             AMOUNT = ifelse(check == 1, sum(AMOUNT), AMOUNT)) %>% 
      select(-check) %>% 
      distinct()

# A tibble: 5 x 3
# Groups:   INVOICE_ID, MONTH [4]
  INVOICE_ID MONTH AMOUNT
       <dbl> <dbl>  <dbl>
1          1     2      0
2          1     3     10
3          2     5     40
4          2     5     60
5          3     4     50
user_87916350

Here's an approach with dplyr. First, group by INVOICE_ID and MONTH. Then determine if any AMOUNT in the month is negative with any(AMOUNT < 0). I'll add ! because I want to cummulatively sum in the next step. Then, use cumsum to make IDs for the non-negative invoices. Finally, regroup by INVOICE_ID, MONTH and our new ID. Then summarise AMOUNT.

library(dplyr)
data %>%
  group_by(INVOICE_ID,MONTH) %>%
  mutate(ID = !any(AMOUNT < 0), ID = cumsum(ID)) %>%
  group_by(INVOICE_ID,MONTH,ID) %>%
  dplyr::summarise(across(AMOUNT,sum))
## A tibble: 5 x 4
## Groups:   INVOICE_ID, MONTH [4]
#  INVOICE_ID MONTH    ID AMOUNT
#       <int> <int> <int>  <int>
#1          1     2     0      0
#2          1     3     0     10
#3          2     5     1     40
#4          2     5     2     60
#5          3     4     1     50

You can remove the ID with dplyr::select(-ID).

user_130953260
Related