Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I have a data file (data_array.txt in the example) and index file, index.txt, at which I want to extract the data from the data file that have the same ID in the index file and store in to a new file, Out.txt. I also want to put NA ,in the Out.txt, for those ID's that have no value in the data file. I know how to do it for one column. But my data has more than 1000 columns (from 1 to 1344). I want you help me with a script that can do it faster. My data file, index id and proposed out put as follows.

data_array.txt

Id  1   2   3   .   .   1344
1   10  20  30  .   .   -1
2   20  30  40  .   .   -2
3   30  40  50  .   .   -3
4   40  50  60  .   .   -4
6   60  60  70  .   .   -5
8   80  70  80  .   .   -6
10  100 80  90  .   .   -7

index.txt

Id
1
2
8
9
10

Required Output is

Out.txt

Id  1   2   3   .   .   1344
1   10  20  30  .   .   -1
2   20  30  40  .   .   -2
8   80  70  80  .   .   -6
9   NA  NA  NA          NA
10  100 80  90  .   .   -7
share|improve this question
    
I wanted to answer your Q too, but it was very unclear to me what you actually wanted. –  slm Jan 2 at 16:14

2 Answers 2

up vote 2 down vote accepted

Here is a little awk script I came up with that should search for lines matching your indexes. Just put it in a file (eg lookup.awk) and run as follow:

lookup.awk

BEGIN {
        # read lookup variables from the commandline and put them in an array
        split(indexes, index_array, " ");
}

NR=1 {
        # set the number of columns to the amount that's on the first line (only used for NA printing)
        nr_of_fields = NF-1;
}

# For every line in your data file do the following
{
        # check if the first field matches a value in the index array
        for (var in index_array) {
                if ($1 == index_array[var]) {
                        # when a match is found print the line and remove the value from the index array
                        print $0;
                        delete index_array[var];
                        next;
                }
        }
}

END {
        # after all matching lines are found, print "NA" lines for the indexes that are still in the array
        for (var in index_array) {
                printf index_array[var];
                for (i=1; i<nr_of_fields; i++) {
                        printf "  NA";
                }
                printf "\n";
        }
}

You can then run it like this:

$ awk -f ./lookup.awk -v indexes="1 2 3 4 5 6 7 8 9 10" data.txt | sort -n
1   10  20  30  .   .   -1
2   20  30  40  .   .   -2
3   30  40  50  .   .   -3
4   40  50  60  .   .   -4
5  NA  NA  NA  NA  NA
6   60  60  70  .   .   -5
7  NA  NA  NA  NA  NA
8   80  70  80  .   .   -6
9  NA  NA  NA  NA  NA
10  100 80  90  .   .   -7

Please note that this awk script does not output the values in the some order as your index (that would require some extra logic).

share|improve this answer
    
Thank you! When I run $ awk -f ./lookup.awk -v indexes="1 2 3 4 5 6 7 8 9 10" data.txt | sort -n for this example, it work. The Problem is that my indexes are more than 1000. The indexes are not a whole numbers like the example. They are latitude locations. –  Abraham Jan 2 at 16:14

I'm not sure I understand, it looks like you want the output to be exactly like the input with an added line containing NAs for any index not present in the input file. If so, this should work:

$ awk '{
  if(NR==1){fields=NF;}
  if(NR==FNR){i[$1]=$0;}
  else{
    if($1 in i){print i[$1]}
    else{
      printf "%s%s", $1,OFS; 
      for(k=1;k<fields;k++){printf "%s%s","NA",OFS}
      print ""
    }  
  } 
}' data index 
Id  1   2   3   .   .   1344
1   10  20  30  .   .   -1
2   20  30  40  .   .   -2
3   30  40  50  .   .   -3
4   40  50  60  .   .   -4
5 NA NA NA NA NA NA 
6   60  60  70  .   .   -5
7 NA NA NA NA NA NA 
8   80  70  80  .   .   -6
9 NA NA NA NA NA NA 
10  100 80  90  .   .   -7

Personally though, I would do this in Perl:

$ cat data index | perl -ne '@a=split(/\s+/); 
   if($#a>1){$id{$a[0]}=$_; $na||="NA "x$#a . "\n";}
   else{$f=$id{$a[0]}||"$a[0] $na"; print "$f"}'
Id  1   2   3   .   .   1344
1   10  20  30  .   .   -1
2   20  30  40  .   .   -2
3   30  40  50  .   .   -3
4   40  50  60  .   .   -4
5 NA NA NA NA NA NA 
6   60  60  70  .   .   -5
7 NA NA NA NA NA NA 
8   80  70  80  .   .   -6
9 NA NA NA NA NA NA 
10  100 80  90  .   .   -7
share|improve this answer
    
@slm NA is for no data in the data index. I want to look the values from the data index that have the same index ID and store in a new file. My example may be poor to explain my question. I have to files, data index and index Id. I want create a new file from the data index that have the same index ID. –  Abraham Jan 2 at 16:07
    
@Abraham - I just edited Terdon's answer, direct your comment to him. Also if you need to refine your Q then please do so, and not just here on an A's comment. That way everyone can benefit from your Q becoming more clear. –  slm Jan 2 at 16:12
    
@Terdon - Abraham has some feedback on your A. –  slm Jan 2 at 16:12
    
@Abraham so you don't want NA? Just nothing? Please edit your question and show the exact output you want. –  terdon Jan 2 at 16:57
    
@terdon, I edited my question. I want NA to be stored in the output file for Id's that have no values in the data file. Thank you! –  Abraham Jan 2 at 17:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.