How to match negative and positive numbers within a group?

user_13799810 0
Viewed 67

I have a dataframe like the one below

ItemNo    OrderAmount     Date
3845         320       2020-01-21
3245        -100       2020-01-20
4045        -200       2020-01-20
3845         300       2020-01-19
3845         320       2020-01-18
3245         100       2020-01-18
3645         230       2020-01-18
3645        -230       2020-01-18
3245        -100       2020-01-18
3845         320       2020-01-17
4045           0       2020-01-17
3845         320       2020-01-17
3845        -300       2020-01-17
3245         200       2020-01-17
3645         230       2020-01-16
4045         200       2020-01-15
3845        -300       2020-01-15
3245         100       2020-01-15
3245         100       2020-01-15
3845         320       2020-01-15
4045         240       2020-01-15
4045           0       2020-01-15

I wish to match negative and positive numbers within a group (ItemNo), and then delete the matched rows from the data frame. If the OrderAmount is 0, then it is kept in the dataframe. I want the output to be

ItemNo    OrderAmount     Date
3845          320       2020-01-21
3845          320       2020-01-18
3245          100       2020-01-18
3645          230       2020-01-18
3845          320       2020-01-17
4045            0       2020-01-17
3845          320       2020-01-17
3845         -300       2020-01-17
3245          200       2020-01-17
3845          320       2020-01-15
4045          240       2020-01-15
4045            0       2020-01-15

I have tried using:

# Dataframe
DF <- data.frame(ItemNo=c(3845,3245,4045,3845,3845,3245,3645,3645,3245,3845,4045,3845,3845,3245,3645,4045,3845,3245,3245,3845,4045,4045),
                 OrderAmount = c(320,-100,-200,300,320,100,230,-230,-100,320,0,320,-300,200,230,200,-300,100,100,320,240,0),
                 Date = c("2020-01-21","2020-01-20","2020-01-20","2020-01-19","2020-01-18","2020-01-18","2020-01-18","2020-01-18","2020-01-18","2020-01-17","2020-01-17","2020-01-17","2020-01-17","2020-01-17","2020-01-16","2020-01-15","2020-01-15","2020-01-15","2020-01-15","2020-01-15","2020-01-15","2020-01-15"))
DF$Date <- as.Date(DF$Date)
# Order by date -> oldest first
DF <- DF[order(desc(DF$Date)),]

DF %>%
  group_by(ItemNo, nvalue = abs(OrderAmount)) %>% 
  filter(!duplicated(OrderAmount)) %>%
  filter(sum(OrderAmount) >= 0) %>% 
  ungroup() %>%
  select(-nvalue)

E.g. I want the output for ItemNo 3245 to be

ItemNo    OrderAmount     Date
3245        -100       2020-01-20 #DELETE: Matched with 5. Row
3245         100       2020-01-18
3245        -100       2020-01-18 #DELETE: Matched with 6. Row
3245         200       2020-01-17 
3245         100       2020-01-15 #DELETE: Matched with 1. Row
3245         100       2020-01-15 #DELETE: Matched with 3. Row
dplyr dataframe r
2 Answers

This is a tricky algorithm to solve without iteratively building or weeding out a data frame, which would be slow. Here's one possible solution, but it is fairly complex:

balanced_table <- function(d) {
  d <- d[order(d$Date),]
  x <- d$OrderAmount
  x <- factor(x, c(unique(abs(x[x != 0])), 0, -unique(abs(x[x != 0]))))
  x <- table(x)
  neg <- -x[as.numeric(names(x)) < 0]
  pos <- x[as.numeric(names(x)) > 0]
  names(neg) <- -as.numeric(names(neg))
  totals <- pos + neg
  final <- c(totals, x[as.numeric(names(x)) == 0])
  names(final)[final < 0] <- -as.numeric(names(final)[final < 0])
  final[final < 0] <- -final[final < 0]
  final
  res <- tidyr::uncount(as.data.frame(as.table(final[final != 0])), Freq)
  vals <- as.numeric(as.character(res$Var1))
  do.call(rbind, lapply(split(vals, vals), function(v) {
    d[which(d$OrderAmount == v[1])[seq_along(v)],]
  }))
}

`rownames<-`(do.call(rbind, lapply(split(df, df$ItemNo), balanced_table)), NULL)
#>    ItemNo OrderAmount       Date
#> 1    3245         100 2020-01-15
#> 2    3245         200 2020-01-17
#> 3    3645         230 2020-01-16
#> 4    3845        -300 2020-01-15
#> 5    3845         320 2020-01-15
#> 6    3845         320 2020-01-17
#> 7    3845         320 2020-01-17
#> 8    3845         320 2020-01-18
#> 9    3845         320 2020-01-21
#> 10   4045           0 2020-01-15
#> 11   4045           0 2020-01-17
#> 12   4045         240 2020-01-15

Or, to keep the original ordering and thus match the expected output of your question:

df$row <- seq(nrow(df))
df2 <- do.call(rbind, lapply(split(df, df$ItemNo), balanced_table))
`rownames<-`(df2[order(df2$row), names(df2) != "row"], NULL)
#>    ItemNo OrderAmount       Date
#> 1    3845         320 2020-01-21
#> 2    3845         320 2020-01-18
#> 3    3845         320 2020-01-17
#> 4    4045           0 2020-01-17
#> 5    3845         320 2020-01-17
#> 6    3245         200 2020-01-17
#> 7    3645         230 2020-01-16
#> 8    3845        -300 2020-01-15
#> 9    3245         100 2020-01-15
#> 10   3845         320 2020-01-15
#> 11   4045         240 2020-01-15
#> 12   4045           0 2020-01-15

Data

df <- structure(list(ItemNo = c(3845L, 3245L, 4045L, 3845L, 3845L, 
3245L, 3645L, 3645L, 3245L, 3845L, 4045L, 3845L, 3845L, 3245L, 
3645L, 4045L, 3845L, 3245L, 3245L, 3845L, 4045L, 4045L), OrderAmount = c(320L, 
-100L, -200L, 300L, 320L, 100L, 230L, -230L, -100L, 320L, 0L, 
320L, -300L, 200L, 230L, 200L, -300L, 100L, 100L, 320L, 240L, 
0L), Date = structure(c(18282, 18281, 18281, 18280, 18279, 18279, 
18279, 18279, 18279, 18278, 18278, 18278, 18278, 18278, 18277, 
18276, 18276, 18276, 18276, 18276, 18276, 18276), class = "Date")), 
row.names = c(NA, -22L), class = "data.frame")

df
#>    ItemNo OrderAmount       Date
#> 1    3845         320 2020-01-21
#> 2    3245        -100 2020-01-20
#> 3    4045        -200 2020-01-20
#> 4    3845         300 2020-01-19
#> 5    3845         320 2020-01-18
#> 6    3245         100 2020-01-18
#> 7    3645         230 2020-01-18
#> 8    3645        -230 2020-01-18
#> 9    3245        -100 2020-01-18
#> 10   3845         320 2020-01-17
#> 11   4045           0 2020-01-17
#> 12   3845         320 2020-01-17
#> 13   3845        -300 2020-01-17
#> 14   3245         200 2020-01-17
#> 15   3645         230 2020-01-16
#> 16   4045         200 2020-01-15
#> 17   3845        -300 2020-01-15
#> 18   3245         100 2020-01-15
#> 19   3245         100 2020-01-15
#> 20   3845         320 2020-01-15
#> 21   4045         240 2020-01-15
#> 22   4045           0 2020-01-15
user_125003150

I tried this and it seemed to work:

DF <- data.frame(ItemNo=c(3845,3245,4045,3845,3845,3245,3645,3645,3245,3845,4045,3845,3845,3245,3645,4045,3845,3245,3245,3845,4045,4045),
                 OrderAmount = c(320,-100,-200,300,320,100,230,-230,-100,320,0,320,-300,200,230,200,-300,100,100,320,240,0),
                 Date = c("2020-01-21","2020-01-20","2020-01-20","2020-01-19","2020-01-18","2020-01-18","2020-01-18","2020-01-18","2020-01-18","2020-01-17","2020-01-17","2020-01-17","2020-01-17","2020-01-17","2020-01-16","2020-01-15","2020-01-15","2020-01-15","2020-01-15","2020-01-15","2020-01-15","2020-01-15"))

DF$Date <- as.character(DF$Date)
DF <- DF[order(DF$Date),]
DF$rowNumber <- 1:nrow(DF)
DF_TEMP <- DF %>%
    group_by(ItemNo, OrderAmount) %>% 
    mutate(id_row = row_number()) %>%
    group_by(ItemNo, id_row, ab = abs(OrderAmount)) %>%
    filter(n() > 1) %>% ungroup() %>%
    select(-id_row, -ab)
DF <- DF[!(DF$rowNumber %in% DF_TEMP$rowNumber),]
user_137998100
Related