filter distinct value based on two columns with inverse string values in `r`
<p>I am trying to filter my dataset to get rid of doubled rows. However, I want to do my filter on two different column that are identical if taken inversely (Origin-Destination data). Here is an example of data:</p>

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

<p>I used the <code>dplyr</code> library and <code>distinct</code> which works fine with my number of passengers and revenue as with the code below:</p>

<pre><code>library(dplyr)
data4 &lt;- distinct(data2, Total.Passengers, Total.Revenue)
</code></pre>

<p>However, my real dataset has millions of rows and sometimes, the number of passengers, for a same city-pair, is not exactly the same (difference of decimals). But, I still need to filter the data and keep only one record so I won't be counting twice the passengers and the revenue. </p>

<p>Though, I am looking for a function that will allow me to filter based on the Origin and the Destination or on the City.Pair. </p>

<p>As part of my trials, I have tried to use the <code>anti_join</code> function by merging a doubled of the dataset but it does keep all the rows. I also tried with the <code>union</code> but got the same result. </p>

<pre><code>data3&lt;- data2
data5&lt;- anti_join(data2, data3, by=c("Origin.City" = "Destination.City", "Destination.City" = "Origin.City"))
</code></pre>

<p>My desired output should be something as follow:</p>

<pre><code>  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
</code></pre>

<p>What would be the best function for the task ? Or what can I correct in my actual code ?</p>

<p>Thanks!</p>

<p><strong>EDIT</strong></p>

<p>How can I change the code to include another condition into the filtering?
Let's say one row is coded and I also want to subset/filter based on that column.</p>

<p>Here is the new dataframe:</p>

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

<p>So the desired output should be as follow:</p>

<pre><code>  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
</code></pre>

<p>I am performing multiple trials but can't perform the filter on two columns at the same time.. Here is my code:</p>

<pre><code>dat1&lt;- 
  data2 %&gt;%
  group_by(Code, City.Pair, Origin.City, Destination.City) %&gt;%
  filter(Origin.City!=Destination.City &amp; Destination.City!=Origin.City) %&gt;%
  summarise(Passengers=sum(Total.Passengers), 
          Revenue=sum(Total.Revenue))
</code></pre>