合并多个具有部分匹配键列的 CSV 文件

合并多个具有部分匹配键列的 CSV 文件

我有 100 个csv文件,每个文件包含 2 列。第一个是taxonomy,第二个是counts。每个文件大约有 10000 行。每个文件中的数据taxonomy仅部分共享,总共约 50000 个唯一值。我需要将它们合并到一个表中,taxa其中一个文件中的缺失被分配了值0。结果应该是一个包含 50000 行和 101 列的表(csv或)。tsv一个简化的示例是: 文件 1 ( R1.csv):

A,1
B,20
C,30

文件2(R2.csv):

C,1
D,13
E,15
F,19

文件3(R3.csv):

A,1
B,4
E,2
G,6
H,8

预期结果:

Taxa,R1,R2,R3
A,1,0,1
B,20,0,4
C,30,1,0
D,0,13,0
E,0,15,2
F,0,19,0
G,0,0,6
H,0,0,8

知道如何使用 bash 脚本来做到这一点吗?

答案1

我使用awk它一次处理多个文件:

sed 's/,R[1-9]\+\.csv:/,/g' <(awk -v HEADER="$(printf ",%s:" R{1..3}.csv)" -F, '
    { seen[$1]=seen[$1]","FILENAME":"$2; }
    END { print HEADER; for (x in seen) print x seen[x]}' R{1..3}.csv \
|awk -F, 'NR==1{split($0,arr,/,/);next} {SEP=""; fld=1;
    for (x in arr){printf ($0 ~ arr[x])?SEP""$(fld++):",0";SEP=","};print ""}')

输出:

A,1,0,1
B,20,0,4
C,30,1,0
D,0,13,0
E,0,15,2
F,0,19,0
G,0,0,6
H,0,0,8

代码分解:

awk -F, '{ seen[$1]=seen[$1]","FILENAME":"$2; }
    END{ print HEADER; for (x in seen) print x seen[x] }' R{1..3}.csv

代码的主要部分将所有文件中的所有第二列连接成一个,并打印属于具有相同第一列的文件的值。这里seen是一个数组名称,其中键作为第一列,值采用,FILENAME:$2附加模式。

Inseen[$1]=seen[$1]","FILENAME":"$2;表示打印一个逗号,,后跟当前处理FILENAME文件awk,冒号:后跟第二列值$2(当它具有相同的第一列时)seen[$1]=...,并附加到相同的键索引=seen[$1]...并保存在相同的键值中。

END声明,awk当所有记录/行读取时,最终执行此块,并且我们使用 for 循环来迭代数组见过并打印钥匙第一个和键的值在接下来的。

将导致:

A,R1.csv:1,R3.csv:1
B,R1.csv:20,R3.csv:4
C,R1.csv:30,R2.csv:1
D,R2.csv:13
E,R2.csv:15,R3.csv:2
F,R2.csv:19
G,R3.csv:6
H,R3.csv:8

好的,现在我们知道现有值来自哪些文件以及哪些文件没有这些数据。为了用 填充不存在的文件的数据0,我使用了 shell 命令替换生成包含所有文件名的标题行并传递给awk作为HEADER -v变量:

awk -v HEADER="$(printf ",%s:" R{1..3}.csv)" ...

稍后我们将使用这一HEADER行并用0.现在我们的输入是这样的格式:

$ awk -v HEADER="$(printf ",%s:" R{1..3}.csv)" -F, '
    { seen[$1]=seen[$1]","FILENAME":"$2; }
    END { print HEADER; for (x in seen) print x seen[x]}' R{1..3}.csv 
,R1.csv:,R2.csv:,R3.csv:
A,R1.csv:1,R3.csv:1
B,R1.csv:20,R3.csv:4
C,R1.csv:30,R2.csv:1
D,R2.csv:13
E,R2.csv:15,R3.csv:2
F,R2.csv:19
G,R3.csv:6
H,R3.csv:8

接下来我在下面使用了另一个awk用于填充非退出文件数据的脚本,0我从问题的另一个答案中复制了这些数据“根据列格式化并填充缺失的数据”

... |awk -F, 'NR==1{split($0,arr,/,/);next} {SEP=""; fld=1;
    for (x in arr){printf ($0 ~ arr[x])?SEP""$(fld++):",0";SEP=","};print ""}'

最后,sed 's/,R[1-9]\+\.csv:/,/g'使用 来将结果中现有的文件名替换为单个逗号,

答案2

使用伟大的磨坊主,你可以这样做

mlr --csv --implicit-csv-header put '$f=FILENAME;$f=sub($f,"\..+","")' then \
label Taxa then \
reshape -s f,2 then \
unsparsify then \
fill-empty -v 0 then \
sort-within-records then \
reorder -f Taxa *.csv

要得到

Taxa,R1,R2,R3
A,1,0,1
B,20,0,4
C,30,1,0
D,0,13,0
E,0,15,2
F,0,19,0
G,0,0,6
H,0,0,8

答案3

这会很痛苦

 join -t, -j1 -a1 -e0 -o auto r1.csv r2.csv > r12a.csv
 join -t, -j1 -a2 -e0 -o auto r1.csv r2.csv > r12b.csv
 sort -u r12?.csv > r12.csv
 join -t, -j1 -a1 -e0 -o auto r12.csv r3.csv > r123a.csv
 join -t, -j1 -a2 -e0 -o auto r12.csv r3.csv > r123b.csv
 sort -u r123{a,b}.csv
  • 第一个连接打印文件 x 中的不成对的值(-ax),默认值为(-e0),-o auto告诉连接打印 0
  • sort -u排序并保留唯一记录。

我不确定awk代码会更具可读性。

答案4

有很多方法可以处理命令行上的 CSV,或者Archemar的回答。但是,由于您的要求,我建议使用 python。我在 python 3.5 中测试了这个脚本,应该可以解决问题,或者至少给你一个好的开始:

import os,re,argparse
import csv

parser = argparse.ArgumentParser(description='join csvs with rows of the \
        form \w+,[1-9], inserting a zero for a row label if it does not \
        exist.')
parser.add_argument('infiles', type=str, help='infile names', nargs='+')
args = parser.parse_args()

d = {}
file_idx = 0
for infile in args.infiles:
    with open(infile, 'r') as f:
        for line in f:
            parsed_line = re.match('(\w+),([0-9]+)', line)
            if not parsed_line:
                print("line {} not parsed in file {}".format(line, infile))
                continue
            if parsed_line.group(1) in d:
                d[parsed_line.group(1)].append(parsed_line.group(2))
            else:
                l = [0]*(file_idx)
                l.append(parsed_line.group(2))
                d[parsed_line.group(1)]=l
        for k in d:
            if (len(d[k]) == file_idx):
                d[k].append(0)
            if not(len(d[k]) == file_idx+1):
                print("problem with file {}, dict {}, key {}".format(f,d,k))
    file_idx = file_idx + 1

## output time
with open('results.csv','w') as csvfile:
    cwriter = csv.writer(csvfile)
    header = [os.path.splitext(x)[0] for x in args.infiles]
    header.insert(0,'Taxa')
    cwriter.writerow(header)
    for k in sorted(d.keys()):
        d[k].insert(0,k)
        cwriter.writerow(d[k])

相关内容