Data transformation

Shell Programming and Scripting
1

I do have an input text file of the following format with 1000's of lines

input file:

3386(11:11,Ani:0,Bri:1,ch:1,Jwe:0,Jor:0,LP:0,Lo:0,NS:1,al:1,bo:0,boy:0,bru:0,sh:0,cor:1,dum:0,ery:0,mac:0,mic:0)
3387(11:11,Ani:1,Bri:0,ch:1,Jwe:2,Jor:0,LP:0,Lo:0,NS:3,al:1,bo:0,boy:0,bru:0,sh:0,cor:4,dum:0,ery:1,mac:0,mic:0)
386(11:11,Ani:1,Bri:1,ch:1,Jwe:4,Jor:0,LP:0,Lo:3,NS:1,al:1,bo:7,boy:0,bru:9,sh:0,cor:1,dum:0,ery:0,mac:0,mic:0)
.....
.......
1000's lines

Would like to transform this data into a tab delimiited file of the following format:
Basically the first number on each file will be a column and the rows (horizontal) content on the input file will be the row. The number after each of the : will be value in the matrix

Desired output

      3386  3387 386
Ani  0       1       1
Bri   1       0       1
ch    1       1       1
Jwe  0       2       4
Jor   0       0       0
LP    0       0       0
Lo    0       0       3
NS   1       3       1
al     1       1       1
bo    0       0       7
boy  0       0       0
bru  0        0      9
sh   0        0       0
cor  1        4       1
dum 0        0       0
ery   0        1       0
mac  0        0       0
mic   0        0       0

It would be great if I could get some help in awk or another language to do this data transformation

2

something along these lines: awk -f kanja.awk myFile where kanja.awk is:

BEGIN {
  FS="[():,]"
  OFS="\t"
}
{
  idL=(!idL)?$1:idL OFS $1
  for(i=4;i<=NF-1;i=i+2) {
    if (FNR==1) orderA[++j]=$i
    a[$i]=(!($i in a))? $(i+1) : a[$i] OFS $(i+1)
  }
}
END {
  print OFS,idL
  for(i=1;i in orderA;i++ )
     print orderA, OFS a[orderA]
}
2 Likes
3 
$
$
$ cat data.txt
3386(11:11,Ani:0,Bri:1,ch:1,Jwe:0,Jor:0,LP:0,Lo:0,NS:1,al:1,bo:0,boy:0,bru:0,sh:0,cor:1,dum:0,ery:0,mac:0,mic:0)
3387(11:11,Ani:1,Bri:0,ch:1,Jwe:2,Jor:0,LP:0,Lo:0,NS:3,al:1,bo:0,boy:0,bru:0,sh:0,cor:4,dum:0,ery:1,mac:0,mic:0)
386(11:11,Ani:1,Bri:1,ch:1,Jwe:4,Jor:0,LP:0,Lo:3,NS:1,al:1,bo:7,boy:0,bru:9,sh:0,cor:1,dum:0,ery:0,mac:0,mic:0)
$
$ perl -lne '/^(\d+).*?,(.*)\)$/;
             push @col, $1;
             @x = split(/,/, $2);
             @row = map { ($y = $_) =~ s/:\d+$//; $y } @x if $.==1;
             push @data, [ map { s/.*://; $_ } @x ];
             END {
                 print "\t", join("\t", @col);
                 foreach $i (0..$#row) {
                     print $row[$i],"\t",join("\t", map{$_->[$i]} @data)
                 }
             }' data.txt
        3386    3387    386
Ani     0       1       1
Bri     1       0       1
ch      1       1       1
Jwe     0       2       4
Jor     0       0       0
LP      0       0       0
Lo      0       0       3
NS      1       3       1
al      1       1       1
bo      0       0       7
boy     0       0       0
bru     0       0       9
sh      0       0       0
cor     1       4       1
dum     0       0       0
ery     0       1       0
mac     0       0       0
mic     0       0       0
$
$
$
2 Likes
4

Note that on many systems you may not be able to do much with your output file. All of the text processing tools you're used to using on files are only guaranteed to work on text files. And, by definition, text files can't contain more than LINE_MAX bytes per line (including the terminating <newline> character). You can find the value of LINE_MAX on your system using the command:

getconf LINE_MAX

but 2048 is a common value for this limit.

UNIX systems are frequently more concerned about LINE_MAX than Linux systems, but even when the text processing utilities can handle "unlimited" line lengths, visualizing lines that long is hard for humans. What do you intend to do with the output produced by this script?

What operating system are you using?