I want to join variables (columns) from two different files with a matching column using join - problems with output

Question

Good day. I am new to linux and really hope someone can help me.

I have two files:

File 1 : Column1=id_nr; Column2=snp_info

File 2 : Column1=id_nr; Column2=recoded_id_nr

I want to join the two files so that I end up with a file with Column1=recoded_id_nr and Column2=snp_info for animals (id_nr) present in File 1 and File 2. The id_nr are the matching variable and both files are sorted on it.

In linux I used the various forms of the join function, but get the same as the common one below:

join file1 file2

Result:

It gives the correct animals that are present in both files, but the problem is with the first column. The first integer is the correct recoded_id_nr, but it is fused (concatenated), without any space, with the last 8 digits of the id_nr of the 3 animals that are present in both files. Thus, it cuts of the first 2 digits of the id_nr and replace it with the recoded_id_nr. The second column (snp_info) is fine.

Any help would be immensely appreciated. Michiel

Please replace images by real text so we can copy to our editors and reply. — user9101329, Jan 06 '24 at 18:45
When text ends up displaced after joining file contents, my first guess is that one or both input files has Windows (CRLF) line endings. See for example ed does not join lines with ASCII text, with CRLF, LF line terminators and How to test whether a file uses CRLF or LF without modifying it? — steeldriver, Jan 06 '24 at 19:17
@steeldriver Thank you very much. Windows line endings was indeed the problem and something I was not even aware of. I corrected it with sed -i.bak -E 's/[[:space:]]+$//' file.txt and it worked. The join is now successful. — Michiel Van Niekerk, Jan 07 '24 at 08:42

I want to join variables (columns) from two different files with a matching column using join - problems with output

0 Answers0