☰

使用另一个 csv 查找表中的值将列附加到 csv 文件

tag-icon • tag-icon text-processing csv
使用另一个 csv 查找表中的值将列附加到 csv 文件

我对 Unix 很陌生；在这里寻找我的问题的解决方案。没有任何代码可以开始:)只有问题和所需的解决方案。

我希望自动化以下过程（即在 Excel 中复制 Vlookup 函数）：

  • 系统生成多个CSV文件，不同的列数、分隔符。

所有生成的文件都包含一个键（事务#） - 可以位于不同文档之间的不同列中。

编辑：假设摘录未按交易#排序。

例如，表1：

field1,field2,field3,Transaction#,field4
ABC,ABC,ABC,1,CFG
ABC,ABC,ABC,2,CFG
ABC,ABC,ABC,3,CFG

例如，表2：

field1;Transaction#;field3;field4;field5
ABC;1;ABC;ABC;CFG
ABC;2;ABC;ABC;CFG
ABC;3;ABC;ABC;CFG

  • 我有一个查找表，如下所示：

    Transaction#    New#
    1            122
    2            123
    3            124

  • 我需要在每个 csv 表的末尾添加带有标题 New# 的列：

编辑：假设并非查找表中的所有事务#都存在于输入表中；并且并非输入表中的所有事务#都出现在查找表中。

例如，表1：

field1,field2,field3,Transaction#,field4,new#
ABC,ABC,ABC,1,CFG,122 
ABC,ABC,ABC,2,CFG,123
ABC,ABC,ABC,3,CFG,124

例如，表2：

field1;Transaction#;field3;field4;field5;new#
ABC;1;ABC;ABC;CFG;122
ABC;2;ABC;ABC;CFG;123
ABC;3;ABC;ABC;CFG;124

答案1

我假设您的表和查找文件都是 CSV，它们具有相同的分隔符，并且使用相同的引用约定。如果没有，您需要首先通过其他方式将它们标准化。

我还将假设查找文件足够小，可以在内存中读取。如果不是，那么您可能应该将数据转换为 SQL。

有了这些假设，您可以使用awk：

awk -F , -v OFS=, -v col=4 '
    NR == 1 { next }
    NR == FNR {
        n[$1] = $2
    }
    NR != FNR {
        NF++
        $NF = FNR == 1 ? "new" : n[$col]
        print
    }' lookup.csv Table1.csv

您可以调整-F、OFS和col以上以匹配 CSV 分隔符和表中的相关列。

答案2

我认为文本处理工具无法胜任这项任务。相反，我建议使用适当的语言来处理 CSV 文件。

这是 R 中的一个提案（http://r-project.org，如果你不知道的话，很难有效地进行谷歌搜索）。

#!/usr/bin/Rscript
args <- commandArgs(TRUE)

# Here, we read each table passed as argument on the commandline
tablenames <- list()
for (tablename in args) {
    header <- readLines(tablename, n=1)
    # we try to detect the separator (the character that surrounds "Transaction#")
    # That doesn't work if you use multi-characters separators
    sep <- sub(".*(.)Transaction#.*","\\1",header)
    if (nchar(sep[1]) != 1) {
        sep <- sub(".*Transaction#(.).*","\\1",header)
    }
    if (nchar(sep[1]) != 1) {
        print(paste0("Could not detect separator around column 'Transaction#' in file ",tablename))
    } else {
        # each table where the separator is succesfully detected
        # is added to a list of tablenames
        tablenames[[tablename]] <- list(name=tablename,sep=sep)
    }
}

# we parse each table in the list of tablenames
tables <- lapply(tablenames, function(tab) { read.csv(tab$name, check.names=FALSE, sep=tab$sep) })

# we also parse the lookup table, which has a different format
lookup <- read.table("lookup",header=TRUE,check.names=FALSE,comment.char="")

# then for each table, we add the new column
for (i in 1:length(tablenames)) {
  # This line magic:
  # - it adds a new column called "New#" to the table
  # - this column is populated from table lookup
  # - lines in lookup are filtered and ordered so that column "Transaction#" matches columns "Transaction#" in the table
  # - we add only column "New#" from lookup to the table
  tables[[i]][,"New#"] <- lookup[match(tables[[i]][,"Transaction#"],lookup[,"Transaction#"]),"New#"]

  # we write back the table under the name "new <original name>"
  write.table(tables[[i]], file=paste("new",tablenames[[i]]$name), sep=tablenames[[i]]$sep, quote=FALSE, row.names=FALSE)
}

您必须从表所在的目录调用此脚本：

./script table1 table2 ...

其中table1, table2,... 是表的文件​​名。在编写脚本时，查找表必须位于文件中lookup，但这可以轻松更改。

例如 ：

表格1

field1,field2,ffield1,field2,field3,Transaction#,field4
 ABC,ABC,ABC,1,CFG
 ABC,ABC,ABC,3,CFG

表2

field1;Transaction#;field3;field4;field5
ABC;2;ABC;ABC;CFG
ABC;1;ABC;ABC;CFG
ABC;3;ABC;ABC;CFG

我们跑./script.R table1 table2。

抬头

Transaction#   New#
    1            122
    2            123
    3            124

结果是：

新表1

field1,field2,field3,Transaction#,field4,New#
 ABC,ABC,ABC,1,CFG,122
 ABC,ABC,ABC,3,CFG,124

新表2

field1;Transaction#;field3;field4;field5;New#
ABC;2;ABC;ABC;CFG;123
ABC;1;ABC;ABC;CFG;122
ABC;3;ABC;ABC;CFG;124

相关内容