Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Data transformation

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Data transformation
# 1  
Old 03-24-2015
Data transformation
I do have an input text file of the following format with 1000's of lines

input file:
Code:
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

Code:
      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  
Old 03-24-2015
something along these lines: awk -f kanja.awk myFile where kanja.awk is:
Code:
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[i], OFS a[orderA[i]]
}

Last edited by vgersh99; 03-24-2015 at 05:18 PM..
These 3 Users Gave Thanks to vgersh99 For This Post:
Kanja yifangt
# 3  
Old 03-24-2015
Code:
$
$
$ 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
$
$
$

These 2 Users Gave Thanks to durden_tyler For This Post:
Kanja yifangt
# 4  
Old 03-24-2015
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:
Code:
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?
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Row to Column transformation

Hello Experts, I need to transform rows into column using awk. I tried few things but failed to obtain desired output, as I'm fairly new to awk. i/p file 100, READ, 12 100, WRITE, 8 100, SEEK, 1 142, READ, 2 142, WRITE, 34 142, SEEK, 3 O/p Needed PROC_ID 100 142 READ 12 ... (2 Replies)
Discussion started by: sybadm
2 Replies

2. Shell Programming and Scripting

Clipboard transformation scripting

Hello all, I've done a bit of clipboard transformation scripting using xclip before, piping contents with " xclip -o -selection clipboard " to grep, sed, awk, then back into the clipboard with " xclip -i -selection clipboard " ... but I am not a fantastically skilled user of either of the three... (4 Replies)
Discussion started by: la2ar0
4 Replies

3. Shell Programming and Scripting

XML file transformation

Hi all, I have to transform a XML file like this: <?xml version="1.0"?> <vocabulary> <voc_id>102</voc_id> <name>Vocabulary Name</name> <description>Voc description</description> <relations>3</relations> <hierarchy>5</hierarchy> <word> <word_id>1</word_id> ... (1 Reply)
Discussion started by: aLittleBeat
1 Replies

4. UNIX for Advanced & Expert Users

Need help in xslt transformation

Hi I have one input xml file <param name="EXTR_COL" valueDesc="AUTHD_RFLL" value="rx.AUTHD_RFLL" /> There is a mapping parameters in Database. if EXTR_COL is present in input XML then it is mapped to fieldlist. so the o/p XML looks like <fieldlist> <datasource... (1 Reply)
Discussion started by: srinu19
1 Replies

5. Shell Programming and Scripting

xslt transformation through Unix

Hi .. I have one input XML and I want to convert into another XML using parameter mapping through Database through Unix shell script. But I dont have idea how to do that. And how can I create xsl sheet if mapping is through database tables. Please help me on this. (1 Reply)
Discussion started by: srinu19
1 Replies

6. Shell Programming and Scripting

XML to csv transformation

Hi, I want to write a perl script. Which should accept the xml file, one xsl file and the loaction. The perl script should process the xml file using the xsl file and puts the out put in specified location. For example: My.perl is perls cript. my.xml is like this <?xml version="1.0"... (2 Replies)
Discussion started by: siba.s.nayak
2 Replies

7. Shell Programming and Scripting

text transformation with sed or awk

Hi there, I'm trying to extract automatically opening hours from a website. The page displaying the schedules is http://www.natureetdecouvertes.com/pages/gener/view_FO_STORE_corgen.asp?mag_cod=xxx with xxx going from 101 to 174 I managed to get the following output : le lundi de 10.30 à... (4 Replies)
Discussion started by: chebarbudo
4 Replies

8. Shell Programming and Scripting

file name transformation

I've got a multitude of text data files that carry exactly the same kind of data. Unfortunately some of them have a different filename format some are: 'category'_'month'-'year'_act.txt an example being: daf_Apr-1961_act.txt and some are: 'category'_ 'year'-'month'_act.txt an... (16 Replies)
Discussion started by: vrms
16 Replies

9. UNIX for Dummies Questions & Answers

Transformation capital letter

:confused: Hye everybody i would like to know if exist a internet site where i can founs some interesting shell script very usefull I need to transform hundreds names of files escribed in CAPITAL letter in minuscule letter do oyu know a mean o do that that thanks to a script or a shell... (1 Reply)
Discussion started by: Dark Angel
1 Replies
Login or Register to Ask a Question