Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Finding reciprocal columns

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Finding reciprocal columns
# 1  
Old 02-01-2014
Finding reciprocal columns
super newbish question here. I have a file with the following format:

Code:
A  A  200
A  B  100
A  C  90
B  B  203
B  A  101
B  C  87
C  C  300
C  A  91
C  B  86

I would like to find reciprocal sets for columns 1 and 2 and then write the value of column 3 for the reciprocal set into column 4.

An example:

The second row "A B" has a reciprocal set, which is row 5 "B A"

So, I would like to make a script that would output for row 2:

Code:
A  B  100  101

obvious some columns would not have reciprocals in which case I would like the 4th row to be 0.

Is this possible?

cheers!
Last edited by Scrutinizer; 02-01-2014 at 09:48 AM.. Reason: code tags
# 2  
Old 02-01-2014
Like so:
Code:
awk 'NR==FNR{T[$1,$2]=$3; next} {print $0, T[$2,$1]}' file file
A  A  200 200
A  B  100 101
A  C  90 91
B  B  203 203
B  A  101 100
B  C  87 86
C  C  300 300
C  A  91 90
C  B  86 87

?
# 3  
Old 02-01-2014
Is this the result you are looking for?
Code:
A  A  200  0
A  B  100  101
A  C  90  91
B  B  203 0
B  C  87  86
C  C  300  0

Note:-
Code:
B  A  101
C  A  91
C  B  86

Are NOT needed...
# 4  
Old 02-01-2014
@wisecracker - no I would like all the rows if possible.

@RudiC- not sure why but doesn't seem to be working, I just get a blank output file. :-(
Last edited by RawToast; 02-01-2014 at 10:15 PM..
# 5  
Old 02-01-2014
Did you copy and paste above proposal unmodified?
# 6  
Old 02-01-2014
RawToast: What OS are you using? If you're using a Solaris system, try changing awk in RudiC's script to /usr/xpg4/bin/awk.
This User Gave Thanks to Don Cragun For This Post:
RudiC
# 7  
Old 02-01-2014
@RudiC - I tried:

Code:
awk 'NR==FNR{T[$1,$2]=$3; next} {print $0, T[$2,$1]}' test.txt > test_out.txt

@Don - I am on OSX 10.8.5 - my awk command usually works for simple stuff like:

Code:
awk '{print $1}' file

 
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Finding common entries between 10 columns

Hello, I need to find the intersection across 10 columns. Kindly help. my file (INPUT.csv) looks like this 4_R 4_S 8_R 8_S 12_R 12_S 24_R 24_S LOC_Os01g01010 LOC_Os01g01010 LOC_Os01g01010 LOC_Os04g48290 LOC_Os01g01010 LOC_Os01g01010... (1 Reply)
Discussion started by: Sanchari
1 Replies

2. Shell Programming and Scripting

Finding difference between two columns of unequal length

Hi, I have two files which look like this cat waitstate.txt 18.2 82.1 cat gostate.txt 5.6 5.8 6.1 6.3 6.6 6.9 7.2 7.5 (4 Replies)
Discussion started by: jamie_123
4 Replies

3. Shell Programming and Scripting

UNIX scripting for finding duplicates and null records in pk columns

Hi, I have a requirement.for eg: i have a text file with pipe symbol as delimiter(|) with 4 columns a,b,c,d. Here a and b are primary key columns.. i want to process that file to find the duplicates and null values are in primary key columns(a,b) . I want to write the unique records in which... (5 Replies)
Discussion started by: praveenraj.1991
5 Replies

4. Shell Programming and Scripting

Finding value bigger than zero in all columns

Hi everybody, I am a complete novice and please forgive if its answered gazillion times I have a file which looks like this 1 0 2 0 0 0 0 0 0 3 0 1 18 2 6 0 1 7 0 2 4 0 0 0 1 17 16 1 1 0 0 I have to add... (4 Replies)
Discussion started by: amits22
4 Replies

5. Shell Programming and Scripting

Finding standard deviation for all columns in a data file

Hi All, I want someone to modify the below script from this forum so that it can be used for all columns in the file( instead of only printing column 3 mean and standard deviation values). I don't know how to loop around all the columns. ... (3 Replies)
Discussion started by: ks_reddy
3 Replies

6. Shell Programming and Scripting

finding duplicates in csv based on key columns

Hi team, I have 20 columns csv files. i want to find the duplicates in that file based on the column1 column10 column4 column6 coulnn8 coulunm2 . if those columns have same values . then it should be a duplicate record. can one help me on finding the duplicates, Thanks in advance. ... (2 Replies)
Discussion started by: baskivs
2 Replies

7. Shell Programming and Scripting

Matching same columns and finding the smallest match

Hi all, I am wondering if its possible to solve my problem with a simple code. Basically I have a file that looks like this (tab delimited) bob 8 250 tina 8 225 sam 8 225 ellen 9 315 kyle 9 275 sally 9 135 So what I want to do is match columns 2 and 5. If columns 2 and 5... (2 Replies)
Discussion started by: phil_heath
2 Replies

8. Shell Programming and Scripting

finding duplicates in columns and removing lines

I am trying to figure out how to scan a file like so: 1 ralphs office","555-555-5555","ralph@mail.com","www.ralph.com 2 margies office","555-555-5555","ralph@mail.com","www.ralph.com 3 kims office","555-555-5555","kims@mail.com","www.ralph.com 4 tims... (17 Replies)
Discussion started by: totus
17 Replies

9. Shell Programming and Scripting

finding duplicate files by size and finding pattern matching and its count

Hi, I have a challenging task,in which i have to find the duplicate files by its name and size,then i need to take anyone of the file.Then i need to open the file and find for more than one pattern and count of that pattern. Note:These are the samples of two files,but i can have more... (2 Replies)
Discussion started by: jerome Sukumar
2 Replies
Login or Register to Ask a Question