awk para mesclar linhas com base nos valores da célula

Question

Eu quero mesclar linhas usando o awk, onde coluna 1,2,3,4,5,6,7 combina e combina entradas únicas da coluna 8 nele.

Entrada é

AB-DB1,No_IP_Found,11/08/2015,INFINITY,Adhoc-ab-db1-Sunset,/avqln1ic_1_1,infinity ,0014L5
AB-DB1,No_IP_Found,11/08/2015,INFINITY,Adhoc-ab-db1-Sunset,/avqln1ic_1_1,infinity ,0015L5;0017L5
AB-DB1,No_IP_Found,11/08/2015,INFINITY,Adhoc-ab-db1-Sunset,/avqln1ic_1_1,infinity ,0017L5;0019L5
AB-DB1,No_IP_Found,11/08/2015,INFINITY,Adhoc-ab-db1-Sunset,/avqln1ic_1_1,infinity ,0019L5
AB-DB1,No_IP_Found,11/01/2015,INFINITY,Adhoc-ab-db1-Sunset,DB_Backup/Archived/Offline,infinity ,No_Media_Used/Disk
AB-DB1,No_IP_Found,11/01/2015,INFINITY,Adhoc-ab-db1-Sunset,DB_Backup/Archived/Offline,infinity ,No_Media_Used/Disk
AB-DB1,No_IP_Found,11/01/2015,INFINITY,Adhoc-ab-db1-Sunset,DB_Backup/Archived/Offline,infinity ,No_Media_Used/Disk
AB-DB1,No_IP_Found,10/08/2015,INFINITY,Adhoc-ab-db1-Sunset,/c-3885516020-20151008-00,infinity ,0007L5
AB-DB1,No_IP_Found,10/08/2015,INFINITY,Adhoc-ab-db1-Sunset,/cf_ABDB_02qj5qta_1_1_892529578,infinity ,0023L5
APPSRV,172.25.128.100,09/11/2017,12/13/2017,MG16-Genral-OS-LINUX-MONTHLY,/;/Monitoring/;/Monitoring_old/;/NetBackup_7.5_CLIENTS.tar;/bin/;/boot/;/data1/;/data2/;/dev/;/etc/;/home/;/lib/;/lib64/;/lost+found/;/media/;/misc/;/mnt/;/net/;/opt/;/proc/;/root/;/sbin/;/selinux/;/srv/;/sys/;/tftpboot/;/tmp/;/usr/;/var/,3 months ,0089L5
APPSRV,172.25.128.100,09/11/2017,12/13/2017,MG16-Genral-OS-LINUX-MONTHLY,/;/Monitoring/;/Monitoring_old/;/NetBackup_7.5_CLIENTS.tar;/bin/;/boot/;/data1/;/data2/;/dev/;/etc/;/home/;/lib/;/lib64/;/lost+found/;/media/;/misc/;/mnt/;/net/;/opt/;/proc/;/root/;/sbin/;/selinux/;/srv/;/sys/;/tftpboot/;/tmp/;/usr/;/var/,3 months ,0090L5;0094L5;0089L5;0091L5;
APPSRV,172.25.128.100,09/11/2017,12/13/2017,MG16-Genral-OS-LINUX-MONTHLY,/;/Monitoring/;/Monitoring_old/;/NetBackup_7.5_CLIENTS.tar;/bin/;/boot/;/data1/;/data2/;/dev/;/etc/;/home/;/lib/;/lib64/;/lost+found/;/media/;/misc/;/mnt/;/net/;/opt/;/proc/;/root/;/sbin/;/selinux/;/srv/;/sys/;/tftpboot/;/tmp/;/usr/;/var/,3 months ,0091L5
BB-LITE,172.25.129.32,09/25/2017,10/26/2017,MG12-BB-LITE-AP-BROKER-DAILY,/etc/,1 month ,0078L5
BB-LITE,172.25.129.32,09/25/2017,10/26/2017,MG12-BB-LITE-AP-BROKER-DAILY,DB_Backup/Archived/Offline,1 month ,0078L5
BB-LITE,172.25.129.32,09/25/2017,10/26/2017,MG12-BB-LITE-AP-BROKER-DAILY,/home/bblite/,1 month ,0078L5
BB-LITE,172.25.129.32,09/25/2017,10/26/2017,MG12-BB-LITE-AP-BROKER-DAILY,/home/bblite/,1 month ,0060L5

Saída desejada

AB-DB1,No_IP_Found,11/08/2015,INFINITY,Adhoc-ab-db1-Sunset,/avqln1ic_1_1,infinity ,0014L5;0015L5;0017L5;0019L5
AB-DB1,No_IP_Found,11/01/2015,INFINITY,Adhoc-ab-db1-Sunset,DB_Backup/Archived/Offline,infinity ,No_Media_Used/Disk
AB-DB1,No_IP_Found,10/08/2015,INFINITY,Adhoc-ab-db1-Sunset,/c-3885516020-20151008-00,infinity ,0007L5
AB-DB1,No_IP_Found,10/08/2015,INFINITY,Adhoc-ab-db1-Sunset,/cf_ABDB_02qj5qta_1_1_892529578,infinity ,0023L5
APPSRV,172.25.128.100,09/11/2017,12/13/2017,MG16-Genral-OS-LINUX-MONTHLY,/;/Monitoring/;/Monitoring_old/;/NetBackup_7.5_CLIENTS.tar;/bin/;/boot/;/data1/;/data2/;/dev/;/etc/;/home/;/lib/;/lib64/;/lost+found/;/media/;/misc/;/mnt/;/net/;/opt/;/proc/;/root/;/sbin/;/selinux/;/srv/;/sys/;/tftpboot/;/tmp/;/usr/;/var/,3 months ,0089L5;0090L5;0094L5;0091L5;
BB-LITE,172.25.129.32,09/25/2017,10/26/2017,MG12-BB-LITE-AP-BROKER-DAILY,/etc/,1 month ,0078L5
BB-LITE,172.25.129.32,09/25/2017,10/26/2017,MG12-BB-LITE-AP-BROKER-DAILY,DB_Backup/Archived/Offline,1 month ,0078L5
BB-LITE,172.25.129.32,09/25/2017,10/26/2017,MG12-BB-LITE-AP-BROKER-DAILY,/home/bblite/,1 month ,0078L5;0060L5

Atualmente estou usando o código abaixo.

awk -F' *, *' '{a[$1","$2","$3","$4","$5","$6","$7]=a[$1","$2","$3","$4","$5","$6","$7] "," $8} END {for (k in a) {print k a[k] | "sort -u"}}'

Mas parece que não funciona corretamente. Alguém pode sugerir alguma coisa?

    

Answer 1

Com um único processo gawk :

awk -F, 'BEGIN{ PROCINFO["sorted_in"]="@ind_str_asc" }
        { k=$1 FS $2 FS $3 FS $4 FS $5 FS $6 FS $7 }
        { a[k]=a[k]? a[k]";"$8 : $8 }
        END{ 
            for(i in a) { 
                split(a[i],b,";"); r=""; 
                for (j in b) { if(!c[b[j]]++) r=(r=="")? b[j]: r";"b[j] }
                delete c; print i,r 
            } 
        }' OFS=',' file

• PROCINFO["sorted_in"]="@ind_str_asc" - comparação / classificação por chaves / índices do array. Podemos definir a matriz predefinida PROCINFO para um de um conjunto de valores predefinidos. Esses valores especiais descritos aqui (documentação): link

• k=$1 FS $2 FS $3 FS $4 FS $5 FS $6 FS $7 - construindo uma chave exclusiva

• a[k]=a[k]? a[k]";"$8 : $8 - acumulando o oitavo valor de campo para cada grupo

A saída:

AB-DB1,No_IP_Found,10/08/2015,INFINITY,Adhoc-ab-db1-Sunset,/c-3885516020-20151008-00,infinity ,0007L5
AB-DB1,No_IP_Found,10/08/2015,INFINITY,Adhoc-ab-db1-Sunset,/cf_ABDB_02qj5qta_1_1_892529578,infinity ,0023L5
AB-DB1,No_IP_Found,11/01/2015,INFINITY,Adhoc-ab-db1-Sunset,DB_Backup/Archived/Offline,infinity ,No_Media_Used/Disk
AB-DB1,No_IP_Found,11/08/2015,INFINITY,Adhoc-ab-db1-Sunset,/avqln1ic_1_1,infinity ,0014L5;0015L5;0017L5;0019L5
APPSRV,172.25.128.100,09/11/2017,12/13/2017,MG16-Genral-OS-LINUX-MONTHLY,/;/Monitoring/;/Monitoring_old/;/NetBackup_7.5_CLIENTS.tar;/bin/;/boot/;/data1/;/data2/;/dev/;/etc/;/home/;/lib/;/lib64/;/lost+found/;/media/;/misc/;/mnt/;/net/;/opt/;/proc/;/root/;/sbin/;/selinux/;/srv/;/sys/;/tftpboot/;/tmp/;/usr/;/var/,3 months ,0089L5;0090L5;0094L5;0091L5;
BB-LITE,172.25.129.32,09/25/2017,10/26/2017,MG12-BB-LITE-AP-BROKER-DAILY,/etc/,1 month ,0078L5
BB-LITE,172.25.129.32,09/25/2017,10/26/2017,MG12-BB-LITE-AP-BROKER-DAILY,/home/bblite/,1 month ,0078L5;0060L5
BB-LITE,172.25.129.32,09/25/2017,10/26/2017,MG12-BB-LITE-AP-BROKER-DAILY,DB_Backup/Archived/Offline,1 month ,0078L5

    

Answer 2

Vamos primeiro exclusivo sort -u e processá-lo via awk .

awk -F',' -v OFS=, '{cpyNF=$NF;NF--;a[$0]=a[$0]","cpyNF} 
    END{for (i in a) print i""a[i]}' <(sort -u infile)

    

Answer 3

Primeira variante

sed , awk e datamash são usados. Eu posso adicionar uma explicação, se você estiver interessado e o código fizer o que você quiser. As linhas não devem conter : (dois pontos), porque eu escolhi como delimitador para datamash . Claro, pode ser alterado para outro.

sed 's/,/:/7' input.txt |
datamash -t: -g 1 unique 2 | 
awk -F: '{
    printf "%s,", $1;

    size = split($2, arr, "[,;]");

    for(i = 1; i <= size; i++) {
        if(!unique_arr[arr[i]]) {
            printf "%s; ", arr[i];
            unique_arr[arr[i]] = 1;
        }
    }
    print "";
    delete unique_arr;
}' | sed 's/[; ]*$//g'

Resultado

AB-DB1,No_IP_Found,11/08/2015,INFINITY,Adhoc-ab-db1-Sunset,/avqln1ic_1_1,infinity ,0014L5; 0015L5; 0017L5; 0019L5
AB-DB1,No_IP_Found,11/01/2015,INFINITY,Adhoc-ab-db1-Sunset,DB_Backup/Archived/Offline,infinity ,No_Media_Used/Disk
AB-DB1,No_IP_Found,10/08/2015,INFINITY,Adhoc-ab-db1-Sunset,/c-3885516020-20151008-00,infinity ,0007L5
AB-DB1,No_IP_Found,10/08/2015,INFINITY,Adhoc-ab-db1-Sunset,/cf_ABDB_02qj5qta_1_1_892529578,infinity ,0023L5
APPSRV,172.25.128.100,09/11/2017,12/13/2017,MG16-Genral-OS-LINUX-MONTHLY,/;/Monitoring/;/Monitoring_old/;/NetBackup_7.5_CLIENTS.tar;/bin/;/boot/;/data1/;/data2/;/dev/;/etc/;/home/;/lib/;/lib64/;/lost+found/;/media/;/misc/;/mnt/;/net/;/opt/;/proc/;/root/;/sbin/;/selinux/;/srv/;/sys/;/tftpboot/;/tmp/;/usr/;/var/,3 months ,0089L5; 0090L5; 0094L5; 0091L5
BB-LITE,172.25.129.32,09/25/2017,10/26/2017,MG12-BB-LITE-AP-BROKER-DAILY,/etc/,1 month ,0078L5
BB-LITE,172.25.129.32,09/25/2017,10/26/2017,MG12-BB-LITE-AP-BROKER-DAILY,DB_Backup/Archived/Offline,1 month ,0078L5
BB-LITE,172.25.129.32,09/25/2017,10/26/2017,MG12-BB-LITE-AP-BROKER-DAILY,/home/bblite/,1 month ,0060L5; 0078L5

Segunda variante - sem datamash

sed 's/,/:/7; s/\s*;$//' input.txt |
awk -F: '
{
    size = split($2, arr_eight_field, ";"); 
    for(i = 1; i <= size; i++) {
        main_arr[$1][arr_eight_field[i]] = 1;
    }
}
END {
    for(seven_fields in main_arr) {
        eight = "";
        for(i in main_arr[seven_fields]) {
            eight = (eight) ? eight "; " i : i;
        }
        print seven_fields "," eight;
    }
}' | sort

Resultado

AB-DB1,No_IP_Found,10/08/2015,INFINITY,Adhoc-ab-db1-Sunset,/c-3885516020-20151008-00,infinity ,0007L5
AB-DB1,No_IP_Found,10/08/2015,INFINITY,Adhoc-ab-db1-Sunset,/cf_ABDB_02qj5qta_1_1_892529578,infinity ,0023L5
AB-DB1,No_IP_Found,11/01/2015,INFINITY,Adhoc-ab-db1-Sunset,DB_Backup/Archived/Offline,infinity ,No_Media_Used/Disk
AB-DB1,No_IP_Found,11/08/2015,INFINITY,Adhoc-ab-db1-Sunset,/avqln1ic_1_1,infinity ,0017L5; 0015L5; 0019L5; 0014L5
APPSRV,172.25.128.100,09/11/2017,12/13/2017,MG16-Genral-OS-LINUX-MONTHLY,/;/Monitoring/;/Monitoring_old/;/NetBackup_7.5_CLIENTS.tar;/bin/;/boot/;/data1/;/data2/;/dev/;/etc/;/home/;/lib/;/lib64/;/lost+found/;/media/;/misc/;/mnt/;/net/;/opt/;/proc/;/root/;/sbin/;/selinux/;/srv/;/sys/;/tftpboot/;/tmp/;/usr/;/var/,3 months ,0090L5; 0089L5; 0091L5; 0094L5
BB-LITE,172.25.129.32,09/25/2017,10/26/2017,MG12-BB-LITE-AP-BROKER-DAILY,DB_Backup/Archived/Offline,1 month ,0078L5
BB-LITE,172.25.129.32,09/25/2017,10/26/2017,MG12-BB-LITE-AP-BROKER-DAILY,/etc/,1 month ,0078L5
BB-LITE,172.25.129.32,09/25/2017,10/26/2017,MG12-BB-LITE-AP-BROKER-DAILY,/home/bblite/,1 month ,0060L5; 0078L5