logo标签列表

按条件清理数据框架。

rdataframedata-cleaning
4

我将尝试通过删除错误添加的行来清理数据框。

以下是虚拟数据：

temp <- structure(list(Date = c("24/06/2002", "24/06/2002", "25/06/2002","25/06/2002", "26/06/2002", 
                               "02/07/2002","03/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,
                                  -1000,-1000,-1000, 1200,
                                  -1200, 1200, 1200,
                                  200, 56700, -56700, -200), 
                      Code = c("ABC", "M567", "M567","M567", "XYZ", "M567", "ABX" ,
                               "M567","M567", "M567", 
                               "M567", "M567", "M300", 
                               "M678", "M678", "ABC"), 
                      ID = c(NA, "98","187","187","12ee","M11","M13",
                             NA,"K999", 
                             "K999", "111", "111", "11",
                             "12345", NA, NA)), row.names = c(NA, -16L), class = "data.frame")

数据框看起来像这样
         Date payment Code    ID
1  24/06/2002     200  ABC  <NA>
2  24/06/2002    1000 M567    98
3  25/06/2002   -1000 M567   187
4  25/06/2002   -1000 M567   187
5  26/06/2002    1000  XYZ  12ee
6  02/07/2002   -1000 M567   M11
7  03/07/2002   -1000  ABX   M13
8  24/07/2002   -1000 M567  <NA>
9  08/07/2002    1200 M567  K999
10 08/07/2002   -1200 M567  K999
11 15/07/2002    1200 M567   111
12 17/07/2002    1200 M567   111
13 22/07/2002     200 M300    11
14 22/07/2002   56700 M678 12345
15 28/07/2002  -56700 M678  <NA>
16 29/07/2002    -200  ABC  <NA>

如你所见，数据中存在正负支付。负数的支付是错误添加的交易或退款。

例如+1200将与-1200相互抵消，基于代码和ID，然而第14行和15行类似，但ID为空-因此我必须用其正数支付行的ID填充并反之亦然，以便我可以删除这两行。

我曾经在StackOverflow上请求程序员帮助尝试的代码：

library(dplyr)
library(data.table)
library(tidyr)
Final_df <- df1 %>% 
  group_by(Code) %>%
  mutate(ind = rowid(payment)) %>%
  group_by(ind, .add = TRUE) %>% 
  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(names(df1))

但是这里的问题在于第8行 - 24/07/2002 -1000 M567 应该由第2行填充，因为代码和正向支付匹配了 - 这样以后我就可以取消这两行。由于该行远离第8行，所以.direction = 'downup'无法起作用。

我认为有一种更好的方法可以填充NA，而不是使用direction（因为它在相似的行相距很远时无法应用）

预期输出结果为：

         Date payment Code    ID

1  25/06/2002   -1000 M567   187
2  25/06/2002   -1000 M567   187
3  26/06/2002    1000  XYZ  12ee
4  02/07/2002   -1000 M567   M11
5  03/07/2002   -1000  ABX   M13
6  15/07/2002    1200 M567   111
7  17/07/2002    1200 M567   111
8  22/07/2002     200 M300    11

我已经被这个问题困扰了5天。任何解决方案都将非常有帮助。

先行感谢。

另一个可能的虚假数据：

temp_2 <-  structure(list(Date = c("22/06/2002", "23/06/2002","24/06/2002", "25/06/2002","25/06/2002", "26/06/2002", 
                               "02/07/2002","03/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, 1000,
                                  -1000,-1000,-1000, 1200,
                                  -1200, 1200, 1200,
                                  200, 56700, -56700, -200), 
                      Code = c("ABC", "M567","M567", "M567","M567", "XYZ", "M567", "ABX" ,
                               "M567","M567", "M567", 
                               "M567", "M567", "M300", 
                               "M678", "M678", "ABC"), 
                      ID = c(NA,"187", "98","187","187","12ee",NA,NA,
                             NA,"K999", 
                             "K999", "111", "111", "11",
                             "12345", NA, NA)), row.names = c(NA, -17L), class = "data.frame")

temp_2的预期输出：

         Date payment Code    ID

1  23/06/2002   -1000 M567   187
2  25/06/2002   -1000 M567   187
3  25/06/2002   -1000 M567   187
4  26/06/2002    1000  XYZ  12ee
5  03/07/2002   -1000  ABX  <NA>
6  24/07/2002   -1000 M567   98
7 15/07/2002    1200 M567   111
8 17/07/2002    1200 M567   111
9 22/07/2002     200 M300    11
3个回答
2
我们可以使用
library(dplyr)
library(data.table)
f1 <- function(dat) {
        i1 <- is.na(dat$ID) & nrow(dat) > 1
         if(any(i1)) {
              dat$ID[i1] <- dat$ID[!i1][match(dat$payment[i1], 
                -dat$payment[!i1])]
               }
            return(dat)
            }
            
temp %>%
   mutate(rn = row_number()) %>%
   group_by(Code, absPayment = abs(payment)) %>%
   filter(sum(payment) != 0) %>%
   group_modify(~ f1(.x)) %>%
   group_by(ID, .add = TRUE) %>%
   mutate(grp = rowid(sign(payment))) %>% 
   group_by(grp, .add = TRUE) %>%
   filter(n() == 1) %>% 
   ungroup %>%
    arrange(rn) %>%
   select(names(temp))

-输出

# A tibble: 8 × 4
  Date       payment Code  ID   
  <chr>        <dbl> <chr> <chr>
1 25/06/2002   -1000 M567  187  
2 25/06/2002   -1000 M567  187  
3 26/06/2002    1000 XYZ   12ee 
4 02/07/2002   -1000 M567  M11  
5 03/07/2002   -1000 ABX   M13  
6 15/07/2002    1200 M567  111  
7 17/07/2002    1200 M567  111  
8 22/07/2002     200 M300  11

对于第二种情况
 temp_2 %>%
   mutate(rn = row_number()) %>% 
   group_by(Code, absPayment = abs(payment)) %>%
   filter(sum(payment) != 0) %>%
   group_modify(~ f1(.x)) %>%
   group_by(ID, .add = TRUE) %>%
   mutate(grp = rowid(sign(payment))) %>% 
   group_by(grp, .add = TRUE) %>%
   filter(n() == 1) %>% 
   ungroup %>%
   arrange(rn) %>%
   select(names(temp_2))

-输出

# A tibble: 9 × 4
  Date       payment Code  ID   
  <chr>        <dbl> <chr> <chr>
1 23/06/2002   -1000 M567  187  
2 25/06/2002   -1000 M567  187  
3 25/06/2002   -1000 M567  187  
4 26/06/2002    1000 XYZ   12ee 
5 03/07/2002   -1000 ABX   <NA> 
6 24/07/2002   -1000 M567  98   
7 15/07/2002    1200 M567  111  
8 17/07/2002    1200 M567  111  
9 22/07/2002     200 M300  11
1
@bella_pa 我不确定是否还有其他情况。到目前为止，这个解决方案适用于你展示的三种情况（包括其他问题中的一个）。 - akrun
1
我会尝试用真实数据测试。我已经覆盖了所有情况，希望这能够正常工作。 - Bella_18
1
@bella_pa，你的第一步不会起作用，因为相同的ID意味着所有的NAs将被合并在一起，即如果对于一个单一的代码有多个NA，并且它的支付匹配，那么它将被删除。 - akrun
1
@bella_pa，请问在该群组中assignment是指什么？ - akrun
1
@bella_pa 请尝试更新。现在您可以按预期获取行。适用于两个数据集。 - akrun
显示剩余20条评论
1
这是我的解决方案，诀窍在于正确地替换缺失值。
# fill NAs according to their values 
temp <- temp %>% 
  mutate(abs_payment = abs(payment)) %>% 
  group_by(abs_payment, ID, Code) %>% 
  # should consider replacement only if ID has only one row or if it is NA
  mutate(is_candidate = (n() == 1) | is.na(ID)) %>%
  group_by(abs_payment, Code) %>% 
  # we do not want to replace IDs for non-na IDs 
  mutate(new_ID = case_when(is_candidate & is.na(ID) ~ na.omit(ID)[1],
                            TRUE ~ ID))


# remove if sum equal to 0 
temp <- temp %>% 
  group_by(Code, new_ID, abs_payment) %>% 
  mutate(total = sum(payment)) %>% 
  filter(total != 0 )
非常感谢@Kozolovska。您能解释一下步骤“mutate（new_ID = case_when（is_candidate＆is.na（ID）〜na.omit（ID）[1]，TRUE〜ID））”吗？NA是如何被其相反的付款所替换的？我有点困惑。 - Bella_18
case_when是一个开关语句，基本上意味着，如果is_candidate为True且ID为NA，则用第一个不为NA的ID（na.omit(ID)[1]）替换它。如果该语句不为TRUE，则简单地用ID替换它。这一步是为了替换NAs，稍后我会根据绝对值、新ID列和代码总和来汇总付款，并删除所有付款总额为0的组。 - Kozolovska
但是这段代码对于temp_2不起作用（请查看问题的结尾-我添加了一些带有额外列的虚拟数据）。NA被替换为错误的值。您能否帮我解决这个问题？ - Bella_18
0

我不确定这是否正确，但这是我的尝试。我没有看到您如何准确地推导出期望的输出结果。是否有其他过滤条件？您的原始数据有超过8行。

library(tidyverse)

temp |>
  mutate(Date = lubridate::dmy(Date)) |>
  arrange(Code, abs(payment)) |>
  group_by(Code, abs(payment), ID) |>
  mutate(n = n()) |>
  ungroup()|>
  group_by(Code, abs(payment), n) |>
  fill(ID, .direction = "updown") |>
  ungroup()|>
  select(names(temp)) |>
  arrange(Date, abs(payment))
#> # A tibble: 16 x 4
#>    Date       payment Code  ID   
#>    <date>       <dbl> <chr> <chr>
#>  1 2002-06-24     200 ABC   <NA> 
#>  2 2002-06-24    1000 M567  98   
#>  3 2002-06-25   -1000 M567  187  
#>  4 2002-06-25   -1000 M567  187  
#>  5 2002-06-26    1000 XYZ   12ee 
#>  6 2002-07-02   -1000 M567  M11  
#>  7 2002-07-03   -1000 ABX   M13  
#>  8 2002-07-08    1200 M567  K999 
#>  9 2002-07-08   -1200 M567  K999 
#> 10 2002-07-15    1200 M567  111  
#> 11 2002-07-17    1200 M567  111  
#> 12 2002-07-22     200 M300  11   
#> 13 2002-07-22   56700 M678  12345
#> 14 2002-07-24   -1000 M567  M11  
#> 15 2002-07-28  -56700 M678  12345
#> 16 2002-07-29    -200 ABC   <NA>
通常情况下，具有相同ID和代码的负行将与正付款行相互抵消。但是我的数据在退款时有一些NA的ID！因此，在这种情况下，我应该检查具有相同付款和代码的正行以删除行。 - Bella_18
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接