Group rows of a csv file with sum of column values

You have heard about Group functionality in Microsoft Excel, which group the rows on a common value and does the sum of other field. Same thing can be done in unix using awk very easily. See below example:

[ ~]# cat test.csv
a,5
b,10
a,4
c,8
b,9
b,4
[ ~]# awk -F"," '{sum[$1] += $2 } END { for(n in sum)print n"=" sum[n] }' test.csv
a=9
b=23
c=8

Perl version of above script can be seen here:

AWK examples

Find number of columns in a pipe seperated csv file
awk -F"|" '{print NF}' vishnu.csv |uniq

Find sum of column of a pipe seperated csv file
awk -F"|" '{sum += $col } END { print sum }' vishnu.csv
awk -F"|" '{sum += $col } END { print sprintf("%.2f",sum) }' vishnu.csv (print float value)

Replace tab character with a pipe symbol in a csv file
awk '{gsub("\t","|");print}' vishnu.csv

print the 5th and 7th columns of csv file if 5th and 7th column values are not same
awk -F"," '{if($5 != $7) {print $5","$7}}' test.csv

print the line of csv file if 5th and 7th column values are same
awk -F"," '{if($5 !== $7) {print}}' test.csv

Puzzle: Elevator problem

Question: A man lives on the twelfth floor of an apartment building. Every morning he takes the elevator down to the lobby and leaves the building. In the evening, he gets into the elevator, and, if there is someone else in the elevator -- or if it was raining that day -- he goes back to his floor directly. Otherwise, he goes to the tenth floor and walks up two flights of stairs to his apartment.

Answer: The man is a dwarf. He can't reach the upper elevator buttons, but he can ask people to push them for him. He can also push them with his umbrella.

Puzzle: 100 doors in a row

You have 100 doors in a row that are all initially closed. you make 100 passes by the doors starting with the first door every time. the first time through you visit every door and toggle the door (if the door is closed, you open it, if its open, you close it). the second time you only visit every 2nd door (door #2, #4, #6). the third time, every 3rd door (door #3, #6, #9), etc, until you only visit the 100th door.

for example, after the first pass every door is open. on the second pass you only visit the even doors (2,4,6,8...) so now the even doors are closed and the odd ones are opened. the third time through you will close door 3 (opened from the first pass), open door 6 (closed from the second pass), etc..

question: what state are the doors in after the last pass? which are open which are closed?

Solution: you can figure out that for any given door, say door #42, you will visit it for every divisor it has. so 42 has 1 & 42, 2 & 21, 3 & 14, 6 & 7. so on pass 1 i will open the door, pass 2 i will close it, pass 3 open, pass 6 close, pass 7 open, pass 14 close, pass 21 open, pass 42 close. for every pair of divisors the door will just end up back in its initial state. so you might think that every door will end up closed? well what about door #9. 9 has the divisors 1 & 9, 3 & 3. but 3 is repeated because 9 is a perfect square, so you will only visit door #9, on pass 1, 3, and 9... leaving it open at the end. only perfect square doors will be open at the end.

Puzzle: get exactly 2 liters with the 4 ltr and 7 ltr jars

The Problem:

You've got two jars, one of them fits exactly 7 liters, the other one fits exactly 4 liters. How could you get exactly 2 liters with these two jars? You have unlimited supply of water and you are allowed to spoil some water.


The Solution:

7 0 fill the 7 ltr jar completely
3 4 fill the 4 ltr jar using the 7 ltr one completely
3 0 empty the 4 ltr jar
0 3 fill all water from 7 ltr jar to 4 ltr water
7 3 fill the 7 ltr jar completely
6 4 fill 4 ltr jar from 7 ltr one completely
6 0 empty the 4 ltr jar
2 4 fill 4 ltr jar from 7 ltr one completely
2 0 empty the 4 ltr jar

Now you have exactly 2 ltrs in 7 ltr jar

Solaris: colorize stuff

If you want to colorize directory listing in solaris, just add the following alias in your .bashrc

alias ls='ls -h --color=auto'

If you want to colorize vim editor, add below in your .bashrc

export TERM=dtterm (for solaris)
export TERM=xterm (for linux)

Perl: Date formatting

If you need to convert Date from YYYY-MM-DD format to MM/DD/YYYY format, use below perl code.

$str="2008-10-13";
$str =~s/^(\d{4})\-(\d{2})\-(\d{2})/$2\/$3\/$1/;
print $str;

will output:
10/13/2008

sed examples

Delete a line from file which contain particular string (sed -e "//d" file1 > newfile)
print all lines except first line (sed -n '2,$p' vishnu.csv)
Convert YYYYMMDD to MM/DD/YYYY (echo "YYYYMMDD"|sed -n -e "s_\(....\)\(..\)\(..\)_\2/\3/\1_p")
Remove tab character from a file (sed -e \"s/[ <TAB>]*//g\" file > output)
Find and replace values in a file (sed -i 's/find1/replace1/g' fileName)
View only lines of a file that contains a particular string (sed -n '/string/p' fileName)
View particular row number (let say 3rd) of file (sed -n '3p;3q' fileName)
View range of row (let say 2 to 8) of file (sed -n '2,8p;8q' fileName)

Perl : Formatting a number to currency format

sub formatCurrency {
my $number = sprintf "%.2f", shift @_;
# Add one comma each time through the do-nothing loop
1 while $number =~ s/^(-?\d+)(\d\d\d)/$1,$2/;
# Put the dollar sign in the right place
$number =~ s/^(-?)/$1\$/;
return $number;
}