1

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

  • What should happen if there is a third line like 20,2,385,f2 in the input file? – FedonKadifeli Jun 7 at 10:15
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
0

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

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.