2个文件之间的vlookup函数并在终止时追加匹配项
2个文件之间的vlookup函数并在终止时追加匹配项
提问于 2018-10-11 06:03:39
需要从具有多个条目的两个不同文件执行vlookup：
cat file1.csv
aaaaaaa;24/09/2018;06/09/2018;1;89876768
bbbbbbb;15/09/2018;03/09/2018;2;76958489
ccccccc;10/09/2018;28/08/2018;3;57848472
ddddddd;22/09/2018;08/09/2018;4;17929730
eeeeeee;19/09/2018;30/08/2018;5;18393770cat file2.csv
20180901;abc;1
20180901;sdf;2
20180904;jhh;2
20180905;skf;3
20180911;asf;2
20180923;ghf;4
20180925;asb;4
20180918;mnj;3除了file1.csv之外，第四列是file2.csv中的第三列的标识符。
所需的输出为：
aaaaaaa;24/09/2018;06/09/18;1;89876768;20180901
bbbbbbb;15/09/2018;03/09/18;2;76958489;20180901;20180904;20180911
ccccccc;10/09/2018;28/08/18;3;57848472;20180905;20180918
ddddddd;22/09/2018;08/09/18;4;17929730;20180923;20180925
eeeeeee;19/09/2018;30/08/18;5;18393770;unknown回答 1
Stack Overflow用户
回答已采纳
发布于 2018-10-11 08:46:59
你能不能试着跟上。
awk 'BEGIN{FS=OFS=";"}FNR==NR{a[$NF]=a[$NF]?a[$NF] OFS $1:$1;next} {print ($4 in a)?$0 OFS a[$4]:$0 OFS "unknown"}' file2.csv file1.csv输出如下所示。
aaaaaaa;24/09/2018;06/09/2018;1;89876768;20180901
bbbbbbb;15/09/2018;03/09/2018;2;76958489;20180901;20180904;20180911
ccccccc;10/09/2018;28/08/2018;3;57848472;20180905;20180918
ddddddd;22/09/2018;08/09/2018;4;17929730;20180923;20180925
eeeeeee;19/09/2018;30/08/2018;5;18393770;unknown代码的解释：
awk '
BEGIN{ ##Starting BEGIN section for awk here.
FS=OFS=";" ##Setting values for FS and OFS as semi colon.
} ##Closing block for BEGIN section here.
FNR==NR{ ##Checking condition FNR==NR which will be TRUE when first Input_file named file2.csv is being read.
a[$NF]=a[$NF]?a[$NF] OFS $1:$1 ##Creating an array named a whose index is $NF and value is $1 and concatenating its own value with same index.
next ##next will skip all further statements from here.
} ##Closing block for FNR==NR condition here.
{
print ($4 in a)?$0 OFS a[$4]:$0 OFS "unknown" ##These statements will execute when 2nd Input_file is being read and printing value of $0 with condition if $4 is present in array a then concatenate its value with current line else concatenate unknown with it.
}' file2.csv file1.csv ##Mentioning Input_file names here.页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52749390
复制相关文章
相似问题