topic Re: Script question - merging files based on first parameter in Operating System - Linux

Script question - merging files based on first parameter

S.Rider — Wed, 26 Jul 2006 17:21:07 GMT

I have multiple files, here's the first few lines of each.
[ File-1 ]
Parameter,hostname1
NSTRBLKSCHED,-
NSTREVENT,50
NSTRPUSH,16

[ File-2 ]
Parameter,hostname2
NSTRBLKSCHED,-
NSTREVENT,100
NSTRPUSH,26

[ File-3 ]
Parameter,hostname3
NSTRBLKSCHED,-
NSTREVENT,150
NSTRPUSH,36

etc - etc - etc

I would like to read multiple input files, and if the first value in each line matches, output a single output line.
For example, from the above, I would get

Parameter,hostname1,hostname2,hostname3
NSTRBLKSCHD,-,-,-
NSTREVENT,50,100,150
NSTRPUSH,16,26,36

I would then use the csv file to create an xls comparing the kernal parms of multiple servers. Is there an easy way to merge files like this based on the 1st parameter ?

Re: Script question - merging files based on first parameter

James R. Ferguson — Wed, 26 Jul 2006 18:30:47 GMT

Hi:

The following should serve your needs:

# cat cmpkparm
#!/usr/bin/perl
use strict;
use warnings;
my %lines;
my ( $parm, $value );
while (<>) {
chomp;
( $parm, $value ) = split( /,/, $_ );
if ( exists $lines{$parm} ) {
push( @{ $lines{$parm} }, $value );
}
else {
push( @{ $lines{$parm} }, $parm, $value );
}
}
foreach $parm ( sort keys %lines ) {
print "@{$lines{$parm}}";
print "\n";
}
1;

...Run as:

# ./cmpkparm file1 file2 file3 ...

That is, pass as many file names on the commandline as you need to process. The format of each file is as you posted.

Regards!

...JRF...

Re: Script question - merging files based on first parameter

S.Rider — Wed, 26 Jul 2006 20:22:28 GMT

THANKS - but one dumb perl question.
How do I get the commas between the values outputed ?

Re: Script question - merging files based on first parameter

Sandman! — Wed, 26 Jul 2006 21:01:25 GMT

How about giving this awk construct a try:

awk -F, '{if(p[$1])p[$1]=p[$1]","$2;else p[$1]=$2}END{for(i in p) print i,p[i]}' files

cheers!

Re: Script question - merging files based on first parameter

Sandman! — Wed, 26 Jul 2006 22:39:02 GMT

Matter of fact the code is easier to follow if indented and you can specify as many files as you like on the awk input line...so here goes:

awk -F"," '{
if(p[$1])
p[$1]=p[$1]","$2
else
p[$1]=$2
} END{for(i in p) print i","p[i]}' File-1 File-2 File-3

Re: Script question - merging files based on first parameter

Hein van den Heuvel — Thu, 27 Jul 2006 00:03:00 GMT

You can fix JRF's solution to get commas by replacing the final printout with:

foreach $parm ( sort keys %lines ) {
$_ = join ",", @{$lines{$parm}};
chop;
print "$_\n";
}

I did not expect to need to chop the last ",", but it was needed on my windoze box

Mind you, that solution requires that all params are present for each node, and the params are re-ordered alphabetically, including the 'header' line.

Below is an alternative which tolerates missing values and keeps the order initially based on the first file processed.
Enable the '# print' in the middle to see what it is doing.

hth,
Hein.

use strict
my ($name,$value,$param,$params,$host,$hosts,$line,@data);
while (<>) {
chomp;
($name,$value) = split /,/;
next unless $name;
$hosts++ if /^Parameter,/;
$param = $names{$name};
if (!defined $param){
$names{$name} = ++$params;
$param = $params;
$data[$param][0] = $name;
}
$data[$param][$hosts] = $value;
# print "p=$param, h=$hosts, n=$name, v=$value\n";
}
foreach $param (1..$params) {
$_ = "";
foreach $host (0..$hosts) {
$_ .= $data[$param][$host].",";
}
chop;
print "$_\n";
}

Re: Script question - merging files based on first parameter

Peter Nikitka — Thu, 27 Jul 2006 02:23:52 GMT

Hi,

to get the output lines in order with Sandmans awk solution you have to add one additional array only:

awk -F, '{
if(p[$1])
p[$1]=p[$1]","$2
else {key[++j]=$1
p[$1]=$2
}
}
END{for(i=1;i<=j;i++) print key[i]","p[key[i]]}' files ...

mfG Peter

Re: Script question - merging files based on first parameter

James R. Ferguson — Thu, 27 Jul 2006 08:05:34 GMT

Hi (again):

To add the commas to the output, simply change the line:

print "@{$lines{$parm}}";

...to:

print join ",", @{$lines{$parm}};

A simple hack to keeping the header line (sorted) to the top would be to enclose the first header field in angle brackets, e.g. for your File-1:

,hostname1

Regards!

...JRF...

Re: Script question - merging files based on first parameter

Thom Cornwell — Thu, 03 Aug 2006 14:54:31 GMT

I would suggest possibly using the join command line command