Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

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

share|improve this question
2  
Could you explain what a data pivot is, for those of us who don't grok databases? Does it have to be awk — this may be easier to do in perl/python/ruby? –  Gilles Jan 11 '13 at 23:39
    
I'm new to programming... I performed my first data management with sed and now im looking to awk. I don't now if this may be easier to do in perl/python/ruby but I think that awk can do this. Any Help is usefull thank you –  Ludovico Jan 12 '13 at 7:00
    
in And this is my desired output: 2nd line I see an A value of 31.Where does this come from? How does that derive from your example? - sorry for the noise - I've got it –  sparkie Jan 13 '13 at 6:59
1  
Please don't mark your question as "solved": this is a wiki, not a forum. Accept the correct answer so it is marked as such. –  jasonwryan Jan 16 '13 at 19:53

2 Answers 2

up vote 1 down vote accepted

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;
share|improve this answer
    
Perfect works Good!!! Tank you a lot –  Ludovico Jan 14 '13 at 19:25
    
The script fully work whit the example but I tried on an other file and fails. I tried whit a real file that I must edit whit this script... I don't know why but something goes wrong (unexpected line break after column 2)... What's the difference between my example and this file? Did I something wrong? Here there is the output –  Ludovico Jan 14 '13 at 20:37
    
Update: script fails when numbers are present in this format: 1E-02 –  Ludovico Jan 14 '13 at 21:33
    
@Ludovico: There's nothing wrong with the script. You're just not using it correctly. Please see the update. HTH. –  Steve Jan 16 '13 at 4:54
    
Thank you @steve I modified the awk script as you write. It's work very well. I hope this discussion will help other people –  Ludovico Jan 16 '13 at 19:46

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.

share|improve this answer
    
Thank you for your help... It's a good start but I should do it's a little bit different... My example was not so clear... I give you an other... I Modified original post. The semicolon at the end of the indicate the missing value (see the new example row two) –  Ludovico Jan 12 '13 at 7:22
    
So, are you saying that you can handle it from here? Or do you still need help? I suggest that you work harder at making you question(s) understandable. For example, TimeString and Time_ms go together –– couldn’t you have rearranged your input to put them in adjacent columns? Also, every one of your TimeString values begins with 23.11.201215:0, and every one of your Time_ms values begins with 4123662. This makes it very difficult for a person to look at the values and see which ones are the same and which ones are different. I suggest that you remove the duplication. –  Scott Jan 13 '13 at 20:54
    
You're right, I apologize –  Ludovico Jan 14 '13 at 18:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.