r - 使用 R 更正/整理数据

Question

我处理的交易数据有两个来源，各有利弊。第一个只有有准确的 $ 和售出单位(DF_Lookup)，第二个有正确的人口统计数据(DFI)，但一些 $并且售出的单位不正确。因此，我编写了以下代码来处理这个问题。

这是我的数据:

发展金融机构

dput(DFI)
structure(list(PO_ID = c("P1234", "P1234", "P1234", "P1234", 
"P1234", "P1234", "P2345", "P2345", "P3456", "P4567"), SO_ID = c("S1", 
"S1", "S1", "S2", "S2", "S2", "S3", "S4", "S7", "S10"), F_Year = c(2012, 
2012, 2012, 2013, 2013, 2013, 2011, 2011, 2014, 2015), Product_ID = c("385X", 
"385X", "385X", "450X", "450X", "900X", "3700", "3700", "A11U", 
"2700"), Revenue = c(1, 2, 3, 34, 34, 6, 7, 88, 9, 100), Quantity = c(1, 
2, 3, 8, 8, 6, 7, 8, 9, 40), Location1 = c("MA", "NY", "WA", 
"NY", "WA", "NY", "IL", "IL", "MN", "CA")), .Names = c("PO_ID", 
"SO_ID", "F_Year", "Product_ID", "Revenue", "Quantity", "Location1"
), row.names = c(NA, 10L), class = "data.frame")

DF_Lookup

dput(DF_Lookup)

structure(list(PO_ID = c("P1234", "P1234", "P1234", "P2345", 
"P2345", "P3456", "P4567"), SO_ID = c("S1", "S2", "S2", "S3", 
"S4", "S7", "S10"), F_Year = c(2012, 2013, 2013, 2011, 2011, 
2014, 2015), Product_ID = c("385X", "450X", "900X", "3700", "3700", 
"A11U", "2700"), Revenue = c(50, 70, 35, 100, -50, 50, 100), 
    Quantity = c(3, 20, 20, 20, -10, 20, 40)), .Names = c("PO_ID", 
"SO_ID", "F_Year", "Product_ID", "Revenue", "Quantity"), row.names = c(NA, 
7L), class = "data.frame")

第一次尝试:

策略: - 使用 Join 覆盖 DFI 中 DF_Lookup 的条目

DF_Generated <- DFI %>% 
  left_join(DF_Lookup,by = c("PO_ID", "SO_ID", "F_Year", "Product_ID")) %>%
  dplyr::group_by(PO_ID, SO_ID, F_Year, Product_ID) %>%
  dplyr::mutate(Count = n()) %>%
  dplyr::ungroup()%>%
  dplyr::mutate(Revenue = Revenue.y/Count, Quantity = Quantity.y/Count) %>%
  dplyr::select(PO_ID:Product_ID,Location1,Revenue,Quantity)

预期输出:

dput(DF_Generated)
structure(list(PO_ID = c("P1234", "P1234", "P1234", "P1234", 
"P1234", "P1234", "P2345", "P2345", "P3456", "P4567"), SO_ID = c("S1", 
"S1", "S1", "S2", "S2", "S2", "S3", "S4", "S7", "S10"), F_Year = c(2012, 
2012, 2012, 2013, 2013, 2013, 2011, 2011, 2014, 2015), Product_ID = c("385X", 
"385X", "385X", "450X", "450X", "900X", "3700", "3700", "A11U", 
"2700"), Location1 = c("MA", "NY", "WA", "NY", "WA", "NY", "IL", 
"IL", "MN", "CA"), Revenue = c(16.6666666666667, 16.6666666666667, 
16.6666666666667, 35, 35, 35, 100, -50, 50, 100), Quantity = c(1, 
1, 1, 10, 10, 20, 20, -10, 20, 40)), class = c("tbl_df", "tbl", 
"data.frame"), row.names = c(NA, -10L), .Names = c("PO_ID", "SO_ID", 
"F_Year", "Product_ID", "Location1", "Revenue", "Quantity"))

挑战:这适用于较小的数据集。我正在处理的原始数据有大约 9000 万条记录。所以，上面的代码需要永远。

第二次尝试:因此，我考虑只更新那些 $ 和 单位超出 +/-10% 范围的行。

这是我的代码:

#Find out whether the numbers are within +/-10% range.
DF_Mod<-DFI %>%
  dplyr::group_by(PO_ID, SO_ID, F_Year, Product_ID) %>%
  dplyr::summarise(Rev_agg = sum(Revenue), Qty_agg = sum(Quantity)) %>%
  left_join(DF_Lookup) %>%
  dplyr::rowwise() %>%
  #check for +/- 10% confidence interval
  dplyr::mutate(Compute = ifelse((abs(Rev_agg-Revenue)/Revenue <=0.1) & (abs(Qty_agg-Quantity)/Quantity <=0.1),"N","Y")) %>%
  dplyr::rowwise() %>%
  dplyr::ungroup() %>%
  dplyr::select(PO_ID:Product_ID,Compute) %>%
  dplyr::right_join(DFI)

#Now, filter Compute == "Y" and then do the join with DF_Lookup.
DF_Generated_2 <- DF_Mod %>% 
  dplyr::filter(Compute == "Y") %>%  
  left_join(DF_Lookup,by = c("PO_ID", "SO_ID", "F_Year", "Product_ID")) %>%
  dplyr::group_by(PO_ID, SO_ID, F_Year, Product_ID) %>%
  dplyr::mutate(Count = n()) %>%
  dplyr::ungroup()%>%
  dplyr::mutate(Revenue = Revenue.y/Count, Quantity = Quantity.y/Count) %>%
  dplyr::select(PO_ID:Product_ID,Location1,Revenue,Quantity)

#Bind the rows
DF_Final <- rbind(DF_Generated_2,DFI[DF_Mod$Compute=="N",]) #Expected output

这里，DF_Final 确实是预期的输出。

问题:即使采用上述方法，由于涉及太多连接，性能仍然很慢。无论如何我们可以加快这个过程吗？有没有其他更好的方法来完成我想做的事情？

我很感激你的想法。我已经在这上面度过了一天，但仍然无处可去。我真的卡住了。

Answer 1

我还没有在大型数据集上对此进行测试，但它仍然可能是您所需要的(并且可能仍然有更快的方法来执行此操作):

# load data table library
library(data.table)

# convert data frames to data tables
DFI <- data.table(DFI)
DF_Lookup <- data.table(DF_Lookup)

# left join
df <- merge(DFI, DF_Lookup, all.x = TRUE, by = c("PO_ID", "SO_ID", "F_Year", "Product_ID"))

# Calculate the strange quantity and revenue
df2 <- df[, list(Revenue = Revenue.y/.N, Quantity = Quantity.y/.N),
              by = list(PO_ID, SO_ID, F_Year, Product_ID)]