Create a new column based on the values of other one-CodePudding

I have the file data.csv with information about the customer experience in one shop and the total spent in the shop. Each customer gives points depending on their customer experience so the dataset looks like this:

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

I would like to create a new column called "experience" where its values depend on the "ranking" column. For example:

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"

So the desired output is:

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

I tried with the following code but is not working:

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

CodePudding user response：

You are really close. Just OFS missing. To avoid having to escape quotes you can create a script like this

#! /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}

give permission

chmod  x myscript

and run it

./myscript data.csv