Shell Programming and Scripting

Hi,

I have 5 files with two columns. I need to merge all the 5 files based on column 1. If any of them are missing then corresponding 2nd column should be substituted by missing value.

I know hoe to do this for 2 files. but how can I implement for 5 files. I tried this based on 5 files but it does not work

Code:

awk 'FNR==NR{a[$2]=$3;next}{print $0,a[$2]?a[$2]:"NA"}' file2 file1

input:

Code:

ENSG00001 22
ENSG00002 20
ENSG00003 40

input2

Code:

ENSG00001 22
ENSG00004 22
ENSG00005 22

input3:

Code:

ENSG00000 22
ENSG00002 20
ENSG00003 40

input4

Code:

ENSG00002 22
ENSG00004 22
ENSG00005 22

output:

Code:

ENSG00000    22 
ENSG00001 22 22   
ENSG00002 22  20 22
ENSG00003 40  40 
ENSG00004 22  22
ENSG00005  22  22

missing values should be NULL

Thanks,

Moderator's Comments:

CODE tags also for code

Well, a suggestion for logic (no code yet)Does that seem a sensible approach?

How many output files do you want? Is it one output for each input?




Robin

I'm confused. You said that you tried:

Code:

awk 'FNR==NR{a[$2]=$3;next}{print $0,a[$2]?a[$2]:"NA"}' file2 file1

to handle five files, but you only gave this script two files?

The code above seems to be using the 2nd field (not the 1st) as the common key between files and using the 3rd field (not the 2nd) as the data for that key. This is probably why this wasn't doing what you wanted for two files.

Your sample output shows empty fields (with a single space field separator) when a field was missing; you said "missing fields should be NULL" (which could be interpreted to mean you want a null string or that you want the literal string "NULL"); but the code above seems to be trying to put out the literal string NA when the data is an empty string, 0, or missing.

So, I can guess that you want one output file with one output column for each input file.

But it isn't clear if you care whether or not the output is sorted on the 1st output column. Do you care if the output is sorted?

And it isn't clear what you want for output if the data in the 2nd field of an input file is missing, is an empty string, or is a numeric string that evaluates to 0. If there is no line in an input file for a 1st field value, do you want the output to be an empty string, the string NULL, or the string NA? If there is a line in an input file with a given 1st field value but there is no second field on that line in the input file, do you want the output to be an empty string, the string NULL, or the string NA? If there is a line in an input file with a given 1st field and the 2nd field is a string of one or more zeros, do you want the output to be an empty string, the string NULL, the string NA, or the string of one or more zeros found on the corresponding input line?

What do you want to use as the output field separator?