2

I'm new to shell commands. I'm kinda struggling with this question based on a CSV dataset fbnews.csv.

The CSV dataset looks like this:

D,E,F,   message,                 score,    A,B,C,   ID
d,e,f,  Let's read a book,           24,    a,b,c,    1
j,k,l,   Read this book,             39,    d,e,f,    2
m,n,o,   Have you read this book?,   15,    g,h,i,    3

This is just a sample. The original dataset contains 20,000 thousand rows and 20 columns.

From this dataset,

  1. Find the rows that have the word 'read' in them and have score more than 20. 2. From these rows, print only the score and ID columns, sorted based on score value.
  2. Store these sorted columns in a text file.

The expected output is:

Score   ID
24      1
39      2

How can I do this using shell commands?

0
2

using Miller (https://github.com/johnkerl/miller) and starting from

D,E,F,message,score,A,B,C,ID
d,e,f,Let's read a book,24,a,b,c,1
j,k,l,Read this book,39,d,e,f,2
m,n,o,Have you read this book?,15,g,h,i,3

and running

mlr --csv filter -S '$message=~"(r|R)ead" && $score>20' then cut -f score,ID input.csv >output.csv

you will have

score,ID
24,1
39,2

Some details about the command:

  • --csv, to set input and output format
  • filter -S '$message=~"(r|R)ead" && $score>20' to apply your filter
  • cut -f score,ID to select your fields

If you have a wrong CSV, with more cells than heading columns, as this one

D,E,F,message,score,A,B,C,ID
d,e,f,Let's read a book,24,a,b,c,1
j,k,l,Read this book,39,d,e,f,2,a wrong cell,another wrong cell
m,n,o,Have you read this book?,15,g,h,i,3

you can apply ragged option and running

mlr --csv --ragged unsparsify then filter -S '$message=~"(r|R)ead" && $score>20' then cut -f score,ID input.csv>output.csv

However if you have a problematic CSV, the best it would be tho share it here entirely

2
  • 1
    hi @aborruso, I tired your code. I installed miller. It is giving an error header/data length mismatch. – klam Sep 28 '19 at 3:32
  • hi @klam I have added a note to my answer, useful when you have a wrong CSV – aborruso Sep 28 '19 at 6:52
1

I'm learning awk, so I expect the feedback of wise ones:

cat file | tr -s ' ' | awk -F, 'BEGIN { print "Score ID" } tolower($4) ~ /read/ { if($5 >= 20) print $5,$9 }' > output

In this case, to be able to use the format of OP, change al spaces into just one

tr -s ' '

Use comma as delimiter:

-F,

To make the comparison case insensitive:

tolower($4)

Fourth column has the string "read"

tolower($4) ~ /read/

If the value of fifth column is equal or greater than 20, print:

if($5>=20) print $5,$9

Add the header (I'm trying right now to do this with awk)

 BEGIN { print "Score ID" }

Ouput

score ID
 24  1
 39  2
5
  • Hi Guillermo, thanks for the try. It is giving me an error of "Invalid multi-byte data detected.There may be a mismatch between your data and locale". – klam Sep 26 '19 at 1:58
  • I tried it without the sed command, it gave ouput. But the output txt file doesn't have headers nor it is sorted. – klam Sep 26 '19 at 3:13
  • 1
    A tweak on Guillermo awk -F, '/[rR]ead/ {if ($5>=20) print $5,$9}' file | column -t -N Score,ID – bu5hman Sep 27 '19 at 5:33
  • hi @bu5hman, It is giving an error. the error is column: -N invalid option. When i removed -N and tried it is giving another error like column: Score,ID no such file or directory. – klam Sep 27 '19 at 11:32
  • the column command is simply a utility that tidies up terminal output into tables, puts headers etc. If you don't have it installed then it will give an error for sure. It was just meant as an alternative to guillermo's use of cat and tr to deal with the whitespace. 'column' should be in your repository if you want to give it a try. – bu5hman Sep 27 '19 at 14:45
0

I don't use shell script enough, but I often do things like that in other languages. I will give some things to help you to organize your searches.

1 - You need parse the csv file

You can se how to parse a csv file in this link: https://stackoverflow.com/questions/4286469/how-to-parse-a-csv-file-in-bash

2 - Your need to get lines that matches with word 'read'

You can use regular expression like that and adapt some criteria to catch score greater than 20.2:

/\b(\w*read\w*)\b/g

Put it in this site, to get information about the expression: https://regexr.com/

3 - You need sort the output based in a criteria

You can use sort command to do it. It's easier than assign to an array and sort this array.

4 - Redirect output

You can easy redirect shell output to a file with something like that 'script.sh > my_output.txt'. Or do it inside your script 'var > output.txt'

0

A little awk, a little regex, and tidy the whitespace up by piping to column

awk -F',' '{if ( $4 ~ /[Rr]ead/ &&  $5 > 20 || NR==1) print $5, $9}' data.csv | column -t

Explanation....after setting the field delimiter to a , with -F','

....if the 4th field has a regex match ~ with 'Read' or 'read' and && the 5th field is > 20 or ||we are on the first row (with the titles) NR==1 then print out the columns you are interested in......

Just for fun

If you know the column headers but are too lazy to count....

Load the headers into an associative array

declare -A HEADS=( [mess]=mess [id]=ID [score]=score )

.....awk out the column indices from the first row of your data file into the array

for j in "${!HEADS[@]}"; do HEADS[$j]=$(awk -F',' -v s=${HEADS[$j]} 'NR==1 {for (i=1; i<=NF; ++i) { if ($i ~ s ) print i }}' data.csv) ; done

... back to the top just injecting the indices into awk as variables

awk -v mess=${HEADS[mess]} -v score=${HEADS[score]} -v id=${HEADS[id]} -F',' '{if ( $mess ~ /[Rr]ead/ &&  $score >20 || NR==1) print $score, $id}' data.csv | column -t

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.