Column to row Transpose

Shell Programming and Scripting
1

Hi Folks,

Iam a kinda newbie to unix shell scripting, the scenario is i have a text file containing the following info

Charlie chicago 15
Charlie newyork 26
jonny chicago 14
jonny newyork 15
joe chicago 15
joe newyork 18

output should be

Name chicago   newyork       
Charlie   15   26
jonny   14   15
joe   15   18

Output is row to column transpose,chicago & newyork are fixed cities, Person name varies and both cities will be contiguos, if only 1 value per say that is only chicago is there then the value for newyork should be "N/A" in the resulting output.Apreciate your help.

Thank you.

2 
awk '   BEGIN {
                print "Name", "chicago", "newyork"
        }
        /chicago/ {
                C[$1] = $3
        }
        /newyork/ {
                N[$1] = $3
        }
        END {
                for ( k in C )
                        print k, C[k]?C[k]:"N/A", N[k]?N[k]:"N/A"
        }
' OFS='\t' file
3

Hi Yoda,
Thanks for your help, iam getting the required output but the values are not matching.
Actually i just found out that there will be only 3 or 6 fixed persons. if possible can you include those restrictions as well.
Can you please update the code. Appreciate your time & effort.

4

Can you be more specific? May be you can post what you are getting.

By the way this is what I get when I run the code:

$ cat file
Charlie chicago 15
Charlie newyork 26
jonny chicago 14
jonny newyork 15
joe chicago 15
joe newyork 18

$ awk '   BEGIN {
                print "Name", "chicago", "newyork"
        }
        /chicago/ {
                C[$1] = $3
        }
        /newyork/ {
                N[$1] = $3
        }
        END {
                for ( k in C )
                        print k, C[k]?C[k]:"N/A", N[k]?N[k]:"N/A"
        }
' OFS='\t' file
Name chicago newyork
joe     15      18
Charlie 15      26
jonny   14      15

Please note that by default, the order which for ( k in C ) loop scans array C is not defined, it is generally based upon the internal implementation of arrays inside awk .

Applying a restriction is pretty much straightforward, you just have to put an if statement to verify if the first field matches the list of names.