在Unix中转置 [英] Transpose in Unix

问题描述

我以这种方式在文件中每小时记录一次数据

I have an hourly data in file this way

2015-09-03 02:00:00 to 2015-09-03 02:59:59|ABC|673
2015-09-03 02:00:00 to 2015-09-03 02:59:59|AABC|52
2015-09-03 02:00:00 to 2015-09-03 02:59:59|ABCD|787
2015-09-03 02:00:00 to 2015-09-03 02:59:59|ADFGE|35
2015-09-03 02:00:00 to 2015-09-03 02:59:59|AGER|41
2015-09-03 02:00:00 to 2015-09-03 02:59:59|ETECFF|1384
2015-09-03 02:00:00 to 2015-09-03 02:59:59|TRIFD|38
2015-09-03 02:00:00 to 2015-09-03 02:59:59|CVGFFHG|166
2015-09-03 03:00:00 to 2015-09-03 03:59:59|FJREER|36
2015-09-03 03:00:00 to 2015-09-03 03:59:59|DFSD|31
2015-09-03 03:00:00 to 2015-09-03 03:59:59|ASBF|38
2015-09-03 03:00:00 to 2015-09-03 03:59:59|ABC|36
2015-09-03 03:00:00 to 2015-09-03 03:59:59|AABC|35
2015-09-03 03:00:00 to 2015-09-03 03:59:59|ABCD|33
2015-09-03 03:00:00 to 2015-09-03 03:59:59|ADFGE|39
2015-09-03 03:00:00 to 2015-09-03 03:59:59|AGER|33
2015-09-03 03:00:00 to 2015-09-03 03:59:59|ETECFF|537
2015-09-03 03:00:00 to 2015-09-03 03:59:59|TRIFD|620635
2015-09-03 03:00:00 to 2015-09-03 03:59:59|ABC|37
2015-09-03 03:00:00 to 2015-09-03 03:59:59|AABC|702
2015-09-03 03:00:00 to 2015-09-03 03:59:59|ABCD|319
2015-09-03 03:00:00 to 2015-09-03 03:59:59|ADFGE|33
2015-09-03 03:00:00 to 2015-09-03 03:59:59|AGER|306
2015-09-03 03:00:00 to 2015-09-03 03:59:59|ETECFF|34
2015-09-03 03:00:00 to 2015-09-03 03:59:59|TRIFD|44
2015-09-03 03:00:00 to 2015-09-03 03:59:59|CVGFFHG|599
2015-09-03 03:00:00 to 2015-09-03 03:59:59|FJREER|30
2015-09-03 03:00:00 to 2015-09-03 03:59:59|DFSD|82

我想转置数据

1. Column 1 should go in as column header 
2. Column 2 should go in row header
3. Column 3 is data
4. Any absence of data should be represented as 0 (Zero)

这是转置后的数据的样子

Here is how the transposed data should look like

|2015-09-03 02:00:00 to 2015-09-03 02:59:59|2015-09-03 03:00:00 to 2015-09-03 03:59:59
AABC|52|737
ABC|0|73
ABCD|787|352
ADFGE|35|72
AGER|41|339
ASBF|0|38
CVGFFHG|166|599
DFSD|0|113
ETECFF|1384|571
FJREER|0|66
TRIFD|38|620679

我尝试使用sed，但这不起作用.我的awk尚不十分出色，尚未达到高级水平，因此在这里需要帮助

I have tried using sed, but that does not work. I am not quite good in awk yet, not yet reached to advanced level, so needed help here

推荐答案

这是awk的解决方案.它在2D数组values中保存 具有相同关键字key和相同标题列索引i的所有行. 在END处，每个键和列均打印所有这些内容. 数组cols用于检测标题列的更改. hdrs用于将标题保持在正确的顺序以进行输出. keys仅用于保留所有关键字的列表.

Here's a solution with awk. It holds in 2D array values the sum for all rows with the same keyword key and the same header column index i. At the END all these are printed for each key and column. Array cols is used to detect a change of header column. hdrs is used to keep the headers in the right order for output. keys is just used to keep a list of all the keywords.

awk -F'|' '
{ hdr = $1; key = $2; val = $3;
  if(cols[hdr]==0){
    cols[hdr] = ++column;
    hdrs[column] = hdr;
  }
  i = cols[hdr]
  keys[key] = 1
  values[i, key] += val
}
END{
  for(i = 1;i<=column;i++)
   printf  "|%s", hdrs[i]
  printf "\n"
  n = asorti(keys,sort)
  for(j = 1;j<=n;j++){
     key = sort[j]
     printf "%s",key
     for(i = 1;i<=column;i++)
      printf "|%s", values[i, key]+0
     printf "\n"
  }
}'

这篇关于在Unix中转置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！