2

I have fb.csv file like below.

"Source","Time"  
"192.168.137.174","12:26:25"
"10.0.138.163","12:26:25"
"157.240.10.13","12:26:36"
"157.240.10.13","12:26:36"
"157.240.10.23","12:26:41"
"157.240.10.23","12:26:41"
"10.0.138.163","12:26:52"
"192.168.137.174","12:26:52"
"157.240.10.18","12:26:52"
"157.240.10.18","12:26:52"
"157.240.10.23","12:26:53"
"157.240.10.23","12:26:53"
"192.168.137.174","12:27:02"
"10.0.138.163","12:27:02"
"192.168.137.174","12:27:07"

I would like to find the difference value between the longest time and the shortest time for the same "source".

Desired output;

"Source","Duration Time"  
"192.168.137.174","00:01:22"
"10.0.138.163","00:01:17"
"157.240.10.13","00:00:00"
"157.240.10.23","00:00:00"
"157.240.10.18","00:00:00"

Is there any method to that? Thanks

4
  • 2
    It's needless to say there's a oneline solution out there, but I'd write a script to do that – beginning with this monster: source='"192.168.137.174"';sed -e /$source/\!d -e 's/.*"\([0-9]*\)"$/\1/' fb.csv | sort -r | sed ':a;N;$!ba;s/\n/-/g;s/-.*-/-/' | bc | sed 's/.*/'$source',"&"/' – and to get a list of the different sources for the loop to work through: sed '1d;s/,.*//' fb.csv | sort -u. I bet perl or awk can do better though… – dessert Sep 14 '17 at 20:14
  • @pa4080 if you dont mind helping me with this. Thanks – Ayu Sep 15 '17 at 7:53
  • You can't notify someone who didn't comment yet. Btw, did you at least try to solve the problem by yourself in any way? – dessert Sep 15 '17 at 9:01
  • @dessert, The sed command is entered for every source. The source is not fixed as I will capture more data. Could you help me by doing script that will operate with any source captured? Using awk or any bash script. – Ayu Sep 15 '17 at 10:10
2

it's me again, the guy with the huge awk one-line commands... This one is even longer:

awk -F, 'BEGIN{print"\"Source\",\"Duration Time\""}NR>1{gsub(/"/,"",$2);split($2,hms,":");s=hms[1]*3600+hms[2]*60+hms[3];if(!(($1,"MAX")in a)||a[$1,"MAX"]<s)a[$1,"MAX"]=s;if(!(($1,"MIN")in a)||a[$1,"MIN"]>s)a[$1,"MIN"]=s}END{for(idx in a){split(idx,ipm,SUBSEP);if(ipm[2]=="MAX"){d=a[idx]-a[ipm[1],"MIN"];h=int(d/3600);m=int((d-h*3600)/60);s=d%60;printf("%s,\"%02d:%02d:%02d\"\n",ipm[1],h,m,s)}}}' fb.csv 

With your given fb.csv input file from the question, the output looks like this:

"Source","Duration Time"
"157.240.10.23","00:00:12"
"157.240.10.18","00:00:00"
"157.240.10.13","00:00:00"
"10.0.138.163","00:00:37"
"192.168.137.174","00:00:42"

Explanation of the command:

We run awk like this here, setting the field separator that delimits columns to , and using the file fb.csv as input:

awk -F, '<COMMAND>' fb.csv

The awk command (placeholder <COMMAND> above) is this, after proper formatting:

BEGIN {
    print "\"Source\",\"Duration Time\""
}
NR>1 {
    gsub(/"/, "", $2)
    split($2, hms, ":")
    s = hms[1]*3600 + hms[2]*60 + hms[3]
    if ( !(($1,"MAX") in a) || a[$1,"MAX"] < s )
        a[$1,"MAX"] = s
    if ( !(($1,"MIN") in a) || a[$1,"MIN"] > s )
        a[$1,"MIN"] = s
}
END {
    for (idx in a) {
        split(idx, ipm, SUBSEP)
        if (ipm[2]=="MAX") {
            d = a[idx] - a[ipm[1],"MIN"]
            h = int(d / 3600)
            m = int((d - h * 3600) / 60)
            s = d%60
            printf("%s,\"%02d:%02d:%02d\"\n", ipm[1] ,h ,m ,s)
        }
    }
}
  • The BEGIN block simply prints the new CSV header.

  • The NR>1 block runs once per line in the input file, except for the first line which contains the header. Each line gets split into the IP column ($1) and the time column ($2).

    We process the time column by stripping the quotes with gsub and splitting it at the colons into an array hms which contains the hours, minutes and seconds. This is used to convert the time stamp to seconds since midnight, stored in s in this block.

    Next we check the associative array if it does not have an entry with the current line's IP yet or if the entry has smaller MAX or larger MIN time values, in which case it will be updated accordingly.

  • Finally, in the END block the created array gets evaluated and for each IP in it, the difference between the MAX and MIN time stamps is calculated and saved as d. This gets converted back into hours, minutes and seconds and outputted properly formatted.

9
  • can you help me run my file. I'll give you the file. I got some error in the duration time. – Ayu Sep 15 '17 at 13:46
  • Can you simply upload it e.g. to pastebin.org and share the link here? Also, what exactly is wrong? – Byte Commander Sep 15 '17 at 14:51
  • here is the link; dropbox.com/home?preview=tw1.csv : I got problem in the duration time value, for example "104.244.42.1","00:540:23" "192.168.137.241","00:2580:19" "192.168.137.174","00:3360:58". There is problem in the duration time. – Ayu Sep 15 '17 at 17:05
  • @Ayu Can you please use a host that does not require a login? I can not access your file. – Byte Commander Sep 15 '17 at 18:42
  • sure. Here is the link; ufile.io/d42n7 – Ayu Sep 15 '17 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.