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 • 295 views
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

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

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.

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] 1 pairs of key-value loaded
A       B       C       D
1       left    red     turn
2       right   yellow
3       left    red     turn

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

0
Entering edit mode

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

Traffic: 670 users visited in the last hour
FAQ
API
Stats

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