Cfrtjryk

I have two csv files.

File 1 contains header.
File 2 contains data

file 1 format : file1.csv

id,abc,xyz,aaa,bbb,ccc

file 2 format : file2.csv

id,source,value                

1,abc,100      

1,xyz,200   

2,aaa,300   

2,bbb,400   

2,ccc,500

Now, I have to match the data in the source column in file2.csv with header in file1.csv and output should be like below

id,abc,xyz,aaa,bbb,ccc   

1,100,200,null,null,null   

2,null,null,300,400,500         

can't ... resist ... line noise ...

perl -F, -slE'if(@ARGV){say;@h=@F}elsif($.>1){$d{$F[0]}->@{@h}=($F[0],("null")x@h)unless$d{$F[0]};$d{$F[0]}{$F[1]}=$F[2]}close ARGV if eof}END{say$d{$_}->@{@h}for keys%d' -- -,=, file{1,2}.csv

or a (somewhat) more sensible one-liner

perl -F, -lane '

    if (@ARGV) {print; @sources = @F[1..$#F]}  # the first file

    elsif ($. > 1) {                           # the 2nd file, minus the header

        $data{$F[0]}->@{@sources} = ("null") x @sources unless $data{$F[0]};

        $data{$F[0]}{$F[1]} = $F[2]; 

    }

    close ARGV if eof;    # reset $. for each new file

  } END {

    $, = ",";

    print $_, $data{$_}->@{@sources} for sort keys %data

' file1.csv file2.csv

or, this is "combine.pl"

#!/usr/bin/env perl

use v5.22;

use Path::Tiny;        # convenience module from CPAN



# read the header from the first file

my $file = path("file1.csv");

my @lines = $file->lines;

my $header = $lines[0];

chomp $header;

my @sources = split /,/, $header;



# read the data from the second file

$file = path("file2.csv");

chomp( @lines = $file->lines );

shift @lines;          # ignore the header

my %data;

for my $line (@lines) {

    my ($id, $source, $value) = split /,/, $line, 3;

    if (not exists $data{$id}) {

        # initialize the output data for a new id

        $data{$id}->@{ @sources } = ($id, ("null") x scalar(@sources));

    }

    # and store this value

    $data{$id}{$source} = $value;

}



# output the results

say $header;

$, = ",";

for my $id (sort {$a <=> $b} keys %data) {

    say $data{$id}->@{@sources};

}

then: perl combine.pl > output.csv

... and the inevitable awk proposal:

awk -F, '

function PRT()  {printf "%d", ID                                        # print first field: ID

                 for (i=2; i<=MAX; i++) printf ",%s",PF[i]?PF[i]:"null" # print popuated fields in sequence, "null" if empty

                 printf ORS                                             # line feed

                }



NR==FNR         {print                                                  # in first file; print header

                 for (MAX=n=split ($0, T); n; n--) COL[T[n]] = n        # split header and find column No. per header field

                 next                                                   # no further processing of this line

                }

FNR > 2 &&                                                              # skip printout for first ID (in second line of file2)

$1 != ID        {PRT()                                                  # print if new ID found

                 split ("", PF)                                         # empty the print array

                }



                {ID = $1                                                # retain ID

                 PF[COL[$2]] = $3                                       # collect col values into respective column

                }



END             {PRT()                                                  # print last IDs record

                }

' file[12]                                                              # shells "pattern matching"  expands resp. files

id,abc,xyz,aaa,bbb,ccc

1,100,200,null,null,null

2,null,null,300,400,500

it's a classical unmelt or untidy operation.

Using Miller (http://johnkerl.org/miller/doc) and starting from


id,source,value
1,abc,100
1,xyz,200
2,aaa,300
2,bbb,400
2,ccc,500


you can run

mlr --csv reshape -s source,value then unsparsify input.csv

And have


id,abc,xyz,aaa,bbb,ccc
1,100,200,,,
2,,,300,400,500