CODEWITHSUNDEEP
awk
Desslock
Desslock

Reputation: 25

Another awk transpose, multiple columns but transpose last one

A wrinkle on prior awk transpose questions. I'd like to transpose the following set using awk. The wrinkle is that only 4th column is transposed (and the 3rd column can be deleted). Delimiter is tab:

Source (actual source has 52weeks and millions of rows):

2014 Product1 WK01 333.33
2014 Product1 WK02 222.33
2014 Product1 WK03 344.33
2014 Product2 WK01 10.33
2014 Product2 WK02 55.10
2014 Product2 WK03 20.33

Transpose to (header row for week is not necessary):

2014 Product1 333.33 222.33 344.33
2014 Product2 10.33 55.10 20.33

Upvotes: 0

Views: 130

Answers (2)

jaypal singh
jaypal singh

Reputation: 77155

Here is another way in awk:

$ awk '
BEGIN { FS = OFS = "\t" }
$1==year && $2==product { line = line FS $NF; next }
{
    if (line) { print year, product, line }
    line = $NF; year = $1; product = $2
}
END { 
    print year, product, line
}' file
2014 Product1 333.33 222.33 344.33
2014 Product2 10.33 55.10 20.33

This will preserve the output but expects the data to be in order. It does not store entire file in memory. It will print as soon as the product or year changes.

Upvotes: 1

Jotne
Jotne

Reputation: 41460

Here is one awk

awk '{a[$1 FS $2]=a[$1 FS $2] FS $4} END {for (i in a) print i,a[i]}' file
2014 Product1  333.33 222.33 344.33
2014 Product2  10.33 55.10 20.33

It uses field #1 and #2 as index in an array, then add field #4 as data.
At the end it print all out.

If you do not like the extra space after product, use this:

awk '{a[$1 FS $2]=a[$1 FS $2]?a[$1 FS $2] FS $4:$4} END {for (i in a) print i,a[i]}' file
2014 Product1 333.33 222.33 344.33
2014 Product2 10.33 55.10 20.33

Upvotes: 1

Related Questions