topic Re: Awk scripting problem - columns in Operating System - HP-UX

Awk scripting problem - columns

Luke Morgan — Thu, 28 Nov 2002 10:44:58 GMT

Hi,
I have a data file with a number of records, each with two fields.
One of the fields is an index number, and the other is a quantity value.
I need to put the data in a result file that has a large number of
columns (around 200).

By specifying NF, I can create the number of fields I want in my output
file, but I need to be able to specify that one of the input
values corresponds to a column number and insert its quantity value
in that column.

ie
data column quantity
1 4
4 2

would give a result file (using | delimiter)
4|||2|||

How can I specify the column number using a variable number?

thanks.

Luke

Re: Awk scripting problem - columns

Steve Steel — Thu, 28 Nov 2002 11:10:14 GMT

Hi

I am not sure that awk will do what you want but

http://sparky.rice.edu/~hartigan/awk.html

May help your search.

Try an array in a shell script with the array set to "|" in every value and then just fill up
the variables with number| where relevant.

see man ksh

Regards

Steve Steel

Quote of the moment
-------------------
"We are drowning in information but starved for knowledge."
-- John Naisbitt

Re: Awk scripting problem - columns

Jean-Louis Phelix — Thu, 28 Nov 2002 11:19:02 GMT

Hi,

This one fixes number of columns to greatest index :

#! /usr/bin/sh
awk '
BEGIN {
max=0
}
{
t[$1]=$2
if (max < $1)
max=$1
}
END {
for (i=1 ; i <= max ; i++)
if (t[i] == 0)
printf("|")
else
printf("%d|", t[i])
print
}' $1

and this one fixes number of columns as first argument :

#! /usr/bin/sh
awk -v NB=$1 '
{
t[$1]=$2
}
END {
for (i=1 ; i <= NB ; i++)
if (t[i] == 0)
printf("|")
else
printf("%d|", t[i])
print
}' $2

if file contains :

1 4
4 8

> script1 infile
4|||8|
> script2 10 infile
4|||8|||||||

Regards

Re: Awk scripting problem - columns

Robin Wakefield — Thu, 28 Nov 2002 11:19:04 GMT

Luke,

Not sure if this is what you want, but assuming your output is 20 columns wide:

awk '{a[$1]=$2}END{for (i=1;i<20;i++){printf ("%s|",a[i])};print}' filename

Rgds, Robin

Re: Awk scripting problem - columns

Christian Gebhardt — Thu, 28 Nov 2002 11:38:21 GMT

Hi
what about this:

awk '{max=10;for (i=1;i<=max;i++)
{
if ($1==i)
{ printf("%s|",$2)}
else
{ printf ("|")}
if (i==max) printf("\n")
}}' file

file looks like that:

1 4
4 2
3 3
2 5

output looks like that:

4||||||||||
|||2|||||||
||3||||||||
|5|||||||||

Chris

Re: Awk scripting problem - columns

Luke Morgan — Thu, 28 Nov 2002 12:07:01 GMT

Christian,

That is great, thanks.
What I really need, is to get all of them on the same output line. (they are unique index numbers)

ie
instead of
4|||||||
|||2||||
||3|||||
|5||||||

it reads
4|5|3|2||||||

Luke

Re: Awk scripting problem - columns

Vincent Stedema — Thu, 28 Nov 2002 13:01:26 GMT

Hi Luke,

Using awk:

cat | sort -0 +1 -b | awk 'BEGIN {ORS="\|"} {print $2} END {ORS="\n"}' -

I can't guarantee that this version will work, as I don't have access to a unix/linux box at the moment....

Using perl:

perl -wle "open(FILE, $ARGV[0]) || die qq(can't open file); foreach(){ ($num,$val) = ($1 - 1,$2) if $_ =~ /^(\d+)\s+(\d+)$/; $fields[$num] = $val; }
print join('|', @fields)"

Regards,

Vincent

Re: Awk scripting problem - columns

Vincent Stedema — Thu, 28 Nov 2002 13:05:42 GMT

BTW, you might want to ditch the perl "-w" switch so it won't complain about the uninitialized array elements.

Re: Awk scripting problem - columns

john korterman — Thu, 28 Nov 2002 14:33:31 GMT

Hi Luke,

for this input file:
1 4
4 2
6 14
3 3
9 23
2 5

you can get this output:
|4|5|3|2||14|||23

by running the attached script with the input file as $1

hope it helps,
John K.

Re: Awk scripting problem - columns

H.Merijn Brand (procura — Thu, 28 Nov 2002 14:42:09 GMT

And a shorter and simpler version of Vincent's good solution (Vincent, use magic open)

# cat data_file
1 4
4 2
3 3
2 5
# perl -nle'/(\d+)\D+(\d+)/ and$x[$1-1]=$2}END{$"="|";print"@x"' data_file
4|5|3|2
#

Re: Awk scripting problem - columns

Christian Gebhardt — Thu, 28 Nov 2002 14:52:59 GMT

Hi
just for info zhe solution in awk

sort file | awk '{zeile[NR]=$0;maxfield=$1}
END {
run=1
for (i=1;i<=maxfield;i++)
{
split(zeile[run],feld)
if ( i == feld[1])
{
printf("%s|",feld[2])
run++
}
else printf("|")
}
}'

perl is much more elegant than this.

Chris

Re: Awk scripting problem - columns

Luke Morgan — Thu, 28 Nov 2002 15:10:50 GMT

Thank you all very much for your suggestions.

The perl script from procura
works perfectly although I know
nothing about perl so I hope I dont have to debug it
before I get chance to read up!
:o)

Thanks again

Luke

Re: Awk scripting problem - columns

H.Merijn Brand (procura — Thu, 28 Nov 2002 15:11:43 GMT

can still save three keys on that :)

# perl -nle'/(\d+)\D+(\d+)/and$x[$1-1]=$2}END{$,="|";print@x' data_file
4|5|3|2
#

Re: Awk scripting problem - columns

H.Merijn Brand (procura — Thu, 28 Nov 2002 15:19:25 GMT

using the features you should not, even shorter ...

# perl -nle'/\D+(\d+)/and$x[$`-1]=$1}END{$,="|";print@x' data_file
4|5|3|2
#

Note that we cannot do

# perl -nle'/\D+/and$x[$`-1]=$'}END{$,="|";print@x' data_file

because $' will end the -e expression

Re: Awk scripting problem - columns

Luke Morgan — Thu, 28 Nov 2002 16:20:37 GMT

Is it possible to use that perl script in a loop to do
a number of datafiles in one go?
And is it possible to maintain the sign of the numbers?
ie keep -4 in the data as -4

thanks

Luke

Re: Awk scripting problem - columns

H.Merijn Brand (procura — Thu, 28 Nov 2002 17:08:26 GMT

Sure, hold on ...

l1:/tmp 110 > perl -le'$,="|";for(@ARGV){open X,"<$_"or die"$_:$!";@x=();while(){/(\d+)\D+?(-?\d+)/and$x[$1-1]=$2}print@x}' data_file1 data_file2
4|5|3|2
5|-12|-99|8000
l1:/tmp 111 > cat data_file1
1 4
4 2
3 3
2 5
l1:/tmp 112 > cat data_file2
3 -99
1 5
4 8000
2 -12
l1:/tmp 113 >

this what you want?

Re: Awk scripting problem - columns

Luke Morgan — Fri, 29 Nov 2002 09:08:06 GMT

I tried that on the command line and I got an error:

Modification of non-creatable array value attempted, subscript -1 at -e line 1, line 1.

Unfortunately I don't know a thing about perl so I have no
idea what the problem is.

The looping I need to do is because I have a directory with
a number of files in of the same format. I need to take each
file and change its format in the way that the first perl script
you gave me did.

I was thinking a simple script along the lines of
for a in `ls `
do
perl
done

I tried this loop using the first script you gave (that works
perfectly on the command line) and put it into the loop and
got the same error as the looping perl script...

Luke

Re: Awk scripting problem - columns

H.Merijn Brand (procura — Fri, 29 Nov 2002 09:46:45 GMT

l1:/tmp 102 > perl5.00503 -le'$,="|";for(@ARGV){open X,"<$_"or die"$_:$!";@x=();while(){/(\d+)\D+?(-?\d+)/and$x[$1-1]=$2}print@x}' data_file1 data_file2
4|5|3|2
5|-12|-99|8000
l1:/tmp 103 > perl5.6.1 -le'$,="|";for(@ARGV){open X,"<$_"or die"$_:$!";@x=();while(){/(\d+)\D+?(-?\d+)/and$x[$1-1]=$2}print@x}' data_file1 data_file2
4|5|3|2
5|-12|-99|8000
l1:/tmp 104 > perl5.8.0 -le'$,="|";for(@ARGV){open X,"<$_"or die"$_:$!";@x=();while(){/(\d+)\D+?(-?\d+)/and$x[$1-1]=$2}print@x}' data_file1 data_file2
4|5|3|2
5|-12|-99|8000
l1:/tmp 105 > perl5.9.0 -le'$,="|";for(@ARGV){open X,"<$_"or die"$_:$!";@x=();while(){/(\d+)\D+?(-?\d+)/and$x[$1-1]=$2}print@x}' data_file1 data_file2
4|5|3|2
5|-12|-99|8000
l1:/tmp 106 >

For a change I realy tested the perl examples I posted. What is your perl version? (perl -v)

Show me the failing script.

Re: Awk scripting problem - columns

Luke Morgan — Fri, 29 Nov 2002 10:28:46 GMT

Perl version is 5.6.0 for i386-linux.

The simple script Im trying to use to run your perl
against all the files in the directory is this:

for a in `ls`
do
perl -le'$,="|";for(@ARGV){open X,"<$_"or die"$_:$!";@x=();while(){/(\d+)\D+?(-?\d+)/and$x[$1-1]=$2}print@x}' $a > $a.res
done

Also, when I run the perl script (its the version 5.6.1 copy from your last post) on the command line, i still get the error about modification of a non-creatable array.

Luke

Re: Awk scripting problem - columns

H.Merijn Brand (procura — Fri, 29 Nov 2002 10:45:58 GMT

as I showed that 5.005_03 is good enough, I guess that 5.6.0 is good enough too. Any chance to upgrade to 5.6.1 or 5.8.0? Both are much better than 5.6.0, but that aside.

In the script you show, you /mix/ the all-in-one solution and the one-by-one solution. To play safe, and readable, do:

for a in `ls` ; do
perl -nle'/(\d+)\D+(\d+)/and$x[$1-1]=$2}END{$,="|";print@x' $a >$a.res
done

Show me the exact error.

Be careful with my posts at the moment. I'm using the latest Opera 7 beta, which removes even more whitespace than the forum already does :/