Reformatting Column into rows | Unix Linux Forums | Shell Programming and Scripting

  Go Back    


Shell Programming and Scripting Post questions about KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and OTHER shell scripts and shell scripting languages here.

Reformatting Column into rows

Shell Programming and Scripting


Closed Thread    
 
Thread Tools Search this Thread Display Modes
    #1  
Old 12-18-2012
kieranfoley kieranfoley is offline
Registered User
 
Join Date: May 2009
Last Activity: 23 July 2014, 8:04 AM EDT
Posts: 24
Thanks: 2
Thanked 0 Times in 0 Posts
Reformatting Column into rows

I have a file that I need to reformat so that every time I match a certain string in the first column it prints to the string as the heading and under the sting it prints the remaining entries on the line that matched the string.

For example, I need to reformat this

Code:
xxx : yyy zzz
11 : 111 222 xxx
xyz : abc def
xxx : aaa bbb
11 : 555 333 yy
xyz : abc xyz

To look like this with xxx, 11 and xyz as the headers/titles for each column.

Code:
xxx              11                   xyz
yyy zzz        111 222 xxx      abc def
aaa bbb        555 333 yy      abc xyz


Last edited by Franklin52; 12-18-2012 at 07:07 AM.. Reason: Please use code tags for data and code samples
Sponsored Links
    #2  
Old 12-18-2012
Yoda's Avatar
Yoda Yoda is offline Forum Advisor  
Jedi Master
 
Join Date: Jan 2012
Last Activity: 25 July 2014, 1:29 AM EDT
Location: Galactic Empire
Posts: 3,355
Thanks: 230
Thanked 1,190 Times in 1,123 Posts

Code:
awk -F: ' BEGIN {
 printf "%-15s%-15s%-15s\n", "xxx", "11", "xyz" } {
 if(NR%3==0) {
  sub(" ","",$2);
  printf "%-15s\n", $2;
 }
 else {
  sub(" ","",$2);
  printf "%-15s", $2;
 }
} ' infile

Sponsored Links
    #3  
Old 12-18-2012
Don Cragun's Avatar
Don Cragun Don Cragun is offline Forum Staff  
Moderator
 
Join Date: Jul 2012
Last Activity: 24 July 2014, 9:12 PM EDT
Location: San Jose, CA, USA
Posts: 4,135
Thanks: 162
Thanked 1,412 Times in 1,197 Posts
If you were looking for something that self adjusts based on the column headings and data to be displayed, you could try something like:

Code:
awk -F" : " '
# c[heading] = # of values accumulted for heading
# h[heading] = heading_field
# hc = # of headings
# ho[heading_field] = heading
# r = # of rows to be printed (not counting headings)
# v[heading,c[heading]] = value to be printed in row c[heading] under heading
# w[heading_field] = widest field data for heading_field
{       v[$1,++c[$1]] = $2
        if(!($1 in h)) {
                ho[++hc] = $1
                h[$1] = hc
                w[hc] = length($1)
        }
        if(w[h[$1]] < length($2)) w[h[$1]] = length($2)
        if(c[$1] > r) r = c[$1]$1, c[$1], $1, h[$1], hc, ho[hc], r, $1, $1, c[$1], v[$1,c[$1]], h[$1], w[h[$1]])}
END {   for(i = 1; i < hc; i++)
                printf("%-*.*s", w[i] + 3, w[i] + 3, ho[i])
        printf("%-s\n", ho[hc]);
        for(j = 1; j <= r; j++) {
                for(i = 1; i < hc; i++)
                        printf("%-*.*s", w[i] + 3, w[i] + 3, v[ho[i], j]);
                printf("%-s\n", v[ho[hc], j]);
        }
}' file

With the sample input file given in the 1st message in this thread, it produces the output:

Code:
xxx       11            xyz
yyy zzz   111 222 xxx   abc def
aaa bbb   555 333 yy    abc xyz

    #4  
Old 12-18-2012
rdrtx1 rdrtx1 is offline
Registered User
 
Join Date: Sep 2012
Last Activity: 15 July 2014, 11:14 AM EDT
Location: Houston, Texas, USA
Posts: 675
Thanks: 0
Thanked 203 Times in 195 Posts
try also:

Code:
awk -F" *: *" '!a[$1]++ {b[c++]=$1;}
{cn=ca[$1]++; e[$1]=$1; d[$1, a[$1]-1]=$2;}
END {
  for (i=0; i<c; i++) {printf("%-20s", b[i])}; print "";
  for (j=0; j<=cn; j++) {for (i=0; i<c; i++) {printf("%-20s",d[e[b[i]],j])} print ""; }
}' infile

Sponsored Links
    #5  
Old 12-19-2012
kieranfoley kieranfoley is offline
Registered User
 
Join Date: May 2009
Last Activity: 23 July 2014, 8:04 AM EDT
Posts: 24
Thanks: 2
Thanked 0 Times in 0 Posts
Thank you very much guys. These posts have been extremely helpful. They all work!
Sponsored Links
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
[Solved] Deleting all rows where the first column equals the second column evelibertine UNIX for Dummies Questions & Answers 2 11-12-2012 10:31 PM
merging rows into new file based on rows and first column A-V UNIX for Dummies Questions & Answers 1 09-12-2012 03:09 PM
awk command to print only selected rows in a particular column specified by column name ks_reddy Shell Programming and Scripting 4 09-01-2012 11:32 AM
Reformatting single column text file starting new line when finding particular string kieranfoley Shell Programming and Scripting 7 10-21-2011 02:02 PM
How to sum rows in e.g. column 1 by a category in e.g. column 2 auburn UNIX for Dummies Questions & Answers 8 04-23-2010 04:24 AM



All times are GMT -4. The time now is 02:44 AM.