Linux Shell下实现列转行并按IP分组生成CSV

原始输入文件 #

192.168.0.35 GigabitEthernet0/19 AP-CISCO-02
192.168.0.35 GigabitEthernet0/20 AP-CISCO-12
192.168.0.36 GigabitEthernet0/12 AP-CISCO-70
192.168.0.36 GigabitEthernet0/20 AP-CISCO-71
192.168.0.40 GigabitEthernet1/0/22 AP-CISCO-73
192.168.0.254 GigabitEthernet2/47 AP-CISCO-19
192.168.0.254 GigabitEthernet3/37 AP-CISCO-86
192.168.0.254 GigabitEthernet3/38 AP-CISCO-85

目标CSV格式 #

要求将列转成行，并按第一列的交换机IP分组：第一行是去重后的交换机IP，第二行是对应IP下的接口合并结果，第三行是对应IP下的描述合并结果。

192.168.0.35, 192.168.0.36, 192.168.0.40, 192.168.0.254
"GigabitEthernet0/19, GigabitEthernet0/20", "GigabitEthernet0/12, GigabitEthernet0/20", "GigabitEthernet1/0/22", "GigabitEthernet2/47, GigabitEthernet3/37, GigabitEthernet3/38"
"AP-CISCO-02, AP-CISCO-12", "AP-CISCO-70, AP-CISCO-71", "AP-CISCO-73", "AP-CISCO-19, AP-CISCO-86, AP-CISCO-85"

最初尝试的awk代码及问题 #

尝试了以下awk代码：

awk '{
    for (f = 1; f <= NF; f++) {
        a[NR, f] = $f 
    } 
}

NF > nf { nf = NF }

END {
   for (f = 1; f <= nf; f++) {
       for (r = 1; r <= NR; r++) {
           printf "\""a[r, f]"\"" (r==NR ? RS : FS=",")
       }
   }
}' myfile

但输出不符合需求：IP重复出现，接口和描述也没有按IP分组合并，结果如下：

"192.168.0.35","192.168.0.35","192.168.0.36","192.168.0.36","192.168.0.40","192.168.0.254","192.168.0.254","192.168.0.254"
"GigabitEthernet0/19","GigabitEthernet0/20","GigabitEthernet0/12","GigabitEthernet0/20","GigabitEthernet1/0/22","GigabitEthernet2/47","GigabitEthernet3/37","GigabitEthernet3/38"
"AP-CISCO-02","AP-CISCO-12","AP-CISCO-70","AP-CISCO-71","AP-CISCO-73","AP-CISCO-19","AP-CISCO-86","AP-CISCO-85"

已实现的解决方案 #

我已经用awk实现了需求，代码如下：

{
    for (f = 1; f <= NF; f++) {
        a[NR, f] = $f 
    } 
}

NF > nf { nf = NF }

END {
 intlist="\""
 desclist="\""
 for (f = 1; f <= nf; f++) {
       for (r = 1; r <= NR; r++) {
           if (f==1) {
                if (a[r,1]!=a[r+1,1]) {
                    printf a[r,f] (r==NR ? RS="\n" : FS=",")
                }
           }
           
           if (f==2){
                if (r==1){
                    intlist=intlist""a[r,2]
                }
                else{
                    if (a[r,1]==a[r-1,1]) {
                        intlist=intlist", "a[r,2]
                    }
                    else {
                        printf intlist"\","
                        intlist="\""a[r,2] 
                    }
                }
                
                if (r==NR) {
                   printf intlist"\"\n"
                }
           }
           
           if (f==3){
                if (r==1){
                    desclist=desclist""a[r,3]
                }
                else{
                    if (a[r,1]==a[r-1,1]) {
                        desclist=desclist", "a[r,3]
                    }
                    else {
                        printf desclist"\","
                        desclist="\""a[r,3] 
                    }
                }
                
                if (r==NR) {
                   printf desclist"\""
                }
           }
       }
   }
}

更简洁的优化方案 #

可以用逻辑更清晰的awk写法，直接按IP分组存储接口和描述，最后统一输出：

awk '{
    ip[$1]
    ints[$1] = (ints[$1] ? ints[$1] ", " : "") $2
    descs[$1] = (descs[$1] ? descs[$1] ", " : "") $3
}
END {
    # 输出IP行
    sep = ""
    for (i in ip) {
        printf "%s%s", sep, i
        sep = ", "
    }
    print ""
    
    # 输出接口行
    sep = ""
    for (i in ip) {
        printf "%s\"%s\"", sep, ints[i]
        sep = ", "
    }
    print ""
    
    # 输出描述行
    sep = ""
    for (i in ip) {
        printf "%s\"%s\"", sep, descs[i]
        sep = ", "
    }
    print ""
}' myfile

这种写法避免了二维数组的复杂遍历，直接用数组按IP聚合数据，代码更易读维护。

内容的提问来源于stack exchange，提问作者Rodrigo Antunes