4

A smaller version of many data files I have looks like this:

0 0 0
0.05 9.6877884e-06 0.0024898597
0.1 4.2838688e-05 0.0049595502
0.15 0.00016929444 0.0074092494
0.2 0.00036426881 0.009839138
0.25 0.00055234582 0.012249394
0.3 0.00077448576 0.014640196
0.35 0.00082546537 0.017011717
0.4 0.0012371619 0.019364133
0.45 0.0013286382 0.02169761

I would like to end up with something like the following where the first column is repeated and the entries for the 2nd column comprise columns 2 & above from my original file.

0 0
0.05 9.6877884e-06
0.1 4.2838688e-05
0.15 0.00016929444
0.2 0.00036426881
0.25 0.00055234582
0.3 0.00077448576
0.35 0.00082546537
0.4 0.0012371619
0.45 0.0013286382
0 0
0.05 0.0024898597
0.1 0.0049595502
0.15 0.0074092494
0.2 0.009839138
0.25 0.012249394
0.3 0.014640196
0.35 0.017011717
0.4 0.019364133
0.45 0.021697611

I can generate it using awk '{print $1 " " $2}' data > tmp followed by awk '{print $1 " " $3}' data >> tmp but this becomes very tedious for the number of columns I have.

Is there a smarter way of achieving what I need?

EDIT

I would like a solution for an arbitrary number of columns, n. The correct order of columns to rows is essential for my needs. Thus column 3 of input should be "moved" to underneath column 2 of input, column 4 underneath 2 and 3 etc. with column 1 being stacked underneath repeatedly. The first column should be in ascending numerical order for however many rows each column has i.e. 0, 0.05, ..., 0.45, 0, 0.05,..., 0.45, 0,0.05,...,0.45,etc.

2

Awk

this awk script will work on an arbitrary number of columns > 2 and order of appearance will be preserved as across then down with no assumptions made about what the columns are (i.e. doesn't matter if they are numeric or not, sorted or not, etc):

{
    for (i = 2; i <= NF; i++) {
        a[j + i] = $1 " " $i
    }
    j += (i - 1);
}
END {
    OutNR = NR * NF;
    for (i = 2; i <= NF; i++) {
        for (j = 0; j < OutNR; j += NF) { 
            print a[j + i];
        }
    }
}

Given:

0 0 0 0.2340
0.05 9.6877884e-06 0.0024898597 0.2341
0.1 4.2838688e-05 0.0049595502 0.2342
0.15 0.00016929444 0.0074092494 0.2343
0.2 0.00036426881 0.009839138 0.2344
0.25 0.00055234582 0.012249394 0.2345
0.3 0.00077448576 0.014640196 0.2346
0.35 0.00082546537 0.017011717 0.2347
0.4 0.0012371619 0.019364133 0.2348
0.45 0.0013286382 0.02169761 0.2349

Order by column (2..n) then by line:

0 0
0.05 9.6877884e-06
0.1 4.2838688e-05
0.15 0.00016929444
0.2 0.00036426881
0.25 0.00055234582
0.3 0.00077448576
0.35 0.00082546537
0.4 0.0012371619
0.45 0.0013286382
0 0
0.05 0.0024898597
0.1 0.0049595502
0.15 0.0074092494
0.2 0.009839138
0.25 0.012249394
0.3 0.014640196
0.35 0.017011717
0.4 0.019364133
0.45 0.02169761
0 0.2340
0.05 0.2341
0.1 0.2342
0.15 0.2343
0.2 0.2344
0.25 0.2345
0.3 0.2346
0.35 0.2347
0.4 0.2348
0.45 0.2349

R

Although most people don't think of R for text processing, in this case, it's actually a bit more straight-forward, although all of the option setting makes it appear to be more complex than it really is. The essence of this solution is to simply rbind() multiple cbind():

d.in <- read.table(file = commandArgs(trailingOnly = T)[1]
                    , colClasses = "character");
d.out<-data.frame();
for (i in 2:length(d.in)) {
    d.out <- rbind(d.out, cbind(d.in[,1], d.in[,i]));
}
write.table(d.out, row.names = F, col.names = F, quote = F);

Then, just:

$ Rscript script.R data.txt
0 0
0.05 9.6877884e-06
0.1 4.2838688e-05
0.15 0.00016929444
0.2 0.00036426881
0.25 0.00055234582
0.3 0.00077448576
0.35 0.00082546537
0.4 0.0012371619
0.45 0.0013286382
0 0
0.05 0.0024898597
0.1 0.0049595502
0.15 0.0074092494
0.2 0.009839138
0.25 0.012249394
0.3 0.014640196
0.35 0.017011717
0.4 0.019364133
0.45 0.02169761
0 0.2340
0.05 0.2341
0.1 0.2342
0.15 0.2343
0.2 0.2344
0.25 0.2345
0.3 0.2346
0.35 0.2347
0.4 0.2348
0.45 0.2349
  • I'm finding that the output order isn't ascending in a human way. For example in the first column I find consecutive rows starting 10.84,10.9,1.05,10.95 and indeed the first few rows seem to start 0, 0.44999, 4.94999, 49.95, 50. I'm using GNU Awk 4.0.1 – nstjhp Apr 14 '14 at 15:59
  • @nstjhp exactly how do you want the output ordered? Your example implies by column then by line as noted in my edit. Oh, I see the problem appearing in the 10<sup>th</sup> line. You need a longer sample :P – user61786 Apr 14 '14 at 16:06
  • I would like column 1 to be in numerical order 0 to 0.45 (in this example) for each input column, so the final output of column 1 goes 0, 0.05,..., 0.45, 0, 0.05,....,0.45, 0, 0.05,...,0.45 etc. Does it make sense? Note how your edit goes 0, 0.45, 0.05 in the 1st column - for me this is incorrect. – nstjhp Apr 14 '14 at 16:14
  • @nstjhp ok, it now stacks by columns – user61786 Apr 14 '14 at 16:38
  • Thanks - I'm trying to understand what you did, is AWK setting j=0 by default in line 3? Also this means a[1] doesn't contain anything right? – nstjhp Apr 14 '14 at 18:04
0

if the order doesn't matter, you can simply use this:

awk '{for(i=2;i<=NF;i++)print $1,$i}' file
  • This is true and elegant. I was over-thinking the problem, but I've added a way to preserve order. – user61786 Apr 14 '14 at 15:33
  • The correct order is necessary for my needs unfortunately so this doesn't quite achieve the format I was after. – nstjhp Apr 14 '14 at 15:38
  • @nstjhp You should edit your question to include that requirement. Otherwise, people will continue to make that assumption. – user61786 Apr 14 '14 at 16:43
0

Here is an awk solution:

$ awk '{a[i++]=$1" "$3;print $1,$2}END{for(i=0;i<length(a);i++){print a[i]}}' file
0 0
0.05 9.6877884e-06
0.1 4.2838688e-05
0.15 0.00016929444
0.2 0.00036426881
0.25 0.00055234582
0.3 0.00077448576
0.35 0.00082546537
0.4 0.0012371619
0.45 0.0013286382
0 0
0.05 0.0024898597
0.1 0.0049595502
0.15 0.0074092494
0.2 0.009839138
0.25 0.012249394
0.3 0.014640196
0.35 0.017011717
0.4 0.019364133
0.45 0.02169761

Explanation

  • When processing file, we save $1 and $3 to array a with index from 0 to number of lines with each line. Then print $1 and $2.

  • In the end, we loop through array a, print each of its element (which is value $1 $3). The order is kept because we loop with index from 0 to length of array a again.

Updated

For arbitrary columns n, I use perl:

$ perl -anle '$h{$i++} = [@F[0..$#F]];
  END {
      for $j (1..$#F) {
          for (sort {$a<=>$b} keys %h) {
              print $h{$_}->[0]," ",$h{$_}->[$j]
          }
      }
  }' file
  • Looks good for a 3 column file - how can one generalise to arbitrary columns n? – nstjhp Apr 14 '14 at 15:01
  • Updated my answer! – cuonglm Apr 14 '14 at 17:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.