Case 1 - Please aggregate ID if the Department is the same
cat file1
ADM,515356
FX,320123
FX,539201
Cloud,690392
ADM,539230
FX,859302
Cloud,689493
awk -F',' '{a[$1]=a[$1]($2"^")}END{for(i in a)printf "%s,%s\n", i,a[i]}' file1 | sed 's/\^$//'
The output is:
Cloud,690392^689493
FX,320123^539201^859302
ADM,515356^539230
Case 2 - reverse the operation done in case 1
cat file2
ADM,515356^539230
Cloud,689493^690392
FX,320123^539201^859302
awk -F',' '{split($2,a,"^"); for(i in a) printf "%s,%s\n", $1,a[i]}' file2
You will get the result:
ADM,539230
ADM,515356
Cloud,690392
Cloud,689493
FX,539201
FX,859302
FX,320123
Case 3 - calculate the sales percentage
cat file3
Eastern Country,3000000
Eastern Country,5000000
Western Country,1000000
Northern Country,2000000
Southern Country,3000000
Northern Country,1000000
awk -F',' '{a[$1]+=$2;b+=$2}END{for(i in a)printf "%s,%s,%0.3f\n", i,a[i],a[i]/b}' file3
You can get the result:
Eastern Country,8000000,0.533
Western Country,1000000,0.067
Northern Country,3000000,0.200
Southern Country,3000000,0.200
Written with StackEdit.