2

I have multiple files which need to be merged based on first column from each file

File1:

foo  12  
jhdfeg 25  
kjfdgkl 37

File 2:

foo 23  
jhdfeg 45

File 3:

foo 35  
djhf 37  

The output should be like this

        file1 file2  file3  
foo     12    23     35  
jhdfeg  25    45     0  
kjfdgkl 37    0      0  
djhf    0     0      37
4
  • I think you miss djhf 37 in File 3? May 1, 2017 at 19:53
  • i dont think so. check the last line of the output
    – Bio_Ram
    May 2, 2017 at 8:03
  • None of File1/File2/File3 contains djhf so where from is this coming in the output? All this is based on the data you have shared.
    – user218374
    May 2, 2017 at 8:19
  • i am sorry for the missing line. i edited the question
    – Bio_Ram
    May 2, 2017 at 8:36

4 Answers 4

2

awk approach:

joiner.awk script:

#!/bin/awk -f
BEGIN { 
    f1=ARGV[1]; f2=ARGV[2]; f3=ARGV[3]     # the 1st, 2nd and 3rd file names respectively
    printf("%10s\t%s\t%s\t%s\n", "", f1, f2, f3)   # printing header
}
{ a[$1][FILENAME]=$2 }    # accumulating values
END {
    for (i in a) {
        printf("%-10s\t%d\t%d\t%d\n", i, a[i][f1], a[i][f2], a[i][f3]) 
    }
}

Usage:

awk -f joiner.awk file1 file2 file3

The output:

          file1 fil2 file3
kjfdgkl     37  0   0
foo         12  23  35
djhf        0   0   37
jhdfeg      25  45  0
1
  • Thanks for your reply. But its looks difficult if i want to work on multiple files
    – Bio_Ram
    May 2, 2017 at 8:22
1
perl -F'\s+' -lane '

   $. == 1 and @ARGC = ($ARGV, @ARGV);       # initialize the @ARGC array

   exists $h{$F[0]} or $h[keys %h] = $F[0];  # needed to remember order

   $h{$F[0]}->[@ARGC-@ARGV-1] = $F[1];       # populate hash

   END {
      $, = "\t";           # set the OFS to TAB

      print q//, @ARGC;    # print the first line with filenames

      for my $key (@h) {   # print remaninig lines with data
         print $key,
            map { $h{$key}->[$_] // 0 } 0 .. $#ARGC;
      }
   }

' file1 file2 file3 # ... you can give as many files here

Output

        file1   file2   file3
foo     12      23      35
jhdfeg  25      45      0
kjfdgkl 37      0       0
djhf    0       0       37
4
  • Thanks for the answer but it is messing up with the numbers from differently files
    – Bio_Ram
    May 2, 2017 at 8:19
  • Can you share your data on which you are running? Coz using the data you are currently sharing it generates the output that you are expecting.
    – user218374
    May 2, 2017 at 8:20
  • They are really large files. here are first few lines that appear in most of the files look like this R1_mod_3p_AAA_- 11 R2_---adar 401 R3_3p_CC- 78 R4_---adar 14 R5_mod_3p_C- 146 R6_mod_3p_GU_- 11
    – Bio_Ram
    May 2, 2017 at 8:32
  • I see that you have TABs in your input. You should use this then: perl -F'\s+' -lane '...'
    – user218374
    May 2, 2017 at 8:50
1

If you have 3 files as in your example you could do this with some join magic. First, write the tab delimited names of the files to the output file:

for i in File*; do printf "\t%s" "$i" >> RES; done

Add an empty line for actual results:

printf '\n' >> RES

Use join on File1 and File2 and redirect output to a temporary file:

join  -a1 -a2  -e0 <(sort File1) -o 0 1.2 2.2 <(sort File2) > TEMP_FILE

And now use it again with output from the above command and File3 (you could also use a pipe (|) here):

join  -a1 -a2  -e0 <(sort TEMP_FILE) -o 0 1.2 1.3 2.2 <(sort File3) >> RES

And replace whitespaces with tabs in RES:

tr ' ' '\t' < RES > FINAL_RES

Your results are in FINAL_RES:

$ cat FINAL_RES
        File1   File2   File3
foo     12      23      35
jhdfeg  25      45      0
kjfdgkl 37      0       0
1

And here a more general approach, independent from the number of files with sed:

sed '1{x;s/$/_/;x;}
  /foo/{x;s/_/ 0_/g;x;}
  G;s/^\([a-z]*\)  *\([0-9]*\).*\n\(.*_\)\1\([^_]*\)0/\3\1\4\2/
  s/^\([a-z]*\) *\([0-9]*\).*\n\([^_]*\)0_\(.*\)/\30_\4\1\3\2_/
  $! {h;d;}
  s/[^_]*_//
  y/_/\n/' file*

This relies on each file starting with the foo line, as in your example.

Given that you have basic knowledge about how sed works, pattern space and hold space, here comes the explanation:

The main idea is to construct the whole output table in the hold space. At each line the hold space contains the table at that point along with a template row you need for new rows. We use _ as line separator during processing. And now step by step:

1{x;s/$/_/;x;}

This initializes the hold space with a single _ as the start of our template row.

/foo/{x;s/_/ 0_/g;x;}

/foo/ addresses lines that contain foo, which indicates the beginning of a new file. In this case the commands in {} are executed: Each row in the hold space (actual table rows and the template row) get 0 appended. If we later meet the keyword of that row, the 0 gets replaced by the correct number; if the keyword doesn't occur, the 0 stays.

G;s/^\([a-z]*\) *\([0-9]*\).*\n\(.*_\)\1\([^_]*\)0/\3\1\4\2/

'G' appends the hold space to the pattern space. The s command has four \(\) sections: The first contains the keyword, the second the value, the third everything after the newline (so this is the table appended from hold space) up to the second occurence of the keyword (backreference \1). The fourth hold everything in that row excluding the final 0. So we have found an already existing line with that keyword and replace the 0. We drop everything up to the newline and just keep the updated table.

s/^\([a-z]*\) *\([0-9]*\).*\n\([^_]*\)0_\(.*\)/\30_\4\1\3\2_/

Another match including a newline \n, so we know we didn't find the keyword in the table (otherwise the newline would have been removed in the line before. So this time we add a new row to the end, composed of the keyword, the template row and the value. And this is the trick about the template row: We added one 0 column to it for each new file, so if we remove one 0 we have a 0 column for each file where this keyword didn't exist.

$! {h;d;}

If this wasn't the last line, move the modified table back to the hold space (h) and start over (d).

s/[^_]*_//

For the last line, this removes the template row.

y/_/\n/

And this replaces _ by newline. In addition, you could also replace spaces with tabs, if you like.

Edit

If the assumption is wrong that each file starts with the foo line, we need a different method to tell sed when a new file starts, like adding an extra line for each file start and stream the whole thing to sed:

for file in file*; do
  echo Start of $file
  cat $file
done | sed '1{x;s/$/__/;x;}
  /Start of/{G;s/_/ 0_/g;s/Start of \(.*\)\n\([^_]*\)_\([^_]*\) 0/\2_\3 \1/;x;d;}
  G;l;s/^\([a-z]*\)  *\([0-9]*\).*\n\(.*_\)\1\([^_]*\)0/\3\1\4\2/
  l;s/^\([a-z]*\) *\([0-9]*\).*\n\([^_]*\)0_\(.*\)/\3 0_\4\1\3\2_/
  $! {h;d;}
  s/[^_]*_//
  y/_/\n/'

This version also generates the head row of the table woth all file names as column headers.

4
  • It looks bit complicated can you plz explain it. Thank you
    – Bio_Ram
    May 2, 2017 at 8:01
  • @tarakaramji Indeed. Almost magical, right? It was more a fun exercise for me, but it nicely demonstrates the hidden power of sed (and how sed scripts are sometimes easier to write than to read). I added an explanation for you. Ask back if something remains unclear. Feel free to add an l command somewhere to see how the table builds up.
    – Philippos
    May 2, 2017 at 8:58
  • Sed is really amazing. Maybe can i ask what if foo is not present in all the files i use? or can it be independent of any row?
    – Bio_Ram
    May 2, 2017 at 9:43
  • sed would not know when a new files starts. I've appended a solution for this case, which also generates the table header.
    – Philippos
    May 2, 2017 at 11:34

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.