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!

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. Mar 16 at 15:35

2 Answers 2

0

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.