Filter multiple files to keep rows with common first column

Question

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

Answer 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

Answer 2

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.