1

I have many files, which I want to merge using common Ids in Column 1.

File1:

MYORGANISM_I_05140.t1   Atypical/PIKK/FRAP
MYORGANISM_I_06518.t1   CAMK/MLCK
MYORGANISM_I_00854.t1   TK-assoc/SH2/SH2-R
MYORGANISM_I_12755.t1   TK-assoc/SH2/Unique

File2:

MYORGANISM_I_05140.t1   VALUES to be taken
MYORGANISM_I_12766.t1   what

FILE3:

MYORGANISM_I_16941.t1   OK
MYORGANISM_I_93484.t1   LET IT BE

I want to merge many file and add '-NA-' if value is missing, my desired Output:

MYORGANISM_I_05140.t1   Atypical/PIKK/FRAP  VALUES to be taken  -NA-
MYORGANISM_I_06518.t1   CAMK/MLCK   -NA-    -NA-
MYORGANISM_I_00854.t1   TK-assoc/SH2/SH2-R  -NA-    -NA-
MYORGANISM_I_12755.t1   TK-assoc/SH2/Unique -NA-    -NA-
MYORGANISM_I_12766.t1   -NA-    what    -NA-
MYORGANISM_I_16941.t1   -NA-    -NA-    OK
MYORGANISM_I_93484.t1   -NA-    -NA-    LET IT BE
Improve this question

3 Answers 3

Reset to default
0

This is probably not possible using a single tool. Here is a script-based suggestion that involves a call to sort, and two temporary external files.

#!/bin/bash

# The number of columns is equal to the number of input files, which is
# equal to the number of command-line arguments.
NUMCOLS=$#


# Use associative container to record all "IDs" and associated fields
declare -A entries
col=0


# Read the fields from all files and store them so that the field values can be
# associated with the file they came from (= the column they belong).
for FILE in "$@"
do
    while read id value
    do
        SORTKEY="$id"__"$col"
        entries[$SORTKEY]="$value"
        echo "$id" >> "tmp.ids"
    done < $FILE
    let col=$col+1
done

# Sort the IDs
sort -u "tmp.ids" > "tmp.ids.sorted"


# Read the sorted IDs back in and generate output lines, where the
# column fields are taken from the associative container "entries" and
# tab-separated.
# If "entries" doesn't contain a value for a given key, output "-NA-" instead.

while read id
do
    LINE="$id"
    for (( col=0; col<NUMCOLS; col++ ))
    do
        SORTKEY="$id"__"$col"
        if [[ -z "${entries[$SORTKEY]}" ]]
        then
            LINE=$(printf "%s\t-NA-" "$LINE")
        else
            LINE=$(printf "%s\t%s" "$LINE" "${entries[$SORTKEY]}")
        fi
    done
    echo "$LINE" >> "outfile.txt"
done < "tmp.ids.sorted"

rm tmp.ids tmp.ids.sorted

You would call this as ./sortscript.sh <file1> <file2> ... <fileN>.

This would generate an associative container entries and store all fields read from your input files under a key generated from the "ID" field and the column number. The IDs are written into an external file tmp.ids so that they can be sorted, which seems to be what you want.

After sorting, the IDs are read back in. Then, for each ID, all available fields belonging to that key are read from the container entries and placed on on an output line (the variable LINE). If no value is available for a specific ID/column combination, write -NA- instead.

The output lines are then written to a file outfile.txt.

Improve this answer
4
  • sorry, I am not able to understand this code. For the trial purpose, I have taken two files and change the script NUMCOLS=$# to NUMCOLS=$2 . But it is not working, do I need to change col=0 to col=2. Commented Jan 2, 2020 at 15:37
  • Actually, all you should need to do is copy the code and paste it into a file called sortscript.sh. All internal variables are automatically set, you only have to call the script as indicated in my answer. Could you elaborate in what way the code doesn't work for you? Commented Jan 2, 2020 at 15:39
  • After changing your script as described in my earlier comment, I wrote the command bash merging.sh /media/anjali/anjali_10TB/result_annotation_with_id_1 /media/anjali/anjali_10TB/interproscan-5.38/final_with_id error: merging.sh: line 49: tmp.keys.sorted: No such file or directory rm: cannot remove 'tmp.keys': No such file or directory rm: cannot remove 'tmp.keys.sorted': No such file or directory Commented Jan 2, 2020 at 15:44
  • I see. As I wrote, you don't need to change the script. The $# variable is an internal bash variable and automatically contains the number of command-line arguments, which is the number of files you called the script with. Please try it again without changes. As for the error messages that is a typo on my side. Please replace "tmp.keys" and "tmp.keys.sorted" with "tmp.ids" and "tmp.ids.sorted". Commented Jan 2, 2020 at 15:51
0

You could use the join utility twice to produce two "outer joins" on the three files. Assuming all three files are tab-delimited, first the two first files: 

$ join -a 1 -a 2 -o 0,1.2,2.2 -e '-NA-' -t $'\t' <( sort File1 ) <( sort File2 )
MYORGANISM_I_05140.t1   Atypical/PIKK/FRAP      VALUES to be taken
MYORGANISM_I_06518.t1   CAMK/MLCK       -NA-
MYORGANISM_I_00854.t1   TK-assoc/SH2/SH2-R      -NA-
MYORGANISM_I_12755.t1   TK-assoc/SH2/Unique     -NA-
MYORGANISM_I_12766.t1   -NA-    what

This asks the join utility to join the sorted files on the first field (which is the default). We explicitly say -a 1 -a2 to get all lines from both files, even if they don't match, and with -o 0,1.2,2.2 we request the output to consist of the join field (the first column) along with the second column of each file. The -e '-NA-' option specifies what string to fill empty fields with.

The above gives us a new dataset that we could use in a second join with the third file. For simplicity, assume that the above result is available in tmpdata (after having been redirected there), then

$ join -a 1 -a 2 -o 0,1.2,1.3,2.2 -e '-NA-' -t $'\t' tmpdata <( sort FILE3 )
MYORGANISM_I_00854.t1   TK-assoc/SH2/SH2-R      -NA-    -NA-
MYORGANISM_I_05140.t1   Atypical/PIKK/FRAP      VALUES to be taken      -NA-
MYORGANISM_I_06518.t1   CAMK/MLCK       -NA-    -NA-
MYORGANISM_I_12755.t1   TK-assoc/SH2/Unique     -NA-    -NA-
MYORGANISM_I_12766.t1   -NA-    what    -NA-
MYORGANISM_I_16941.t1   -NA-    -NA-    OK
MYORGANISM_I_93484.t1   -NA-    -NA-    LET IT BE

This repeats more or less the same "outer join" from before, but also adds an extra column with the -o option.

Improve this answer
0

One liner to be executed in shell (/bin/sh or /bin/bash):

FILES="File1 File2 FILE3"; LIST=$(for F in ${FILES}; do cat ${F}|awk '{print $1}'; done|sort|uniq|xargs); for i in ${LIST}; do echo -n "$i"; for F in ${FILES}; do L=$(grep "^${i}\s" ${F}|head -1|sed 's/\t/ /'|cut -d' ' -f 2-|sed 's/^\s*//g'); [ -z "${L}" ] && echo -n " -NA-" || echo -n " ${L}" ; done; echo; done|sort

Output:

MYORGANISM_I_00854.t1 TK-assoc/SH2/SH2-R -NA- -NA-
MYORGANISM_I_05140.t1 Atypical/PIKK/FRAP VALUES to be taken -NA-
MYORGANISM_I_06518.t1 CAMK/MLCK -NA- -NA-
MYORGANISM_I_12755.t1 TK-assoc/SH2/Unique -NA- -NA-
MYORGANISM_I_12766.t1 -NA- what -NA-
MYORGANISM_I_16941.t1 -NA- -NA- OK
MYORGANISM_I_93484.t1 -NA- -NA- LET IT BE

Explanation:

# create list of files
# it can be created based on search like
# find . -type f -name filename.txt
# or something different
FILES="File1 File2 FILE3";
# create a list if unique first lines from all files from the list FILES
LIST=$(for F in ${FILES}; do cat ${F}|awk '{print $1}'; done|sort|uniq|xargs);
# take one by one each first line
# and go through all the files find corresponding lines endings
# and put them together
# or take '-NA-' for non-existing
for i in ${LIST}; do
    echo -n "$i";
    for F in ${FILES}; do
        #
        # old version of line commented out
        # L=$(grep "^${i}\s" ${F}|head -1|cut -d' ' -f 2-|sed 's/^\s*//g');
        # new version of line to make tab separator working
        L=$(grep "^${i}\s" ${F}|head -1|sed 's/\t/ /'|cut -d' ' -f 2-|sed 's/^\s*//g');
        #
        [ -z "${L}" ] && echo -n " -NA-" || echo -n " ${L}" ;
    done;
    echo;
done|sort
# sorted results printed
Improve this answer
5
  • It is working but creating two columns of IDs and 3rd column with first file and 4th column again IDs then File2 and File3. Commented Jan 2, 2020 at 16:02
  • 1) What do you mean saying "column"? There are 2 part of each line: "key " and "the rest", because if we count columns, the line "MYORGANISM_I_05140.t1 Atypical/PIKK/FRAP VALUES to be taken -NA-" has 7 columns? There is nothing specified about "columns" in the task. Commented Jan 2, 2020 at 16:41
  • 2) I created your 3 files and executed the one line command specified above. I printed my output. Is your output different? Can you provide with more details? Commented Jan 2, 2020 at 16:41
  • It is working on these sample FILES but with my original file, it is not working. May be because my files are having tab delimiter. Commented Jan 2, 2020 at 17:12
  • I edited both: a) the single line command, 2) version with explanation. The code added is "|sed 's/\t/ /'", it should make files with tab separator working. Commented Jan 2, 2020 at 17:30

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.