I have a data file similar to this (but many millions of lines long). You can assume that it is totally unsorted but has no duplicate rows.
Date ,Tool_Type ,Tool_ID ,Time_Used
3/13/2014,Screwdriver,Screwdriver02, 6
3/13/2014,Screwdriver,Screwdriver02,20
3/13/2014,Screwdriver,Screwdriver02, 1
3/13/2014,Screwdriver,Screwdriver03,14
3/13/2014,Screwdriver,Screwdriver01, 9
3/13/2014,Screwdriver,Screwdriver01,10
3/13/2014,Screwdriver,Screwdriver03,16
3/13/2014,Screwdriver,Screwdriver02,14
3/14/2014,Screwdriver,Screwdriver01,16
3/14/2014,Screwdriver,Screwdriver01, 6
3/14/2014,Screwdriver,Screwdriver01, 7
3/14/2014,Screwdriver,Screwdriver02,15
3/14/2014,Screwdriver,Screwdriver02, 7
3/14/2014,Screwdriver,Screwdriver01, 5
3/14/2014,Screwdriver,Screwdriver01, 4
3/13/2014,Hammer ,Hammer03 ,16
3/13/2014,Hammer ,Hammer02 , 9
3/13/2014,Hammer ,Hammer01 ,11
3/13/2014,Hammer ,Hammer01 ,13
3/13/2014,Hammer ,Hammer02 ,15
3/13/2014,Hammer ,Hammer02 ,19
3/13/2014,Hammer ,Hammer02 ,17
3/13/2014,Hammer ,Hammer01 ,14
3/13/2014,Hammer ,Hammer01 , 4
3/14/2014,Hammer ,Hammer02 , 9
3/14/2014,Hammer ,Hammer02 ,16
3/14/2014,Hammer ,Hammer01 , 4
3/14/2014,Hammer ,Hammer01 ,11
3/14/2014,Hammer ,Hammer03 , 5
3/14/2014,Hammer ,Hammer03 ,17
I want to get summary statistics by day-tool_type and day-tool_ID. Order is not important and I can concatenate the result of two queries if I need to.
Date ,Tool ,Times_Used,Total_Time_Used
3/13/2014,Hammer (all) ,9 ,118
3/13/2014,Hammer01 ,4 , 42
3/13/2014,Hammer02 ,4 , 60
3/13/2014,Hammer03 ,1 , 16
3/13/2014,Screwdriver (all),8 , 90
3/13/2014,Screwdriver01 ,2 , 19
3/13/2014,Screwdriver02 ,4 , 41
3/13/2014,Screwdriver03 ,2 , 30
3/14/2014,Hammer (all) ,6 , 62
3/14/2014,Hammer01 ,2 , 15
3/14/2014,Hammer02 ,2 , 25
3/14/2014,Hammer03 ,2 , 22
3/14/2014,Screwdriver (all),7 , 60
3/14/2014,Screwdriver01 ,5 , 38
3/14/2014,Screwdriver02 ,2 , 22
I think AWK is the best tool.
I think I could run through the file NR>1 building three associative arrays
count[$1 "-" $2]++
count[$1 "-" $3]++
sum[$1 "-" $2] += $4
sum[$1 "-" $3] += $4
tags[$1 "-" $2] = $1 "," $2 " (all)"
tags[$1 "-" $3] = $1 "," $3
What I don't know how to do is barf up the existing array elements at the end. Any suggestions? Am I going to run out of memory with this approach and is there a better way?
Mike
---------- Post updated at 03:59 AM ---------- Previous update was at 03:25 AM ----------
Will this work?
OFS = ","; for ( var in count) { print tags[var],count[var],sum[var] }
Mike
---------- Post updated at 09:20 AM ---------- Previous update was at 03:59 AM ----------
It works!
awk -F, 'NR == 1 {print "Headers,Redacted,For,IP,Reasons,Demand,Cum_Demand"}
NR > 1 {byToolType = ($1 "-" $2 "-" $10); byTool = ($1 "-" $3 "-" $10)
count[byToolType]++; count[byTool]++
sum[byToolType] += $7; sum[byTool] += $7
tags[byToolType] = $10","$1","$2","$2"xxx,"$1"-"$2"xxx"
tags[byTool] = $10","$1","$2","$3","$1"-"$3
}
END {OFS = ","; for ( var in count) { print tags[var], count[var], sum[var] }
}' "$scratchDir""$filename" > "$scratchDir""$outputNameString""$filename"
Never estimate the havoc leaving out -F, can wreck on an AWK script!
