Shell Programming and Scripting

Hi,

I have number of files which are collected from different machines. Files contains user id and their number of attempts to login. How can I combine these files based on uniq id and add number of attempts together.

For example:

$ cat file1
testid1,3
testid2,1
testid3,5

$ cat file2
testid1,2
testid4,1
testid3,1
testid6,2

$ cat file3
testid1,5
testid4,1
testid6,2

....



Desired output:

$ cat uniq_ids_with_total_login_attempts

testid1,10
testid2,1
testid3,6
testid4,2
testid6,4
.......



I tried my luck with 'join' but didn't work. Do you guys have any idea how it can be done?


Thanks,

If you join-keeping-all from file1 to file2 making file2, file 2x to file3 getting file 3x, you end up with a variable length line like:

key,#,#,#

I have a many to one join with keep-all I wrote that allows pipes (no seeks, file 1 has to be the many and file 2 the one), m1join.c, because I love pipes and hate temp files and step by step.

From there, it is not hard to process it, like with sed into ksh input:

echo "key<tab>$(( # + # + # ))"

Each ID appears only once in the file ?
If so you could create an ID file then use it to load ID to grep in the 3 files
then just sum the values of second field...

Try:

Code:

awk -F, '{a[$1]+=$2}END{for (i in a){print i FS a[i]}}' file*

PERL is good at storing and updating keyed values, too.

---------- Post updated at 10:20 AM ---------- Previous update was at 09:54 AM ----------

If you sort the files together, it is not hard to do a little ksh loop that adds while the id is identical and prints when it changes. Put a dummy id zzzzzzzz into the stream to force the last print.

Code:

(
sort . . .
echo zzzzzzzz
)| (
zlast= ztot=0
while read ln
do
 zid=${ln%%,*}

 if [ "$zid" != "$zlast" ]
 then
  if [ $ztot != 0 ]
 then
  echo $zlast,$ztot
fi  

zlast=$zid
  ztot=${ln##*,}
else
  ztot=$(( $ztot + ${ln##*,} ))
 fi
done
)

Thank you all for awesome ideas. Am going to try bartus11 & DGPickett's code on aprox. 100K ids to see which one is faster.


Thanks again.

Code:

$ ruby -F","  -ane 'BEGIN{h={};h.default=0}; h[$F[0].strip]+=$F[1].to_i if $F[0]; END{h.each_pair{|x,y| print "#{x} #{y}\n"}}' file file1 file2