Formatting a text file based on row numbers 2

[mguha06]

Hello,

I am trying to rewrite a text files in a certain format:
where the files look like:

"Row","Column","Values"
1,86,0.002
1,87,0.01
1,88,1.01
2,86,0.2
2,87,0.4
2,88,1.0
3,86,0.3
3,87,0.2
3,88,5.0
4,86,6.2
4,87,0.3
4,88,0.2

I want to now write the file based on the row number as, only the "values"
field will be displaced, and would look like a matrix, eg., shown below:

0.002 0.01 1.01
0.2 0.4 1.0
0.3 0.2 5.0
6.2 0.3 0.2


I now have multiple of similar files, which need to be converted to a matrix
format. I have the file names in a sequence format, such as
f1.dat, f2.dat, f3.dat, etc.

Could someone please help me to write the files in a matrix format and also
help to automate, so that all the other files are written similarly,
instead of running awk everytime for each file.

Thanks for any help.

 

[PHV]

A starting point:
awk -F, '
/^[1-9]/ && NF==3{a[$1]=a[$1]" "$3}
END{for(i=1;i<=$1;++i)print substr(a,2)}
' /path/to/input > matrix

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mguha06]

Hi PHV,

The code does'nt work. Getting error. Remember, I do have a string header called, "Row","Column","Values". Further help is needed.
Thanks.

 

[PHV]

Getting error
any chance you could post the whole error message and what you really did ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mguha06]

Hi PHV,

I am running awk95 and I have renamed the code, test.awk and running the code from DOS. The test.awk looks like
{
^[1-9]/ && NF==3{a[$1]=a[$1]" "$3}
END{for(i=1;i<=$1;++i)print substr(a,2)}
}

and in the DOS prompt, I am typing
awk95 -f test.awk test.dat > test_output.dat

The error that I am getting is:

syntax error at source line 2 source file test.awk

 

[PHV]

test.awk:

/^[1-9]/ && NF==3{a[$1]=a[$1]" "$3}
END{for(i=1;i<=$1;++i)print substr(a[i],2)}

at the DOS prompt:
awk95 -F, -f test.awk test.dat > test_output.dat

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mguha06]

Hi PHV,

Sorry, I checked the code, it runs, and arranges the rows, however, what I intend to do is not doing. That is, it is printing all the contents, i.e.,
"Row","Column","Values" 1,86,0.002 1,87,0.01 1,88,1.01
2,86,0.2 2,87,0.4 2,87,0.4
3,86,0.3 3,87,0.2 3,88,5.0
4,86,6.2 4,87,0.3 4,88,0.2

My intended target is:

0.002 0.01 1.01
0.2 0.4 1.0
0.3 0.2 5.0
6.2 0.3 0.2

 

[PHV]

Again, what did you really ?

 

[mguha06]

Hi PHV,

I made a test.awk file, the test.awk file is:

/^[1-9]/ && NF==3{a[$1]=a[$1]" "$3}
END{for(i=1;i<=$1;++i)print substr(a,2)}

and from the DOS prompt ran the following:

awk95 -f test.awk test.dat >test_output.dat

 

[PHV]

awk95 [!]-F,[/!] -f test.awk test.dat > test_output.dat

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mguha06]

Hi PHV,

I ran a different script,

{$3=$3; if (0==NR%3) print $0
else printf "%s ", $0}

and got the following result (after removing the header):

1,86,0.002 1,87,0.01 1,88,1.01
2,86,0.2 2,87,0.4 2,88,1.0
3,86,0.3 3,87,0.2 3,88,5.0
4,86,6.2 4,87,0.3 4,88,0.2


however, still I have not achieved, what I intent to do, i.e.,

0.002 0.01 1.01
0.2 0.4 1.0
0.3 0.2 5.0
6.2 0.3 0.2

 

[PHV]

Have you read my previous post ?

 

[mguha06]

Hi PHV,

I tried based on the previous post, however, the output file is empty.

 

[PHV]

test.awk:

BEGIN{FS=","}
/^[1-9]/ && NF==3{a[$1]=a[$1]" "$3}
END{for(i=1;i<=$1;++i)print substr(a[i],2)}

at the DOS prompt:
awk95 -f test.awk test.dat > test_output.dat

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mguha06]

Hi PHV,

Still writing empty file, after running the above file.

the data set is, test.dat, shown below:

"row","column","values"
1,86,0.002
1,87,0.01
1,88,1.01
2,86,0.2
2,87,0.4
2,88,1.0
3,86,0.3
3,87,0.2
3,88,5.0
4,86,6.2
4,87,0.3
4,88,0.2

 

[PHV]

and with this program ?
BEGIN{FS=","}
/^[1-9]/ && NF==3{a[$1]=a[$1]" "$3[!];l=$1[/!]}
END{for(i=1;i<=[!]l[/!];++i)print substr(a,2)}

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mguha06]

Hi PHV,

Congratulations! It works. Your perseverance works. I am giving you few stars. I have many such files, and I want to automate it, what should I do. I have close to 500 such files. My files are written in a sequencing format, such as f1.dat, f2.dat, etc.

Thanks.

 

[PHV]

Sorry, I'm an unix guy and thus don't know well the DOS shell capacities.

 

[feherke]

Hi

There is a [tt]for[/tt] command in DOS too :

for %%f in (*.dat) do awk95 -f test.awk %%f > output_dir/%%f

Or you can generate a batch file with an [tt]awk95[/tt] script, then run that batch file :

BEGIN {
  for (i=1;i<=500;i++) print "awk95 -f test.awk f" i ".dat > output_dir/f" i ".dat"
}

Feherke.

http://rootshell.be/~feherke/

 

[feherke]

Hi

Oops. DOS times was a long time ago. So :
- change the slashes ( / ) to backslashes ( \ ) in my above code
- if you type that [tt]for[/tt] command directly in command line, use single percent sign ( % ) only, double is for inside a batch files

Feherke.

http://rootshell.be/~feherke/