根据其他列的值创建一个新列

Question

我有一个文件data.csv，其中包含关于一家商店的客户体验和在该商店的总消费的信息。每个客户根据他们的客户体验给出分数，所以数据集看起来像这样:

Ranking     Total Spent 
9.5         1245
5           500.58
7.8         1000.69
3           200
6.2         412.45

我要创建一个名为"experience"的新专栏。其中它的值取决于"排名"。列。例如:

ranking >= 8 the new column value will be the string "Very satisfied" 
ranking >= 6 && ranking < 8 the new column value will be "Satisfied"
ranking >= 5 && ranking < 6 the new column value will be "Neutral"
ranking >= 3 && ranking < 5 the new column value will be "Bad"
ranking >= 0 && ranking < 3 the new column value will be "Horrible"

所以期望的输出是:

Ranking     Total Spent      Experience
9.5         1245             Very satisfied
5           500.58           Neutral
7.8         1000.69          Satisfied
3           200              Bad
6.2         412.45           Satisfied

我尝试使用以下代码，但不工作:

awk -F,'NR==1{$3="Experience";print;next} 
$1>=8 {print $0, "Very satisfied";next}
$1>=6 && $1<8 {print $0, "Satisfied";next}
$1>=5 && $1<6 {print $0, "Neutral";next}
$1>=3 && $1<5 {print $0, "Bad";next}
$1>=0 && $1<3 {print $0, "Horrible";next}' data.csv

Answer 1

你真的很接近了。只缺OFS。为了避免转义引号，您可以创建如下脚本

#! /usr/bin/awk -f
NR==1 {FS=","; OFS="t"; $2="Experience";print;next}
$1>=8 {print $0, "Very satisfied";next}
$1>=6 && $1<8 {print $0, "Satisfied";next}
$1>=5 && $1<6 {print $0, "Neutral";next}
$1>=3 && $1<5 {print $0, "Bad";next}
$1>=0 && $1<3 {print $0, "Horrible";next}

给予许可

chmod +x myscript

并运行

./myscript data.csv