Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Complex transpose awk script

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Complex transpose awk script
# 8  
Old 01-18-2013
@Jotne: Ooh, got you, you were talking char positions... Yes, that should work.
@Ophiuchus: use old END statement and put in a line before:
Code:
     /^NONE/   {getline; rec=rec ",no values"}

Be aware that all of this depends heavily on the structure of your file, i.e. the sequence of keywords and lines. Should this not be stable/reliable, a far more sophisticated code would be necessary...
# 9  
Old 01-19-2013
Hello RudiC,

Thaank you. I've changed it. Still appear 3 more commas but is not a big deal.

I think I can remove them.

Many thanks for the help.

It would have been good to see the proposal of solution by jotne, to learn other method.

Thanks anyway for the comments.

Regards
# 10  
Old 01-19-2013
The three commas are due to the fact that you wanted all three OCTDP TCTDP GPRSTDP fields listed even when empty. No need to remove them externally, can be done within the awk code. BUT - it would have been nice had the specification been complete from the beginning...
Try:
Code:
awk 'BEGIN {split("OCTDP TCTDP GPRSTDP",TDPAr);
            print "MSISDN,OCTDP,TCTDP,GPRSTDP,GCSO,MCSO,SSLO,GC2SO,MC2SO,TIF,GPRSSO,OSMSSO,GC3SO,MC3SO,GC4SO,MC4SO,MMSO"}
     /^MSISDN/   {getline; rec=$0}
     /^TDPTYPE/  {noflds=0
          while (1)
                     {getline; if ($0 ~ /^$/) break
              OFS="|"; tmp=$1; $1=""; sub("\|",""); tmpAr[tmp]=$0; noflds=3 ;
                     }
                     for (i=1; i<=noflds; i++) rec=rec","tmpAr[TDPAr[i]]; delete tmpAr
                 }
     /^(GCSO|GC3SO|MMSO)/ {
                      getline; OFS=","; $1=$1; rec=rec","$0}
     /^NONE/   {getline; rec=rec ",no values"}
     /END/       {print rec; rec=""}
    ' file
MSISDN,OCTDP,TCTDP,GPRSTDP,GCSO,MCSO,SSLO,GC2SO,MC2SO,TIF,GPRSSO,OSMSSO,GC3SO,MC3SO,GC4SO,MC4SO,MMSO
20385503286,2|15|2038759581|0|2,18|15|2038759581|1|2|Y,,0,5,0,1,2,0,1,0,1,0,1,1,1
20386671719,2|15|2038759581|0|2|7,18|15|2038759594|1|2|Y,18|15|20387592403|0|3,0,5,0,1,2,0,1,0,1,2,1,2,0
20387323200,,18|15|2038759581|1|2|Y,18|15|20387592403|0|3,1,3,0,1,2,0,1,1,0,2,2,2,2
20385503289,no values

# 11  
Old 01-19-2013
Hello RudiC,

Thanks for your answer! It works, and I understan, I've especified the condition when a number don't have values in post #3. Thank youu!

Last question:

I have your awk script in a file Script.sh and works, but I have inmediately below your code another awk script that uses the output of your script as input,
but when I execute the script as . Script.sh input.txt only is executed the first script but if I execute both scripts individually work fine.

I have like this within Script.sh:
Code:
Input=input.txt
Output=output.txt

awk 'BEGIN {split("OCTDP TCTDP GPRSTDP",TDPAr);
            print "MSISDN,OCTDP,TCTDP,GPRSTDP,GCSO,MCSO,SSLO,GC2SO,MC2SO,TIF,GPRSSO,OSMSSO,GC3SO,MC3SO,GC4SO,MC4SO,MMSO"}
     /^MSISDN/   {getline; rec=$0}
     /^TDPTYPE/  {noflds=0
          while (1)
                     {getline; if ($0 ~ /^$/) break
              OFS="|"; tmp=$1; $1=""; sub("\|",""); tmpAr[tmp]=$0; noflds=3 ;
                     }
                     for (i=1; i<=noflds; i++) rec=rec","tmpAr[TDPAr[i]]; delete tmpAr
                 }
     /^(GCSO|GC3SO|MMSO)/ {
                      getline; OFS=","; $1=$1; rec=rec","$0}
     /^NONE/   {getline; rec=rec ",no values"}
     /END/       {print rec; rec=""}
    ' $Input > $Output
    
awk '...' $Output > Output.csv  # This second script is not executed.

Maybe you can suggest me or see what could happen.

Thanks in advance.
# 12  
Old 01-19-2013
I can't see a reason why the second script should not be executed. Maybe it is, and its output is not what you expect?
Aside from piping it from the first one, there certainly will be opportunities to fulfill your requirements in one single awk script.
Why don't you post the second awk script, or, the output you expect?
# 13  
Old 01-21-2013
Hello RudiC,

Simply it seems the 2nd script doesnt execute.

The 2nd awk script verifies if the fields are different to the "correct values" that they should have configured, so using the same sample, the output of your script gives:
Code:
MSISDN,OCTDP,TCTDP,GPRSTDP,GCSO,MCSO,SSLO,GC2SO,MC2SO,TIF,GPRSSO,OSMSSO,GC3SO,MC3SO,GC4SO,MC4SO,MMSO
20385503286,|2|15|2038759581|0|2,|18|15|2038759581|1|2|Y,,0,5,0,1,2,0,1,0,1,0,1,1,1
20386671719,|2|15|2038759581|0|2|7,|18|15|2038759594|1|2|Y,|18|15|20387592403|0|3,0,5,0,1,2,0,1,0,1,2,1,2,0
20387323200,,|18|15|2038759581|1|2|Y,|18|15|20387592403|0|3,1,3,0,1,2,0,1,1,0,2,2,2,2
20385503289,No Values

well, this should be the input for the next awk script that will compare concatenating all fields from 2 to last field in order to determine which one is different based on the parameters in red.
Code:
awk '{  x = substr($0, index($0, ","))
        gsub(/,/, "", x)
        if(x !~ /2\|15\|2038759581\|0\|2\|7,18\|15\|2038759594\|1\|2\|Y,18\|15\|20387592403\|0\|30501201012120/) print $0, "Different"
}' OFS=","    Input1 > output1.csv

The output I have so far is (runnig one awk script after another):
Code:
MSISDN,OCTDP,TCTDP,GPRSTDP,GCSO,MCSO,SSLO,GC2SO,MC2SO,TIF,GPRSSO,OSMSSO,GC3SO,MC3SO,GC4SO,MC4SO,MMSO,STATE
20385503286,2|15|2038759581|0|2,18|15|2038759581|1|2|Y,,0,5,0,1,2,0,1,0,1,0,1,1,1,Different
20386671719,2|15|2038759581|0|2|7,18|15|2038759594|1|2|Y,18|15|20387592403|0|3,0,5,0,1,2,0,1,0,1,2,1,2,0,
20387323200,,18|15|2038759594|1|2|Y,18|15|20387592403|0|3,1,3,0,1,2,0,1,1,0,2,2,2,2,Different
20385503289,No_values,,,,,,,,,,,,,,,,,Different

This 2nd awk code works saying me which number has different "something" in its values, but doesn't especify which paarmeter/parameters is/are different.
If the number has something different in its parameters, I would like the same output shown above, but adding the suffix "_I" for the parameter that is different as below:
Code:
MSISDN,OCTDP,TCTDP,GPRSTDP,GCSO,MCSO,SSLO,GC2SO,MC2SO,TIF,GPRSSO,OSMSSO,GC3SO,MC3SO,GC4SO,MC4SO,MMSO,STATE
20385503286,2|15|2038759581|0|2_I,18|15|2038759581|1|2|Y_I,_I,0,5,0,1,2,0,1,0,1,0_I,1,1_I,1_I,Different
20386671719,2|15|2038759581|0|2|7,18|15|2038759594|1|2|Y,18|15|20387592403|0|3,0,5,0,1,2,0,1,0,1,2,1,2,0,
20387323200,_I,18|15|2038759594|1|2|Y,18|15|20387592403|0|3,1_I,3_I,0,1,2,0,1,1_I,0_I,2,2_I,2,2_I,Different
20385503289,No_values,,,,,,,,,,,,,,,,

But, If adding the "_I" to the output becomes too much difficult, please only help me if it possible, to join the 2 awk scripts I have so far, in a single one Smilie.

Thanks in advance for any help.
Last edited by Ophiuchus; 01-21-2013 at 01:49 AM..
# 14  
Old 01-21-2013
I'd propose to put the line to be compared to into another file like:
Code:
$ cat tobe
20386671719,2|15|2038759581|0|2|7,18|15|2038759594|1|2|Y,18|15|20387592403|0|3,0,5,0,1,2,0,1,0,1,2,1,2,0

and then run this modified code against both files:
Code:
awk 'BEGIN {split("OCTDP TCTDP GPRSTDP",TDPAr);
            print "MSISDN,OCTDP,TCTDP,GPRSTDP,GCSO,MCSO,SSLO,GC2SO,MC2SO,TIF,GPRSSO,OSMSSO,GC3SO,MC3SO,GC4SO,MC4SO,MMSO,STAT"
     }

     NR==FNR     {cnt = split ($0, cmpAr, ","); next}

     /^MSISDN/   {getline; rec=$0}
     /^TDPTYPE/  {noflds=0
                  while (1)
                     {getline; if ($0 ~ /^$/) break
                      OFS="|"; tmp=$1; $1=""; sub("\|",""); tmpAr[tmp]=$0; noflds=3 ;
                     }
                  for (i=1; i<=noflds; i++) rec=rec","tmpAr[TDPAr[i]]
                 }
     /^(GCSO|GC3SO|MMSO)/ {
                  getline; OFS=","; $1=$1; rec=rec","$0}
     /^NONE/     {getline; rec=rec ",no values"}
     /END/       {split (rec, tmpAr, ","); rec=tmpAr[1]
                  for (i=2;i<=cnt;i++) {if (tmpAr[i] != cmpAr[i]) {tmpAr[i]=tmpAr[i]"_I"; dif=1} rec=rec","tmpAr[i]}
                  if (dif) rec=rec",different"
                  print rec; rec=""; delete tmpAr; dif=0}
    ' tobe file
MSISDN,OCTDP,TCTDP,GPRSTDP,GCSO,MCSO,SSLO,GC2SO,MC2SO,TIF,GPRSSO,OSMSSO,GC3SO,MC3SO,GC4SO,MC4SO,MMSO,STAT
20385503286,2|15|2038759581|0|2_I,18|15|2038759581|1|2|Y_I,_I,0,5,0,1,2,0,1,0,1,0_I,1,1_I,1_I,different
20386671719,2|15|2038759581|0|2|7,18|15|2038759594|1|2|Y,18|15|20387592403|0|3,0,5,0,1,2,0,1,0,1,2,1,2,0
20387323200,_I,18|15|2038759581|1|2|Y_I,18|15|20387592403|0|3,1_I,3_I,0,1,2,0,1,1_I,0_I,2,2_I,2,2_I,different
20385503289,no values_I,_I,_I,,_I,,_I,_I,,_I,,_I,_I,_I,_I,,different
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Transpose table with awk

I am trying to format the table below to the output input: cand week sub1 sub2 sub3 sub4 joe 1 94.19 70.99 43.93 60.14 joe 2 94.07 51.02 41.07 38.92 joe 3 26.24 30.95 44.56 67.67 joe 4 72.36 60.92 40.78 83.25 joe 5 51 70.01 44.66 82.22... (7 Replies)
Discussion started by: aydj
7 Replies

2. Shell Programming and Scripting

Transpose rows to columns complex

Input: IN,A,1 IN,B,3 IN,B,2 IN,C,7 BR,A,1 BR,A,5 BR,C,9 AR,C,9 Output: CNTRY,A,B,C IN,1,5,7 BR,6,0,9 AR,0,0,9 (7 Replies)
Discussion started by: unme
7 Replies

3. Shell Programming and Scripting

Transpose using awk

Hi Friends, Very urgent requirement please do needful ASAP.. Input: |1||1|1||1|3||3|2||2|4||4|2||2|3||3|NA||0|5||5|NA||0|4||4|3||3 output: |1||1 |1||1 |3||3 |2||2 |4||4 |2||2 |3||3 |NA||0 |5||5 (4 Replies)
Discussion started by: bharat1211
4 Replies

4. Shell Programming and Scripting

Transpose data as rows using awk

Hi I have below requirement, need help One file contains the meta data information and other file would have the data, match the column from file1 and with file2 and extract corresponding column value and display in another file File1: CUSTTYPECD COSTCENTER FNAME LNAME SERVICELVL ... (1 Reply)
Discussion started by: ravlapo
1 Replies

5. Shell Programming and Scripting

awk to transpose every 7 rows into columns

input: a1 a2 a3 a4 a5 a6 a7 b1 b2 b3 .. b7 .. z1 .. z7 (12 Replies)
Discussion started by: ux4me
12 Replies

6. Shell Programming and Scripting

Complex match of numbers between 2 files awk script

Hello to all, I hope some awk guru could help me. I have 2 input files: File1: Is the complete database File2: Contains some numbers which I want to compare File1: "NUMBERKEY","SERVICENAME","PARAMETERNAME","PARAMETERVALUE","ALTERNATENUMBERKEY"... (9 Replies)
Discussion started by: Ophiuchus
9 Replies

7. Shell Programming and Scripting

awk script (complex)

picked this up from another thread. echo 1st_file.csv; nawk -F, 'NR==FNR{a++;next} a{b++} END{for(i in b){if(b-1&&a!=b){print i";\t\t"b}else{print "NEW:"i";\t\t"b} } }' OFS=, 1st_file.csv *.csv | sort -r i need to use the above but with a slight modification.. 1.compare against 3 month... (25 Replies)
Discussion started by: slashbash
25 Replies

8. Shell Programming and Scripting

How can i transpose this rerult by using awk?

From>>> ATOM 1 ca 2 o 3 h 4 h 5 o dE/dx 0.2057422D-01 0.2463722D-01-0.1068047D-01-0.1495280D-01-0.3725362D-02 dE/dy -0.7179106D-02-0.1554542D-01 0.1016889D-01 0.3268502D-02-0.4888578D-01 dE/dz -0.5600872D-02 0.3110649D-01-0.4088230D-02-0.2295107D-01-0.2832048D-01 ATOM 6 h 7 h 8 o 9 h 10 h... (1 Reply)
Discussion started by: wanchem
1 Replies

9. Shell Programming and Scripting

Transpose using awk

I have a requirement to transpose the below xml which is in a text file on unix: <?xml version="1.0" ?> <REQUEST> <ID>XXX</ID> <TIMESTAMP>20090720062610</TIMESTAMP> <FLAG>Y</FLAG> <TO_FLAG>Y</TO_FLAG> </REQUEST> to <?xml version="1.0"... (13 Replies)
Discussion started by: new_ds_man
13 Replies

10. Shell Programming and Scripting

How to transpose data elements in awk

Hi, I have an input data file :- Test4599,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,2,0,2,2,Rain Test90,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,1,1,0,Not Rain etc.... I wanted to transpose these data to:-... (2 Replies)
Discussion started by: ahjiefreak
2 Replies
Login or Register to Ask a Question