1

I have 24 files and I want to filter the files so each of them only contain rows with common strings in the first column of each file (in the example geneA and geneF are the only strings in Column1 common to each file). The output should preserve the 3 columns. The files are tab-delimited.

My files look like:

file1.txt

Column1 Column2 Column3
geneA   11  C
geneB   34  T
geneC   22  A
geneD   23  A
geneE   2   G
geneF   34  A

file2.txt

Column1 Column2 Column3
geneA   34  A
geneF   67  G
geneG   77  A
geneZ   45  G
geneY   99  T

file24.txt

Column1 Column2 Column3
geneA   22  A
geneF   7   T
geneL   34  C
geneK   66  A
geneM   34  T
geneP   47  G

My desired output would be:

file1.txt

Column1 Column2 Column3
geneA   11  C
geneF   34  A

file2.txt

Column1 Column2 Column3
geneA   34  A
geneF   67  G

file24.txt

Column1 Column2 Column3
geneA   22  A
geneF   7   T
2
  • 1
    Welcome to the site. Since you indicated awk in the question tags, is there anything you have already tried (and maybe stumbled upon some unwanted behaviour) that could serve as a starting point for a possible solution? – AdminBee Jan 16 '20 at 15:07
  • Can each Column1 value only appear once in each file or could geneK appear twice in file1 for example? – Ed Morton Jan 16 '20 at 15:23
1

With GNU awk for "inplace" editing this will work even if a given column1 value can appear multiple times in an input file:

$ cat tst.awk
BEGIN {
    for (fileNr=1; fileNr<ARGC; fileNr++) {
        file = ARGV[fileNr]
        delete thisFile
        while ( (getline < file) > 0 ) {
            thisFile[$1]
            if ( fileNr == 1 ) {
                common[$1]
            }
        }
        close(file)
        for ( val in common ) {
            if ( !(val in thisFile) ) {
                delete common[val]
            }
        }
    }
}
(FNR == 1) || ($1 in common)

.

$ awk -i inplace -f tst.awk file{1..3}

$ tail -n +1 file{1..3}
==> file1 <==
Column1 Column2 Column3
geneA   11  C
geneF   34  A

==> file2 <==
Column1 Column2 Column3
geneA   34  A
geneF   67  G

==> file3 <==
Column1 Column2 Column3
geneA   22  A
geneF   7   T

but if the column1 values can only appear once in each file then it can be briefer:

$ awk -i inplace -v comm="$(cut -f1 file{1..3} | sort | uniq -c | awk '$1==3')" '
    BEGIN{split(comm,tmp); for (i in tmp) common[tmp[i]]} (FNR == 1) || ($1 in common)
' file{1..3}

or if you don't have an awk with inplace editing:

$ comm="$(cut -f1 file{1..3} | sort | uniq -c | awk '$1==3')"
$ for file in file{1..3}; do
    awk -v comm="$comm" '
        BEGIN{split(comm,tmp); for (i in tmp) common[tmp[i]]} (FNR == 1) || ($1 in common)
    ' "$file" > tmp && mv tmp "$file"
done
5
  • Thanks for the answer. Yes, values only appear once in Column1. However, the problem I'm having is that it seems I don't have the inplace option. This is my gawk version: GNU Awk 4.0.2. – palomo11 Jan 16 '20 at 15:41
  • Then install a newer version of gawk BUT I added a shell script that runs one file at a time as an alternative in case that's not possible for some reason. – Ed Morton Jan 16 '20 at 15:51
  • I will ask the HPC team to install a newer version of gawk. Meanwhile, I have tried the code you provide to run without inplace, but I just get the Columns headers in the output files... – palomo11 Jan 16 '20 at 16:20
  • I just installed a newer version of gawk in my mac, and the inplace code works fine. Thanks. However, the code without inplace still does not work. – palomo11 Jan 16 '20 at 16:36
  • Then you probably copy/pasted it wrong. Without more concrete information than "does not work" I can't help you more than that. – Ed Morton Jan 16 '20 at 17:57
0

One liner (for /bin/sh or /bin/bash using join):

tmp=$(cat file1); for f in file{2..3}; do tmp=$(join -j1 -o1.1 <(echo "${tmp}"|sort) <(sort $f)); done; for f in file{1..3}; do echo "> File $f:"; for i in $(echo $tmp); do grep "^$i\s" $f; done; done

Output:

> File file1:
Column1 Column2 Column3
geneA   11      C
geneF   34      A
> File file2:
Column1 Column2 Column3
geneA   34      A
geneF   67      G
> File file3:
Column1 Column2 Column3
geneA   22      A
geneF   7       T

Explanation:

#!/bin/sh
# find first column members existing in each file
tmp=$(cat file1);
for f in file{2..3}; do
   tmp=$(join -j1 -o1.1 <(echo "${tmp}"|sort) <(sort $f)); 
done;
#
# going through files and printing lines containing found members
for f in file{1..3}; do
    echo "> File $f:";
    for i in $(echo $tmp); do
        grep "^$i\s" $f;
    done;
done

P.S. It just prints results, but does not rewrite files, however it can be easily changed.

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.