0

I have financial price data in a csv file, a typical line of which looks like this:

2012-06-06 18:00:00,2012-06-06 19:00:00,4,2012-06-06 14:00:00,4,1644.8005,1644.8,1631.232,1632.266,7353,TRUE

and I would like to conditionally replace, in this particular example, the contents of column 7 with that of column 6 such that columns 6 and 7 both contain the same value.

The file line numbers on which such conditional operations should be completed are to be read from a separate text file ( let's call it "line_index_file" which is output from another program, Octave ) and this looks, for example, like this:

100
125

which means the above operations should only be applied to lines 100 and 125 of the original data file.

How would I do this with either sed or awk?

1

You can try this awk

awk 'BEGIN{FS=OFS=","}NR==FNR{a[$0];next}FNR in a {$7=$6}1' line_index_file file.csv
1

You may use the sed editor to do this task also:

cmds=$(< line_index_file xargs -l printf '\t%sba\n')

sed -e "
${cmds}
    ;# skip these
    b
    :a
    s/,/\n/5;s//\n/5;s//\n/5
    s/\n\(.*\)\n.*\n/,\1,\1,/
" input.csv

Explanation:

  • First create a shell variable, cmds, holding the branching to the label :a information for the lines mentioned in the line_index_file.
  • Skip processing the other lines, that is those lines that are not mentioned in line_index_file.
  • Place a marker \n (since a newline is guaranteed to not be found in the pattern space of sed in our case) at the ending of 5th, 6th, and 7th fields.
  • Then perform the operation to place the contents of the 6th field into the 7th.
0

Try this

 for i in `cat line_index_file`
 do
     echo -e "`awk -v b=$i -F ',' 'BEGIN{OFS = ","} NR!=b{$7=$6};{print $0}' file.csv`" > file.csv
 done
  • NR!=b ignore the line numbers for the condition
  • This is not working for me. If I run it once only there are no problems, but if I then run a similar bash script with different values in a different line_index_file_2 and replacing different column's values, e.g. NR!=b{$8=$6}, the new values in the line_index_file_2 seem to be ignored and the original line_index_file values are used. – babelproofreader Aug 20 '18 at 11:28

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.