# Column to Row base on first and second Column

**URL:** <https://community.unix.com/t/column-to-row-base-on-first-and-second-column/363254>\
**Category:** Shell Programming and Scripting\
**Created:** [October 27, 2016, 3:59pm UTC](https://community.unix.com/t/column-to-row-base-on-first-and-second-column/363254 "2016-10-27T15:59:45Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![pareshkp](https://community.unix.com/letter_avatar/pareshkp/32/6_22734026db12803ebb3e059622ac3534.png) [@pareshkp](https://community.unix.com/u/pareshkp)\
**Post date:** [October 27, 2016, 3:59pm UTC](https://community.unix.com/t/column-to-row-base-on-first-and-second-column/363254/1 "2016-10-27T15:59:45Z")

</div>

Input :-

Column to Row

```nohighlight
Time CT 1 2 3 
17:45 X 10 15 20
18:00 X 15 20 30 
18:15 X 10 10 10
18:30 X 5 5 5
17:45 Y 10 15 20
18:00 Y 15 20 30 
18:15 Y 10 10 10
18:30 Y 5 5 5

```

output:-

```nohighlight
Time P X Y  
17:45 1 10 10 
18:00 1 15 15 
18:15 1 10 10 
18:30 1 5 5  
17:45 2 15 15 
18:00 2 20 20 
18:15 2 10 10 
18:30 2 5 5  
17:45 3 15 15 
18:00 3 20 20 
18:15 3 10 10 
18:30 3 5 5  

```

I have tried below code :- but no luck

```nohighlight
perl -ane 'BEGIN{$lc=0;} if ($lc==0) {@r=@F;$lc++;} else {for $i (1 .. $#r) {print "$F[0]\t$r[$i]\t$F[$i]\n";}$lc=0;@r=();}' T2.csv

```

---

<div class="post-metadata">

**Author:** ![rdrtx1](https://community.unix.com/user_avatar/community.unix.com/rdrtx1/32/2721_2.png) [@rdrtx1](https://community.unix.com/u/rdrtx1)\
**Post date:** [October 27, 2016, 5:53pm UTC](https://community.unix.com/t/column-to-row-base-on-first-and-second-column/363254/2 "2016-10-27T17:53:32Z")

</div>

```nohighlight
awk '
NR==1 {r1c1=$1; r1c2="P"; for (i=3; i<=NF; i++) orows[orc++]=$i; next;}
{
 if (!times[$1]) times[tmc++]=$1; times[$1]=$1;
 if (!pcols[$2]) cols[cc++]=$2; pcols[$2]=$2;
 for (i=3; i<=NF; i++) odata[$1,$2,i-3]=$i;
}
END {
   printf "%-6s%-6s", r1c1, r1c2;
   for (i=0; i<cc; i++) printf "%-6s", cols;
   print "";
   for (j=0; j<orc; j++) {
      for (i=0; i<tmc; i++) {
         printf "%-6s%-6s", times, orows[j];
         for (k=0; k<cc; k++) printf "%-6s", odata[times,cols[k],j];
         print ""
      }
   }
}
' infile

```

Verify point 3 row/cols in posted output.

---

<div class="post-metadata">

**Author:** ![pareshkp](https://community.unix.com/letter_avatar/pareshkp/32/6_22734026db12803ebb3e059622ac3534.png) [@pareshkp](https://community.unix.com/u/pareshkp)\
**Post date:** [November 10, 2016, 12:06pm UTC](https://community.unix.com/t/column-to-row-base-on-first-and-second-column/363254/3 "2016-11-10T12:06:24Z")

</div>

Thanks