Home / ubuntu / Questions / 1216656
In Process
Lulu' Nisrina
Asked: 2020-03-13 08:23:17 +0800 CST

How to combine TSV file

  • 772

I have four TSV files like this:

file_1:

abc \t 1
def \t 3
ghi \t 5

file_2:

abc \t 10
def \t 4
ghi \t 7

file_3:

abc \t 8
def \t 5
ghi \t 1

file_4:

abc \t 4
def \t 2
ghi \t 1

I want to combine those TSV files into the one TSV like this:

dataset \t file_1 \t file_2 \t file_3 \t file_4
abc \t 1 \t 10 \t 8 \t 4
def \t 3 \t 4 \t 5 \t 2
ghi \t 5 \t 7 \t 1 \t 1

How I can get that?

text-editor text-processing awk

2 Answers

  1. Using awk:

    $ awk '
    BEGIN{OFS=FS="\t"} 
    FNR==1{f = f "\t" FILENAME} 
    NR==FNR{a[$1] = $2} 
    NR!=FNR{a[$1] = a[$1] "\t" $2} 
    END{printf "dataset%s\n", f; for(i in a) print i, a[i]}
  ' file_{1..4}
dataset file_1  file_2  file_3  file_4
def     3       4       5       2
abc     1       10      8       4
ghi     5       7       1       1

    If you need sorted output, either pipe the result through sort or (assuming GNU awk 4.0 or above) add PROCINFO["sorted_in"] = "@ind_str_asc" in either the BEGIN or END block.

    Using Miller

    $ cat file_{1..4} | mlr --tsv --implicit-csv-header --headerless-csv-output --quote-none \
    nest --implode --values --across-records -f 2 --nested-fs tab
abc     1       10      8       4
def     3       4       5       2
ghi     5       7       1       1

    With a sufficiently new version of Miller you can use the shorthand nest --ivar tab -f 2 in place of nest --implode --values --across-records -f 2 --nested-fs tab

    • 1

  2. Using Miller (https://github.com/johnkerl/miller) and running

    mlr -N --tsv nest --ivar ";" -f 2 then nest --explode --values --across-fields -f 2 0*.tsv

    you will have

    abc     1       10      8       4
def     3       4       5       2
ghi     5       7       1       1
    • 1