Merging values with same row names

Question

I have a table like:

   Genes     Category
1. Gene1     dark_11
2. Gene1     dark_9
3. Gene2     dark_7
4. Gene1     dark_5

I want to merge the Category values separated by comma with same row name in the Genes column to get the output like:

   Genes      Category
1. Gene1      dark_11, dark_9, dark_5
2. Gene2      dark_7

Any help will be highly appreciated.

Answer 1

You can use awk arrays indexed by the condition to be met, and then append to each value of the array each category associated, for example:

awk 'NR==1 {print $0}; NR>1 {if(cat[$2])cat[$2]=cat[$2]", "$3; else cat[$2]=$3;}; END{j=1; for (i in cat) print j++".", i, cat[i]}' test.txt

being test.txt your input file.

The cat array would contain the two values, cat[GeneX] which will contain a string with the categories concatenated for each gene. Note that before adding a category to the correct value of the array you have to check if the array is empty or not, just to add the comma between categories.