remove a value with its counterpart conditional on an ID

Question

I have the following data frame

neto	damo
356	008
-356	008
-175	009
700	009
-700	009
250	009
-36	018
-36	018
-36	018

I want to eliminate the data with its counterpart conditioned to the ID and that it looks like this:

neto	damo
-175	009
250	009
-108	018

I have tried to put it in absolute values and leave the value that is unique, but this can produce an error if both are negative and should not be eliminated.

Please provide enough code so others can better understand or reproduce the problem. — Community, Jan 27 '23 at 17:06
por ejemplo debe eliminarse los que son iguales en el id y tienen contraparte: ID Neto 1 -34 1 34 1 - 8 1 - 8 debe quedar ID neto 1 -8 1 -8 — Belky Xiomara Mayorga Ardila, Jan 27 '23 at 17:07

one · Answer 1 · 2023-01-27T17:14:46.713

0

df %>%
  mutate(abs_neto=abs(neto)) %>%
  group_by(damo,abs_neto) %>%
  mutate(one_plus_one_minus= any(neto>=0) & any(neto<=0))%>%
  filter(n()==1 | !one_plus_one_minus)%>%
  ungroup 

   neto  damo abs_neto one_plus_one_minus
  <int> <int>    <int> <lgl>             
1  -175     9      175 FALSE             
2   250     9      250 FALSE             
3   250     9      250 FALSE

edited Jan 27 '23 at 17:14

answered Jan 27 '23 at 17:09

one

3,121
1
4
24

akrun · Answer 2 · 2023-01-27T21:17:06.620

We may group by 'damo', remove the groups having only a single absolute 'neto' value, grouped by absolute of 'neto', filter out the groups having one value

library(dplyr)
df1 %>%
   mutate(s1 = sign(neto), absneto = abs(neto)) %>% 
   arrange(damo, desc(absneto), s1 == -1) %>% 
   group_by(damo) %>% 
   mutate(grp = cumsum(s1 > 0)) %>% 
   group_by(grp, .add = TRUE) %>% 
   filter(n_distinct(absneto) > 1|n_distinct(s1) == 1) %>% 
   group_by(s1, .add = TRUE) %>% 
   summarise(neto = sum(neto), .groups = "drop") %>%   
   select(-grp, -s1)

-output

# A tibble: 3 × 2
   damo  neto
  <int> <int>
1     9  -175
2     9   250
3    18  -108

data

df1 <-structure(list(neto = c(356L, -356L, -175L, 700L, -700L, 250L, 
-36L, -36L, -36L), damo = c(8L, 8L, 9L, 9L, 9L, 9L, 18L, 18L, 
18L)), class = "data.frame", row.names = c(NA, -9L))

I like your idea, but what would I do in the case where there was no positive value, only negative, and they had to be accumulated? I add it to the question thank you very much for your help! — Belky Xiomara Mayorga Ardila, Jan 27 '23 at 21:02

remove a value with its counterpart conditional on an ID

2 Answers2

data