Identify and Remove entire column only if every row within it is either $VAR1 or $VAR2

Question

similar to question 'Remove entire row only if every column within it is either $VAR1 or $VAR2'

BUT its to remove an entire COLUMN instead, and is based on the make up of every ROW in the column

I have a text file where column and row number will always vary, and want to remove the entirety of any columns from the txt file only if every row within said column is equal to either $VAR1 or $VAR2. For example:

Lets say $VAR1="X" and $VAR2="N" and I want to remove any column where $VAR1 and $VAR2 makes up the entire column.

This would be my input:

hajn 32 ahnnd namm X 543 asfn F
namd 90 jsnfu namm X 098 asfn S
5739 dw 32eff Sfff N asd 3123 1

And this would be my desired output:

hajn 32 ahnnd namm 543 asfn F
namd 90 jsnfu namm 098 asfn S
5739 dw 32eff Sfff asd 3123 1

I can solve this with a loop, but I was wondering if there's a powerful one liner way of doing this, preferably awk.

Answer 1

Check this out.

$ cat data
hajn 32 ahnnd namm X 543 asfn F
namd 90 jsnfu namm X 098 asfn S
5739 dw 32eff Sfff N asd 3123 1

Using awk with an if-expression and an or-expression || inside and if so, then exclude cloumn #5 and print the rest of the data.

$ awk '{ if ($5=="X" || $5=="N") { $5=""; print } }' < data
hajn 32 ahnnd namm  543 asfn F
namd 90 jsnfu namm  098 asfn S
5739 dw 32eff Sfff  asd 3123 1

Answer 2

Tried with below method and it worked fine too

VAR1="X"
VAR2="N"

Count_of_columns=`awk '{print NF}' filename| sort -nr| sed -n '$p'` 

for((i=1;i<=$Count_of_columns;i++)); do awk -v i="$i" -v VAR1="$VAR1" -v VAR2="$VAR2" '$i == VAR1||$i == VAR2{$i="";print $0}' filename; done

output

hajn 32 ahnnd namm  543 asfn F
namd 90 jsnfu namm  098 asfn S
5739 dw 32eff Sfff  asd 3123 1

Answer 3

You will need to store the lines because the decision to drop any column cannot be made until the last line has been examined.

$ perl -lane '
    push @{$A[$.]}, @F;my $i;
    $h[$i++] ||= !/X/ && !/N/ for @F}{
    my @I2P = grep { $h[$_] } 0 .. $#h;
    print join $", @{$A[$_]}[@I2P] for 1 ..  $#A;
' inp