logo标签列表

合并具有共同列的非常大的CSV文件

perlunixjoincsvawk
3
例如，我有两个csv文件， 0.csv
100a,a,b,c,c
200a,b,c,c,c
300a,c,d,c,c

and 1.csv

100a,Emma,Thomas
200a,Alex,Jason
400a,Sanjay,Gupta
500a,Nisha,Singh

我希望你能够将结果输出为以下格式：
100a,a,b,c,c,Emma,Thomas
200a,b,c,c,c,Alex,Jason
300a,c,d,c,c,0,0
400a,0,0,0,0,Sanjay,Gupta
500a,0,0,0,0,Nisha,Singh

我应该如何在Unix shell脚本或Perl中实现这个功能？我知道Unix中有"join"命令，对于小文件来说这是一个很好的解决方法。例如，为了得到我的结果，我可以这样做：

join -t , -a 1 -a 2 -1 1 -2 1 -o 0 1.2 1.3 1.4 1.5 2.2 2.3 -e "0" 0.csv 1.csv

但是对于我的目的来说，这并不可行，因为我的实际数据文件有超过一百万个列（总数据大小在几千兆字节以上），因此我的Unix命令也会超过一百万个字符。这可能是最重要的头痛之一，因为效率低下的代码很容易被拖垮。

另外，请注意，每当存在缺失数据时都需要一个占位符字符“0”。这防止我简单地使用此操作。

join -t , -a 1 -a 2 -1 1 -2 1 0.csv 1.csv

我也是一个初学Perl编程的人，所以一些细节信息会非常有用。虽然我更倾向于使用Perl或Shell脚本来解决问题，但任何有效的方法都可以。

1
你如何识别数据是否缺失？（顺便说一句，我不是那个点踩的人）。 - Thor
总行数应该是唯一的“ID”元素的总数，而列数的总和是两个文件的列数之和（当然要减去索引列）。因此，当第一个文件中缺少“400a”时，我们有四个“0”作为占位符，当第二个文件中缺少“300a”时，输出中另外两个“0”也会出现。每一行都应该有相同数量的元素，对于每一行也是如此。 - awacs
在您期望的输出中，300a,c,c,c,c,0,0 应该改为 300a,c,d,c,c,0,0 吗？ - Steve
是的，谢谢你发现了这个问题。@steve - awacs
6个回答
2
如果您可以在每个文件中添加一个标题，那么您可以使用 tabulator 来解决问题。例如：

0.csv:

key,letter_1,letter_2,letter_3,letter_4
100a,a,b,c,c
200a,b,c,c,c
300a,c,d,c,c

1.csv:

key,name_1,name_2
100a,Emma,Thomas
200a,Alex,Jason
400a,Sanjay,Gupta
500a,Nisha,Singh

然后，tbljoin -lr -n 0 0.csv 1.csv 会产生：
key,letter_1,letter_2,letter_3,letter_4,name_1,name_2
100a,a,b,c,c,Emma,Thomas
200a,b,c,c,c,Alex,Jason
300a,c,d,c,c,0,0
400a,0,0,0,0,Sanjay,Gupta
500a,0,0,0,0,Nisha,Singh

请注意（与纯粹的Unix join命令相反），输入文件不需要排序；此外，您不需要担心内存消耗，因为该实现基于Unix sort，并且对于大文件将使用基于文件的归并排序。
1

你也可以使用 awk 来完成这个操作。

确定两个文件中最宽行的长度，并将其保存到 max0 和 max1 中：

awk -F, '
  ARGIND == 1 && NF > max0 { max0 = NF }
  ARGIND == 2 && NF > max1 { max1 = NF }
  END { print max0, max1 }
' 0.csv 1.csv | read max0 max1

使用这个 awk 脚本来进行连接：

foo.awk

BEGIN { 
  max1--
  FS  = OFS = ","
}

ARGIND == 1 {
  A[$1] = $2

  # Copy columns from first file to key
  for(i=3; i<=NF; i++)
    A[$1] = A[$1] FS $i

  # Pad until we have max0 columns
  for( ; i<=max0; i++)
    A[$1] = A[$1] FS "0"
}

ARGIND == 2 {
  # Pad rows which are only in second file
  if(A[$1] == "") {
    A[$1] = 0
    for(i=3; i<=max0; i++)
      A[$1] = A[$1] FS "0"
  }

  # Copy columns from second file to key
  for(i=2; i<=NF; i++)
    A[$1] = A[$1] FS $i

  # Pad until we have max1 columns
  for( ; i<=max1; i++)
    A[$1] = A[$1] FS "0"
}

END { 
  for(key in A) {
    # Pad rows which are only in first file
    split(A[key], fields, ",")
    for(i=1; i <= max0+max1-length(fields)-1; i++)
      A[key] = A[key] FS "0"

    # Finally print key and accumulated column values
    print key, A[key]
  }
}

使用以下命令运行：

awk -f foo.awk -v max0=$max0 -v max1=$max1 0.csv 1.csv | sort -n

使用-v传递最宽的行值。输出来自哈希表并且未排序，因此在显示之前请使用sort -n进行排序。

0

当你处理大量数据且两个源的大小大致相同时，合并连接是最好的选择。这是因为一旦两个（每个）源都排序了，它就使用恒定数量的内存。合并连接也是完全外部连接的不错选择，并且可以在Perl中编写得相当优雅。

要使用以下Perl脚本，您必须将两个文件按第一列键的字典顺序排序，并且该键必须是唯一的。它还假定两个文件中每行具有完全相同的列数。

#!/usr/bin/perl

use strict;
use warnings;
use Text::CSV_XS;

die "Usage $0 file1.csv file2.csv" unless @ARGV > 1;

my ( $file1, $file2 ) = @ARGV;

open my $fh1, '<', $file1 or die "Can't open $file1: $!";
open my $fh2, '<', $file2 or die "Can't open $file2: $!";

my $csv = Text::CSV_XS->new( { binary => 1, eol => "\n" } );

my $r1 = $csv->getline($fh1) or die "Missing data in $file1";
my $r2 = $csv->getline($fh2) or die "Missing data in $file2";

# same amount of zeros as number of fields in each file
my @cols1 = (0) x ( @$r1 - 1 );
my @cols2 = (0) x ( @$r2 - 1 );

while ( $r1 || $r2 ) {    # there are some data

    # compare keys only if there are rows in both files
    # zero silences warnings in numeric comparisons below
    my $cmp = $r1 && $r2 && ( $$r1[0] cmp $$r2[0] ) || 0;

    # row is defined and has less or equal key than another one
    my $le1 = $r1 && $cmp < 1;
    my $le2 = $r2 && $cmp > -1;

    $csv->print(
        *STDOUT,
        [   $le1 ? $$r1[0] : $$r2[0],    # key
            ( $le1 ? @$r1[ 1 .. @cols1 ] : @cols1 ),    # first file fields
            ( $le2 ? @$r2[ 1 .. @cols2 ] : @cols2 )     # second file fields
        ]
    );

    #read next rows
    $r1 = $csv->getline($fh1) if $le1;
    $r2 = $csv->getline($fh2) if $le2;
}

使用方法为script.pl 0.csv 1.csv > result.csv。如果文件未排序，请使用sort -u -d -t, -k1,1进行排序。

该脚本在线性时间内运行（已排序时），并且仅使用内存来存储每个文件的一行，即“常量”大小。

您可以使用脚本对文件进行排序。

$ENV{LC_ALL} = 'C';
open my $fh1, "( sed '1!d' $file1; sed 1d $file1 | sort -u -d -t, -k1,1 ) |"
    or die "Can't sort $file1: $!";
open my $fh2, "( sed '1!d' $file2; sed 1d $file2 | sort -u -d -t, -k1,1 ) |"
    or die "Can't sort $file2: $!";
-1

csvkit是一种处理csv文件的工具，可以进行连接（以及其它功能）。

请参见csvjoin。它的命令行接口紧凑，并且可以处理多种csv格式（tsv、其它分隔符、编码、转义字符等）。

您所要求的操作可以通过以下方式完成：

csvjoin --columns 0 0.csv 1.csv
使用这个特定的工具来完成这个特定任务还不清楚。 - Hynek -Pichi- Vychodil
它不起作用。根据问题需要 --outer。同时也没有清楚如何定义每个文件中缺失行的默认值。你有没有尝试使用示例数据来解决它？ - Hynek -Pichi- Vychodil
-1
如果您的文件足够小，可以放入内存中，这将很容易。您可以使用Perl读取文件，解析它们，并使用公共行中的值作为哈希键将行推入数组哈希表中。 然后通过其键迭代哈希表的内容并打印出数组。
问题中明确说明了总数据大小以千兆字节为单位。 - Hynek -Pichi- Vychodil
-1

这是我想出来的（Perl）：

my $output={};

open FILE1, '</path/to/file1';
while (<FILE1>){
    chomp;
    my @values=split(/,/, $_);
    my $id=shift(@values);
    if($output->{$id}){
        my $temparray=$output->{$id};
        push (@$temparray, @values);
    }else{
        $output->{$id}=@values;
    }
}
close FILE1;
open FILE2, '</path/to/file2';
while (<FILE2>){
    chomp;
    my @values=split(/,/, $_);
    my $id=shift(@values);
    if($output->{$id}){
        my $temparray=$output->{$id};
        push (@$temparray, @values);
    }else{
        $output->{$id}=@values;
    }   
}
close FILE2;
在mage.pl第9行附近的"@values"处，push的arg 1类型必须是数组（而不是哈希元素）。 在mage.pl第21行附近的"@values"处，push的arg 1类型必须是数组（而不是哈希元素）。 - awacs
嗯，这一次什么也没有发生。或许我是个新手，误解了代码。所以我应该将“</path/to/file2”更改为我正在使用的路径（例如，“</home/data/0.csv”），还是我完全误解了？ - awacs
/path/to/file1 将会是 /home/data/0.csv，而 /path/to/file2 将会是 /home/data/1.csv。 - orhanhenrik
1/ 当数据总量达到几个GB时，将整个数据读入内存是一个不好的想法。 2/ 当有像Text::CSV这样的模块存在时，仅使用split解析CSV数据是不明智的。 - Hynek -Pichi- Vychodil
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接