1

I have a data file A.tsv (field separator = \t) :

id  clade   mutation
243 40A SDF
254 20B
261 40A
267 20B

B.tsv (field separator = \t) :

id  clade   mutation
243 40A
254 20B
261 40A
267 20B SLT

C.tsv (field separator = \t) :

id  clade   mutation
243 40A
254 20B
261 40A MGG
267 20B

And I want to concatenate all the file into one, knowing that the only column that is different between all the file is the column mutation. The result expected is :

id  clade   mutation
243 40A SDF
254 20B
261 40A MGG
267 20B SLT

So far I tried :

awk 'BEGIN{FS=OFS="\t"} NR==FNR{klos[NR]=$3; next} $3==""{$3=klos[FNR]} 1' *.tsv > output.tsv

But it didn't works. Do you have a way to do that ? Thanks

PS: Here is a sample test, in my real condition I have more than 3 files. But like the example, there are not two file with the same thing in the mutation column.

4
  • 1
    You say there are no two files with the same data in the mutation field. What about two files that have different data in that field? You are only showing us examples where exactly one file has data in that field for a particular row. Also, are all id-clade pairs represented in all files, as you show?
    – Kusalananda
    Jul 5 at 12:43
  • For example, for the first sample line (first line after the header), only one file have something in the mutation column. For the other column; I use the same command for generate all the file so only the mutation column differ from one file to another
    – nstatam
    Jul 5 at 12:50
  • I've edit my question do take in count that the line with no field in the mutation column also need to be print
    – nstatam
    Jul 5 at 13:16
  • One should never under specify or oversimplify a pbm. Now I'm not sure how many fields your files have. Most of the time the approach taken are a very strong function of the specs. So they must reflect the actual data.O otherwise be prepared for lots of iterations before converging to a working solution.The time saved in cooking up artificial data is more than used up in getting the solution to work.
    – guest_7
    Jul 5 at 15:58
5

Using any awk in any shell on every Unix box:

$ cat tst.awk
BEGIN { FS=OFS="\t" }
(NR == FNR) || ($3 != "") {
    fnr2rec[FNR] = $0
}
FILENAME == ARGV[ARGC-1] {
    print fnr2rec[FNR]
}

$ awk -f tst.awk A.tsv B.tsv C.tsv
id      clade   mutation
243     40A     SDF
254     20B
261     40A     MGG
267     20B     SLT
2

Using paste then processing its output with awk:

paste file[ABC].tsv |\
awk 'BEGIN{ FS=OFS="\t" } { for(i=3; i<=NF; i+=3) if($i"")break; print $1, $2, $i }'

the used condition if($i"")break is to break the for-loop (double quotes is used to force awk to do string comparison) as soon as first non-empty field was found, then later just print fields #1, #2 and that non-empty field which was found previously in field #i.

0
2

This should work (if you don't mind the results in reverse).

[root@SE ~]# awk 'BEGIN{FS=OFS="\t"} { if($3>0) { print }}' A.tsv B.tsv C.tsv | sort -ru

I am sure there may be a simpler way to achieve this, as I am still relatively new to awk.

What I am doing is simply printing all 3 files and removing all rows in mutation without characters. The sort -u pipe then removes all duplicates. The -r flag reverses the order.

[root@SE ~]# awk 'BEGIN{FS=OFS="\t"} { if($3>0) { print }}' A.tsv B.tsv C.tsv | sort -ru
id  clade   mutation
267 20B SLT
261 40A MGG
254 20B SLT
243 40A SDF

@Quasimodo has simplified this. Thank you.

awk 'BEGIN{FS=OFS="\t"} (FNR>1 || NR==1) && $3' A.tsv B.tsv C.tsv

This will print a line if it is either the very first line processed (i.e. the header of the first input file), or any line which is not the header, but contains a non-empty 3rd field. It works because a boolean expression outside of a rule block ({ ... }) that evaluates to "true" instructs awk to print the current line.

10
  • 2
    I've edit my question do take in count that the line with no field in the mutation column also need to be print
    – nstatam
    Jul 5 at 13:16
  • Just add a sort pipe. awk '(NR==1 || FNR>1) { print }' A.tsv B.tsv C.tsv | sort -uk3 | sort -r id clade mutation 267 20B SLT 261 40A MGG 254 20B 243 40A SDF
    – HatLess
    Jul 5 at 13:40
  • @HatLess with the command of your last comment, the result is not good because it print also the line that don't have a field in a file but has in another one. Meaning that I end up with "just" a concatenation of the file
    – nstatam
    Jul 5 at 14:06
  • I am working with 3 files and the values that you provided. In my test, all 4 rows are returned with the one empty field in mutation.
    – HatLess
    Jul 5 at 14:11
  • 1
    @EdMorton After the update, 2 further sort pipes were included so it can execute as expected, therefore giving the expected result as far as the data that has been provided is concerned. The updated code can be found in this comment section near the top. awk '(NR==1 || FNR>1) { print }' A.tsv B.tsv C.tsv | sort -uk3 | sort -r
    – HatLess
    Jul 6 at 13:22
1

You could consider using Miller's nest verb, to implode values across records:

$ mlr --tsv --ragged nest --ivar "" -f mutation {A..C}.tsv
id      clade   mutation
243     40A     SDF
254     20B
261     40A     MGG
267     20B     SLT

(assuming a shell that supports brace expansion). You can omit the --ragged input modifier if the missing mutation values are empty rather than absent. The empty nested-fs "" means that if there are conflicting values they will be concatentated.

1
  • I'm sorry but I have experiment mlr and it generates some format issue. Also I need to use tool that are native for CentOS machine (i.e awk, grep, sort, python ...)
    – nstatam
    Jul 5 at 13:35
0

One approach using the bash commandline utilities can be:

## initialization 
ref= tmpdir=$(mktemp -d) cn=3

for f in ./*.tsv;do
  : ${ref:="$f"}
 < "$f" cut -f"$cn" > "$tmpdir/$f"
done

paste <(cut -f-"$((cn-1))" "$ref") "$tmpdir"/* |
tr -s '\t' |
sed -ne "1s/\t/\n/$cn" -e 's/\t*$//;P'

Results:

id  clade   mutation
243 40A SDF
254 20B
261 40A MGG
267 20B SLT
2
  • I'm trying to understand your code but I'm not sure if I have fully understand. If with my real data the column mutation is in $47 , what part of your code need to be change apart from -f3
    – nstatam
    Jul 5 at 14:09
  • I have included the column number as a variable $cn now.
    – guest_7
    Jul 5 at 15:11
0
awk -F '\t' 'BEGIN { OFS=FS }
    m[$1,$2] == "" { m[$1,$2] = $3 }
    END { for (k in m) { split(k,a,SUBSEP); print a[1],a[2],m[k] } }' [ABC].tsv |
sort -n

This keeps track of the mutation for a specific id and clade in the associative array m (indexed by id and clade). The mutation for a particular key is updated if a non-empty value hasn't yet been associated with that particular key.

At the end, we loop over all keys in m, break them apart into id and clade (in a[1] and a[2] respectively) and output the corresponding values. We need to sort the output at the end since the ordering of keys coming out of a hash is random.

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.