Get both common and missing values from multiple files
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
input:
input2
input3:
input4
output:
missing values should be NULL
Thanks,
Moderator's Comments:
CODE tags also for code
Last edited by Scrutinizer; 05-21-2014 at 03:55 PM..
I'm confused. You said that you tried:
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?
Hi all,
Say i have multiple files x1 x2 x3 x4, all with common header (date, time, year, age),,
How can I merge them to one singe file "X" in shell scripting
Thanks for your suggestions. (2 Replies)
Hello,
Suppose I have these 2 tab delimited files, where the second column in first file contains matching values from first column of the second file, I would like to get an output like this:
File A
1 A
2 B
3 C
File B
A Apple
C Cinnabon
B Banana
I would like... (1 Reply)
Good morning all,
I have a problem that is one step beyond a standard awk compare.
I would like to compare three files which have several thousand records against a fourth file. All of them have a value in each row that is identical, and one value in each of those rows which may be duplicated... (1 Reply)
Hi everyone,
I've a requirement to modify an existing line which is common to multiple files. I need to replace that existing line with a new line. I've almost 900 ksh files to edit in the similar fashion in the same directory.
Example:
Existing Line: . $HOME/.eff.env (notice the "." at the... (3 Replies)
Hi All,
I have two files like below:
File1
MYFILE_28012012_1112.txt|4
MYFILE_28012012_1113.txt|51
MYFILE_28012012_1114.txt|57
MYFILE_28012012_1115.txt|57
MYFILE_28012012_1116.txt|57
MYFILE_28012012_1117.txt|57
File2
MYFILE_28012012_1110.txt|57
MYFILE_28012012_1111.txt|57... (2 Replies)
I have this code
awk 'NR==FNR{a=$1;next} a' file1 file2
which does what I need it to do, but for only two files. I want to make it so that I can have multiple files (for example 30) and the code will return only the items that are in every single one of those files and ignore the ones... (7 Replies)
I am trying to extract common list of Organisms from different files
For example I took 3 files and showed expected result. In real I have more than 1000 files. I am aware about the useful use of awk and grep but unaware in depth so need guidance regarding it.
I want to use awk/ grep/ cut/... (7 Replies)
Hi All,
I have multiple (5+) text files with single columns and I would like to grep the common values across all the text files and parse it to a new file. All the values are numerical. Please let me know how to do it using awk. (6 Replies)
Dear Experts,
Have been seraching this forum from this morning for my query but dint find hence posting it her... Basically i have two input files BSS and MSS which has a unique string , hence i hav tried and seperated the text to compare frm both files .. Any my present input files look like... (6 Replies)