How to swap the column to row?

Shell Programming and Scripting
1

Hi,
I am new to shell scripting and have one complex req like below. I have one file like below and want oupt put like the 2nd file.

Time|Measures|Product|store1|store2|store3|store4|store5|--
Week Ending 06-15-14|Dollar Sales|BACARDI PLUS|40|30|20|23|45|
Week Ending 06-22-14|Unit Sales|BACARDI PLUS|50|60|70|80|90|
Week Ending 06-21-14|volume Sales|BACARDI PLUS|33|44|55|66|77|

NB: Time column is having more other values,product can be more also .Measures are only these 3 values and stores are more store1,store2,store3....like this

O/P file

Time|product|storeDollar Sales|Unit Sales|volume Sales|
Week Ending 06-15-14|BACARDI PLUS|store1|40||
Week Ending 06-15-14|BACARDI PLUS|store2|30||
Week Ending 06-15-14|BACARDI PLUS|store3|20||
Week Ending 06-15-14|BACARDI PLUS|store4|23||
Week Ending 06-15-14|BACARDI PLUS|store5|45||
Week Ending 06-22-14|BACARDI PLUS|store1||50|
Week Ending 06-22-14|BACARDI PLUS|store2||60|
Week Ending 06-22-14|BACARDI PLUS|store3||70|
Week Ending 06-22-14|BACARDI PLUS|store4||80|
Week Ending 06-22-14|BACARDI PLUS|store5||90|
Week Ending 06-21-14|BACARDI PLUS|store1|||33
Week Ending 06-21-14|BACARDI PLUS|store2|||44
Week Ending 06-21-14|BACARDI PLUS|store3|||55
Week Ending 06-21-14|BACARDI PLUS|store4|||66
Week Ending 06-21-14|BACARDI PLUS|store4|||77

Can some body help me regarding this?

2

Welcome jaggy,

I have a few to questions pose in response first:-

  • Is this homework/assignment? There are specific forums for these.
  • What have you tried so far?
  • What output/errors do you get?
  • What OS and version are you using?
  • What are your preferred tools? (C, shell, perl, awk, etc.)
  • What logical process have you considered? (to help steer us to follow what you are trying to achieve)

Most importantly, What have you tried so far?

There are probably many ways to achieve most tasks, so giving us an idea of your style and thoughts will help us guide you to an answer most suitable to you so you can adjust it to suit your needs in future.

We're all here to learn and getting the relevant information will help us all.

Regards,
Robin

3

Hi Robin,
Thanks for looking into it.

I am using Linux server to achieve this and trying with shell. I tried split function along with awk also. But I am not getting desired out put.

I tried to traverse each record with something like (i=1;i<=n;i++) {$2=s;print}} and when it will encounter the 3rd pipe it will sort the data afterwards in columnar fashion.

Something like below But not actual

awk '{n=split($3,s,"|");for (i=1;i<=n;i++) {$3=s;print}}' test.text
4

Your spec is a bit inconsistent, a delimiter is missing between "store" and "Dollar" in the header; and also the delimiter count at line end differs between header and data lines. However, try

awk -F\| 'NR==1         {for (i=4; i<=NF; i++) ST=$i
                         TTL="Time|product|store|Dollar Sales|Unit Sales|volume Sales|"
                         split (TTL, SEG)
                         print TTL
                         next
                        }
                        {for (j=4; j<7; j++) if ($2==SEG[j]) break;
                         for (i=4; i<=NF; i++)  {printf "%s|%s|%s|", $1, $3, ST
                                                 for (k=4; k<j; k++) printf "|"
                                                 printf "%s", $i
                                                 for (k=j; k<7; k++) printf "|"
                                                 printf "\n"
                                                }
                        }
        ' file
Time|product|store|Dollar Sales|Unit Sales|volume Sales|
Week Ending 06-15-14|BACARDI PLUS|store1|40|||
Week Ending 06-15-14|BACARDI PLUS|store2|30|||
Week Ending 06-15-14|BACARDI PLUS|store3|20|||
Week Ending 06-15-14|BACARDI PLUS|store4|23|||
Week Ending 06-15-14|BACARDI PLUS|store5|45|||
Week Ending 06-15-14|BACARDI PLUS|--||||
Week Ending 06-22-14|BACARDI PLUS|store1||50||
Week Ending 06-22-14|BACARDI PLUS|store2||60||
Week Ending 06-22-14|BACARDI PLUS|store3||70||
Week Ending 06-22-14|BACARDI PLUS|store4||80||
Week Ending 06-22-14|BACARDI PLUS|store5||90||
Week Ending 06-22-14|BACARDI PLUS|--||||
Week Ending 06-21-14|BACARDI PLUS|store1|||33|
Week Ending 06-21-14|BACARDI PLUS|store2|||44|
Week Ending 06-21-14|BACARDI PLUS|store3|||55|
Week Ending 06-21-14|BACARDI PLUS|store4|||66|
Week Ending 06-21-14|BACARDI PLUS|store5|||77|
Week Ending 06-21-14|BACARDI PLUS|--||||
1 Like