Add Delimiter after 2 decimal point for a particular column


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Add Delimiter after 2 decimal point for a particular column
# 8  
Old 04-04-2017
Thanks RudiC, It willl be great if you can explain the sed command you share as I not really understand sed command well.
# 9  
Old 04-04-2017
It's a substitute command, matching a not-too-complicated regex:
Code:
sed -r 's/(\.[0-9][0-9])([^§])/\1§\2/2' file

sed -r          # switching on ERE (extended regex) behaviour
'               # single quote to open the first parameter
s               # substitute command
/               # default char to open a regex
(               # opening the first parenthesized subexpression for back reference
\.[0-9][0-9]    # regex: escaped dot, double digit
)               # closing first par. subexp.
([^§])          # second par. subexp.: any char except §
/               # default char to switch from matching pattern to replacement string
\1              # use first back reference
§               # insert literal §
\2              # use second b.r.
/               # close replacement string
2               # option flag: apply to second occurrence only
'               # close first parameter
 file           # file to read / modify

This User Gave Thanks to RudiC For This Post:
# 10  
Old 04-04-2017
Thanks RudiC for your clear explanation.
# 11  
Old 04-04-2017
Quote:
Originally Posted by ckwan123
...
...
manage to get it resolve by minor change on the occurrence, instead of /g use /2 on the sed command we use.
Code:
  sed 's/\(\.[0-9][0-9]\)\([^§]\)/\1§\2/2'

  1007937820§L§2016-12-19§000000002§2018-02-01§2050-12-01§00395§M§146713.57§00005.05000§762.59§00395§M§301223.05§28§1165§2017-03-31 20:34:25

Hopes we can also know how it can be achieve by using perl command too.
...
...
I must say, that feature of sed is pretty impressive!
In Perl, you could use the "e" modifier with some Perl code in the replacement pattern that increments a counter and replaces conditionally. I didn't try it though.
Alternatively, here's the long version of the solution.
I loop through a line based on the pattern "\.\d\d", incrementing a counter as well and replacing, if required.
Some flexibility has been added as well, so you can replace only 2nd occurrence, or 2nd,4th,6th or 2nd through 5th or all etc.

Code:
$ 
$ cat -n replace_occurrences.pl 
     1    #!/usr/bin/perl
     2    # Usage: perl replace_occurrences.pl data.txt 2      # replace 2nd occurrence only
     3    #        perl replace_occurrences.pl data.txt 2,4,5  # replace 2nd, 4th, 5th occurrences
     4    #        perl replace_occurrences.pl data.txt 2-6    # replace 2nd through 6th occurrences
     5    #        perl replace_occurrences.pl data.txt        # replace all occurrences
     6    #        perl replace_occurrences.pl data.txt 3-     # replace the 3rd and all occurrences after it
     7    use strict;
     8    my $infile = $ARGV[0];
     9    my $rep_range = $ARGV[1];
    10    my %rep;
    11    my $till_the_end = 0;
    12    my $start_index;
    13    
    14    # Subroutine section
    15    sub set_rep_indexes {
    16        if ($rep_range =~ /^\d+$/) {
    17            $rep{$rep_range}++;
    18        } elsif ($rep_range =~ /,/) {
    19            %rep = map {$_ => 1} split(',', $rep_range);
    20        } elsif ($rep_range =~ /(\d+)-(\d+)/) {
    21            for(my $i=$1; $i<=$2; $i++) { $rep{$i}++ }
    22        } elsif ($rep_range eq "" or $rep_range =~ /(\d+)-/) {
    23            $start_index = $rep_range eq "" ? 1 : $1;
    24            $till_the_end = 1;
    25        }
    26    }
    27    
    28    sub replace_occurrences {
    29        my $x = shift;
    30        my ($str, $pre, $token, $post) = ("", "", "", "");
    31        my $count = 1;
    32        do {
    33            ($pre, $token, $post) = $x =~ /(.*?)(\.\d\d)([^§])/;
    34            if ($pre ne "") {
    35                if (($till_the_end and $count >= $start_index) or defined $rep{$count}) {
    36                    $str .= "${pre}${token}§${post}"
    37                } else {
    38                    $str .= "${pre}${token}${post}"
    39                }
    40                $x =~ s/$pre$token$post//;
    41                $count++;
    42            }
    43        } until ($pre eq "");
    44        $str .= $x;
    45        return $str;
    46    }
    47    
    48    # Main section
    49    set_rep_indexes;
    50    open(FH, '<', $infile) or die "Can't open $infile: $!";
    51    while (<FH>) {
    52        chomp(my $line = $_);
    53        print replace_occurrences($line),"\n";
    54    }
    55    close(FH) or die "Can't close $infile: $!";
    56    
$ 
$ # Original and unchanged pattern is in red, modified pattern is in purple.
$ cat data.txt
1007937820§L§2016-12-19§000000002§2018-02-01§2050-12-01§00395§M§146713.57§00005.05000§762.59§00395§M§301223.0528§1165§2017-03-31 20:34:25
0.556abc§def§0005.0500§ghi§jkl§123.45§678§mn§opq§5678.90123§rs§tuvw§0.1234§xyz§6.55
$ 
$ perl replace_occurrences.pl data.txt 2
1007937820§L§2016-12-19§000000002§2018-02-01§2050-12-01§00395§M§146713.57§00005.05000§762.59§00395§M§301223.05§28§1165§2017-03-31 20:34:25
0.556abc§def§0005.05§00§ghi§jkl§123.45§678§mn§opq§5678.90123§rs§tuvw§0.1234§xyz§6.55
$ 
$ perl replace_occurrences.pl data.txt 1,2,4
1007937820§L§2016-12-19§000000002§2018-02-01§2050-12-01§00395§M§146713.57§00005.05§000§762.59§00395§M§301223.05§28§1165§2017-03-31 20:34:25
0.55§6abc§def§0005.05§00§ghi§jkl§123.45§678§mn§opq§5678.90123§rs§tuvw§0.12§34§xyz§6.55
$ 
$ perl replace_occurrences.pl data.txt 1-3
1007937820§L§2016-12-19§000000002§2018-02-01§2050-12-01§00395§M§146713.57§00005.05§000§762.59§00395§M§301223.05§28§1165§2017-03-31 20:34:25
0.55§6abc§def§0005.05§00§ghi§jkl§123.45§678§mn§opq§5678.90§123§rs§tuvw§0.1234§xyz§6.55
$ 
$ perl replace_occurrences.pl data.txt 2-
1007937820§L§2016-12-19§000000002§2018-02-01§2050-12-01§00395§M§146713.57§00005.05000§762.59§00395§M§301223.05§28§1165§2017-03-31 20:34:25
0.556abc§def§0005.05§00§ghi§jkl§123.45§678§mn§opq§5678.90§123§rs§tuvw§0.12§34§xyz§6.55
$ 
$ perl replace_occurrences.pl data.txt
1007937820§L§2016-12-19§000000002§2018-02-01§2050-12-01§00395§M§146713.57§00005.05§000§762.59§00395§M§301223.05§28§1165§2017-03-31 20:34:25
0.55§6abc§def§0005.05§00§ghi§jkl§123.45§678§mn§opq§5678.90§123§rs§tuvw§0.12§34§xyz§6.55
$

If you are looking for a one-liner, this isn't it unfortunately.
And it doesn't have to be in Perl. It's just an algorithm that you can implement in any language: awk, Bash, Python, C etc. that you are comfortable with and is in the toolset at your disposal.
Cheers!

Last edited by durden_tyler; 04-05-2017 at 10:10 AM..
This User Gave Thanks to durden_tyler For This Post:
# 12  
Old 04-04-2017
Thanks Durden for your good effort to try this solution in perl language. Indeed unix sed is a mutual, powerful tool. Without it, we are probably need to write lengthy script to achieve same objective.
# 13  
Old 04-05-2017
Quote:
Originally Posted by ckwan123
...Indeed unix sed is a mutual, powerful tool. Without it, we are probably need to write lengthy script to achieve same objective.
Agreed.

Code:
$ 
$ cat data.txt
1007937820§L§2016-12-19§000000002§2018-02-01§2050-12-01§00395§M§146713.57§00005.05000§762.59§00395§M§301223.0528§1165§2017-03-31 20:34:25
0.556abc§def§0005.0500§ghi§jkl§123.45§678§mn§opq§5678.90123§rs§tuvw§0.1234§xyz§6.55
$ 
$ perl -plne '$i=1; s/(\.\d\d)(?!§)/$i++ == 2 ? $1."§" : $1/eg' data.txt
1007937820§L§2016-12-19§000000002§2018-02-01§2050-12-01§00395§M§146713.57§00005.05000§762.59§00395§M§301223.05§28§1165§2017-03-31 20:34:25
0.556abc§def§0005.05§00§ghi§jkl§123.45§678§mn§opq§5678.90123§rs§tuvw§0.1234§xyz§6.55
$ 
$


Last edited by durden_tyler; 04-05-2017 at 12:27 AM..
This User Gave Thanks to durden_tyler For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Convert a numeric to 2 decimal point value

Hi , I have a file which contains text like A|Mau|Code|12|Detail B|Mau|Code|20|Header I want to write a command using awk which will output A|Mau|Code|12.00|Detail B|Mau|Code|20.00|Header I used a command like awk -F"|" {printf "%s|%s|%s|%.2f|%s",$1,$2,$3,$4,$5}' which does the... (4 Replies)
Discussion started by: LoneRanger
4 Replies

2. UNIX for Beginners Questions & Answers

How to add following decimal point to a CSV value?

hi there I being trying to figure out way to add " .0" to an integer value in a csv using sed or awk with out success. just as a work around for 2147483647 32 bit limitation that influxdb is currently having the data base will accept values and work fine if it has the XXX.0 ... (7 Replies)
Discussion started by: sash99
7 Replies

3. Shell Programming and Scripting

Help with Round Up with 2 decimal point at specific column

Input file: USA 20.5683 UK 3.54221 Japan 2.54001 China 2.50897 Germany 2.05816 . . Desired output file: USA 20.57 UK 3.54 Japan 2.54 China 2.51 Germany 2.06 . . (2 Replies)
Discussion started by: perl_beginner
2 Replies

4. Shell Programming and Scripting

Check for decimal point and add it at the end if its not there using awk/perl

I have test.dat file with values given below: 20150202,abc,,,,3625.300000,,,,,-5,,,,,,,,,,,,,,,,,,,,,, 20150202,def,,,,32.585,,,,,0,,,,,,,,,,,,,,,,,,,,,, 20150202,xyz,,,,12,,,,,0.004167,,,,,,,,,,,,,,,,,,,,,, My expected output is shown below: ... (1 Reply)
Discussion started by: nvk_vinoth
1 Replies

5. Shell Programming and Scripting

awk decimal point numbers matching

Hi All, Can some one help me in identifying the significance of character "$" ,Which is playing critical role in matching decimal point numbers as below. $ echo "01#.01"|awk '{if ($0 ~ /^+(\.*)?$/) print}' $ echo "01#.01"|awk '{if ($0 ~ /^+(\.*)?/) print}' 01#.01 $ Regards, Rmkganesh. (3 Replies)
Discussion started by: rmkganesh
3 Replies

6. Shell Programming and Scripting

remove directories with two digits after decimal point

Hi everyone, I am new here and generally not experienced with linux. My question must be easy, but as for now I have no idea how to do it. I have lots of directories with numerical names, e.g. 50 50.1 50.12 etc. What I want is to leave directories with no or single digit after the decimal... (2 Replies)
Discussion started by: cabaciucia
2 Replies

7. Shell Programming and Scripting

Insert decimal point for numbers

Hi In Unix, I have a file with some numbers like : 45600 12345 I want to insert a decimal point for these numbers based on user input. If the input is 2, the numbers should be changed to 456.00 123.45 If the input is 3, the numbers should be changed to 45.600 12.345 Can... (2 Replies)
Discussion started by: yoursdivu
2 Replies

8. Shell Programming and Scripting

Comparing two numbers with decimal point

How to compare two numbers with decimal points ? Is there a way in bash to do this? (33 Replies)
Discussion started by: kinny
33 Replies

9. Shell Programming and Scripting

Insert a decimal point

Hi all. Using /bin/sh on an HPUX system. I want to place a decimal in the field 2 charactors from the right (yes, converting to currency). The field lengths are variable. Here's what I'm doing: exec < filename while read FIELD1 FIELD2 do FIELD1="echo $FIELD1 | sed 'syntax that will... (4 Replies)
Discussion started by: lyoncc
4 Replies

10. Shell Programming and Scripting

how to get rid of decimal point?

Hi, I have input with decimal point ( 9.99 ) for hours variable hrs. I need to change it to seconds. Here is my code: secs=`/usr/ucb/echo $hrs*3600 |bc` But I don't want to see the decimal point. I can use awk to trim it if there is one. I am just wondering if there is better standard... (2 Replies)
Discussion started by: cin2000
2 Replies
Login or Register to Ask a Question