Rearranging a csv file

问题:

I have a file with contents similar to the below

Boy,Football
Boy,Football
Boy,Football
Boy,Squash
Boy,Tennis
Boy,Football
Girl,Tennis
Girl,Squash
Girl,Tennis
Girl,Tennis
Boy,Football

How can I use 'awk' or similar to rearrange this to the below:

     Football Tennis Squash
Boy  5        1      1
Girl 0        3      1

I'm not even sure if this is possible, but any help would be great.

回答1:

I would just loop normally:

awk -F, -v OFS="\t" '
          {names[$1]; sport[$2]; count[$1,$2]++}
          END{printf "%s", OFS;
              for (i in sport) 
                   printf "%s%s", i, OFS;
              print "";
              for (n in names) {
                   printf "%s%s", n, OFS
                   for (s in sport) 
                        printf "%s%s", count[n,s]?count[n,s]:0, OFS; print ""
                   }
               }' file

This keeps track of three arrays: names[] for the first column, sport[] for the second column and count[name,sport] to count the occurrences of every combination.

Then, it is a matter of looping through the results and printing them in a fancy way and making sure 0 is printed if the count[a,b] does not exist.

Test

$ awk -F, -v OFS="\t" '{names[$1]; sport[$2]; count[$1,$2]++} END{printf "%s", OFS; for (i in sport) printf "%s%s", i, OFS; print ""; for (n in names) {printf "%s%s", n, OFS; for (s in sport) printf "%s%s", count[n,s]?count[n,s]:0, OFS; print ""}}' a
    Squash  Tennis  Football    
Boy 1   1   5   
Girl    1   3   0   

Format is a bit ugly, there are some trailing OFS.

To get rid of trailing OFS:

awk -F, -v OFS="\t" '{names[$1]; sport[$2]; count[$1,$2]++} END{printf "%s", OFS; for (i in sport) {cn++; printf "%s%s", i, (cn<length(sport)?OFS:ORS)} for (n in names) {cs=0; printf "%s%s", n, OFS; for (s in sport) {cs++; printf "%s%s", count[n,s]?count[n,s]:0, (cs<length(sport)?OFS:ORS)}}}' a

You can always pipe to column -t for a nice output.

回答2:

$ cat tst.awk
BEGIN{ FS=","; OFS="\t" }
{
    genders[$1]
    sports[$2]
    count[$1,$2]++
}
END {
    printf ""
    for (sport in sports) {
        printf "%s%s", OFS, sport
    }
    print ""
    for (gender in genders) {
        printf "%s", gender
        for (sport in sports) {
            printf "%s%s", OFS, count[gender,sport]+0
        }
        print ""
    }
}

$ awk -f tst.awk file
        Squash  Tennis  Football
Boy     1       1       5
Girl    1       3       0

In general when you know the end point of the loop you put the OFS or ORS after each field:

for (i=1; i<=n; i++) {
    printf "%s%s", $i, (i<n?OFS:ORS)
}

but if you don't then you put the OFS before the second and subsequent fields and print the ORS after the loop:

for (x in array) {
    printf "%s%s", (++i>1?OFS:""), array[x]
}
print ""

I do like the:

n = length(array)
for (x in array) {
    printf "%s%s", array[x], (++i<n?OFS:ORS)
}

idea to get the end of the loop too, but length(array) is gawk-specific.

Another approach to consider:

$ cat tst.awk
BEGIN{ FS=","; OFS="\t" }
{
    for (i=1; i<=NF; i++) {
        if (!seen[i,$i]++) {
            map[i,++num[i]] = $i
        }
    }
    count[$1,$2]++
}
END {
    for (i=0; i<=num[2]; i++) {
        printf "%s%s", map[2,i], (i<num[2]?OFS:ORS)
    }
    for (i=1; i<=num[1]; i++) {
        printf "%s%s", map[1,i], OFS
        for (j=1; j<=num[2]; j++) {
            printf "%s%s", count[map[1,i],map[2,j]]+0, (j<num[2]?OFS:ORS)
        }
    }
}

$ awk -f tst.awk file
        Football        Squash  Tennis
Boy     5       1       1
Girl    0       1       3

That last will print the rows and columns in the order they were read. Not quite as obvious how it works though :-).