5

I need a command that will remove any columns in a text file if they have =>${MaxAllowedNumberOfFs} 'F's within the column (a column that will have a varying number of rows).

I have some pseudo code that is close, but I don't know how to set a match number limiter.

say the limiter is set to 3 and the Example input file is:

F G F H H
G F F F A
F G F F F
F F F T F

Then the desired output would be:

G H H
F F A
G F F
F T F

pseudo code that's close (the limiter can and will change depending on files):

MaxAllowedNumberOfFs="1012"

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 x="$MaxAllowedNumberOfFs" '$i == F =>x number of times {$i="";print $0}' filename; done

Obviously I could loop through all the columns count number of occurences within column using grep, and then remove columns that don't meet criteria. but that would be really slow. Really want a pretty awk command for this, but I don't have the awk skills

Improve this question
5
  • 1
    How big are the input files - a few hundred lines or a few billion? Put another way, can one hold all the data in memory?
    – icarus
    Jul 18, 2019 at 14:21
  • 1
    Will each row have the same number of columns, as your sample input shows, or could you have short and long rows?
    – icarus
    Jul 18, 2019 at 14:25
  • Nearly all input files will range from hundreds to tens of thousands of lines, but certainly never >200,000. Each row will have a varied number of columns, ranging from 100-10,000
    – Giles
    Jul 18, 2019 at 14:32
  • 2
    In general unix utilities are line oriented, so perhaps you could approach this as follows? Find a 'transpose'' utility, which swaps rows and columns, then construct a pipeline ` transpose filename | grep -v '^[^F]*F[^F]*F[^F]*F' | transpose ` you will want to write a one line command to construct the regular expression from the number 3
    – icarus
    Jul 18, 2019 at 14:35
  • okay, so transpose and then grab everything that doesn't have that many columns or more starting with F. Where the string given to grep with the number of columns starting with F, is created in a previous loop based on the number limit. after which I could transpose again, resulting in a cleaned version of the file. It would work, but there must be a cleaner faster way with awk
    – Giles
    Jul 18, 2019 at 14:41

3 Answers 3

Reset to default
3

One approach is to read the file twice. The first time one counts the F's, and the second time one outputs the line. So something like

#!/bin/sh

awk -v n=3 '
        NR==FNR { for (i=1;i<=NF;i++) { if ($i == "F") { c[i]++ }} ;next }                                                                            
        { for (i=1;i<=NF;i++) { if (c[i] < n) { printf("%s ", $i) } } ;printf("\n") }                                                                 

' filename filename

The NR==FNR is a trick to see if this is the first or second time we are reading the file. Assuming there are any lines at all in the file then it is true only when reading the file the first time. The array c is a count of the number of F characters in that column. The next says that all the processing for that line is finished when reading the file the first time. The second line is executed the second time the file is read.

Improve this answer
1
  • Sorry for the delay, your solution works perfectly. I was trying to sort another way, felt like I was getting close, but at the moment my way is still just pseudo code: for((i=1;i<=$Count_of_columns;i++)); do awk -v x=$MaxAllowedNumberOfFs ' BEGIN {count=0;} { if ($i == "F") count+=1;} {if (count => x) print;}' ~/tmp done
    – Giles
    Jul 18, 2019 at 15:31
2

Here's an illustration of the transpose - line filter - transpose method. It's perhaps unsuitable for your (large file) case but may be of value to others:

$ cat file
F G F H H
G F F F A
F G F F F
F F F T F

then

$ rs -T < file | perl -alne 'print unless (grep { $_ eq "F" } @F) > 3' | rs -T
F  G  H  H
G  F  F  A
F  G  F  F
F  F  T  F
Improve this answer
3
  • Thanks, I didn't know you could do that with gsub, that's pretty cool. Does this just filter a column if a given row has more than 3 F's though? I've edited my question to make it clearer if that is the case. As I need to filter a column based on how many times across all the rows in the column that F appears. forgive me if I've just mis-understood your code
    – Giles
    Jul 18, 2019 at 13:44
  • @Giles no, I misunderstood your question - I will delete this answer (at least until I can modify it to address your updated question)
    – steeldriver
    Jul 18, 2019 at 14:00
  • Completely revised based on better understanding of question
    – steeldriver
    Jul 18, 2019 at 15:41
0

Tried with below script and it worked fine

for ((i=1;i<=5;i++)); do c=`awk -v i="$i" '{print $i}' o.txt|awk '$1=="F" {print $0}'| sed -n '/F/{;=;p}'| sed "N;s/\n/ /g"| sort -k1 -rn|sed -n '1p'| awk '{print $1}'`; if [[ $c -lt 3 ]]; then awk -v i="$i" '{print $i}' o.txt >file_$i; fi; done

paste  file_*

output

G H H
F F A
G F F
F T F
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .