主页 / coding / 问题 / 78100535
Accepted
minseong
Asked: 2024-03-04 18:23:55 +0800 CST

命令行将长表旋转为宽表

  • 772

我怎样才能把一张长桌子变成这样

Room,"Variable Name",Value
Bedroom,Temperature,23
Bedroom,Humidity,78
Office,Temperature,26
Office,Humidity,82
Bathroom,Temperature,18
Bathroom,Humidity,97

进入一张宽桌子，就像

Room,Temperature,Humidity
Bedroom,23,78
Office,26,82
Bathroom,18,97

在命令行？

我已经尝试通过csvfix flatten -smq但是会产生

Room,"Variable Name",Value
Bedroom,Temperature,23,Humidity,78
Office,Temperature,26,Humidity,82
Bathroom,Temperature,18,Humidity,97
awk

2 个回答

  1. Best Answer

    使用任何 awk：

    $ cat tst.awk
BEGIN { FS=OFS="," }
{
    key = $1
    hdr = ( NR == 1 ? $1 : $2 )

    rowNr = key2row[key] = ( key in key2row ? key2row[key] : ++numRows )
    colNr = hdr2col[hdr] = ( hdr in hdr2col ? hdr2col[hdr] : ++numCols )

    vals[rowNr,1] = key
    vals[1,colNr] = hdr
    vals[rowNr,colNr] = $3
}
END {
    for ( rowNr=1; rowNr<=numRows; rowNr++ ) {
        for ( colNr=1; colNr<=numCols; colNr++ ) {
            printf "%s%s", vals[rowNr,colNr], (colNr<numCols ? OFS : ORS)
        }
    }
}


    $ awk -f tst.awk file
Value,Temperature,Humidity
Bedroom,23,78
Office,26,82
Bathroom,18,97
    • 2

  2. 假设你有这个文件：

    $ cat file
Room,"Variable Name",Value
Bedroom,Temperature,23
Bedroom,Humidity,78
Office,Humidity,82
Bathroom,Temperature,18
Office,Temperature,26
Bathroom,Humidity,97

    （请注意，房间的顺序不正确，温度和湿度的顺序不同......）

    你可以像这样使用 Ruby：

    ruby -F, -lane '
BEGIN{h=Hash.new{|h,k| h[k]={} }; header=$<.readline }
h[ $F[0] ][ $F[1] ]=$F[2]
END{
    fields=h.values.map{|x| x.keys}.flatten.to_set.to_a
    puts (["Room"]+fields).join(",")
    h.keys.zip(h.
        map{|k,v| v.sort_by{|k,v| fields.find_index(k)}.map(&:last)}).
        each{|l| puts ([l[0]]+l[1..]).join(",") }
}
' file

    印刷：

    Room,Temperature,Humidity
Bedroom,23,78
Office,26,82
Bathroom,18,97
    • 0

相关问题