Wednesday, January 19, 2022

[SOLVED] How to copy unique columns from multiple txt files to form one new txt file in unix

January 19, 2022 shell, terminal, unix

Issue

I have three .txt files (tab separated)

FileA.txt
Data_ID sample_ID
2253791 20481
2253793 20483
2253798 20488

FileB.txt

Subject_ID        age    sex     smok
1869793 11      2       1
1869585 7       1       3
1870238 9       2       1

FileC.txt

Subject_ID        Data_ID
1869793 2253798
1869585 2253793
1870238 2253791

I want to merge these three files in a way that my output.txt contain all the unique columns of three files like following

Subject_ID Data_ID sample_ID  age sex smok
1869793 2253798 20481 11      2       1
1869585 2253793 20483 7       1       3
1870238 2253791 20488 9       2       1

I have tried paste like following but its not working. Where i am doing wrong? Is there some other solution with paste or awk too?

paste <(awk '{print $1"\t"$2}' FileA.txt)\
<(awk '{print $1}' FileC.txt)\  
 <(awk '{print $2"\t"$3"\t"$4"}' FileC.txt)\ 
> output.txt

Solution

output without order or any relationship:

1, $4"}' there is a extra ", should remove it in your command.

2, the last FileC.txt should change FileB.txt.

3, the paste following but its not working's code maybe has some error format? could your upload a screenshot for that ?

4, just only merge the column without something relationship judgement ?

If yes, try this( only some format fixed by your code to one-line ):

paste <(awk '{print $1"\t"$2}' FileC.txt) <(awk '{print $2}' FileA.txt)  <(awk '{print $2"\t"$3"\t"$4}' FileB.txt) > output.txt

get what you want before me edit your question:

$ cat output.txt 
Subject_ID  Data_ID sample_ID   age sex smok
1869793 2253798 20481   11  2   1
1869585 2253793 20483   7   1   3
1870238 2253791 20488   9   2   1

output with order and the relationship:

join -1 2 -2 1 <(join -1 2 -2 1 <(sort -k2 FileC.txt) <(sort -k1 FileA.txt) | sort -k2) <(sort -k1 FileB.txt) | tac | awk 'NR==1 {line=$0; next} 1; END{print line}' | tac

get the output:

Subject_ID Data_ID sample_ID age sex smok
1869585 2253793 20483 7 1 3
1869793 2253798 20488 11 2 1
1870238 2253791 20481 9 2 1

1, use join to merge two file's line by the same column which had been sort.

2, use tac file | awk 'NR==1 {line=$0; next} 1; END{print line}' | tac move the header to the top.

Answered By - VictorLee

This Answer collected from stackoverflow and tested by PythonFixing community admins, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0