0

I have a CSV file filename.csv with the following contents.

filename.csv:

"Afghanistan","94.0","81.1"
"Bahamas","42.9","43.2"
"Bolivia (Plurinational State of)","86.7","31.9"
"Brazil","76.7","0.0"

I want to compute the difference between two columns (Column 2 - Column 3), and paste the output to the fourth column. After, I want to numerically sort by the fourth column. However, the command I am using is not sorting by the fourth column.

Command I used: awk -F'","' '{ print $0, $2 - $3 }' filename.csv | sort -k4 -n

Output I got:

"Afghanistan","94.0","81.1" 12.9
"Bahamas","42.9","43.2" -0.3
"Bolivia (Plurinational State of)","86.7","31.9" 54.8
"Brazil","76.7","0.0" 76.7

Expected output file:

"Bahamas","42.9","43.2","-0.3"    
"Afghanistan","94.0","81.1","12.9"
"Bolivia (Plurinational State of)","86.7","31.9","54.8"
"Brazil","76.7","0.0","76.7"

Any help is appreciated. Thanks!

Improve this question
1
  • You’ll be better able to solve problems like this if you learn how to identify problems better.  If command foo | sort doesn’t give you the final result you want, look at the output from foo without running sort. (foo here represents your awk command.)  Obviously you are getting value₁,value₂,value₃ value₄ with a space between value₃ and value₄ instead of a comma.  So you don’t even have a fourth column.  (Also, value₄ is not surrounded by quotes.)  sort will never give you the output you want if you don’t give it the input you intend to give it. Commented Mar 16, 2022 at 15:35

2 Answers 2

Reset to default
1

the problem that you're experiencing is that [sort] understands the transition from nonblank to blank as a field transition, as you're not using -t to define your field separator, it's using the default and not the ",", however, in your awk, you forgot to include the "," and in this way if you change to:

awk -F'","' '{ print $0, $2 - $3 }' filename.csv| sort -k2 -n

You'll get:

"Bahamas","42.9","43.2" -0.3
"Bolivia (Plurinational State of)","86.7","31.9" 54.8
"Afghanistan","94.0","81.1" 12.9
"Brazil","76.7","0.0" 76.7

However, this is not what yo'll be expecting due to the comma issue, and this will be addressed through:

awk -F'","'  'BEGIN {IFS=OFS=","}{ print $0, $2 - $3 }' entrada | sort -k4 -n -t","

And getting the expected result:

"Bahamas","42.9","43.2",-0.3
"Afghanistan","94.0","81.1",12.9
"Bolivia (Plurinational State of)","86.7","31.9",54.8
"Brazil","76.7","0.0",76.7

I'll be a little bit reluctant on using this approach since CSV's tend to use commas inside text fields and this is handled quoting such fields, that will oblige to do another parsing to avoid this situation and after that generate some other OFS and use that for sorting and after that returning that OFS to the previous comma.

HTH

Improve this answer
0

I'd recommend using OFS to specify the output field separator so it is also using , rather than space. Then, you can use the -t option of sort to specify the field separator.

awk -F'","'  'OFS=","{ print $0,$2-$3 }' filename.csv | sort -t ',' -k4 -n
Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.