output a column group by another columns in shell

Question

Thanks in advance!

I have a file with 3 columns like this:

serv1   red     group1
serv1   black   group1
serv1   orange  group1
serv1   red     group2
serv1   orange  group2
serv1   red     group3
serv1   black   group3
serv1   orange  group3
serv2   orange  group1
serv2   red     group2
serv2   orange  group2
serv2   red     group3
serv2   black   group3
serv2   orange  group3
serv3   orange  group1
serv3   red     group1

And I would show like this:

serv1   group1  red black   orange
serv1   group2  red orange
serv1   group3  red black   orange
serv2   group1  orange
serv2   group2  red orange
serv2   group3  red black   orange
serv3   group1  orange  red

If I a try to group only by 1st with the next code, I show good, but I can´t doing grouping wtit more than one:

awk '{ V_GRUPO[$1]= (V_GRUPO[$1]==""?"":V_GRUPO[$1] OFS) $2 }END  { for(x in V_GRUPO) print x, V_GRUPO[x] }' file.txt

serv1   red black   orange red  orange  red black   orange
serv2   orange  red orange  red black   orange
serv3   orange  red

¿how could I do it???

Thanks!!

Answer 1

Assuming that the fields in the input are delimited by multiple spaces (a sort of "pretty printed tabular" format) and that you want to collapse the entries in the second field, based on the other two fields, into a space-delimited list:

$ mlr --pprint -N nest --ivar ' ' -f 2 then reorder -f 1,3,2 file
serv1 group1 red black orange
serv1 group2 red orange
serv1 group3 red black orange
serv2 group1 orange
serv2 group2 red orange
serv2 group3 red black orange
serv3 group1 orange red

This does the collapsing and the reordering of the original fields using Miller (mlr).

If the data is tab-delimited (TSV), and you want tab-delimited output, then change --pprint into --tsv. Would you want a different delimiter for the collapsed list of data, then change --ivar ' ' into e.g. --ivar ';' (to use ;).

For example, with tab-delimited input and output, with ; as the list delimiter:

$ mlr --tsv -N nest --ivar ';' -f 2 then reorder -f 1,3,2 file
serv1   group1  red;black;orange
serv1   group2  red;orange
serv1   group3  red;black;orange
serv2   group1  orange
serv2   group2  red;orange
serv2   group3  red;black;orange
serv3   group1  orange;red

Another example, reading TSV input, adding labels to each field, using these labels instead of numerical fields, and producing pretty-printed tabular output:

$ mlr --itsv --implicit-csv-header --opprint --barred label Server,Colour,Group then nest --ivar ';' -f Colour then reorder -f Server,Group,Colour file
+--------+--------+------------------+
| Server | Group  | Colour           |
+--------+--------+------------------+
| serv1  | group1 | red;black;orange |
| serv1  | group2 | red;orange       |
| serv1  | group3 | red;black;orange |
| serv2  | group1 | orange           |
| serv2  | group2 | red;orange       |
| serv2  | group3 | red;black;orange |
| serv3  | group1 | orange;red       |
+--------+--------+------------------+

Answer 2

Using an awk script, script-file.awk :

{
    k = $1 OFS $3
    a[k] = (k in a ? a[k] OFS : "" ) $2
}
END{
    for(key in a){
        print key, a[key]
    }
}

Then run :

awk -f script-file.awk input

• a[k] is an array using the key k = $1 OFS $3
• OFS is the Output Field Separator
• ? is a conditional (test ? true : false)
• for(key in a) for each key

---

This may not be sorted, so if you need it sorted pipe it :

awk -f script-file.awk input | sort

Answer 3

Since your input is already sorted by your 2 key fields, this will only save the values for 1 key pair at a time in memory, using any awk:

$ awk '
    { key = $1 OFS $3 }
    key != prev { if (NR>1) print vals; prev=vals=key }
    { vals = vals OFS $2 }
    END { print vals }
' file
serv1 group1 red black orange
serv1 group2 red orange
serv1 group3 red black orange
serv2 group1 orange
serv2 group2 red orange
serv2 group3 red black orange
serv3 group1 orange red

If your real input isn't already sorted as your sample input is then just sort it first:

sort -b -k1,1 -k3,3 file | awk '...'

Answer 4

To collapse a column group by another (or other columns) datamash can be used.

$ datamash -sW groupby 1,3 collapse 2 --collapse-delimiter ' ' <file

About Collapse delimiter from its manual:

--collapse-delimiter=x
-c x
Use character X instead of comma to delimit items in a ‘collapse’ or ‘unique’ (aka ‘uniq’) list.