我有 3-5 个 .csv 文件，需要将它们合并在一起，同时将所有内容保留在各自的列中，下面是一个简单的示例，其中文件具有不同行数。file1 file2 file3 file4 file5 > finalfile。
文件1
1 1
1 1
1 1
文件2
2 2 2
2 2 2
文件3
3
3
3
3
文件4
4
4
文件5
5
5
5
5
5
我需要 .csv 文件中的结果将所有文件合并在一起并将所有内容保留在各自的列中。在我的示例中，0 是空白单元格/列。
最终文件
1 1 2 2 2 3 4 5
1 1 2 2 2 3 4 5
1 1 0 0 0 3 0 5
0 0 0 0 0 3 0 5
0 0 0 0 0 0 0 5
如果这些单元格/列中没有数据，我当前尝试的所有内容都会将所有内容滑动到左侧。
最终文件
1 1 2 2 2 3 4 5
1 1 2 2 2 3 4 5
1 1 3 5
3 5
5
答案1
csvkit不使用标头-H并禁用推理-I（否则 的值1将被解释为TRUE
csvjoin -H -I file*
到目前为止（有一些错误消息，因为没有标题，所以它会抱怨并添加一些）
a,b,a2,b2,c,a2_2,a2_3,a2_4
1,1,2,2,2,3,4,5
1,1,2,2,2,3,4,5
1,1,,,,3,,5
,,,,,3,,5
,,,,,,,5
如何从那里替换缺失值并丢失分隔符是一个品味问题，但您可以迭代 中的字段awk，将分隔符设置为-F","，跳过标题NR>1 并添加0转换NULL字段
csvjoin -H -I file* | awk -F"," 'NR>1{for (i=1; i<=NF; i++) printf ("%s ", $i+0); print""}'
这会让你到达你想去的地方
1 1 2 2 2 3 4 5
1 1 2 2 2 3 4 5
1 1 0 0 0 3 0 5
0 0 0 0 0 3 0 5
0 0 0 0 0 0 0 5
或者，纯awk版本假设每个文件中的字段数量是恒定的
awk '{for (i=1; i<=NF; i++) {mx[FNR][nf+i]=$i}}
ENDFILE{nf+=NF; nor=(nor<FNR)?FNR:nor}
END{for (i=1;i<=nor;i++) {for (j=1;j<=nf;j++) printf ("%s ", mx[i][j]+0); print ""}}' file*
它只是将每个文件中的值加载到数组中mx[][]
{for (i=1; i<=NF; i++) {mx[FNR][nf+i]=$i}}
在每个文件的末尾，将列索引右移nf文件中的字段数NF，并取当前记录数NR或最后一个矩阵大小中的较大者nor
ENDFILE{nf+=NF; nor=(nor<FNR)?FNR:nor}
最后只需迭代矩阵维度并转换NULL值
END{for (i=1;i<=nor;i++) {for (j=1;j<=nf;j++) printf ("$s ", mx[i][j]+0); print ""}}'
答案2
% stitch --autocol --ofs="\\t" one two three four five
1 1 2 2 2 3 4 5
1 1 2 2 2 3 4 5
1 1 3 5
3 5
5
已经很paste接近了，但还没有完全实现。设置--ofs=,和--ifs=,来获取实际的 CSV 数据，但要注意 split-at-comma 是一个非常糟糕的 CSV 解析器。
#!/usr/bin/env perl
use strict;
use warnings;
use Getopt::Long qw(GetOptions);
GetOptions(
'autocol|ac' => \my $Flag_AutoCol,
'ifs=s' => \my $Flag_IFS,
'ofs=s' => \my $Flag_OFS,
'rs=s' => \my $Flag_RS,
) or exit 64;
$Flag_IFS //= ' ';
$Flag_OFS //= ' ';
$Flag_RS //= '\n';
$Flag_IFS =~ s/(\\t)/qq!"$1"!/eeg;
$Flag_OFS =~ s/(\\[nrt])/qq!"$1"!/eeg;
$Flag_RS =~ s/(\\[nrt])/qq!"$1"!/eeg;
my @fhs;
my $seen_stdin = 0;
for my $arg (@ARGV) {
# "file" (no spec) or "file:" (no spec but colon) or "file:spec"
# where no spec means "print all columns and do not preserve column
# positions as will not try to guess that"
my ( $file, $spec );
if ( $arg =~ m/^([^:]+)$/ ) {
$file = $1;
} elsif ( $arg =~ m/^(.+):([^:]*)$/ ) {
$file = $1;
$spec = $2;
}
die "could not parse file from '$arg'\n" if !defined $file;
my $fh;
if ( $file eq '-' and !$seen_stdin ) {
$fh = \*STDIN;
$seen_stdin = 1;
} else {
open $fh, '<', $file or die "could not open $file: $!\n";
}
push @fhs, [ $fh, defined $spec ? specify($spec) : undef ];
}
my $have_fhs = @fhs;
while ($have_fhs) {
my $pad_col = 0;
for my $i ( 0 .. $#fhs ) {
if ( defined $fhs[$i]->[0] ) {
my $line = readline $fhs[$i]->[0];
if ( !defined $line ) {
# EOF on an input file
$fhs[$i]->[0] = undef;
$have_fhs--;
$pad_col += @{ $fhs[$i]->[1] } if defined $fhs[$i]->[1];
next;
}
# Complicated due to not wanting to print the empty columns if
# there's nothing else on the line to print (works around getting
# an ultimate blank line that messes up the shell prompt)
if ($pad_col) {
print( ($Flag_OFS) x $pad_col );
$pad_col = 0;
}
chomp $line;
my @fields = split /$Flag_IFS/, $line;
# Set field count from the first line of input (may cause
# subsequent uninit warnings if the number of columns then drops)
if ( $Flag_AutoCol and !defined $fhs[$i]->[1] ) {
$fhs[$i]->[1] = [ 0 .. $#fields ];
}
if ( defined $fhs[$i]->[1] ) {
print join( $Flag_OFS, @fields[ @{ $fhs[$i]->[1] } ] );
} else {
print join( $Flag_OFS, @fields );
}
print $Flag_OFS if $i != $#fhs;
} elsif ( defined $fhs[$i]->[1] ) {
$pad_col += @{ $fhs[$i]->[1] };
}
}
print $Flag_RS if $have_fhs;
}
exit 0;
# Parse 1,2,3,5..9 type input into Perl array indices
sub specify {
my $spec = shift;
my @indices;
SPEC: {
if ( $spec =~ m/\G(\d+)\.\.(\d+),?/cg ) {
push @indices, $1 .. $2;
redo SPEC;
}
if ( $spec =~ m/\G(\d+),?/cg ) {
push @indices, $1;
redo SPEC;
}
if ( $spec =~ m/\G(.)/cg ) {
warn "unknown character '$1' in spec '$spec'\n";
exit 65;
}
}
# Assume user will use awk- or cut-like column numbers from 1, shift
# these to perl count-from-zero internally.
$_-- for @indices;
return \@indices;
}
__END__
=head1 NAME
stitch - joins columns from multiple input files
=head1 SYNOPSIS
$ cat a
a b c
$ cat b
1 2 3
4 5 6
7 8 9
$ stitch --ofs=\\t a:2 b:1,3
b 1 3
4 6
7 9
That is, column two from the first file, and columns one and three from
the second. The range operator C<..> may also be used to select a range
of columns, e.g. C<1,4..6,8>.
=head1 DESCRIPTION
This program joins columns by line number from multiple input files.
=head1 USAGE
$ stitch [--ac] [--ifs=s] [--ofs=s] [--rs=s] file[:spec] [file[:spec] ..]
Use C<-> to select columns from standard input; otherwise, specify files
to read input from, along with the optional column specification (by
default, all columns will be selected).
This program supports the following command line switches:
=over 4
=item B<--autocol> | B<--ac>
Set the number of columns from the first line of input seen from a
C<file> if a column specification was not provided for said C<file>.
Influences empty field padding (which only happens with a column
specification should a file run short before the others).
=item B<--ifs>=I<s>
Specify the input field separator (space by default). A C<\t> will be
expanded to the actual character:
$ perl -E 'say join("\t", qw/a b c/)' | stitch --ifs=\\t -- -:2
Or, use a regex:
$ perl -E 'say join("\t", qw/a b c/)' | stitch --ifs='\s+' -- -:2
=item B<--ofs>=I<s>
Output field separator (space by default). Similar expansion done as
for B<--ifs>, though also C<\n> and C<\r> are allowed.
=item B<--rs>=I<s>
Output record separator (newline by default). Expansion done as
for B<--ofs>.
=back
=head1 SECURITY
Probably should not be run under elevated privs due to user-supplied
input to the L<perlfunc/"split"> function.
Passing a user-supplied regex to L<perlfunc/"split"> might be a bit
sketchy especially if L<sudo(1)> or the like is involved. It might be
nice to have per-file IFS (so one could split on spaces on stdin, and
C<:> from C<passwd>), but that would add complications.
=head1 SEE ALSO
awk(1), comm(1), cut(1), join(1), perl(1)
=cut