UNIX for Beginners Questions & Answers

Afghanistan|10000|1
Albania|25000|4
Algeria|25000|7
Andorra|10000|4
Angola|25000|47
Antigua and Barbuda|25000|23
Argentina|5000|3
Armenia|100000|12
Aruba|20000|2
Australia|50000|2

Nation|5000|10000|20000|25000|50000|100000
Afghanistan|0|1|0|0|0|0
Albania|0|0|0|4|0|0
Algeria|0|0|0|7|0|0
Andorra|0|4|0|0|0|0
Angola|0|0|0|47|0|0
Antigua and Barbuda|0|0|0|23|0|0
Argentina|3|0|0|0|0|0
Armenia|0|0|0|0|0|12
Aruba|0|0|2|0|0|0
Australia|0|0|0|0|2|0

awk 'NR==1 {split ($0, D)} NR>1 {for (i=2; i<=NF; i++) print $1,$2, D[i], $i} ' FS="|" OFS="|" input

awk '
BEGIN {	# Set input and output field separators.
	FS = OFS = "|"
}
{	# For each line read from the input file(s)...
	# Gather data.
	d[$1,$2] = $3
	# If we have not seen this nation before...
	if(!($1 in nation)) {
		# ... save the new nation and add it to the list specifying the
		# output order.
		nation[$1]
		nation_order[++nationc] = $1
	}
	# If we have not seen this value before...
	if(!($2 in values)) {
		# ... save the new value and increment the number of values we
		# have seen.
		values[$2]
		valuesc++
	}
}
END {	# After we have gathered data from all of the input file(s), sort the
	# values we have seen into increasing order while setting the field
	# headers.
	headers[++headersc] = "Nation"
	# While we have a value left in the values[] array...
	while(valuesc--) {
		# ... find the smallest value left in values[].
		m = 1E20
		for(j in values) {
			if(j + 0 < m)
				m = j + 0
		}
		# Set the next output field header to the lowest value found,
		# increment the number of header fields, and delete the
		# minimum value from the values[] array.
		headers[++headersc] = m
		delete values[m]
	}
	# Print the header line.
	for(i = 1; i <= headersc; i++)
		printf("%s%s", headers[i], (i < headersc) ? OFS : ORS)
	# Print the data line for each nation in our list of nations...
	for(i = 1; i <= nationc; i++) {
		# ... print the nation...
		printf("%s%s", nation_order[i], OFS)
		for(j = 2; j <= headersc; j++)
			# ... and print the data values for this nation.
			printf("%s%s", d[nation_order[i], headers[j]] + 0,
			    (j < headersc) ? OFS : ORS)
	}
}' input	# End the awk script and name the file to be processed.

Nation|5000|10000|20000|25000|50000|100000
Afghanistan|0|1|0|0|0|0
Albania|0|0|0|4|0|0
Algeria|0|0|0|7|0|0
Andorra|0|4|0|0|0|0
Angola|0|0|0|47|0|0
Antigua and Barbuda|0|0|0|23|0|0
Argentina|3|0|0|0|0|0
Armenia|0|0|0|0|0|12
Aruba|0|0|2|0|0|0
Australia|0|0|0|0|2|0