CODEWITHSUNDEEP
bashshellunixawkksh
Ell
Ell

Reputation: 947

Transpose list to tablular format (awk)

I'm looking to convert a list containing days of the week with timings next to them, into a table like format.

Input below:

Mon 0100
Mon 0700
Tue 0700
Wen 0100
Wen 0700
Thu 0100
Thu 0700
Fri 0100
Fri 0700
Sat 0100
Sun 0100
Sun 0700

Ouput below:

        Mon  Tue  Wed  Thu  Fri  Sat  Sun   
0100    X         X    X    X    X    X   
0700    X    X    X    X    X         X

Looking for answers in awk ideally. Preservation of format in the output given is preferred. In the scenario whereby a weekday does not exist, it should not be included (If possible it would be nice to also have the answer which would include the missing weekday in the output, if it does not exist in the input).

I gave this a shot, but aside from printing the first row, I really can't get my head around the construct of the arrays, even though I imagine it is relatively simple.

Thanks Ell

Upvotes: 2

Views: 116

Answers (2)

jaypal singh
jaypal singh

Reputation: 77105

Here is one way using awk: 

awk '
!($1 in day) { days[++d] = $1; day[$1]++ }
!($2 in num) { nums[++n] = $2; num[$2]++ }
             { map[$1,$2]++ }
END {
    printf "\t"
    for (i=1; i<=d; i++) {
        printf "\t%s", days[i]
    }
    print ""
    for (j=1; j<=n; j++) {
        printf "%s ", nums[j]
        for (i=1; i<=d; i++) {
            printf "\t%s", (map[days[i],nums[j]] ? "X" : FS )
        }
        print ""
    }
}' file

Output:

        Mon Tue Wen Thu Fri Sat Sun
0100    X       X   X   X   X   X
0700    X   X   X   X   X       X

Upvotes: 5

Sean Bright
Sean Bright

Reputation: 120644

I'm sure there is a much more elegant solution (I don't really know awk - just going by the gawk manual):

BEGIN {
        days["Mon"] = 1      
        days["Tue"] = 2      
        days["Wen"] = 4      
        days["Thu"] = 8      
        days["Fri"] = 16      
        days["Sat"] = 32      
        days["Sun"] = 64      
}

{
        hours[$2] = or(hours[$2], days[$1])
}

END {
        print "        Mon  Tue  Wed  Thu  Fri  Sat  Sun"

        for (key in hours) {
                printf "%s    ", key
                for (i = 0; i < 7; i++) {
                        if (and(hours[key], 2 ** i))
                                printf "X"
                        else
                                printf " "
                        printf "    "
                }
                print ""
        }
}

Note that in your sample input file, Wednesday is "Wen" but in your output it is "Wed." If the sample input file has a typo on your part, you will need to change line 4 of the script above to match.

Upvotes: 1

Related Questions