Re: AWK or otherway to convert transpose Columns to Rows

From: John L (jl_at_lammtarra.notthisbit.fslife.co.uk)
Date: 06/29/05 

Date: Wed, 29 Jun 2005 08:59:48 +0100

"Akhwashah" <akhwashah.@WARMmail.com> wrote in message news:d9tgv7$u9b$1@otis.netspace.net.au...
> HI All,
>
>
> I have a data file with roughly the following format :
>
> AAA,BBB,CCC,DDD,1234,5678,555,678....999
>
> basically a list of text fields, with several columns of data by month
>
> Which I would like converted to :
>
> AAA,BBB,CCC,DDD,JAN,1234
> AAA,BBB,CCC,DDD,FEB,5678
> AAA,BBB,CCC,DDD,MAR,555
> AAA,BBB,CCC,DDD,MAR,678
> :
> AAA,BBB,CCC,DDD,DEC,999
>
> I have been able to get it happening very manually by using multiple print
> statments in an awk program file, but I would like to make it more dynamic,
> as the columns may represent different months at any point in time, so i was
> hoping to somehow provide an argument. ie data could be for JAN-DEC or
> AUG-DEC..
>

There is an awk newsgroup, news:comp.lang.awk

#!/bin/sh

# process options and arguments, for now we'll just do:
FIRSTMONTH=$1
# and check that this is a number between 1 and 12

awk -vFIRST=${FIRSTMONTH} '
# sets awk variable FIRST from shell variable $FIRSTMONTH
        BEGIN {
                FS = OFS = ","
                split("Jan Feb Mar Apr May Jun ...", Monthnames, " ")
        }

        {
                for (i = 1; i <= 12; i++)
                {
                        month = i - 1 + FIRST
                        print $1, $2, $3, $4, Monthnames[month], $(i + 4)
                }
        }
'

and you can see how this can be extended if the last month
also varies. 

-- 
John.

Relevant Pages

  • Shell(AWK) Sheet
    ... column5 and column7 of a data file or output of any ... Syntax of running an awk program ... list all the files whose owner is not "oracle ... Replace first column as "ORACLE" in a data file ...
    (comp.unix.shell)
  • Re: general compression with awk
    ... William James wrote: ... >>my problem is to compress a long text file with recurrent patterns. ... >>experimented with awk but could not find a satisfying solution. ... then on my actual data file. ...
    (comp.lang.awk)
  • Re: How to use DTS to create a scenario like Word - Mailmerge kind of functionality
    ... If AWK works, ... > not a typical formatted document template and a data file which I can ... > I am currently doing this on UNIX using awk which is a powerful UNIX ... > variable value in the static template. ...
    (microsoft.public.sqlserver.dts)
  • Re: extracting text in bourne shell
    ... > I want to write a script that exract text from data file i.e ... > % cat data ... > how to prgramme in awk, so if suggesting me the solution in awk please ... Lazlo's Chinese Relativity Axiom: ...
    (comp.unix.shell)
  • Re: Using dates during mail merge
    ... The other thing that might be causing problems here is that, regardless of how the DATE field nested in the ASK field is formatted, the user will need to enter the date in the format set up in their regional options (or any other format where the month and day are not ambiguous. ... I was however remiss in not changing the field name from that in my data file as this added some unwelcome confusion:( ... You included 'MyDate' and 'Date2' in your ...
    (microsoft.public.word.mailmerge.fields)