Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Match value in column and append file with new values
Top Forums Shell Programming and Scripting Match value in column and append file with new values Post 302916412 by RavinderSingh13 on Wednesday 10th of September 2014 01:39:24 AM
Old 09-10-2014
Hello,

Following may help you. (It will give Tra word at last)

Code:
cat check_file2.ksh
awk 'NR==FNR{
{if(FNR==1)
        {for(i=1;i<=NF;i++)
                {Y[i]=$i}
        }
}
{if(NR>1)
        {for(j=1;j<=NF;j++)
                {if($j == "Yes")
                        {A=A?A","Y[j]:Y[j]}
                                {if(j==NF && A)
                                        {X[$1]=A OFS "Yes";A=""}
                                }
 
                {if($j == "No")
                        {p++}
                }
                {if(p==NF)
                        {X[$1]="-" OFS "No";}
                }
                }
 
        }
}
            }
($2 in X) {print $0 OFS X[$2]}
' OFS="\t" file_1 file_2

Output will be as follows.

Code:
192.1   g13950  192.168.1.2.58  79%     324     BRK brag        11.1663 Dressrossa season       Ca,Pro,Su,Ot,Tra        Yes
192.1   k17720  192.168.1.2.77  81%     777     BRK brag        12.9651 Dragon era      An,Tra  Yes
192.1   g12640  192.168.1.2.78  73%     569     FA match        26.4916 Dragon era      -       No
192.1   g05640  192.168.1.2.86  93%     961     Zorro Crack     20.1251 In the sky      AA,An,Ca,Su,Ot,Tra      Yes
188.1   g05760  192.168.1.2.86  91%     1225    Lucy Sanji      25.974  Island  AA,Su,Ot,Tra    Yes


EDIT: Following command will remove Tra word as per your condition.

Code:
cat check_file2.ksh
awk 'NR==FNR{
{if(FNR==1)
        {for(i=1;i<=NF;i++)
                {Y[i]=$i}
        }
}
{if(NR>1)
        {for(j=1;j<=NF;j++)
                {if($j == "Yes" && Y[j] !~ "Tra")
                        {A=A?A","Y[j]:Y[j]}
                                {if(j==NF && A)
                                        {X[$1]=A OFS "Yes";A=""}
                                }
 
                {if($j == "No")
                        {p++}
                }
                {if(p==NF)
                        {X[$1]="-" OFS "No";}
                }
                }
 
        }
}
            }
($2 in X) {print $0 OFS X[$2]}
' OFS="\t" file_1 file_2

Output will be as follows.

Code:
192.1   g13950  192.168.1.2.58  79%     324     BRK brag        11.1663 Dressrossa season       Ca,Pro,Su,Ot    Yes
192.1   k17720  192.168.1.2.77  81%     777     BRK brag        12.9651 Dragon era      An      Yes
192.1   g12640  192.168.1.2.78  73%     569     FA match        26.4916 Dragon era      -       No
192.1   g05640  192.168.1.2.86  93%     961     Zorro Crack     20.1251 In the sky      AA,An,Ca,Su,Ot  Yes
188.1   g05760  192.168.1.2.86  91%     1225    Lucy Sanji      25.974  Island  AA,Su,Ot        Yes

Thanks,
R. Singh
Last edited by RavinderSingh13; 09-10-2014 at 02:56 AM.. Reason: changed OFS to tab + improved solution which will remove Tra in Output
This User Gave Thanks to RavinderSingh13 For This Post:
redse171
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Finding multiple column values and match in a fixed length file

Hi, I have a fixed length file where I need to verify the values of 3 different fields, where each field will have a different value. How can I do that in a single step. (6 Replies)
Discussion started by: naveen_sangam
6 Replies

2. Shell Programming and Scripting

Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2

Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2 file 1 sample SNDK 80004C101 AT XLNX 983919101 BB NETL 64118B100 BS AMD 007903107 CC KLAC 482480100 DC TER 880770102 KATS ATHR 04743P108 KATS... (7 Replies)
Discussion started by: rydz00
7 Replies

3. UNIX for Dummies Questions & Answers

Match values from 2 files and append certain fields

Hi, I need help on appending certain field in my file1.txt based on matched patterns in file2.txt using awk or sed. The blue color need to match with one of the data in field $2 in file2.txt. If match, BEGIN and FINISHED value in red will have a new value from field $3 and $4 accordingly. ... (1 Reply)
Discussion started by: redse171
1 Replies

4. UNIX for Dummies Questions & Answers

Comparing two text files by a column and printing values that do not match

I have two text files where the first three columns are exactly the same. I want to compare the fourth column of the text files and if the values are different, print that row into a new output file. How do I go about doing that? File 1: 100 rs3794811 0.01 0.3434 100 rs8066551 0.01... (8 Replies)
Discussion started by: evelibertine
8 Replies

5. UNIX for Dummies Questions & Answers

Match values/IDs from column and text files

Hello, I am trying to modify 2 files, to yield results in a 3rd file. File-1 is a 8-columned file, separted with tab. 1234:1 xyz1234 blah blah blah blah blah blah 1234:1 xyz1233 blah blah blah blah blah blah 1234:1 abc1234 blah blah blah blah blah blah n/a RRR0000 blah blah blah... (1 Reply)
Discussion started by: ad23
1 Replies

6. Shell Programming and Scripting

Adding Column Values Using Pattern Match

Hi All, I have a file with data as below: A,FILE1_MYFILE_20130309_1038,80,25.60 B,FILE1_MYFILE_20130309_1038,24290,18543.38 C,FILE1_dsc_dlk_MYFILE_20130309_1038,3,10.10 A,FILE2_MYFILE_20130310_1039,85,110.10 B,FILE2_MYFILE_20130310_1039,10,12.10... (10 Replies)
Discussion started by: angshuman
10 Replies

7. Shell Programming and Scripting

awk Print New Column For Every Two Lines and Match On Multiple Column Values to print another column

Hi, My input files is like this axis1 0 1 10 axis2 0 1 5 axis1 1 2 -4 axis2 2 3 -3 axis1 3 4 5 axis2 3 4 -1 axis1 4 5 -6 axis2 4 5 1 Now, these are my following tasks 1. Print a first column for every two rows that has the same value followed by a string. 2. Match on the... (3 Replies)
Discussion started by: jacobs.smith
3 Replies

8. UNIX for Dummies Questions & Answers

Match sum of values in each column with the corresponding column value present in trailer record

Hi All, I have a requirement where I need to find sum of values from column D through O present in a CSV file and check whether the sum of each Individual column matches with the value present for that corresponding column present in the trailer record. For example, let's assume for column D... (9 Replies)
Discussion started by: tpk
9 Replies

9. Shell Programming and Scripting

awk script to append suffix to column when column has duplicated values

Please help me to get required output for both scenario 1 and scenario 2 and need separate code for both scenario 1 and scenario 2 Scenario 1 i need to do below changes only when column1 is CR and column3 has duplicates rows/values. This inputfile can contain 100 of this duplicated rows of... (1 Reply)
Discussion started by: as7951
1 Replies

10. UNIX for Beginners Questions & Answers

Compare 1st column from 2 file and if match print line from 1st file and append column 7 from 2nd

hi I have 2 file with more than 10 columns for both 1st file apple,0,0,0...... orange,1,2,3..... mango,2,4,5..... 2nd file apple,2,3,4,5,6,7... orange,2,3,4,5,6,8... watermerlon,2,3,4,5,6,abc... mango,5,6,7,4,6,def.... (1 Reply)
Discussion started by: tententen
1 Replies
All times are GMT -4. The time now is 12:39 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy