Command to insert column with text according to specific text in another column

Question

[Nelo]

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.

Answer 1

[Kevin Blighe]

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

[Nelo]

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

[Kevin Blighe]

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.

Answer 2

[shenwei356]

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

[Nelo]

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

[shenwei356]

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