add a new column to the file based on another file

1
votes

I have two files file1 and file2 as shown below. file1 has two columns and file2 has one column. I want to add second column to the file2 based on file1. How can I do this with awk?

file1

2WPN  B
2WUS  A
2X83  A
2XFG  A
2XQR  C

file2

2WPN_1
2WPN_2
2WPN_3    
2WUS
2X83
2XFG_1
2XFG_2
2XQR

Desired Output

2WPN_1  B
2WPN_2  B
2WPN_3  B    
2WUS    A
2X83    A
2XFG_1  A
2XFG_2  A
2XQR    C

your help would be appreciated.

1
awk

1 Answers

3
votes
awk -v OFS='\t' 'FNR == NR { a[$1] = $2; next } { t = $1; sub(/_.*$/, "", t); print $1, a[t] }' file1 file2

Or

awk 'FNR == NR { a[$1] = $2; next } { t = $1; sub(/_.*$/, "", t); printf "%s\t%s\n", $1, a[t] }' file1 file2

Output:

2WPN_1  B
2WPN_2  B
2WPN_3  B
2WUS    A
2X83    A
2XFG_1  A
2XFG_2  A
2XQR    C

You may pass output to column -t to keep it uniform with spaces and not tabs.