Convert rows into columns in bash shell script

Question

I need to write a script to convert the below input into desired output. The input is a csv file

Sample input

Id, sid,value,name
20,2,245,f1
20,2,345,f2
20,3,445,f3

Sample output

Id,sid,f1,f2,f3
20,2,245,345,
20,3,,,445

The third and fourth columns in each row needs to be converted into columns.

For example since first and second column of first and second row are same, the values of f1 and f2 should be 3rd column of each row and f3 should be null

Answer 1

You can use csvsql from csvkit.

csvsql -S --query '
    select
    "Id",
    sid,
    max(case when name="f1" then value else null end) as f1,
    max(case when name="f2" then value else null end) as f2,
    max(case when name="f3" then value else null end) as f3
    from file
    group by "Id",sid
'  file

• Note that your column is called " sid" with leading space, thus we need -S (skip initial spaces) --- or use " sid" in the query.
• Note, that I used max() to determine which value to use if multiple of the same Id/sid pairs should be found. You might want to use avg() or min() or so (See @FedonKadifeli's comment for an example).

Output:

Id,sid,f1,f2,f3
20,2,245,345,
20,3,,,445

Answer 2

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

mlr --csv reshape -s name,value then unsparsify input.csv

you will have

Id, sid,f1,f2,f3
20,2,245,345,
20,3,,,445