重新排列csv文件 [英] Rearranging a csv file

问题描述

我有类似下面的

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

我怎么可以使用'awk的'或类似的重新安排这下面的：

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.

推荐答案

我只想循环正常：

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.

那么，它是通过成果循环并以奇特的方式印制和制作的事情肯定 0 如果打印算[ A，b] 不存在。

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.

$ 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   

格式是有点难看，也有一些尾随OFS。

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

要摆脱尾随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

您可以随时管道列-t 的一个不错的输出。

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

这篇关于重新排列csv文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！