根据在“r”中具有反向字符串值的两列筛选非重复值

Question

我正在尝试过滤我的数据集以摆脱双行。但是，我想对两个不同的列进行过滤，如果反向取，它们会相同（起点-目的地数据）。下面是一个数据示例：

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"拆分为"-"，sort list输出中的元素，paste them together to give a向量"，检查重复项（"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