2

I lets say I have a csv that looks like:

1,20
1,13
20,1

I need to remove 20,1 because 1,20 is already in the csv. My idea to do this is to go row by row and check if the reverse is already in the csv, but I have no idea how to do this with a shell script.

Edit: 2 of the same numbers will never be in the same row, for example, 20,20 will never appear

1
  • Your solution would be very slow for a large file - other solutions are possible - especially if the order of the rows is not important. Dec 3 '21 at 23:57
2

In order to make this happen wih reasonable speed you need some kind of hash table. Traditionally you would reach for awk to do this, but any of the modern scripting languages would do, including bash with its associative arrays.

awk 'BEGIN{FS=","}                                                                   
{                                                                               
   rev=$NF; for(i=NF-1;i>0;i--) {rev = rev "," $i}                                                                         
   if (!seen[rev]) { print }                                                    
   seen[$0]=1                                                               
}'

This constructs the reversed line in rev, prints the line if the reverse of it has not already been seen, and then records that this line has been seen.

3
  • Note that just using a reversed key in the general case of NF > 2 is not enough, if I understand the issue properly. You would have to not only use the reversed key, but all combination of fields. It would therefore make sense to instead use a sorted key, as Ed is doing for the NF == 2 case. Also note that using , as the delimiter is safe in this case, but that it would not be a suitable generic delimiter for obvious reasons.
    – they
    Dec 4 '21 at 7:48
  • 1
    @they I am using the title of the question "commands to remove row from file if reverse row is in file" to infer that the general case is not required, but I agree that is an assumption. In particular I am allowing input data of "1,20|1,20|1,20|20,1|1,20" where | represents a newline to give "1,20|1,20|1,20" as output as for the first 3 lines there is no preceeding "20,1". Yes sorting the key is correct if you want all combinations. Usually with a CSV file the order of the data matters. I wonder if the OP is looking at some graphs where the file is edges and the values are vertices.
    – icarus
    Dec 4 '21 at 16:15
  • Oh, you read the title. That's cheating! :-) You're all correct, and I too wonder whether they are looking at graph data.
    – they
    Dec 4 '21 at 17:03
2
$ cat file
4,bumblebee
1,20
1,13
20,1
20,1
20,13
bumblebee,3
13,1
bumblebee,4
$ awk -F, '!seen[$1,$2]++ && !seen[$2,$1]' file
4,bumblebee
1,20
1,13
20,13
bumblebee,3

This is using an associative array, seen, in which we count the number of times we have seen a pair of fields in the input.

If we haven't previously seen the pair, nor the reversed pair, the line is printed.

This does not assume that the fields are numeric.

1

Assuming what you're really trying to do is just make sure some combination of parts only appears once regardless of the order it's in, the idiomatic way to do this is to sort the parts that you want to be unique into a specific order and then check if that resulting key has previously been seen. When you only have 2 parts it devolves to just:

$ awk -F',' '!seen[$1>$2 ? $1 FS $2 : $2 FS $1]++' file
1,20
1,13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.