4

I have a CSV that has the output of time commands of different programs including the ID of the execution.

Therefore my files contain records in the following form:

ID,execution_time

Example Record:

345,0m0.047s

I only need the number of execution and the number of seconds.

Therefore the above record needs to be

345,0047 (without the "0m" the "." and the "s").

Is it possible to achieve that?

1
  • $ time sleep 61 outputs: real 1m1.046s (ie, you don't want to get rid of the minutes... and you need sometimes to have leading zeroes (here, 1s is "1" and not "01")) Jul 3, 2014 at 7:44

5 Answers 5

10

What if there are minutes, eh? Here's one that counts for that, converting them into seconds:

awk -F'[m,]' '{print $1 "," $2*60 + $3}' file.csv

And a few demos:

$ ... <(echo -e "345,0m0.047s\n345,1m0.047s\n345,1m2.047s")
345,0.047
345,60.047
345,62.047

I'm not sure why you'd ever want to remove the decimal point but you can pass the output through sed 's/\.//g' if you really need that:

$ ... <(echo -e "345,0m0.047s\n345,1m0.047s\n345,1m2.047s") | sed 's/\.//g'
345,0047
345,60047
345,62047

Eugh.


And —assuming this is a time output— what if the value contains hours or even days? I've played around a bit and have come up with this little beaut:

awk -F'[dhms,]' 'BEGIN{split("1 60 3600 86400", T, " ")}{t=0; for (i=NF-1; i>1; i--) t+=T[NF-i]*$i; printf("%s,%.3f\n", $1, t)}'

This will handle the following formats: *s *m*s *h*m*s and *d*h*m*s and convert them all into seconds (to three decimal places). Obligatory demo time:

$ ... <(echo -e "345,1.2s\n345,12m5s\n345,1h2m5s\n345,1d2h1m2.047s")
345,1.200
345,725.000
345,3725.000
345,93662.047
3
  • So does mine. I think. :)
    – kraxor
    Jul 2, 2014 at 9:01
  • 1
    @kraxor Yours doesn't multiply them by 60 though, you're just leaving the minute value on the front of the seconds.
    – Oli
    Jul 2, 2014 at 9:05
  • @Oli you're right. I guess I took "the number of seconds" literally. OP also said "withouth the 0m". +1 to you though for a more generic solution.
    – kraxor
    Jul 2, 2014 at 9:14
5

Here is my solution:

sed -i 's/[0-9]*m\|s\|\.//g' file.csv

You may want to skip the first line, in case it contains the header, like this:

sed -i '2,$s/[0-9]*m\|s\|\.//g' file.csv

Note that the -i option causes sed to work "in place", altering the original file. If you want to create a new file instead, use this:

sed 's/[0-9]*m\|s\|\.//g' file.csv > new_file.csv
0
4

It should be:

sed 's/m0\.\(.*\)s$/\1/g'

4
  • 1
    ooohhh!!! thanks a lot Yaron! Upvoted :) and how I do that if my file has several records?
    – Jim Blum
    Jul 2, 2014 at 8:54
  • I tried that at this record "5234,0m1.022s", but I think I cannot make it work
    – Jim Blum
    Jul 2, 2014 at 8:58
  • Just write them line by line in a file (you can do this using your favourite text editor or automatically using the bash concatenation feature, and then just type cat myfile.txt | sed... or sed -i.bak sed ... myfile.txt, hope it works fine for you :)
    – Yaron
    Jul 2, 2014 at 8:58
  • Sure @JimBlum it has 1. in it and the script is only removing 0., give me some more examples and I'll try to make something more general (the original and desired outcome of course)
    – Yaron
    Jul 2, 2014 at 9:00
1

Another one through awk,

awk -F, -v OFS="," '{gsub(/0m/,"",$2);gsub(/\./,"",$2);gsub(/s/,"",$2); print $1,$2}' file

Example:

$ echo '345,0m0.047s' | awk -F, -v OFS="," '{gsub(/0m/,"",$2);gsub(/\./,"",$2);gsub(/s/,"",$2); print $1,$2}'
345,0047

And the appropriate one,

$ echo '345,52m0.047s' | awk -F, -v OFS="," '{gsub(/^.*?m/,"",$2);gsub(/\./,"",$2);gsub(/s/,"",$2); print $1,$2}'
345,0047
1

The answer of @kraxor uses sed to remove "everything else" from the line, and then just show the line.

But - what actually is "everything else"? Here we have an idea at least, from one sample line. But what when we get a CSV header line? What to remove?
In general, we don't know.

Therefore, it is better to actually show the data we want to show!

echo '345,0m0.047s' | sed -n -r 's/^(.*),.*[^0-9]([0-9]*)\.(.*)s$/\1,\2\3/p'

345,0047

Works so far!

What does it do?
We do not print the line normaly (-n)
(and activate the nicer "extended regular expressions" (-r)) now look for the ID, second, and fractional second,
and if we found them, put them into the line, in the right format (adding ","),
and print the newly created line.

Now some other input, pretty normal, with two lines of data:

ID,execution_time
123, Oops a comment0m0.0333s
345,0m0.047s

Huh?! Looks like real world data, actually!

echo "ID,execution_time\n123, Oops a comment0m0.0333s\n345,0m0.047s" | sed -r -n 's/^(.*),.*[^0-9]([0-9]*)\.(.*)s$/\1,\2\3/p'

123,00333
345,0047

Looks nice and just right!



To show that it has some merit to do it this way, I'll compare to the earlier answer:

echo "ID,execution_time\n123, Oops a comment0m0.0333s\n345,0m0.047s" | sed 's/[0-9]*m\|s\|\.//g'

ID,execution_tie
123, Oop a coent00333
345,0047

Ok, the actual, clean data lines went through nicely;
But the other parts, not so much (note the header was not just passed, but cut off.).

(Note there is a solution to explicitly skip the header line (or maybe the first data line, do we know?))

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .