Group by and translate rows to column

Shell Programming and Scripting
1

I've a comma separated file with data below:

61401370587,505,1;0402686146,123;2387936.0;20170812
61401370587,505,2;0401296221,34;3.0;20170811
61401370587,505,5;0431169322,123;2387936.0;20170812

My requirement is to group by using 1st,2nd column . And translate the 3rd column's row data as 1 column - "|" separated data and do the same for the 4th column.
My expected output ins below:

61401370587,505, 
1;0402686146|2;0401296221|5;0431169322,
123;2387936.0;20170812|34;3.0;20170811|123;2387936.0;20170812

Could someone please help me out.

2

Welcome bhagat-reena,

I have a few to questions pose in response first:-

  • Is this homework/assignment? There are specific forums for these.
  • What have you tried so far?
  • What output/errors do you get?
  • What OS and version are you using?
  • What are your preferred tools? (C, shell, perl, awk, etc.)
  • What logical process have you considered? (to help steer us to follow what you are trying to achieve)
  • Have you searched the forum for similar requests? Transposing rows to columns is a very common thing people ask for.

Most importantly, What have you tried so far?

I have wrapped your data in & tags to make it easier to read. This would also preserve multiple spaces for indenting or fixed-width data.

There are probably many ways to achieve most tasks, so giving us an idea of your style and thoughts will help us guide you to an answer most suitable to you so you can adjust it to suit your needs in future.

We're all here to learn and getting the relevant information will help us all.

Kind regards,
Robin