mjhjmtu

I have a large dataset, which shows how variable y changes as a function of both space (x) and time (t). There are n columns, each representing one time step. They are tab delimited.

note: there are no headers in the actual text file, they are just added here for explanation. Nor should there be headers in the output.

x y(0) y(1) y(2) y(3) ... y(n)
1 4 4.5 5 5.5 ... 100
2 5 5.5 6 6.5 ... 101
3 7 8 9 10 ... 102
4 10 12 14 16 ... 103

I need to reorganise my file so that I have only 3 columns; t, x, y, which should be sorted in that order, as below.

0 1 4
0 2 5
0 3 7
0 4 10
1 1 4.5
1 2 5.5
1 3 8
1 4 12
2 1 5
2 2 6
2 3 9
2 4 14
3 1 5.5
3 2 6.5
3 3 10
3 4 16
etc

Any help much appreciated. I feel like this should be possible using awk, but any solution is fine.

GNU awk solution:

awk '
 k=NR; x[k]=$1;
 for (i=2; i<=NF; i++) 
 t[i-1][k]=$i 
 
 END
 for (i in t) 
 for (j in t[i]) 
 print i-1, x[j], t[i][j] 
 ' file

• 
  k=NR - crucial key reflecting function y axis value (NR - record number)


• 
  x[k]=$1 - capture value for axis x
  


• 
  for (i=2; i<=NF; i++) - iterating though fields starting from the 2nd
  
  
  • 
    t[i-1][k]=$i - fill up the time axis array t with function y values
  
  

The output:

0 1 4
0 2 5
0 3 7
0 4 10
1 1 4.5
1 2 5.5
1 3 8
1 4 12
2 1 5
2 2 6
2 3 9
2 4 14
3 1 5.5
3 2 6.5
3 3 10
3 4 16
...

If you don't mind looping over the input n times:

n=4 ### your N here
for((t=0; t <= n)); t++))
do 
 awk -F$'t' -v t=$t 'print t, $1, $(t+2)' < input
done > output

Alternative GNU datamash + awk solution:

datamash -W transpose <filename 
| awk 'NR==1 for(i=1; i<=NF; i++) x[i]=$i 
 NR > 1
 for (i=1; i<=NF; i++) print NR-2, x[i], $i 
 '

The output:

0 1 4
0 2 5
0 3 7
0 4 10
1 1 4.5
1 2 5.5
1 3 8
1 4 12
2 1 5
2 2 6
2 3 9
2 4 14
3 1 5.5
3 2 6.5
3 3 10
3 4 16