Make the 1st column as header and list its corresponding values using shell script

Question

I have a large file contents are as shown below :-

Quantity    20589
Quantity    12297
Quantity    100346
Quantity    0
Quantity    141999
Quantity    23662
Quantity    551071
Quantity    72917
Quantity    60460
Quantity    19712
Quantity    35530
Quantity    0
Quantity    29818
Price   0
Price   0
Price   0
Price   0
Price   0
Price   0
Price   0
Price   0
Price   0
Price   0
Price   0
Price   0
Price   0
Discount    100
Discount    100
Discount    100
Discount    100
Discount    100
Discount    100
Discount    100
Discount    100
Discount    100
Discount    100
Discount    100
Discount    100
Discount    100

From this file i want to create a new file which should have data as :-

Quantity,Price,Discount
20589,0,100
12297,0,100    
100346,0,100
0,0,100    
141999,0,100
23662,0,100
551071,0,100
72917,0,100
60460,0,100
19712,0,100
35530,0,100
0,0,100
29818,0,100

i.e. read the original file, the column name should be the header for the new file and the corresponding values listed as shown above.

Please help me to write a new file using shell script.

Answer 1

Everything can be done within single awk command by building big array with all data, but if the file is very big you may have problems with available memory. Thus I would do this in several steps:

header=$(awk '{print $1}' file | uniq | tr '\n' ',')
printf "${header%?}\n" > output
paste -d, <(awk '$1=="Quantity"{print $2}' file) \
          <(awk '$1=="Price"{print $2}' file) \
          <(awk '$1=="Discount"{print $2}' file) >> output

The only tricky part here is removing last comma at the end of the header. I've used ${par%?} construct for this.

Answer 2

With perl you can have this way,

#!/usr/bin/perl

use strict;
use warnings;

my $file=$ARGV[0];

open my $fId, '<', $file or die "Error opeining file <$file>.";

my @qty = ();
my @price = ();
my @discount = ();

while(<$fId>){
    chomp;

    my @fields = split (/\t/, $_);

    push @qty      , $fields[1] if $fields[0] =~ m/Quantity/;
    push @price    , $fields[1] if $fields[0] =~ m/Price/;
    push @discount , $fields[1] if $fields[0] =~ m/Discount/;
}
close $fId;

print "Quantity,Price,Discount\n";
for(my $i = 0; $i < scalar @qty; $i++){
    print "$qty[$i],$price[$i],$discount[$i]\n";
}

You will need to pass filename as argument.

e.g., ./test.pl input_file