Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

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?

share|improve this question
    
$ 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")) –  Olivier Dulac Jul 3 at 7:44

5 Answers 5

up vote 5 down vote accepted

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
share|improve this answer
    
Thanks a lot kraxor. Upvoted :) I am trying that now :) –  Jim Blum Jul 2 at 8:59

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
share|improve this answer
    
So does mine. I think. :) –  kraxor Jul 2 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 at 9:05
    
Wow, very good answer. Upvoted :) Thanks! –  Jim Blum Jul 2 at 9:07
    
@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 at 9:14

It should be:

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

share|improve this answer
1  
ooohhh!!! thanks a lot Yaron! Upvoted :) and how I do that if my file has several records? –  Jim Blum Jul 2 at 8:54
    
I tried that at this record "5234,0m1.022s", but I think I cannot make it work –  Jim Blum Jul 2 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 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 at 9:00

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
share|improve this answer

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?))

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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