Transposing X and Y axis of CSV data

Shell Programming and Scripting
1

Hello list,

I have a source CSV data file as follows:

PC_NAME,MS11-040,MS11-039,MS11-038,MS11-035
abc123,Not Applicable,Not Applicable,Not Applicable,Not Applicable
abc987,Not Applicable,Not Applicable,Not Applicable,Not Applicable
tnt999,Not Applicable,Not Applicable,Applicable,Not Applicable
terminal,Installed,Installed,Installed,Installed

One thing to note is every time a new patch comes out, new field(s) of data will be appended to the source data field. And I am going to have to account for this.

What I am trying to achieve is reformat the data so that I have three fields:

<Patch_name>,<PC_Name>,<Applicable/Not/Installed>
MS11-040,abc123, Not Applicable
MS11-040,abc987, Not Applicable
MS11-040,tnt999, Not Applicable
MS11-040,terminal, Installed
etc...

Does anyone know of any python/perl/awk than could achieve this?

Any help would be appreciated.

Thanks

Land.

2

I think this might do it:

awk -F , '
    NR == 1 {    # capture list from the first line
        for( i = 2; i <= NF; i++ )
            pname[i-1] = $(i);
        next;
    }
    {        # apply each from first line to the current line
        for( i = 1; i <= length( pname ); i++ )
            printf( "%s,%s,%s\n", pname, $1, $(i+1) );
    }
' data-file-name |sort

This makes the assumption that there is only one set of data in the file.

1 Like
3

Fantastic, It works perfectly!
Thanks agama!