Visit Our UNIX and Linux User Community


How to pick values from column based on key values by usin AWK


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to pick values from column based on key values by usin AWK
# 1  
Old 07-14-2009
How to pick values from column based on key values by usin AWK

Dear GuyzSmilie

I have 2 different input files like this. I would like to pick the values or letters from the inputfile2 based on inputfile1 keys (A,F,N,X,Z).
I have done similar task by using awk but in that case the inputfiles are similar like in inputfile2 (all keys in 1st column and values in second column) by using the following.

Quote:
awk 'NR==FNR{_[$1]=$1;next}$1 in _{print _[$1],$0}' INPUT1.txt INPUT2.txt
But in this case inputfile1 is different , the keys are just not in single column but also in other columns.
Could you please help me on this.

Inputfile1

A tab F tab N
D
X tab Z

Inputfile2

A tab 1200
B tab 1000
D tab 3000
F tab AHAHA
G tab XXXXX
N tab YYYYY
X tab EEEEE
Z tab GGGG

I would like to join the the values or letters based on keys in 1st input file1
(A,F,N,D,X,Z) like the following

Output
A \t 1200 \t F \t AHAHA \t N \t YYYYY
D \t 3000
X \t EEEEE Z \t GGGG
# 2  
Old 07-15-2009
Try:
Code:
while read line
do
   s=""
   for i in $line
   do
      s=$s"\t"$(grep -w $i input2.txt)
   done
   echo $s
done < input1.txt

Tested:
Code:
  A 1200 F AHAHA N YYYYY
  D 3000
  X EEEEE Z GGGG

# 3  
Old 07-15-2009
Thanx Man

Smilie
Thanx alot I will try it
# 4  
Old 07-15-2009
Try this:
Code:
> fileout.txt
while read line ; do
   xline='^'$(echo "$line" | sed 's/\t/|^/g')
   echo "$(echo "$(egrep "$xline" Inputfile2)"| tr '\n' '\t')"  >> fileout.txt
done < Inputfile1


Last edited by edidataguy; 07-19-2009 at 02:24 AM.. Reason: Correction
# 5  
Old 07-15-2009
Code:
nawk 'NR==FNR{_[$1]=$2}NR!=FNR{for(i=1;i<=NF;i++){printf("%s %s ",$i,_[$i])}print ""}' file1 file2

# 6  
Old 07-15-2009
Guys I have a Problem . Sorry for troubling.
I think you misunderstood me.
he output 1st row has to contain 3 keys as I mentioned (A, F and N)and their values
and 2nd row 1 key D and its value
3rd row ..............................................

Code:
while read line
do
   s=""
   for i in $line
   do
      s=$s"\t"$(grep -w $i input2.txt)
   done
   echo $s
done < input1.txt

Its working fine but not merging lines into a single line with tabs.like
Quote:
A 1200
\t F AHAHA
\tN YYYYY
\\t
\tD 3000
\\t
\\tX EEEEE
\tZ GGGG
others
Code:
while read line ; do
xline='^'$(echo "$line" | sed 's/\t/|^/g')
echo "$(echo "$(egrep "$xline" Inputfile2)"| tr '\n' '\t')" >> fileout.txt
done < Inputfile1

I didn't find any output

@cherry
Code:
nawk 'NR==FNR{_[$1]=$2}NR!=FNR{for(i=1;i<=NF;i++){printf("%s %s ",$i,_[$i])}print ""}' file1 file2

same problem like the 1st one. all values in seperate line like
A 1200
F AHAHA
N YYYYY
D 3000
X EEEEE
Z GGGG
# 7  
Old 07-15-2009
Quote:
Originally Posted by repinementer
[...]
@cherry
Code:
nawk 'NR==FNR{_[$1]=$2}NR!=FNR{for(i=1;i<=NF;i++){printf("%s %s ",$i,_[$i])}print ""}' file1 file2

same problem like the 1st one. all values in seperate line like
A 1200
F AHAHA
N YYYYY
D 3000
X EEEEE
Z GGGG
Just swap the input files:

Code:
nawk ... file2 file1


Previous Thread | Next Thread
Test Your Knowledge in Computers #606
Difficulty: Easy
MySQL 7.0 added the JSON utility function JSON_PRETTY(), which outputs an existing JSON value in an easy-to-read format.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Filtering based on column values

Hi there, I am trying to filter a big file with several columns using values on a column with values like (AC=5;AN=10;SF=341,377,517,643,662;VRT=1). I wont to filter the data based on SF= values that are (bigger than 400) ... (25 Replies)
Discussion started by: daashti
25 Replies

2. Shell Programming and Scripting

Concatenate values in the first column based on the second column.

I have a file (myfile.txt) with contents like this: 1.txt apple is 3.txt apple is 5.txt apple is 2.txt apple is a 7.txt apple is a 8.txt apple is a fruit 4.txt orange not a fruit 6.txt zero isThe above file is already sorted using this command: sort -k2 myfile.txtMy objective is to get... (3 Replies)
Discussion started by: shoaibjameel123
3 Replies

3. Shell Programming and Scripting

Fetch the values based on a Key using awk from single file

Hi, Please help to fetch the values for a key from below data format in linux. Sample Input Data Format 11055005|PurchaseCondition|GiftQuantity|1 11055005|PurchaseCondition|MinimumPurchase|400 11055005|GiftCatalogEntryIdentifier|Id|207328014 11429510|PurchaseCondition|GiftQuantity|1... (2 Replies)
Discussion started by: mohanalakshmi
2 Replies

4. UNIX for Dummies Questions & Answers

Repositioning based on column values

Dear all ... I have a file which I want to change the structure based on the values in some columns and I would be grateful if you can help... one of my files looks like ... they all have ten rows 1,0,0 10,0,0 2,0,0 3,0,0 4,1,1 4,1,1 4,1,1 5,0,0 6,0,0 7,0,0 8,0.5,2 9,0.33,3 9,0.33,3... (1 Reply)
Discussion started by: A-V
1 Replies

5. Shell Programming and Scripting

Sum column values based in common identifier in 1st column.

Hi, I have a table to be imported for R as matrix or data.frame but I first need to edit it because I've got several lines with the same identifier (1st column), so I want to sum the each column (2nd -nth) of each identifier (1st column) The input is for example, after sorted: K00001 1 1 4 3... (8 Replies)
Discussion started by: sargotrons
8 Replies

6. Shell Programming and Scripting

Adding values of a column based on another column

Hello, I have a data such as this: ENSGALG00000000189 329 G A 4 2 0 ENSGALG00000000189 518 T C 5 1 0 ENSGALG00000000189 1104 G A 5 1 0 ENSGALG00000000187 3687 G T 5 1 0 ENSGALG00000000187 4533 A T 4 2 0 ENSGALG00000000233 5811 T C 4 2 0 ENSGALG00000000233 5998 C A 5 1 0 I want to... (3 Replies)
Discussion started by: Homa
3 Replies

7. Shell Programming and Scripting

Pick the column value based on another column using awk or CUT

My scenario is that I need to pick value from third column based on fourth column value, if fourth column value is 1 then first value of third column.Third column (2|3|4|6|1) values are cancatenated. Please someone help me to resolve this issue. Source column1 column2 column3 column4... (2 Replies)
Discussion started by: Ganesh L
2 Replies

8. Shell Programming and Scripting

Average values in a column based on range

Hi i have data with two columns like below. I want to find average of column values like if the value in column 2 is between 0-250000 the average of column 1 is some xx and average of column2 is ww then if value is 250001-5000000 average of column 1 is yy and average of column 2 is zz. And my... (5 Replies)
Discussion started by: bhargavpbk88
5 Replies

9. Shell Programming and Scripting

How to averaging column based on first column values

Hello I have file that consist of 2 columns of millions of entries timestamp and throughput I want to find the average (throughput ) for each equal timestamp before change it to proper format e.g : i want to average 2 coloumnd fot all 1308154800 values in column 1 and then print... (4 Replies)
Discussion started by: aadel
4 Replies

10. Shell Programming and Scripting

Print a key with its all values using awk/others

input COL1 a1 b1 c1 d1 e1 f1 C1 10 10 10 100 100 1000 C2 20 20 200 200 200 2000 output C1 a1 10 1 C1 b1 10 1 C1 c1 10 1 C1 d1 100 2 C1 e1 100 2 C1 f1 1000 3 C2 ... (12 Replies)
Discussion started by: ruby_sgp
12 Replies

Featured Tech Videos