Click here to Skip to main content
15,885,096 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I have many csv files with dataframes looking like these.
 
Dummy Data:
df1 <- structure(list(Date = c("24/06/2002", "24/06/2002", "25/06/2002", 
                               "02/07/2002","24/07/2002", "08/07/2002",
                               "08/07/2002", "15/07/2002", "17/07/2002", 
                               "22/07/2002", "22/07/2002", "28/07/2002", "29/07/2002"), 
                      payment = c(200,   1000, 1000, 
                                  -1000,-1000, 1200,
                                  -1200, 1200, 1200,
                                  200, 56700, -56700, -200), 
                      Code = c("ABC", "M567", "M567", "M567", 
                               "M567","M567", "M567", 
                                "M567", "M567", "M300", 
                               "M678", "M678", "ABC"), 
                      ID = c(NA, "98","98","M11",
                             NA,"K999", 
                             "K999", "111", "111", "11",
                             "12345", NA, NA)), row.names = c(NA, -13L), class = "data.frame")

Some of the rows are just return of payment (falsely entered):

        Date payment Code    ID
1  24/06/2002     200  ABC  <NA>
2  24/06/2002    1000 M567    98
3  25/06/2002    1000 M567    98
4  02/07/2002   -1000 M567   M11
5  24/07/2002   -1000 M567  <NA>
6  08/07/2002    1200 M567  K999
7  08/07/2002   -1200 M567  K999
8  15/07/2002    1200 M567   111
9  17/07/2002    1200 M567   111
10 22/07/2002     200 M300    11
11 22/07/2002   56700 M678 12345
12 28/07/2002  -56700 M678  <NA>
13 29/07/2002    -200  ABC  <NA>

I want to delete the rows that has a same +ve and -ve payment by taking Code and ID into consideration. 
i.e., 

 - if the code and ID column matches and the payment is cancelling out then delete those two columns 
 - if ID is present for +ve payment and absent for -ve payment viseversa then only take Code for grouping
- If ID is absent for both +ve and -ve then only take Code column for grouping

So, the resultant dataframe for all the csv files should be like these:

  Date       payment Code  ID   
  <chr>        <dbl> <chr> <chr>
1 22/07/2002     200 M300  11   
2 15/07/2002    1200 M567  111  
3 17/07/2002    1200 M567  111  
4 25/06/2002    1000 M567  98   
5 02/07/2002   -1000 M567  M11        
       
      
Can anyone help me with this?<pre>


What I have tried:

#But this code doesnt work for cases with NAs
library(dplyr)
library(data.table)
library(tidyr)
df1 %>% 
  group_by(Code) %>%
  fill(ID, .direction = "downup") %>%
  ungroup %>% 
  mutate(absPayment = abs(payment)) %>% 
  arrange(ID, Code, absPayment) %>%
  group_by(Code, ID, absPayment) %>%
  mutate(grp = rowid(sign(payment))) %>% 
  group_by(grp, .add = TRUE) %>%
  filter(n() == 1) %>%
  ungroup %>% 
  select(-grp, -absPayment)
Posted
Updated 14-Oct-22 8:26am
v6
Comments

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900