how to get all third entries, till they appear in the second column

Question

I have a file, that is sorted by first column.

10,W,A
20,W,E
30,I,W
40,A,E
50,P,E
60,S,A
70,A,P
80,A,I
100,A,S
110,I,S
120,A,N
130,E,N

I need to get all the first columns together until when the third column doesn't appear in the second column. If my third column value has already appeared in second column, it should be ignored.

My attempt to bring it using awk is as follows

$ awk -F"," ' { f[$2]++; if( !f[$3] ) { d[$3]=$1 }  f[$3]++ } END { for(i in d) print i, d[i] } ' cg.txt
N 120
A 10
E 20

what I'm expecting is

N 120, 130
A 10
E 20, 40, 50

A comes in 3rd column in the first line itself, so it should be counted.. in 4th line it is there in second column. — stack0114106, Nov 19 '19 at 16:06
I think you need to be a lot more explicit about the algorithm that gets us from your input file to your required output. Please talk us through it line by line. — Dave Cross, Nov 19 '19 at 16:06
E comes in the third column untill the last line so 20 40 and 50 should be concatenated — stack0114106, Nov 19 '19 at 16:08
just remove `f[$3]++` and change `d[$3]=$1` with `d[$3]=d[$3]", "$1` or more exactly `if (d[$3]) { d[$3]=d[$3] ", " $1 } else { d[$3]= $1}` — Nahuel Fouilleul, Nov 19 '19 at 16:12
@stack0114106, but`N` never came in 2nd column why its value in output? Also `I` is not there too, could you please elaborate it more? — RavinderSingh13, Nov 19 '19 at 16:16
yes, but it is like a corner case and it should be captured till end of the file — stack0114106, Nov 19 '19 at 16:21
@NahuelFouilleul.. you are right!... pls add it as an answer — stack0114106, Nov 19 '19 at 18:09

ikegami · Answer 1 · 2019-11-19T16:56:01.407

perl -F, -lane'
   ++$seen{ $F[1] };
   push @{ $groups{ $F[2] } }, $F[0] if !$seen{ $F[2] };
   END {
      local $" = ", ";
      print "$_ @{ $groups{$_} }" for sort keys %groups;
   }
'

-F, -a causes the input line to be split at commas into @F.
We keep track of what values we've seen in the second column using %seen.
If it's a value we haven't seen, add it to the %groups, a hash of arrays.
And the end, print out the hash of arrays. $" is used so the array elements are separated by ,␠ instead of the default ␠.

Specifying file to process to Perl one-liner

RavinderSingh13 · Answer 2 · 2019-11-19T17:48:21.123

1

Could you please try following. Considering only condition print all of 3rd column values till its first occurrences in 2nd column(tested with samples provided only)

awk '
BEGIN{
  FS=","
  OFS=", "
}
{
  a[$3]=(a[$3]?a[$3] OFS:"")$1
}
{
  ++c[$2]
}
($2 in a) && c[$2]==1{
  print $2 " " a[$2]
}
END{
 for(i in a){
     if(!(i in c)){
         print i" " a[i]
     }
 }
}'  Input_file

edited Nov 19 '19 at 17:48

answered Nov 19 '19 at 16:17

RavinderSingh13

130,504
14
57
93

@stack0114106, Could you please explain more on which part it is not working? As per logic it looked to me working, kindly do let me know on same. – RavinderSingh13 Nov 19 '19 at 17:21
"I" has appeared already in column 2 in line#3 so it should not be in output – stack0114106 Nov 19 '19 at 17:23
@stack0114106, oh ok, I didn't know about that condition, could you please check now? – RavinderSingh13 Nov 19 '19 at 17:26
it misses "N" now – stack0114106 Nov 19 '19 at 17:45
@stack0114106, Sure fixed it, apologies for this, I thought `N` should be skipped honestly, cheers. – RavinderSingh13 Nov 19 '19 at 17:48
it works for the given input.. but still needs refining i.e if I remove the last line, it prints ````N 120```` which is correct, but it also prints an additional blank line.. – stack0114106 Nov 19 '19 at 18:04
@stack0114106, ok, do you have blank lines in your Input_files? – RavinderSingh13 Nov 19 '19 at 18:06
1

my bad!!...here were some trailing blank lines.. never mind, it is not an issue.. thank you – stack0114106 Nov 19 '19 at 18:08

score 1 · Answer 3 · answered Nov 19 '19 at 19:59

1

another awk

$ awk -F, '{a[$2]; k=$3} 
       !(k in a) {b[k]=b[k] s[k] $1; s[k]=FS} 
       END       {for(k in b) print k, b[k]}' file

N 120,130
A 10
E 20,40,50

answered Nov 19 '19 at 19:59

karakfa

66,216
7
41
56

score 1 · Answer 4 · answered Nov 19 '19 at 22:14

Perl code for your data

use strict;
use warnings;

my %seen;
my %data;

while( <DATA> ) {
    chomp;
    my @a = split ',';

    push @{$data{$a[2]}}, $a[0] if not $seen{$a[2]};

    $seen{$a[1]} = 1;
}

while( my($k,$v) = each %data ) {
    printf "%s %s\n", $k, join ", ", @$v;
}

__DATA__
10,W,A
20,W,E
30,I,W
40,A,E
50,P,E
60,S,A
70,A,P
80,A,I
100,A,S
110,I,S
120,A,N
130,E,N

score 0 · Answer 5 · answered Nov 20 '19 at 05:51

0

Added as answer from comment as requested by OP.

Just remove f[$3]++ and change d[$3]=$1 with if (d[$3]) { d[$3]=d[$3] ", " $1 } else { d[$3]= $1}.

answered Nov 20 '19 at 05:51

Nahuel Fouilleul

18,726
2
31
36

how to get all third entries, till they appear in the second column

5 Answers5