☰

按标题组织数据

tag-icon • tag-icon awk sed csv text-formatting gawk
按标题组织数据

我有两个 csv 文件。

文件 1 包含标头。文件2包含数据

文件 1 格式：file1.csv

id,abc,xyz,aaa,bbb,ccc

文件 2 格式：file2.csv

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

现在，我必须将 file2.csv 中的源列中的数据与 file1.csv 中的标题进行匹配，输出应如下所示

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

答案1

这是一个典型的不熔化或不整洁的操作。

使用米勒（http://johnkerl.org/miller/doc）并从

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

你可以跑

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

并且有

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

答案2

无法...抗拒...线路噪音...

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

或者（某种程度上）更明智的俏皮话

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

或者，这是“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};
}

然后：perl combine.pl > output.csv

答案3

...以及不可避免的awk建议：

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

相关内容