CODEWITHSUNDEEP
linuxbashshellcsvawk
Pyralix
Pyralix

Reputation: 41

How to copy a value from one column to another?

I have CSV data with two price columns. If a value exists in the $4 column I want to copy it over the $3 column of the same row. If $4 is empty then $3 should be left as is.

Neither of these work:

awk -F',' '{ if (length($4) == 0) $3=$4 }'
awk -F',' '{ if(!length($4) == 0 ) print $4 }'

This will output every line with the sample table

awk -F',' '{ if(!length($4) == 0 ) print $0 }' inputfile

This will output nothing with the sample table

awk -F',' '{ if(length($4) == 0 ) print $3 }' inputfile

I've cleaned my two input files, fixed the header row, and joined them using sed, awk, sort, and join. Now what I am left with is a CSV which looks like this:

itemnumber,available,regprice,mapprice
00061,9,19.30,
00061030,31,2.87,3.19
00062,9,15.44,
00062410,2,3.59,3.99
00064,9,15.44,
00066850,29,2.87,3.99
00066871,49,4.19,5.99
00066878,3,5.63,7.99

I need to overwrite the $3 column if the $4 column in the same row has a value. The end result would be:

itemnumber,available,regprice,mapprice
00061,9,19.30,
00061030,31,3.19,3.19
00062,9,15.44,
00062410,2,3.99,3.99
00064,9,15.44,
00066850,29,3.99,3.99
00066871,49,5.99,5.99
00066878,3,7.99,7.99

Upvotes: 0

Views: 1738

Answers (4)

user7712945
user7712945

Reputation:

tried on gnu awk

awk -F, -vOFS=, '/[0-9.]+/{if($4)$3=$4} {print}' file

Upvotes: 0

kvantour
kvantour

Reputation: 26471

Let's have a look at all the things you tried:

  1. awk -F',' '{ if (length($4) == 0) $3=$4 }'

    This states, if the length if field 4 is zero then set field 3 equal to field 4. You do not ask awk to print anything, so it will not do anything. This would have printed something: 

    awk -F',' '{ if (length($4) == 0) $3=$4 }{print $0}'

    but with all field separators equal to a space, you should have done:

    awk 'BEGIN{FS=OFS=","}{ if (length($4) == 0) $3=$4 }{print $0}'

  2. awk -F',' '{ if(!length($4) == 0 ) print $4 }'

    Here you state, if the length of field 4 equals zero is not true, print field 4. As you mention that nothing is printed, it most likely indicates that you have hidden characters in field 4, such as a CR (See: Remove carriage return in Unix), or even just spaces. You could attempt something like

    awk -F',' '{sub(/ *\r?$/,""){ if(!length($4) == 0 ) print $4 }'`**

  3. awk -F',' '{ if(!length($4) == 0 ) print $0 }' inputfile

    See 2

  4. awk -F',' '{ if(length($4) == 0 ) print $3 }' inputfile

    This confirms my suspicion of 2

My solution for your problem would be based on the suggestion of 2 and the solution of Ed Morton.

awk 'BEGIN{FS=OFS=","} {sub(/ *\r?/,"")}(NR>1) && ($4!=""){$3=$4} 1' file

Upvotes: 1

Mark
Mark

Reputation: 4455

Here's code that matches your results:

awk -F, -v OFS=, '
  NR == 1 
  NR >  1 { 
    if ( $4 == "" ) 
      print $1,$2,$3,$4 
    else 
      print $1,$2,$4,$4 } 
' $*

I've run into trouble in the past with expressions like $3 = $4, so I just print out all of the fields.

Edit: I got shamed by Ed Morton for avoiding the $3 = $4 without troubleshooting. I gave it another shot here below:

awk -F, -v OFS=, '
  NR == 1 
  NR >  1 { 
    if ( $4 != "" ) 
      $3 = $4
    print 
  }
' $*

The above achieves the same results.

Upvotes: 0

Ed Morton
Ed Morton

Reputation: 203254

$ awk 'BEGIN{FS=OFS=","} (NR>1) && ($4!=""){$3=$4} 1' file
itemnumber,available,regprice,mapprice
00061,9,19.30,
00061030,31,3.19,3.19
00062,9,15.44,
00062410,2,3.99,3.99
00064,9,15.44,
00066850,29,3.99,3.99
00066871,49,5.99,5.99
00066878,3,7.99,7.99

Upvotes: 2

Related Questions