Add In a file 2

[pavithra123]

Hi All,

I have two files in file A, I have the following entries like ( it will have the country codes )

UNITED_STATES_OF_AMERICA,1
EGYPT , 20
MOROCCO , 212
ALGERIA , 213


and in another file B, I have the following entries

46578,212
4363,1
904,213

I need to add in file A the first field in File B whenever the second field in file A matches with the second field in File B.

If there is no match for then the field will have a zero.

For eg: for the above entries there is no match for 20 so a zero must be added in the field.

The output would be something like this

UNITED_STATES_OF_AMERICA,1,4363
EGYPT , 20,0
MOROCCO , 212,46578
ALGERIA , 213,904


Any help would be greatly appreciated.

best regards
Prem.

 

[duncdude]

[b]#!/usr/bin/perl[/b]

chdir "/where/the/files/are/located/";

open (FILEA, "< fileA");
@fileA = <FILEA>;
close FILEA;

open (FILEB, "< fileB");
@fileB = <FILEB>;
close FILEB;

foreach $fileB_line (@fileB) {
  $fileB_line =~ m|^(\d+),(\d+)|;
  $b{$2}=$1;
}

foreach $fileA_line (@fileA) {
  $fileA_line =~ m|^[A-Z_]+\s*,\s*(\d+)|;
  if ($b{$1}) {
    print "$b{$1},$1\n";
  }
}

Kind Regards
Duncan

 

[Ygor]

Could just use awk..
[tt]
awk ' BEGIN {
FS="[, ]+"
OFS=","
while (getline < "fileB") a[$2] = $1
while (getline < "fileA") print $0, a[$2]+0
}'[/tt]

 

[pavithra123]

Hi duncdude,

I do not have perl installed on the UNIX box and also I want to use this on Open VMS box as well. So I would take ygor option using AWK.

Thanks for your suggestion.

Hi Ygor

I was trying to use the awk prg you gave it does not give any error but in the output only zeros are appended even though there are some values in file B.

I need to do the following

I need to add in file A the first field in File B whenever the second field in file A matches with the second field in File B.

If there is no match for then the field will have a zero.

I appreciate your help.
Best Regards
Prem.


I changed in the line while (getline < "fileB") a[$2] = $1

to

while (getline < "fileB") a[$2] = $2 but it gives the same result.

 

[RanganathRamachandra]

if you can remove the extra spaces (tabs?) in the first file, this might work :

sort -n +1 -2 fileB | join -t, -12 -22 fileA - | awk -F, '{print $2 "," $1 "," $3 }'

this assumes that fileA is sorted in the numerical ascending order of the country codes. this is what is being done:
- sort fileB in the same order as fileA
- join the two files based on the 2nd field
- put the fields in the required order

 

[RanganathRamachandra]

ok that was incomplete, here is a better version that can take care of your tabs as well, but still assumes that fileA is sorted in ascending numerical order of country code:

sort -n +1 -2 fileA | sed 's/,/,<tab>/' | join -t, -12 -22 -a1 -e0 -o 1.1,1.2,2.1 fileB -

 

[RanganathRamachandra]

sorry again - as you may have noticed, i switched the filenames! here it is finally:

sort -n +1 -2 fileB | sed 's/,/,<tab>/' | join -t, -12 -22 -a1 -e0 -o 1.1,1.2,2.1 fileA -

 

[Ygor]

The awk solution was tested on the sample data...
[tt]
$ cat fileA
UNITED_STATES_OF_AMERICA,1
EGYPT , 20
MOROCCO , 212
ALGERIA , 213
$ cat fileB
46578,212
4363,1
904,213
$ awk ' BEGIN {
> FS="[, ]+"
> OFS=","
> while (getline < "fileB") a[$2] = $1
> while (getline < "fileA") print $0, a[$2]+0
> }'
UNITED_STATES_OF_AMERICA,1,4363
EGYPT , 20,0
MOROCCO , 212,46578
ALGERIA , 213,904
[/tt]
...which seems okay to me. Perhaps your data is different?

 

[pavithra123]

Hi Yogor,
You were right in the fileA there was a space after the comma, after removing it, the output is right. Thanks a lot for your help.

Even though I have not tried the other options sent by duncdude and ranganath ramachnadra I appreciate your help very much thanks once again.

BR
Prem