Data pivot with awk

Question

I'm trying to pivot a file using awk. This is an example of my input File:

VarName;TimeString;VarValue;Validity;Time_ms
A;23.11.201215:03:53;1;1;41236627696,7593
C;23.11.201215:03:53;2;1;41236627696,7593
D;23.11.201215:03:53;3;1;41236627696,7593
A;23.11.201215:04:53;31;1;41236628391,2037
B;23.11.201215:04:53;12;1;41236628391,2037
C;23.11.201215:04:53;1;1;41236628391,2037
D;23.11.201215:05:53;8;1;41236629097,2222
B;23.11.201215:05:53;7;1;41236629097,2222
C;23.11.201215:05:53;15;1;41236629097,2222

And this is my desired output:

TimeString;Time_ms;A;B;C;D
23.11.201215:03:53;41236627696,7593;1;;2;3
23.11.201215:04:53;41236628391,2037;31;12;1;
23.11.201215:05:53;41236629097,2222;;7;15;8    

Which is the best way to obtain this result?

Solved using the script writed by @steve

Answer 1

Here's one way using gawk. Run like:

awk -f script.awk file

Contents of script.awk:

BEGIN {
    FS=OFS=";"
}

NR==1 {
    r = $2 FS $5
    next
}

{
    !x[$1]
    a[$2,$5][$1]=$3
}

END {

    m = asorti(x,y)
    for (k=1;k<=m;k++) {
        r = r FS y[k]
    }
    print r

    n = asorti(a,b)
    for (i=1;i<=n;i++) {
        for (j=1;j<=m;j++) {
            for (k in a[b[i]]) {
                if (k == y[j]) {
                    var = a[b[i]][k]
                }
            }

            line = line FS var
            var = ""
        }
        sub(SUBSEP, FS, b[i])

        print b[i] line
        line = ""
    }
}

Alternatively, here's the one liner:

awk 'BEGIN { FS=OFS=";" } NR==1 { r = $2 FS $5; next } { !x[$1]; a[$2,$5][$1]=$3 } END { m = asorti(x,y); for (k=1;k<=m;k++) { r = r FS y[k] } print r; n = asorti(a,b); for (i=1;i<=n;i++) { for (j=1;j<=m;j++) { for (k in a[b[i]]) { if (k == y[j]) { var = a[b[i]][k] } } line = line FS var; var = "" } sub(SUBSEP, FS, b[i]); print b[i] line; line = "" } }' file

Results:

TimeString;Time_ms;A;B;C;D
23.11.201215:03:53;41236627696,7593;1;;2;3
23.11.201215:04:53;41236628391,2037;31;12;1;
23.11.201215:05:53;41236629097,2222;;7;15;8

---

---

You need to run dos2unix on your file first. i.e:

dos2unix Flussi0.csv

Alternatively, change the record separator to \r\n so that awk knows what a windows newline ending looks like. You can do this in the BEGIN block:

BEGIN {
    FS=OFS=";"
    RS="\r\n"
}

Results with the input file posted in the comments below:

"TimeString";"Time_ms";"FIT01";"FIT02";"FIT03";"FIT04";"FIT05";"FIT06"
"22.06.2012 09:31:33";41082396909,7222;1,157408E-02;5,787041E-03;2,507718E-02;2,89352E-03;2,314816E-02;5,787035E-04
"22.06.2012 09:32:34";41082397615,7407;1,157408E-02;5,787041E-03;2,314816E-02;2,89352E-03;2,713479E-02;5,787035E-04
"22.06.2012 09:33:35";41082398321,7593;1,157408E-02;5,787041E-03;2,314816E-02;2,89352E-03;2,314816E-02;5,787035E-04
"22.06.2012 09:34:35";41082399016,2037;1,157408E-02;5,787041E-03;2,314816E-02;2,89352E-03;2,535274E-02;5,787035E-04
"22.06.2012 09:35:36";41082399722,2222;;;;;2,314816E-02;

Answer 2

The best way?  I don’t know.  Here’s a way.  I assumed that the code didn’t really need to look at the header line of the input data, and could just hard-code TimeString;Time_ms;.

(line > /dev/null; sort) < input_file > tmp0    # Discard the header line; sort the data.
        # Here lies the basic pivot:
awk -F";" '
    {
        print $1 > "tmp1"
        print $2 > "tmp2"
        print $5 > "tmp5"
    }' tmp0
echo "TimeString;Time_ms;\c"
tr "\n" ";" < tmp1; echo
tr "\n" ";" < tmp2; echo
tr "\n" ";" < tmp5; echo

This will end each line of the output with a semicolon (;).  It wasn’t clear whether you wanted that.  If you don’t want it, you can probably figure out a way to eliminate it.

Answer 3

Using the reshape sub-command of Miller (mlr, a CSV-aware tool), followed by unsparsify:

$ mlr --csv --fs ';' reshape -s VarName,VarValue then unsparsify file
TimeString;Validity;Time_ms;A;C;D;B
23.11.201215:03:53;1;41236627696,7593;1;2;3;
23.11.201215:04:53;1;41236628391,2037;31;1;;12
23.11.201215:05:53;1;41236629097,2222;;15;8;7

The -s option of the reshape sub-command takes a comma-delimited pair consisting of a key-field name and a value-field name. It then does a long-to-wide pivot operation, creating the fields in the key field with the data from the value field.

The unsparsify operation is necessary to add the empty fields in the records that do not have all fields.

To also reorder the four last fields in the correct order and remove the Validity field:

$ mlr --csv --fs ';' reshape -s VarName,VarValue then unsparsify then reorder -e -f A,B,C,D then cut -x -f Validity file
TimeString;Time_ms;A;B;C;D
23.11.201215:03:53;41236627696,7593;1;;2;3
23.11.201215:04:53;41236628391,2037;31;12;1;
23.11.201215:05:53;41236629097,2222;;7;15;8

Or, shorter using only the cut sub-command for rearranging and selecting the output fields:

$ mlr --csv --fs ';' reshape -s VarName,VarValue then unsparsify then cut -o -f TimeString,Time_ms,A,B,C,D file
TimeString;Time_ms;A;B;C;D
23.11.201215:03:53;41236627696,7593;1;;2;3
23.11.201215:04:53;41236628391,2037;31;12;1;
23.11.201215:05:53;41236629097,2222;;7;15;8

In the general case, we may not know the names of the newly generated fields, so we may want to sort the fields by their names, remove the Validity field, and then put the two time fields first without mentioning the new fields by name:

$ mlr --csv --fs ';' reshape -s VarName,VarValue then unsparsify then sort-within-records then cut -x -f Validity then reorder -f TimeString,Time_ms file
TimeString;Time_ms;A;B;C;D
23.11.201215:03:53;41236627696,7593;1;;2;3
23.11.201215:04:53;41236628391,2037;31;12;1;
23.11.201215:05:53;41236629097,2222;;7;15;8