Combine columns of two files matching a column in each with awk

[meinida]

I am new to awk. I have two files and need to combine parts of each file into one by lining up a column in each.

file1
ZOLT:;


LOADING PROGRAM VERSION 6.16-0

SIGNALLING LINK LOAD

ACTIVE UNIT
METERS OF LAST PERIOD: 15:00:00 - 15:30:00 (30 MIN)

TDM BASED LINKS:
================

RECEIVED TRANSMITTED
LINK ERLANGS ERLANGS
==== ========== ===========
0 0.254 0.238
1 0.257 0.222
2 0.073 0.070
3 0.073 0.071
4 0.063 0.038
5 0.063 0.038
7 0.076 0.123
9 0.075 0.090
10 0.073 0.070
11 0.073 0.070
12 0.074 0.069
13 0.077 0.090
14 0.251 0.180
15 0.053 0.051
16 0.079 0.077
20 0.052 0.051
21 0.054 0.050
22 0.052 0.050
23 0.036 0.035
24 0.037 0.035
25 0.076 0.091
26 0.076 0.089
28 0.240 0.201
29 0.029 0.043
30 0.076 0.125

file2
ZNEL;


LOADING PROGRAM VERSION 8.27-0


SIGNALLING LINK STATES

LINK TERM LOG EXTERN INTERN BIT
LINK LINK SET STATE UNIT TERM FUNCT TERM PCM-TSL PCM-TSL RATE
==== ======== ======= ===================================================
0 16 FSTP1 AV-EX CCSU-2 1 1 0 590-01 198-01 56
1 17 FSTP2 AV-EX CCSU-0 1 2 0 696-01 190-02 56
2 18 BFM01 AV-EX BSU-0 1 15 0 592-01 174-15 64
3 18 BFM01 AV-EX BSU-1 1 15 0 656-01 178-15 64
4 19 HLR01 AV-EX CCSU-0 1 8 1 576-01 190-08 64
5 19 HLR01 AV-EX CCSU-2 1 5 1 694-01 198-05 64
7 34 FTMS1 AV-EX CCSU-0 1 7 2 623-01 190-07 64
8 34 FTMS1 UA-INS CCSU-2 1 4 3 687-01 198-04 64
EXCHANGE TERMINAL INACTIVE

9 42 BFM02 AV-EX BSU-0 1 14 1 596-01 174-14 64
10 18 BFM01 AV-EX BSU-1 1 14 1 565-01 178-14 64
11 18 BFM01 AV-EX BSU-0 1 13 2 660-01 174-13 64
12 18 BFM01 AV-EX BSU-1 1 13 2 598-01 178-13 64
13 42 BFM02 AV-EX BSU-0 1 12 3 631-24 174-12 64
14 17 FSTP2 AV-EX CCSU-2 1 0 8 680-01 198-00 56
15 57 BSY01 AV-EX BSU-0 1 11 5 679-24 174-11 64
16 57 BSY01 AV-EX BSU-0 1 10 4 578-24 174-10 64
17 - UA-AD CCSU-2 1 6 5 527-24 198-06 64
SIGNALLING LINK NOT IN SIGNALLING LINK SET

18 57 BSY01 UA-INS BSU-1 1 12 5 675-24 178-12 64
19 66 HYANN AV-EX CCSU-0 1 1 7 527-01 190-01 56
20 57 BSY01 AV-EX BSU-1 1 11 4 575-24 178-11 64
21 57 BSY01 AV-EX BSU-0 1 9 6 589-24 174-09 64
22 57 BSY01 AV-EX BSU-1 1 10 6 585-24 178-10 64
23 18 BFM01 AV-EX BSU-0 1 8 7 639-01 174-08 64
24 18 BFM01 AV-EX BSU-1 1 9 7 661-01 178-09 64
25 42 BFM02 AV-EX BSU-1 1 8 8 607-24 178-08 64
26 42 BFM02 AV-EX BSU-0 1 7 8 670-24 174-07 64
27 42 BFM02 UA-INS BSU-0 1 6 9 641-24 174-06 64
28 16 FSTP1 AV-EX CCSU-0 1 0 4 698-01 190-00 56
29 72 GDI01 AV-EX BSU-1 1 7 14 900-24 178-07 64
30 34 FTMS1 AV-EX CCSU-2 1 3 7 622-01 198-03 64

This is how I would like output
file3

LINK,SET,LINK,R-ERLANGS,T-ERLANGS
0,FSTP1,0,0.254,0.238
1,FSTP2,1,0.257,0.222
2,BFM01,2,0.073,0.07
3,BFM01,3,0.073,0.071
4,HLR01,4,0.063,0.038
5,HLR01,5,0.063,0.038
7,FTMS1,7,0.076,0.123
8,FTMS1,,,
9,BFM02,9,0.075,0.09
10,BFM01,10,0.073,0.07
11,BFM01,11,0.073,0.07
12,BFM01,12,0.074,0.069
13,BFM02,13,0.077,0.09
14,FSTP2,14,0.251,0.18
15,BSY01,15,0.053,0.051
16,BSY01,16,0.079,0.077
17,UA-AD,,,
18,BSY01,,,
19,HYANN,,,
20,BSY01,20,0.052,0.051
21,BSY01,21,0.054,0.05
22,BSY01,22,0.052,0.05
23,BFM01,23,0.036,0.035
24,BFM01,24,0.037,0.035
25,BFM02,25,0.076,0.091
26,BFM02,26,0.076,0.089
27,BFM02,,,
28,FSTP1,28,0.24,0.201
29,GDI01,29,0.029,0.043
30,FTMS1,30,0.076,0.125

Middle link column could be removed. I just left it there to show the matching columns. If anyone has any ideas how to do this please let me know.

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[meinida]

First I tried Perl, but ran into trouble opening 2 files at the same time. I found a couple articles on the web that indicated that Perl has issues with opening more than one at a time.
Then I started researching AWK and it seemed like it should be possible. I have tried multiple different commands like the one below, but I can't seem to get the columns to line up.


paste file2 file1|awk '{print $1, $2, $3, $4, $6}'

I have also played with this script with no joy.

#!/usr/bin/awk -f
#Awk program : paste.awk

function readFile(i, rec) {
if (getline rec < ARGV)
return rec;
else {
++eof;
return "";
}
}

BEGIN {
while (1) {
rec1 = readFile(1);
rec2 = readFile(2);
if (eof[1] && eof[2]) break;
printf("%-60s%s\n", rec1, rec2);
}
exit;
}

awk -f file2 file1

 

[Annihilannic]

I haven't heard of Perl having any issues opening more than one file at a time.

Anyway, try this:

awk -v OFS=, '
        BEGIN {
                print "LINK,SET,R-ERLANGS,T-ERLANGS"
        }
        # reading the first file (FNR == NR)
        FNR == NR {
                # if the first field is numeric
                if ($1 ~ /^[0-9]+$/) {
                        # load into arrays
                        rx[$1] = $2
                        tx[$1] = $3

                }
                next
        }
        # reading the second file (i.e. FNR != NR)
        # if first field is numeric
        $1 ~ /^[0-9]+$/ {
                print $1,$3,rx[$1],tx[$1]
        }
' file1 file2

Annihilannic.

 

[meinida]

Hey that works good if I remove awk -v OFS=, ' from the beginning and ' file1 file2 from the end. That of course leaves the output without coma seperation, but I can live with that. You Rock!!!



I do have two more questions if you don't mind.

How would I add the Date stamp from one of the input files?

How would I change the decimal values to percent?
I tried changing rx[$1] at the bottom to rx[$1*100] but that caused the output to be blank.

 

[meinida]

I just realized that the cut and paste from file1 was missing the date. file1 actually starts like this.

ZOLT:;


LOADING PROGRAM VERSION 6.16-0

XXXX XXXXXXXXX 2009-07-20 15:40:23

SIGNALLING LINK LOAD

ACTIVE UNIT
METERS OF LAST PERIOD: 15:00:00 - 15:30:00 (30 MIN)

TDM BASED LINKS:
================

RECEIVED TRANSMITTED
LINK ERLANGS ERLANGS
==== ========== ===========
0 0.254 0.238
1 0.257 0.222
2 0.073 0.

So the time and date stamp is there Ijust have to extract it. Would I do that with substr?

 

[meinida]

I have added the following code in an attempt to extract the date. What am I doing wrong?


BEGIN {
if ($1 ~ /^XXXX/){
date = substr("Date", 36, 20)
print date
}
print "LINK,SET,R-ERLANGS,T-ERLANGS"
}
# reading the first file (FNR == NR)
FNR == NR {
# if the first field is numeric
if ($1 ~ /^[0-9]+$/) {
# load into arrays
rx[$1] = $2
tx[$1] = $3

}
next
}
# reading the second file (i.e. FNR != NR)
# if first field is numeric
$1 ~ /^[0-9]+$/ {
print $1,$3,rx[$1],tx[$1]
}

 

[meinida]

Thanks Annihilannic
I was able to figure the rest out on my own. I appreciate the script you wrote to get me going.