1

I have a big data.txt file like this:

6712,04444
6712,02344
6712,02220
6712,02233
5322,00040
5322,02230
5322,42002
4444,03342
4444,03323
11,42023
11,00000
11,23443
11,23232

and then I want to make a data.txt like this from the original one:

col6712  col5322  col4444   col11
04444     00040    03342    42023
02344     02230    03323    00000
02220     42002             23443 
02233                       23232

In fact I want the first column of the original file be the column number and the inside of each row in original file be underneath of their related column. is there any command in Linux that I could reach my goal?

  • Let me make a quick suggestion: However you do it, don't make a text file. Make a CSV. Then you can import that into a spreadsheet, plot it, etc. – blujay Sep 12 '15 at 0:48
  • 1
    Is the column-wise tabulation imperative? it is almost certainly easier to arrange it row-wise e.g. awk -F, '{a[$1] = a[$1]? a[$1]"\t"$2 : $2} END {for (i in a) print "row"i"\t"a[i]}' data.txt – steeldriver Sep 12 '15 at 1:00
  • In your case I would use perl or python for the task. shell and awk aren't enough strong to handle an indexed list of lists. But in python or in perl would be these shorter as 10 lines. – peterh Sep 12 '15 at 12:23
  • @steeldriver: where should I put my original file name and where should I specify my newfile name in your script? – zara Sep 13 '15 at 2:35
  • @peterh: hi petreh, do you have any script in perl that I could run it t get what I want? – zara Sep 13 '15 at 2:36
1

With gnu datamash:

datamash -t, -s -g 1 collapse 2 <data.txt | tr , \\t | datamash --no-strict --filler '' transpose

Since datamash requires sorted input, the output will be:

11      4444    5322    6712
42023   03342   00040   04444
00000   03323   02230   02344
23443           42002   02220
23232                   02233

How it works:
The first datamash sorts and groups the , delimited input by 1st field, collapsing (into comma separated list) the values in 2nd column that correspond to a unique value in 1st column. Then all commas are translated to \\tabs and the second datamash transposes the rows/columns, allow lines with varying number of fields via --no-strict and filling the missing values with '' (empty field).


As a side note, the lines in your sample input are already grouped by first field and sorted in reverse order so, to actually get the same order, you could re-sort before piping to the second datamash e.g.

datamash -t, -s -g 1 collapse 2 <data.txt | tr , \\t | \
sort -k1nr | datamash --no-strict --filler '' transpose

produces the desired output:

6712    5322    4444    11
04444   00040   03342   42023
02344   02230   03323   00000
02220   42002           23443
02233                   23232

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.