Command to insert column with text according to specific text in another column
2
0
Entering edit mode
9 days ago
Nelo • 0

Hlo everyone

I have a question here

Can somebody tell me any command in unix to add an extra "column with text" according to a specific text in another column

Suppose:

A      B        C            
1   left      red         
2   right   yellow
3   left      red

This is my file with three column (A, B & C).

I want to add an another column(D) with text "turn" on this column D according to text "left" in column B.

Final file should be like this:

A      B        C           D            
1   left      red        turn        
2   right   yellow  
3   left      red        turn

I have more than 2000 rows, and more than 100 such text ,so it is difficult to do manually.

commands unix • 296 views
ADD COMMENT
1
Entering edit mode
9 days ago

Neat trick

Assumes tab-delimited:

cat test.tsv ;
A   B   C
1   left    red
2   right   yellow
3   left    red

awk -F "\t" '{if (NR == 1) {print $0"\tD"} else {if ($2 ~ /left/) {print $0"\tturn"} else {print $0}}}' test.tsv ;
A   B   C   D
1   left    red turn
2   right   yellow
3   left    red turn

We can tidy-up the AWK command:.

awk -F "\t" '{
  if (NR == 1) {
    print $0"\tD"
  } else {
    if ($2 ~ /left/) {
      print $0"\tturn"
    } else {
      print $0
    }
  }
}' test.tsv ;

A   B   C   D
1   left    red turn
2   right   yellow
3   left    red turn

Kind regards,

Kevin

ADD COMMENT
0
Entering edit mode

Good morning Kevin

Can you please give a little bit of explanation to the command you have suggested.

And also I have to repeat this command multiple numbers of times because I have different text in Column B and have to assign another different text accordingly to column B in column D.

So, I am looking for a command in which, using different text in Column B , I can assign bunch of different text in Column D.

Thank You

ADD REPLY
0
Entering edit mode

Hi, you didn't explain these extra conditions in your initial question. Thus, from my perspective, you can adapt my script on your own time. Currently, my answer does exactly as requested in your initial / original Question, and I will leave it there to avoid the creation of a messy thread. Good luck.

ADD REPLY
1
Entering edit mode
9 days ago

A more general solution with csvtk replace, check the second example.

Here's the mapping file, you can add more key-value pairs.

$ cat map.tsv 
left    turn

Create a new column D first:

$ csvtk mutate -t -n D -f B test.tsv
A       B       C       D
1       left    red     left
2       right   yellow  right
3       left    red     left

Then replace keys with values:

$ csvtk mutate -t -n D -f B test.tsv \
      | csvtk replace -t -f D -k map.tsv -p '(.+)' -r '{kv}'
[INFO] read key-value file: map.tsv
[INFO] 1 pairs of key-value loaded
A       B       C       D
1       left    red     turn
2       right   yellow
3       left    red     turn
ADD COMMENT
0
Entering edit mode

Good morning Shenwei356

I am not able to understand the command you have suggested. Like I said before to Kevin Blighe , I need a command to be used where there is multiple queries in one column and have have to assign different text in another column accordingly.

I hope you get my questions

Thanks SC

ADD REPLY
0
Entering edit mode

I get what you want. Just follow the help message from the link and the example for you.

ADD REPLY

Login before adding your answer.

Traffic: 621 users visited in the last hour
Help About
FAQ
Access RSS
API
Stats

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.

Powered by the version 2.3.6