Following awk script shall aggregate 3rd and 4th column of a file data.csv by 1st and 2nd column.
File data.csv must have data as comma separated. If your file has different separator, replace ',' by your separator.
This script can be used in two manner. 1) Save the script in a shell file, say agg.sh and change its mode by
chmod 755 agg.sh, so that this file can be executed. Use command
cat data.csv|./agg.sh to get your data aggregated. 2) Make this script as one line
by removing newline characters in a file editor and apply directly on your file by
cat data.csv | script_below.
Aggregation of multiple columns by multiple columns
awk -F',' ' { print $1"|"$2","$3","$4;}'| sort -k1,1 -t',' | awk -F ',' ' BEGIN { key = ""; count = 0; sum_of_third_col = 0; sum_of_fourth_col = 0; }{ if(key == $1){ sum_of_third_col += $2; sum_of_fourth_col += $3; count++; } else if(count != 0){ printf("%s,%f,%f\n", key,sum_of_third_col,sum_of_fourth_col); key = $1; sum_of_third_col = $2; sum_of_fourth_col = $3; count = 1; } else{ sum_of_third_col += $2; sum_of_fourth_col += $3; count++; key = $1; } } END { if(count != 0){printf("%s,%f,%f\n", key,sum_of_third_col,sum_of_fourth_col); }}'
Aggregation of One column by another column
Aggregating 2nd column by 1st column in a comma separated file file.csv. The idea is that first sort by the column by which sorting is desired and then run the awk script.
# cat data.csv|sort -k1,1 -t',' |awk -F',' 'BEGIN{ key=""; count = 0 ; sum = 0; } {if(key == $1){ count++; sum += $2;} else if(count != 0){printf("%s,%d\n",key,sum); key = $1; sum = $2; count = 1;}else { key = $1; sum += $2; count++;} } END { if(count != 0){ printf("%s,%d\n",key,sum); }}'
Converting hex number to decimal number
Using "strtonum" along with awk
Function strtonum converts a string to number. You need to write the required input string in a proper hex format. i.e. Use 0x as prefix for hex number and O for octal number.
e.g. Hex 5 should be written as 0x5 and Octal 5 as O5. Execute awk 'BEGIN{print strtonum("0x100");}' to get the result. In the above scenario, result would be 256 which is equivalent to the hex value 100 in number format.
Function printf can also be used to convert an Octal/Hex to decimal and vice versa.
| Conversion | Input | Script | Result |
|---|---|---|---|
| Hex to Decimal | 100 | awk 'BEGIN{printf("%i\n", 0x100)}' | 256 |
| Octal to decimal | 100 | awk 'BEGIN{printf("%i\n", 0100)}' | 64 |
| Number to Hex | 256 | awk 'BEGIN{printf("%X\n", 256)}' | 100 |
| Numberto Octal | 64 | awk 'BEGIN{printf("%o\n", 64)}' | 100 |