r - 根据 `r` 中具有反向字符串值的两列过滤不同的值

Question

我正在尝试过滤我的数据集以去除重复的行。但是，我想对两个不同的列进行过滤，如果反向使用它们是相同的(Origin-Destination 数据)。这是一个数据示例:

data2<-matrix(NA, nrow = 7, ncol=5)  
colnames(data2)<-c("City.Pair", "Origin.City", "Destination.City", "Total.Passengers", "Total.Revenue")
data2[,1] <- c("LIS-BRU","LIS-LHR","LAD-LIS", "LIS-LAD", "FAO-MAN", "MAN-FAO","LIS-ORY")
data2[,2]<- c("LISBON", "LISBON", "LUANDA", "LISBON", "FARO", "MANCHESTER", "LISBON")
data2[,3] <- c("BRUSSELS","LONDON", "LISBON", "LUANDA", "MANCHESTER", "FARO", "PARIS" )
data2[,4] <- c(100, 5000, 200, 200, 4000, 4000, 4000)
data2[,5] <- c(100.66, 5000.25, 200.75, 200.75, 4000.10, 4000.10, 4000.05)
data2<-data.frame(data2)


  City.Pair Origin.City Destination.City Total.Passengers Total.Revenue
1   LIS-BRU      LISBON         BRUSSELS              100        100.66
2   LIS-LHR      LISBON           LONDON             5000       5000.25
3   LAD-LIS      LUANDA           LISBON              200        200.75
4   LIS-LAD      LISBON           LUANDA              200        200.75
5   FAO-MAN        FARO       MANCHESTER             4000        4000.1
6   MAN-FAO  MANCHESTER             FARO             4000        4000.1
7   LIS-ORY      LISBON            PARIS             4000       4000.05

我使用了 dplyr 库和 distinct，它可以很好地处理我的乘客数量和收入，如下面的代码所示:

library(dplyr)
data4 <- distinct(data2, Total.Passengers, Total.Revenue)

但是，我的真实数据集有数百万行，有时，同一城市对的乘客数量并不完全相同(小数点差)。但是，我仍然需要过滤数据并只保留一个记录，这样我就不会重复计算乘客和收入。

不过，我正在寻找一种功能，可以让我根据起点和目的地或城市对进行过滤。

作为试验的一部分，我尝试通过合并双倍数据集来使用 anti_join 函数，但它确实保留了所有行。我也尝试使用 union 但得到了相同的结果。

data3<- data2
data5<- anti_join(data2, data3, by=c("Origin.City" = "Destination.City", "Destination.City" = "Origin.City"))

我想要的输出应该如下所示:

  City.Pair Origin.City Destination.City Total.Passengers Total.Revenue
1   LIS-BRU      LISBON         BRUSSELS              100        100.66
2   LIS-LHR      LISBON           LONDON             5000       5000.25
3   LAD-LIS      LUANDA           LISBON              200        200.75
4   FAO-MAN        FARO       MANCHESTER             4000        4000.1
5   LIS-ORY      LISBON            PARIS             4000       4000.05

任务的最佳函数是什么？或者我可以在我的实际代码中更正什么？

谢谢!

编辑

如何更改代码以将另一个条件包含到过滤中？假设一行已编码，我还想根据该列进行子集/过滤。

这是新的数据框:

data2<-matrix(NA, nrow = 10, ncol=6)  
colnames(data2)<-c("City.Pair", "Origin.City", "Destination.City", "Total.Passengers", "Total.Revenue", "Code")
data2[,1] <- c("LIS-BRU","LIS-LHR","LAD-LIS", "LIS-LAD", "FAO-MAN", "MAN-FAO","LIS-ORY","LAD-LIS", "LAD-LIS", "LIS-LAD")
data2[,2]<- c("LISBON", "LISBON", "LUANDA", "LISBON", "FARO", "MANCHESTER", "LISBON","LUANDA", "LUANDA", "LISBON")
data2[,3] <- c("BRUSSELS","LONDON", "LISBON", "LUANDA", "MANCHESTER", "FARO", "PARIS","LISBON", "LISBON", "LUANDA")
data2[,4] <- c(100, 5000, 200, 200, 4000, 4000, 4000, 20, 40, 40)
data2[,5] <- c(100.66, 5000.25, 200.75, 200.75, 4000.10, 4000.10, 4000.05, 20.5, 40.8, 40.8)
data2[,6] <- c("F", "G","F", "F", "A", "A", "P", "H", "I", "I")
data2<-data.frame(data2)
data2

   City.Pair Origin.City Destination.City Total.Passengers Total.Revenue Code
1    LIS-BRU      LISBON         BRUSSELS              100        100.66    F
2    LIS-LHR      LISBON           LONDON             5000       5000.25    G
3    LAD-LIS      LUANDA           LISBON              200        200.75    F
4    LIS-LAD      LISBON           LUANDA              200        200.75    F
5    FAO-MAN        FARO       MANCHESTER             4000        4000.1    A
6    MAN-FAO  MANCHESTER             FARO             4000        4000.1    A
7    LIS-ORY      LISBON            PARIS             4000       4000.05    P
8    LAD-LIS      LUANDA           LISBON               20          20.5    H
9    LAD-LIS      LUANDA           LISBON               40          40.8    I
10   LIS-LAD      LISBON           LUANDA               40          40.8    I

所以期望的输出应该如下:

  City.Pair Origin.City Destination.City Total.Passengers Total.Revenue Code
1   LIS-BRU      LISBON         BRUSSELS              100        100.66    F
2   LIS-LHR      LISBON           LONDON             5000       5000.25    G
3   LAD-LIS      LUANDA           LISBON              200        200.75    F
5   FAO-MAN        FARO       MANCHESTER             4000       4000.10    A
7   LIS-ORY      LISBON            PARIS             4000       4000.05    P
8   LAD-LIS      LUANDA           LISBON               20         20.50    H
9   LAD-LIS      LUANDA           LISBON               40         40.80    I

我正在执行多项试验，但无法同时对两列执行筛选。这是我的代码:

dat1<- 
  data2 %>%
  group_by(Code, City.Pair, Origin.City, Destination.City) %>%
  filter(Origin.City!=Destination.City & Destination.City!=Origin.City) %>%
  summarise(Passengers=sum(Total.Passengers), 
          Revenue=sum(Total.Revenue))

Answer 1

我们可以将“City.Pair”拆分为“-”，排序 list 输出中的元素，将它们粘贴在一起以给出一个 code>vector`，检查重复项 ('i1') 并使用逻辑向量对 'data2' 的行进行子集化。

i1 <- !duplicated(apply(sapply(strsplit(as.character(data2$City.Pair), "-"), 
                sort), 2, paste, collapse="-"))
data2[i1,]
#    City.Pair Origin.City Destination.City Total.Passengers Total.Revenue
#1   LIS-BRU      LISBON         BRUSSELS              100        100.66
#2   LIS-LHR      LISBON           LONDON             5000       5000.25
#3   LAD-LIS      LUANDA           LISBON              200        200.75
#5   FAO-MAN        FARO       MANCHESTER             4000        4000.1
#7   LIS-ORY      LISBON            PARIS             4000       4000.05

---

或者使用 separate 和 pmin/pmax

library(dplyr)
library(tidyr)
separate(data2, City.Pair, into = c("City", "City2"), remove = FALSE) %>% 
         filter(!duplicated(pmin(City, City2), pmax(City, City2))) %>%
         select(-City, -City2)
#  City.Pair Origin.City Destination.City Total.Passengers Total.Revenue
#1   LIS-BRU      LISBON         BRUSSELS              100        100.66
#2   LIS-LHR      LISBON           LONDON             5000       5000.25
#3   LAD-LIS      LUANDA           LISBON              200        200.75
#4   FAO-MAN        FARO       MANCHESTER             4000        4000.1
#5   LIS-ORY      LISBON            PARIS             4000       4000.05