Text Conversion in Column

Shell Programming and Scripting
1

Hi all;

We have a table:

COL1 COL2 COL3 COL4 COL5 COL6 COL7
val1 val2 val3 val4 val5 1 val7
val1 val2 val3 val4 val5 3 val7
val1 val2 val3 val4 val5 5 val7
val1 val2 val3 val4 val5 2 val7
...

As you see, 6th columns are filled with numbers as, 1, 2, 3, 4, etc... and I want to make a conversion for these values such as:
if 6th column=1, write "RED";
if 6th column=2, write "BLUE";
...

and output should be:

COL1 COL2 COL3 COL4 COL5 COL6 COL7
val1 val2 val3 val4 val5 RED val7
val1 val2 val3 val4 val5 YELLOW val7
val1 val2 val3 val4 val5 ORANGE val7
val1 val2 val3 val4 val5 BLUE val7
...

how can I write it with a shell script?

2

What have you tried?

3

Using Perl

$ perl -ne 'chomp;@colour=qw(COL6 orange yellow green blue indigo violet);@r=split;print join (" ",@r[0..4],$colour[$r[5]],$r[6]),"\n";' tmp/tmp.dat
COL1 COL2 COL3 COL4 COL5 COL6 COL7
val1 val2 val3 val4 val5 orange val7
val1 val2 val3 val4 val5 green val7
val1 val2 val3 val4 val5 indigo val7
val1 val2 val3 val4 val5 yellow val7
4

I had a table like:

COL1 COL2 COL3 COL4 COL5 COL6 COL7
val1 val2 val3 val4 val5 1 val7
val1 val2 val3 val4 val5 3 val7
val1 val2 val3 val4 val5 5 val7
val1 val2 val3 val4 val5 2 val7
...

applied this:

awk 'BEGIN{OFS=FS="\t"}$5==1{$5="RED"}{print}' /var/tmp/db.txt > /var/tmp/db_new.txt

and output is:

COL1 COL2 COL3 COL4 COL5 COL6 COL7
val1 val2 val3 val4 val5 RED val7
val1 val2 val3 val4 val5 3 val7
val1 val2 val3 val4 val5 5 val7
val1 val2 val3 val4 val5 2 val7
...

but, how to add another conditions for

$5==2, 3, 4,

etc...

5 
awk 'BEGIN{a[1] = "RED"; a[2] = "BLUE"; a[3] = "YELLOW"; a[5] = "ORANGE"} NR>1 {$6 = a[$6]}1' file
6

thanks SriniShoo but:

awk 'BEGIN{a[1] = "RED"; a[2] = "BLUE"; a[3] = "YELLOW"; a[5] = "ORANGE"} NR>1 {$6 = a[$6]}1' file
awk: syntax error near line 1
awk: bailing out near line 1

how to add another conditions to:

awk 'BEGIN{OFS=FS="\t"}$5==1{$5="RED"}{print}' /var/tmp/db.txt > /var/tmp/db_new.txt
7

Given that there are no tabs in your sample input nor in your sample output, and that if the spaces shown in your input had been tabs there is no 1 in field 5 (it is in field 6); I don't see how you got that output from the input you showed us using the code you showed us???

It looks like you have sample awk code from SriniShoo and perl code from Skrynesaver that will turn your sample input into your sample output ignoring the implication that you want tab separators in your output.

8

If you're running this on a Solaris system, change awk in SriniShoo's suggestion to /usr/xpg4/bin/awk , /usr/xpg6/bin/awk , or nawk . But, if the code you had shown us before produced the output you said it did, this isn't your problem?????

9

sorry Don;
my all values are \t seperated and my code was:

awk 'BEGIN{OFS=FS="\t"}$6==1{$6="RED"}{print}' /var/tmp/db.txt > /var/tmp/db_new.txt

and it worked. but I just wonder how to add else if conditions into my command?

10

Next time, please save us all a lot of time and aggravation by telling us what OS and shell you're using and showing us sample input and output that matches the data you want to process.

A slightly modified version of SriniShoo's code should do what you want:

/usr/xpg4/bin/awk 'BEGIN{FS = OFS = "\t"; a[1] = "RED"; a[2] = "BLUE"; a[3] = "YELLOW"; a[5] = "ORANGE"} NR>1 {$6 = a[$6]}1' file
1 Like
11

thank you Don.
it is working now..