Matching tables


Login or Register for Dates, Times and to Reply

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Matching tables
# 1  
Matching tables

Hello I have two tables like this

Table1
Code:
Ab01 Ac01 Bd01 Xc01
Ab02 Ac02 Bd02 Xc02
Ab03 Ac03 Bd03 Xc03
Ab04 Ac04 Bd04 Xc04
Ab05 Ac05 Bd05 Xc05

and Table 2 like this

Code:
Group_01.1 Bd03
Group_01.1 Ac03
Group_01.1 Xc03
Group_01.1 Ab03
Group_02.1 Ac02
Group_02.1 Ab05
Group_02.1 Bd03
Group_02.1 Xc04
Group_03.2 Bd04
Group_03.2 Ab05
Group_03.2 Bd03
Group_03.2 Ac02

I needed to print only the group which has all elements in table1 rows like this

Code:
Group_01.1 Bd03
Group_01.1 Ac03
Group_01.1 Xc03
Group_01.1 Ab03

grep and sort wont work here
# 2  
Hello cowa,

Welcome to forums, special thanks for using CODE TAGS for samples.

On UNIX.com we encourage users to show their efforts in their questions which they have put in order to solve their own problems, so kindly do add the same and let us know then.

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
# 3  
transposes function

I was using transposes the rows into columns, then data.frame creates a list of the columns to match the data match(data.frame(t(x)), data.frame(t(y)))
# 4  
Hi,
if only one row has matches
Code:
awk '
NR == FNR               { if (!T[$2]) T[$2] = $1; next }
                        { lin = 0; for (i=1; i<=NF; i++)
                                if ($i in T) lin++
                        }
lin == NF               { for (i=1; i<=NF; i++) print T[$1] FS $i }
' Table2 Table1

otherwise
Code:
awk '
NR == FNR               { if (!T[$2]) T[$2] = $1; next }
                        { lin = 0; for (i=1; i<=NF; i++)
                                if ($i in T) lin++ 
                        }
lin == NF               { for (i=1; i<=NF; i++) D[$i] = T[$i] }
END                     { for (i in D) print D[i] FS i }
' Table2 Table1

These 2 Users Gave Thanks to nezabudka For This Post:
# 5  
This one worked perfectly
# 6  
maybe this is very redundant for such task,
but maybe this will anticipate possible problems with more complex input
This instance with integrated version control Smilie requires gnu awk
Code:
awk '
NR == FNR       { T[$2] = $1 (T[$2]?FS T[$2]:""); next }
                { lin = 0; for (i=1; i<=NF; i++)
                        if ($i in T) lin++ 
                }
lin == NF       { for (i=1; i<=NF; i++) {
                        if (T[$i]) print $i FS gensub(/^.*\s/, "", 1, T[$i])
                        sub(/\s?[^ ]+$/, "", T[$i])
                        }
                }
' Table2 Table1


Last edited by nezabudka; 12-24-2019 at 05:13 PM..
These 2 Users Gave Thanks to nezabudka For This Post:
# 7  
Try also
Code:
awk '
NR==FNR {T[$2] = $1
         next
        }
        {L   = 1
         OUT = ""
         for (i=1; i<=4; i++)   {L = L * ($i in T)
                                 OUT = OUT T[$i] OFS $i ORS
                                }
         if (L) print OUT
        }
' file2 file1


or


Code:
sort file2 |  awk '
NR == FNR       {T[$0]
                 next
                }
                {OUT = OUT NRS $0
                 IX  = IX NFS $2
                 NFS = OFS
                 NRS = ORS
                }
!(FNR%4)        {if (IX in T) print OUT
                 OUT = IX = NFS = ""
                }
' file1 -

Login or Register for Dates, Times and to Reply

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #8
Difficulty: Easy
macOS is based on the Unix operating system.
True or False?

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to combine all matching dates and remove non-matching

Using the awk below I am able to combine all the matching dates in $1, but I can not seem to remove the non-matching from the file. Thank you :). file 20161109104500.0+0000,x,5631 20161109104500.0+0000,y,2 20161109104500.0+0000,z,2 20161109104500.0+0000,a,4117... (3 Replies)
Discussion started by: cmccabe
3 Replies

2. Shell Programming and Scripting

Compare file1 for matching line in file2 and print the difference in matching lines

Hello, I have two files file 1 and file 2 each having result of a query on certain database tables and need to compare for Col1 in file1 with Col3 in file2, compare Col2 with Col4 and output the value of Col1 from File1 which is a) not present in Col3 of File2 b) value of Col2 is different from... (2 Replies)
Discussion started by: RasB15
2 Replies

3. Shell Programming and Scripting

Selecting tables

Hi, Can anyone help me that, How to see the table fields in Oracle database through shell script(ksh). I have tried with the following: sqlplus -s $user/$passwd@$sid << SQL >> session.log select * from Testtab.sql I'm not able to see anything.. Thanks (4 Replies)
Discussion started by: zxcjggu708
4 Replies

4. Shell Programming and Scripting

Insert lines above matching line with content from matching

Hi, I have text file: Name: xyz Gender: M Address: "120_B_C; ksilskdj; lsudlfw" Zip: 20392 Name: KLM Gender: F Address: "65_D_F; wnmlsi;lsuod;,...." Zip:90233I want to insert 2 new lines before the 'Address: ' line deriving value from this Address line value The Address value in quotes... (1 Reply)
Discussion started by: ysrini
1 Replies

5. UNIX for Dummies Questions & Answers

VI editor and tables

I have a host table that looks similar like this: # IP Name Comment 10.10.10.1 Lab1-1 # Static for Lab1 subnet 10.10.10.2 Lab1-2 # Static for Lab1 subnet 10.10.10.3 Lab1-3 ... (2 Replies)
Discussion started by: mjl927
2 Replies

6. Shell Programming and Scripting

comparing two tables

I am comparing two table structure in different databases,Put into 2 txt files , when comparing if column sequnce and data type is not matching ,it has to display that info else Table structure is ok. wrote shell script ,its not working .I am getting "Table structure is not ok" even if both... (1 Reply)
Discussion started by: akil
1 Replies

7. Shell Programming and Scripting

tables in scripts

Hi , I have two tables with same length t1 and t2, I want to cretae a new third table where i put the difference between the elements of t2 and t1, t3= t1 - t2 t3= t1 - t2 I am new to scripts, any help please? thanks (7 Replies)
Discussion started by: Celine19
7 Replies

8. Shell Programming and Scripting

Converting tables of row data into columns of tables

I am trying to transpose tables listed in the format into format. Any help would be greatly appreciated. Input: test_data_1 1 2 90% 4 3 91% 5 4 90% 6 5 90% 9 6 90% test_data_2 3 5 92% 5 4 92% 7 3 93% 9 2 92% 1 1 92% ... Output:... (7 Replies)
Discussion started by: justthisguy
7 Replies

9. UNIX for Dummies Questions & Answers

Routing tables

Hey guys, I needed to add a route to my routing table and I got it to work but on reboot it gets removed. Anyone know what file I can add this route to so it stays on the machine after a reboot? (9 Replies)
Discussion started by: kingdbag
9 Replies

Featured Tech Videos