0

I need help with a bash script that does following:

| section | category | description | date      | metric | value    |
| --------| ---------|-------------|-----------|--------|----------|
| y       | testing  |    abc      |03/02/2022 |        |  14845.0 |
| x       | row      |    pqy      | 01/16/2022|        | 12565.0  |
| x       | row      |    xyz      | 02/21/2021|        | 13888.0  |
| x       | row      |    xyz      | 10/04/2020|        | 18160.0  |

I want to return the value column in descending order along with the date when I search for description of xyz it should only return the values correspond to that and only the highest value related to one date. there can be duplicate dates too. for example:

10/04/2020 18160
02/21/2021 13888 

In above case I have description of xyz and it returns value in descending order along with date.

What I have tried: I have been stuck on this

awk '$2 ~ /xyz/ {print $3}' covid19_cases_demographics_tests_2022-03-21.csv(this is my csv file)

Please guide me correct direction with some example where to start as I am new to shell scripting and unix stuff.Thank you

1 Answer 1

1

Based on the columns you have displayed, you are using the wrong indexes in awk

It looks like you are assuming that the first column (section) will be $0, 2nd col (category) will be $1, 3rd col (description) will be $2. But awk actually uses $0 to represent the entire line. So to search on the description column, you would need to use $3 in the matching logic and $6 to print from the value column. Assuming that you have a proper csv file (not some other format), you may also need to set the -F option to specify custom field delimiter such as comma. But note that this has an issue where more complex csv files that contain quoted strings will probably still break this.

awk -F, '$3 ~ /xyz/ {print $6}' file.csv

If you have more complex csv files that have quoted strings which potentiality contain things like spaces and even commas within the strings that are not used as delimiters, then awk (as well as grep and sed) are probably not the best tools for the job. In this case, the csvtool utility is context aware and should work better. On Fedora, the utility is part of the ocaml-csv package but the package may be named differently on other distros.

In this case, you can write a function to print the column you want, export the function, and then have csvtool pass the columns to it. A bit more work but safer if you have more complex fields.

function printifcol {
    local descCol="$3";
    local valueCol="$6";
    if [[ "xyz" == "${descCol}" ]]; then
        echo "${valueCol}";                               
    fi                       
}
export -f printifcol;
csvtool call printifcol file.csv

In either case, you can pipe the output to the sort command with -r (reverse) option to get things in descending order:

awk -F, '$3 ~ /xyz/ {print $6}' file.csv | sort -r
 
# assumes function was already exported
csvtool call printifcol file.csv | sort -r
3
  • thank you for your help. I works great only had to change one thing sort -nr in the end. I also tried this awk -F, '$3 ~ /Cases of COVID-19 in Colorado by Date Reported to the State/ {print $4,$6}' input.csv | sort -k 2nr | head -n 20 In terms of performance is awk or grep would be better ? I would say faster string search works better with grep. Mar 24, 2022 at 18:07
  • Glad it was useul :-) I'm not sure on the performance aspect; you'd probably need to test that with a sample set of your data to get a decent idea. You can use the time command to get some metrics there, e.g. time awk ... or time grep .... This will run the command following time and then print out some timing metrics. By default, it prints time as "real", "user", and "system"; "real" is the indication of the total amount of real time spent. I generally use that metric and ignore the other two. I think for a fair comparison, you need to do grep + sed (e.g time $(grep ... | sed ...)
    – zpangwin
    Mar 25, 2022 at 1:09
  • ^ the reason being that in the above, you have awk doing multiple tasks (matching & substitution) whereas grep by itself only does the matching part. doing grep + sed together does add the overhead of piping to another command, but I'm not sure which is overall the better performer. The things I generally use them for have small data sets so I don't normally notice any difference either way.
    – zpangwin
    Mar 25, 2022 at 1:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .