Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Compare within same group

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Compare within same group
# 1  
Old 10-12-2014
Compare within same group
please help,I have a table like the following sample

Code:
name1	group1	value1
name2	group1	value1
name1	group2	value1
name2	group2	value2
name3	group2	value1



if the values in the 3rd col are consistent within a group I want the first row with the value, if they are inconsistent within the same group , I want the first row with a value that says 'missing'

So my output will look like

Code:
name1	group1	value1
name1	group2	missing

Last edited by rbatte1; 10-15-2014 at 12:12 PM.. Reason: Capital letter for first person singular
# 2  
Old 10-13-2014
Dear senhia83, try following

Input
Code:
[akshay@nio tmp]$ cat file
name1	group1	value1
name2	group1	value1
name1	group2	value1
name2	group2	value2
name3	group2	value1

Output
Code:
[akshay@nio tmp]$ awk 'FNR==NR{G[$2]++; GV[$2,$3]++; next }$2 in G{$3 = ( G[$2] == GV[$2,$3] ) ? $3 : "missing"; delete G[$2]; delete GV[$2,$3]; print} ' file file
name1 group1 value1
name1 group2 missing

---------- Post updated at 02:31 PM ---------- Previous update was at 02:12 PM ----------

Explanation

Code:
awk '
	FNR==NR{
		
			# Create hash of field2
			G[$2]++

			# Create hash of field2 and field3
			GV[$2,$3]++
			
			# stop processing and go to next line till FNR==NR 
			# this is true only when awk reading first file
			next 
		}
		
	  # Here we read same file 2nd time

	  # IF field2 is in hash G we created in above block then
	  $2 in G{
			
			# Column3 remains same only if two hashes are equal
			# For example : when awk reads line 1 that is when FNR == 1, NR=6
			# G[$2] = 2, G[$2,$3] = 2 so same  group $3 remains same
			# when awk reads line 3, that is when FNR=3 and NR = 8, G[$2] = 3, and G[$2,$3] = 2
			# So field 3 is missing in current group 

			$3 = ( G[$2] == GV[$2,$3] ) ? $3 : "missing"; 

			# delete array element
			delete G[$2] 
			delete GV[$2,$3] 

			# Print line
			print
		  } 
      ' file file

This User Gave Thanks to Akshay Hegde For This Post:
senhia83
# 3  
Old 10-13-2014
thank you very much. i was playing around with this a little bit and testing, everything looks to be fine except for a tiny bit of unnecessary part of the first line (marked in red), any quick fix?

Code:
 
$ cat > test3
name2 id1group1 value1
name4 id1group1 value2
name1 id2group1 value2
name2 id2group1 value2
name4 id2group1 value2
name1 id1group2 value1
name2 id1group2 value2
name3 id1group2 value1




Code:
 
$ awk 'FNR==NR{G[$2]++; GV[$2,$3]++; next }$2 in G{$3 = ( G[$2] == GV[$2,$3] ) ? $3 : "missing"; delete G[$2]; delete GV[$2,$3]; print} ' test3 test3
name1 id1group1 missing id1group1 value1
name1 id2group1 value2
name1 id1group2 missing

...........................
Last edited by senhia83; 10-13-2014 at 07:20 AM..
# 4  
Old 10-13-2014
Quote:
Originally Posted by senhia83
thank you very much. i was playing around with this a little bit and testing, everything looks to be fine except for a tiny bit of unnecessary part of the first line (marked in red), any quick fix?

Code:
 
$ cat > test3
name2 group1 value1
name1 group2 value1
name4 group1 value2
name2 group3 value2
name3 group3 value2
name2 group2 value2
name3 group2 value1
name1 group4 value1
name2 group4 value1
name1 group4 value1
name4 group4 value2
name2 group5 value2
name3 group5 value2
name2 group5 value2
name3 group5 value1

Code:
 
$ awk 'FNR==NR{G[$2]++; GV[$2,$3]++; next }$2 in G{$3 = ( G[$2] == GV[$2,$3] ) ? $3 : "missing"; delete G[$2]; delete GV[$2,$3]; print} ' test3 test3
name1 group1 missing group1 value1
name1 group2 missing
name2 group3 value2
name1 group4 missing
name2 group5 missing

...........................


Actually just realized that rows belonging to the same group being together solves this issue,,i`ll just sort on col2...thanks again
I am getting ouput like this with your new input - no extra field

Code:
name2 group1 missing
name1 group2 missing
name2 group3 value2
name1 group4 missing
name2 group5 missing

There is no need of sorting.
# 5  
Old 10-13-2014
Hello senhia83,

After effort of 4 hours I finally made one solution(Not good as Akshay), but hope it may help you, as for me each combination has worked as follows.

Code:
cat test121
name2 group1 value1
name1 group2 value1
name4 group1 value2
name2 group3 value2
name3 group3 value2
name2 group2 value2
name3 group2 value1
name1 group4 value1
name2 group4 value1
name1 group4 value1
name4 group4 value2
name2 group5 value2
name3 group5 value2
name2 group5 value2
name3 group5 value1
name3 group6 value1
name3 group6 value1

code is as follows:
Code:
cat check_lines_seq.ksh
LINES=`awk 'END{print NR}' test121`

awk -vLIN="$LINES" '{if((X != $2 && X)){S=$2_name;S_S[++i]=$0; {if(k%2==0 && k){print S_S[--i];} else {print Q OFS X OFS "missing";k="";}}}} {if(X==$2 && X){if(Y==$3 && Y){k=k+2} else {k--}}} {A++;} {if(A==LIN){if(X==$2 && Y==$3 && k%2==0){print S_S[i]} else {$3="missing";print $0}}} {Q=$1;X=$2;Y=$3}' test121

Output will be as follows:
Code:
./check_lines_seq.ksh
name2 group1 missing
name1 group2 missing
name4 group1 missing
name2 group3 value2
name3 group2 missing
name4 group4 missing
name3 group5 missing
name3 group6 value1

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
senhia83
# 6  
Old 10-13-2014
Thank you Ravinder for your efforts in helping me, I really appreciate it.

The output that you provided does not match my requirements, I need to have only a single name per group.
# 7  
Old 10-13-2014
Quote:
Originally Posted by senhia83
Thank you Ravinder for your efforts in helping me, I really appreciate it.

The output that you provided does not match my requirements, I need to have only a single name per group.
Hi senhia83,
Could you please try following and let me know if this helps.

Lets say following is the input file.
Code:
cat check_time
name2 group1 value1
name1 group2 value1
name4 group1 value2
name2 group3 value2
name3 group3 value2
name2 group2 value2
name3 group2 value1
name1 group4 value1
name2 group4 value1
name1 group4 value1
name4 group4 value2
name2 group5 value2
name3 group5 value2
name2 group5 value2
name3 group5 value1
name3 group6 value1
name3 group6 value1

Then code is as follows now.
Code:
cat check_time.ksh
LINES=`awk 'END{print NR}' check_time`
 
awk -vLIN="$LINES" '{if((X != $2 && X)){S=$2_name;S_S[++i]=$0; {if(k%2==0 && k){print S_S[--i];} else {print Q OFS X OFS "missing";k="";}}}} {if(X==$2 && X){if(Y==$3 && Y){k=k+2} else {k--}}} {A++;} {if(A==LIN){if(X==$2 && Y==$3 && k%2==0){print S_S[i]} else {$3="missing";print $0}}} {Q=$1;X=$2;Y=$3}' check_time | awk '!S[$2]++'

Output will be as follows for same.
Code:
./check_time.ksh
name2 group1 missing
name1 group2 missing
name2 group3 value2
name4 group4 missing
name3 group5 missing
name3 group6 value1

Hope this helps now.

EDIT: Adding same solution in a better form as follows.
Code:
LINES=`awk 'END{print NR}' check_time`

awk -vLIN="$LINES" '
{if((X != $2 && X))
        {S=$2_name;S_S[++i]=$0;
        {if(k%2==0 && k)
                {print S_S[--i];}
        else
                {print Q OFS X OFS "missing";k="";}
        }
        }
}
{if(X==$2 && X)
        {if(Y==$3 && Y)
                {k=k+2}
        else
                {k--}
        }
}
{A++;}
{if(A==LIN)
        {if(X==$2 && Y==$3 && k%2==0)
                {print S_S[i]}
        else
                {$3="missing";print $0}
        }
}
{Q=$1;X=$2;Y=$3}' check_time | awk '!S[$2]++'

Thanks,
R. Singh
Last edited by RavinderSingh13; 10-14-2014 at 02:24 AM.. Reason: editing file name of input+Added solution in better format
 
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Programming

Sql ORA-00937: not a single-group group function

I'm trying to return only one row with the highest value for PCT_MAX_USED. Any suggestions? When I add this code, I get the ORA-00937 error. trunc(max(decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100))) pct_max_used This is the original and returns all rows. select (select... (3 Replies)
Discussion started by: progkcp
3 Replies

2. Shell Programming and Scripting

need a one liner to grep a group info from /etc/group and use that result to search passwd file

/etc/group tiadm::345:mk789,po312,jo343,ju454,ko453,yx879,iy345,hn453 bin::2:root,daemon sys::3:root,bin,adm adm::4:root,daemon uucp::5:root /etc/passwd mk789:x:234:1::/export/home/dummy:/bin/sh po312:x:234:1::/export/home/dummy:/bin/sh ju454:x:234:1::/export/home/dummy:/bin/sh... (6 Replies)
Discussion started by: chidori
6 Replies

3. AIX

Adding a Volume Group to an HACMP Resource Group?

Hi, I have a 2 node Cluster. Which is working in active/passive mode (i.e Node#1 is running and when it goes down the Node#2 takes over) Now there's this requirement that we need a mount point say /test that should be available in active node #1 and when node #1 goes down and node#2 takes... (6 Replies)
Discussion started by: aixromeo
6 Replies

4. Shell Programming and Scripting

Sort the file contents in each group....print the group title as well

I've this file and need to sort the data in each group File would look like this ... cat file1.txt Reason : ABC 12345-0023 32123-5400 32442-5333 Reason : DEF 42523-3453 23345-3311 Reason : HIJ 454553-0001 I would like to sort each group on the last 4 fileds and print them... (11 Replies)
Discussion started by: prash184u
11 Replies

5. Shell Programming and Scripting

Merge group numbers and add a column containing group names

Hi All I do have a file like this with 6 columns. Groups of data merge together and the group number is indicated above each group. 1 1 12 26 289 3.2e-027 GCGTATGGCGGC 2 12 26 215 6.7e+006 TTCCACCTTTTG 3 9 26 175 ... (1 Reply)
Discussion started by: Lucky Ali
1 Replies

6. Shell Programming and Scripting

Merge group numbers and add a column containing group names

I have a file in the following format. Groups of data merge together and the group number is indicated above each group. 1 adrf dfgr dfg 2 dfgr dfgr 3 dfef dfr fd 4 fgrt fgr fgg 5 fgrt fgr (3 Replies)
Discussion started by: Lucky Ali
3 Replies

7. Shell Programming and Scripting

KSH to group records in a file and compare it with another file

Hi, I've a file like below: DeptFile.csv DeptID EmpID ------- ------ Dep01 Emp01 Dep01 Emp02 Dep01 Emp03 Dep02 Emp04 Dep02 Emp05 I've another file which has EmpFile.csv EmpID Salary ------ ------ (3 Replies)
Discussion started by: Matrix2682
3 Replies

8. UNIX for Advanced & Expert Users

retrieving all group names with a given group number

hi, which Unix/C function can i use to retrieve all group names with a particular group id? The following C code prints out the group id number of a particular group name: ------------------------------------------------------------------------ #include <stdio.h> #include <grp.h> int... (3 Replies)
Discussion started by: Andrewkl
3 Replies

9. Solaris

entry in /etc/group too long - problem using sudo with %group

hi folks, I've been googling for quite some time, but still can't find anything near it...my problem is the following: for useradministration in our company we are using ssh/sudo, now whenever I try to add users (we have quite a number of users) with useradd -G groupname for secondary group I... (4 Replies)
Discussion started by: poli
4 Replies
Login or Register to Ask a Question