我有一个包含以下数据的文件
"col1","col2","col3","col4","col5","col6"
"CACR","0","SO2","50","6","2.0"
"FF","15","CO2","20","4","3"
"CACR","25","NOx","30","10",
"CACR","50","CO","40","5","0"我想找出包含CACR值的每一行,然后使用Linux终端将col2、col4、col5和col6的值除以col6各自的单元格(如果col6的单元格为0或空白,则忽略除法计算)。因此,我的输出如下所示:
"col1","col2","col3","col4","col5","col6"
"CACR","0","SO2","25","3","1"
"CACR","25","NOX","30","10",
"CACR","50","CO","40","5","0"我正在尝试使用grep和awk
grep CACR file.csv | awk -F "," '$6 != 0; $6 == "" {$2 = $2/$6; $4= $4/$6; $5 = $5/$6; $6 = $6/$6}1' 但无法获得任何所需的输出。
发布于 2021-07-28 00:40:54
正如在comment中所概述的,主要问题是字段两边的双引号意味着当字段被解释为数字(例如,使用除法)时,该值为零。我认为你需要编写Awk函数来删除和恢复双引号。有了这些,它主要是一个SMOP -一个简单的编程问题。
这是我的版本。它可以写得更简洁(更少的换行符,更少的空格),但我更喜欢清晰而不是简洁。
script.awk
function strip_quotes(s)
{
gsub(/"/, "", s)
return s
}
function add_quotes(s)
{
return sprintf("\"%s\"", s)
}
BEGIN { FS = "," }
NR == 1 { print; next }
$0 !~ /CACR/ { next }
$6 == "" || $6 == "\"0\"" { print; next }
{
div = strip_quotes($6)
printf("%s,%s,%s,%s,%s,%s\n",
$1,
add_quotes(strip_quotes($2) / div),
$3,
add_quotes(strip_quotes($4) / div),
add_quotes(strip_quotes($5) / div),
add_quotes(strip_quotes($6) / div))
}data
"col1","col2","col3","col4","col5","col6"
"CACR","0","SO2","50","6","2.0"
"FF","15","CO2","20","4","3"
"CACR","25","NOx","30","10",
"CACR","50","CO","40","5","0"输出
$ awk -f script.awk data
"col1","col2","col3","col4","col5","col6"
"CACR","0","SO2","25","3","1"
"CACR","25","NOx","30","10",
"CACR","50","CO","40","5","0"
$变体script3.awk
此代码将输出字段分隔符OFS也设置为逗号,并在使用print打印修改后的$0之前重置$2、$4、$5和$6的值。
function strip_quotes(s)
{
gsub(/"/, "", s)
return s
}
function add_quotes(s)
{
return sprintf("\"%s\"", s)
}
BEGIN { FS = ","; OFS = "," }
NR == 1 { print; next }
$0 !~ /CACR/ { next }
$6 == "" || $6 == "\"0\"" { print; next }
{
div = strip_quotes($6)
$2 = add_quotes(strip_quotes($2) / div)
$4 = add_quotes(strip_quotes($4) / div)
$5 = add_quotes(strip_quotes($5) / div)
$6 = add_quotes(strip_quotes($6) / div)
print
}数据验证
两个版本的脚本都可以更严格,验证是否有5列或6列(拒绝具有更多列或更少列的行,或者抱怨它们)。对标题的检查可以坚持6列。检查div是否是非零数可能是明智的。检查$2、$4、$5和$6中的每一个是否都是数字可能是明智的。示例数据中的除数(第6列)很方便;如果数字不是那么简单,您可能需要做一些工作,例如7,其中结果可能有许多小数位。您需要决定这些数字应该如何格式化(默认值可能是OK,也可能不是)。还有必要检查每个字段中的数据是否与正则表达式/^"[^"]*"$/匹配(因此每个值都用双引号括起来)。
尾随空格
规则$6 == "" || $6 == "\"0\"" { print; next }不能很好地处理尾随空格。它可以修改为:
$6 ~ /^[[:space:]]*$/ || $6 == "\"0\"" { print; next }识别尾随空格并将其视为零的。添加以下内容是可能的,也可能是明智的:
if (div == 0) { print; next }在赋值给div之后。如果找到的值为零,则存在问题。也可以抱怨--生成一个错误消息来诊断“格式错误的数据”。
多少验证和错误预防是值得的,这取决于输入数据的不规范程度。如果您正在处理人类生成的数据,您必须处理人类改变规则的倾向,并将不稳定或错误的数据提供给程序,并且您可能需要处理(诊断)意外输入。如果您处理的是机器生成的数据,那么它通常更加统一,并且您可以轻松地完成验证工作。
大多数依赖于正则表达式的解决方案都必须在足够好的工作和不稳定的输入之间取得平衡。输入越不稳定,就越难设计出防炸弹(防傻子)的正则表达式。俗话说得好,“如果你做了idiot-proof,别人只会成为更好的笨蛋”。
https://stackoverflow.com/questions/68531590
复制相似问题