How to parse Stdout as a mix of CSV and JSON?

Question

I'm currently working on a class that has us submit our code to an auto-grader and then returns our results. The format it returns is kind of hard to visually parse, so I would like to write a script that I can use in a pipe to make it easier to read.

Here is the output of the auto-grader:

Problem,Correct?,Correct Answer,Agent's Answer
"Challenge Problem B-04",0,4,-1
"Basic Problem B-12",0,1,-1
"Challenge Problem B-05",0,6,-1
"Challenge Problem B-07",0,6,-1
"Challenge Problem B-06",0,3,-1
"Basic Problem B-11",0,1,-1
"Basic Problem B-10",0,3,-1
"Challenge Problem B-03",0,3,-1
"Challenge Problem B-02",0,1,-1
"Challenge Problem B-01",0,6,-1
"Challenge Problem B-09",0,4,-1
"Challenge Problem B-08",0,4,-1
"Basic Problem B-08",0,6,-1
"Basic Problem B-09",0,5,-1
"Basic Problem B-04",0,3,-1
"Basic Problem B-05",0,4,-1
"Basic Problem B-06",0,5,-1
"Basic Problem B-07",0,6,-1
"Basic Problem B-01",0,2,-1
"Basic Problem B-02",0,5,-1
"Basic Problem B-03",0,1,-1
"Challenge Problem B-10",0,4,-1
"Challenge Problem B-11",0,5,-1
"Challenge Problem B-12",0,1,-1
{
    "Basic Problems B": {
        "Incorrect": "0",
        "Skipped": "12",
        "Correct": "0",
        "Set": "Basic Problems B"
    },
    "Challenge Problems B": {
        "Incorrect": "0",
        "Skipped": "12",
        "Correct": "0",
        "Set": "Challenge Problems B"
    }
}

It's a mix of comma separated values and JSON. It would be nice to place this all in a pretty table that I can read.

Currently, I have something like

python submit.py --provider gt --assignment error-check | column -t -s, | less -S

Which outputs:

{
    "Basic Problems B": {
        "Incorrect": "0",
        "Skipped": "12",
        "Correct": "0",
        "Set": "Basic Problems B"
    },
    "Challenge Problems B": {
        "Incorrect": "0",
        "Skipped": "12",
        "Correct": "0",
        "Set": "Challenge Problems B"
    }
}
Problem                   Correct?  Correct Answer  Agent's Answer
"Challenge Problem B-04"  0         4               -1
"Basic Problem B-12"      0         1               -1
"Challenge Problem B-05"  0         6               -1
"Challenge Problem B-07"  0         6               -1
"Challenge Problem B-06"  0         3               -1
"Basic Problem B-11"      0         1               -1
"Basic Problem B-10"      0         3               -1
"Challenge Problem B-03"  0         3               -1
"Challenge Problem B-02"  0         1               -1
"Challenge Problem B-01"  0         6               -1
"Challenge Problem B-09"  0         4               -1
"Challenge Problem B-08"  0         4               -1
"Basic Problem B-08"      0         6               -1
"Basic Problem B-09"      0         5               -1
"Basic Problem B-04"      0         3               -1
"Basic Problem B-05"      0         4               -1
"Basic Problem B-06"      0         5               -1
"Basic Problem B-07"      0         6               -1
"Basic Problem B-01"      0         2               -1
"Basic Problem B-02"      0         5               -1
"Basic Problem B-03"      0         1               -1
"Challenge Problem B-10"  0         4               -1
"Challenge Problem B-11"  0         5               -1
"Challenge Problem B-12"  0         1               -1

This gets me most of the way there. Now I'm wondering if there is a way I can handle the JSON?

I can't rely on splitting the output at a certain line-number, but am thinking I can segment the output when it first finds a {.

I would like to do this as minimal as possible so I can share it with classmates. So the less dependencies the better.

I've seen other JSON parsing posts suggesting to use external codes.

An ideal output would look something like:

Problem                   Correct?  Correct Answer  Agent's Answer
"Challenge Problem B-04"  0         4               -1
"Basic Problem B-12"      0         1               -1
"Challenge Problem B-05"  0         6               -1
"Challenge Problem B-07"  0         6               -1
"Challenge Problem B-06"  0         3               -1
"Basic Problem B-11"      0         1               -1
"Basic Problem B-10"      0         3               -1
"Challenge Problem B-03"  0         3               -1
"Challenge Problem B-02"  0         1               -1
"Challenge Problem B-01"  0         6               -1
"Challenge Problem B-09"  0         4               -1
"Challenge Problem B-08"  0         4               -1
"Basic Problem B-08"      0         6               -1
"Basic Problem B-09"      0         5               -1
"Basic Problem B-04"      0         3               -1
"Basic Problem B-05"      0         4               -1
"Basic Problem B-06"      0         5               -1
"Basic Problem B-07"      0         6               -1
"Basic Problem B-01"      0         2               -1
"Basic Problem B-02"      0         5               -1
"Basic Problem B-03"      0         1               -1
"Challenge Problem B-10"  0         4               -1
"Challenge Problem B-11"  0         5               -1
"Challenge Problem B-12"  0         1               -1

Set                   Incorrect Skipped Correct
Basic Problems B      0         12      0
Challenge Problems B  0         12      0

terdon · Accepted Answer · 2020-01-11 00:00:39Z

Separating the JSON from the rest is quite easy. This will give you the non JSON only:

python submit.py --provider gt --assignment error-check | sed '/{/,$d'

And this, only the JSON:

python submit.py --provider gt --assignment error-check | sed -n '/{/,$p'

To illustrate, I have saved your example input as file and:

$ sed '/{/,$d' file
Problem,Correct?,Correct Answer,Agent's Answer
"Challenge Problem B-04",0,4,-1
"Basic Problem B-12",0,1,-1
"Challenge Problem B-05",0,6,-1
"Challenge Problem B-07",0,6,-1
"Challenge Problem B-06",0,3,-1
"Basic Problem B-11",0,1,-1
"Basic Problem B-10",0,3,-1
"Challenge Problem B-03",0,3,-1
"Challenge Problem B-02",0,1,-1
"Challenge Problem B-01",0,6,-1
"Challenge Problem B-09",0,4,-1
"Challenge Problem B-08",0,4,-1
"Basic Problem B-08",0,6,-1
"Basic Problem B-09",0,5,-1
"Basic Problem B-04",0,3,-1
"Basic Problem B-05",0,4,-1
"Basic Problem B-06",0,5,-1
"Basic Problem B-07",0,6,-1
"Basic Problem B-01",0,2,-1
"Basic Problem B-02",0,5,-1
"Basic Problem B-03",0,1,-1
"Challenge Problem B-10",0,4,-1
"Challenge Problem B-11",0,5,-1
"Challenge Problem B-12",0,1,-1

And

$ sed -n '/{/,$p' file
{
    "Basic Problems B": {
        "Incorrect": "0",
        "Skipped": "12",
        "Correct": "0",
        "Set": "Basic Problems B"
    },
    "Challenge Problems B": {
        "Incorrect": "0",
        "Skipped": "12",
        "Correct": "0",
        "Set": "Challenge Problems B"
    }
}

Now, you already deal with the non-JSON perfectly well, so I won't change that. Ideally, the JSON data should be parsed using a JSON parser, like jq. Sadly, I don't know enough jq to do this properly, so the best I could come up with is this, rather inelegant, solution. At least it does do what you want (replace cat file with your python submit.py --provider gt --assignment error-check command:

$ cat file | sed -n 's/[,"]//g; s/^ *//; /{/,$p'  | tac | awk -F': ' 'BEGIN{printf "%-30s%-10s%-10s%-10s\n", "Set", "Incorrect", "Skipped", "Correct"} NF==2 && !/\{/{if($1=="Set"){set=$2;data[set]["Incorrect"] = 0;data[set]["Skipped"] = 0;data[set]["Correct"] = 0;} data[set][$1]=$2}END{for(set in data){printf "%-30s%-10s%-10s%-10s\n", set,data[set]["Incorrect"],data[set]["Skipped"],data[set]["Correct"]}}' 
Set                           Incorrect Skipped   Correct   
Challenge Problems B          0         12        0         
Basic Problems B              0         12        0

Putting all this together in a shell script gives:

#!/bin/bash

tmpFile=$(mktemp)
python submit.py --provider gt --assignment error-check > "$tmpFile";

sed '/{/,$d' "$tmpFile" | column -t -s, 
sed -n 's/[,"]//g; s/^ *//; /{/,$p' "$tmpFile" |
  tac |
  awk -F': ' '
    BEGIN{
      printf "%-30s%-10s%-10s%-10s\n", "Set", "Incorrect", "Skipped", "Correct"
    }
    NF==2 && !/\{/{
      if($1=="Set"){
         set=$2;
         data[set]["Incorrect"] = 0;
         data[set]["Skipped"] = 0;
         data[set]["Correct"] = 0;
      } 
      data[set][$1]=$2
    }
    END{
       for(set in data){
         printf "%-30s%-10s%-10s%-10s\n", set, 
                                     data[set]["Incorrect"], 
                                     data[set]["Skipped"], 
                                     data[set]["Correct"]}
    }' 
rm "$tmpFile"

Which produces the following output:

$ foo.sh
Problem                   Correct?  Correct Answer  Agent's Answer
"Challenge Problem B-04"  0         4               -1
"Basic Problem B-12"      0         1               -1
"Challenge Problem B-05"  0         6               -1
"Challenge Problem B-07"  0         6               -1
"Challenge Problem B-06"  0         3               -1
"Basic Problem B-11"      0         1               -1
"Basic Problem B-10"      0         3               -1
"Challenge Problem B-03"  0         3               -1
"Challenge Problem B-02"  0         1               -1
"Challenge Problem B-01"  0         6               -1
"Challenge Problem B-09"  0         4               -1
"Challenge Problem B-08"  0         4               -1
"Basic Problem B-08"      0         6               -1
"Basic Problem B-09"      0         5               -1
"Basic Problem B-04"      0         3               -1
"Basic Problem B-05"      0         4               -1
"Basic Problem B-06"      0         5               -1
"Basic Problem B-07"      0         6               -1
"Basic Problem B-01"      0         2               -1
"Basic Problem B-02"      0         5               -1
"Basic Problem B-03"      0         1               -1
"Challenge Problem B-10"  0         4               -1
"Challenge Problem B-11"  0         5               -1
"Challenge Problem B-12"  0         1               -1
Set                           Incorrect Skipped   Correct   
Challenge Problems B          0         12        0         
Basic Problems B              0         12        0

It feels hacky though, and I hope someone can come up with a cleaner solution with dedicated JSON parsers.

Steeldriver was nice enough to give a proper jq solution in a comment, so if we incorporate that, we get the far simpler (and safer):

#!/bin/bash

tmpFile=$(mktemp)
python submit.py --provider gt --assignment error-check > "$tmpFile";

sed '/{/,$d' "$tmpFile" | column -t -s, 
sed -n '/{/,$p' "$tmpFile" | 
  jq -r '["Set","Incorrect","Skipped","Correct"], (.[] | [.Set,.Incorrect,.Skipped,.Correct]) | @tsv'
 rm "$tmpFile"

Perhaps something like jq -r '["Set","Incorrect","Skipped","Correct"], (.[] | [.Set,.Incorrect,.Skipped,.Correct]) | @tsv' for the json-to-table part? (Largely hacked together from Stack Overflow, in particular How to add a header to CSV export in jq? ) — steeldriver, Jan 10, 2020 at 20:02
@steeldriver ooh, nice one, thanks! I took the liberty of adding it to my answer, but please let me know if you'd rather post your own (and perhaps explain that thing which is beyond by ken) and I'll happily remove it. — terdon, Jan 11, 2020 at 0:01
No problem... I'm not sure I could explain it if I tried, my approach to jq is pretty much just to scattergun filters together until something "works" ;) — steeldriver, Jan 11, 2020 at 0:14

aborruso · Accepted Answer · 2020-01-11 14:06:44Z

Using Miller (https://github.com/johnkerl/miller) and running

# get the CSV and transform it into a pretty print table
<input grep -P '^("|\w)' | mlr --c2p cat >out
# add a carriage return
echo "" >> out
# convert the json into a pretty print table and add it to the output
<input grep -vP '^("|\w)'  | mlr --j2p cat -n then reshape -r "(Basi|Chal)" -o i,v \
then nest --explode --values --across-fields --nested-fs ":" -f i \
then reshape -s i_2,v \
then cut -x -f i_1,n \
then reorder -f Set >>out

you will have

Problem                Correct? Correct Answer Agent's Answer
Challenge Problem B-04 0        4              -1
Basic Problem B-12     0        1              -1
Challenge Problem B-05 0        6              -1
Challenge Problem B-07 0        6              -1
Challenge Problem B-06 0        3              -1
Basic Problem B-11     0        1              -1
Basic Problem B-10     0        3              -1
Challenge Problem B-03 0        3              -1
Challenge Problem B-02 0        1              -1
Challenge Problem B-01 0        6              -1
Challenge Problem B-09 0        4              -1
Challenge Problem B-08 0        4              -1
Basic Problem B-08     0        6              -1
Basic Problem B-09     0        5              -1
Basic Problem B-04     0        3              -1
Basic Problem B-05     0        4              -1
Basic Problem B-06     0        5              -1
Basic Problem B-07     0        6              -1
Basic Problem B-01     0        2              -1
Basic Problem B-02     0        5              -1
Basic Problem B-03     0        1              -1
Challenge Problem B-10 0        4              -1
Challenge Problem B-11 0        5              -1
Challenge Problem B-12 0        1              -1

Set                  Incorrect Skipped Correct
Basic Problems B     0         12      0
Challenge Problems B 0         12      0

Ed Morton · Accepted Answer · 2020-01-13 04:26:28Z

$ cat tst.awk
BEGIN { FS=","; OFS="\t" }
/{/ { FS="(^|\":)[[:space:]]+\"|\",?" }
FS == "," { $1=$1; print; next }
{ f[$2] = $3 }
/}/ {
    if ( !doneHdr++ ) {
        print "Set", "Incorrect", "Skipped", "Correct"
    }
    print f["Set"], f["Incorrect"], f["Skipped"], f["Correct"]
}

.

$ awk -f tst.awk file | column -s$'\t' -t
Problem                   Correct?   Correct Answer  Agent's Answer
"Challenge Problem B-04"  0          4               -1
"Basic Problem B-12"      0          1               -1
"Challenge Problem B-05"  0          6               -1
"Challenge Problem B-07"  0          6               -1
"Challenge Problem B-06"  0          3               -1
"Basic Problem B-11"      0          1               -1
"Basic Problem B-10"      0          3               -1
"Challenge Problem B-03"  0          3               -1
"Challenge Problem B-02"  0          1               -1
"Challenge Problem B-01"  0          6               -1
"Challenge Problem B-09"  0          4               -1
"Challenge Problem B-08"  0          4               -1
"Basic Problem B-08"      0          6               -1
"Basic Problem B-09"      0          5               -1
"Basic Problem B-04"      0          3               -1
"Basic Problem B-05"      0          4               -1
"Basic Problem B-06"      0          5               -1
"Basic Problem B-07"      0          6               -1
"Basic Problem B-01"      0          2               -1
"Basic Problem B-02"      0          5               -1
"Basic Problem B-03"      0          1               -1
"Challenge Problem B-10"  0          4               -1
"Challenge Problem B-11"  0          5               -1
"Challenge Problem B-12"  0          1               -1
Set                       Incorrect  Skipped         Correct
Basic Problems B          0          12              0
Challenge Problems B      0          12              0
Challenge Problems B      0          12              0

Stack Exchange Network

How to parse Stdout as a mix of CSV and JSON?

3 Answers 3

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged
shell-script
text-processing
awk
csv
json
.

Hot Network Questions

How to parse Stdout as a mix of CSV and JSON?

3 Answers 3

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged shell-scripttext-processingawkcsvjson.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
shell-script
text-processing
awk
csv
json
.