Delete data blocks based on missing combinations

UNIX for Beginners Q & A UNIX for Dummies Questions & Answers
1

Hello masters,

I am filtering data based on completeness. A (Name , Group) combination in File2 is only complete when it has data for all subgroups specified in File1.
All incomplete (Name , Group) combinations do not appear in the output.

So for example , Name1 Group 1 in File2 is incomplete because it doesnt have data for all subgroups ( a,b and c), so this Name Group combination doesnt appear in the output.

File1

Group Subgroup
1 a
1 b
1 c
2 a
2 b
2 c
3 a

File2

Name Group Subgroup Data
Name1 1 a d1
Name1 1 b d2
Name1 2 a d1
Name1 2 b d2
Name1 2 c d3
Name2 1 a d1
Name2 1 b d2
Name2 1 c d5
Name2 3 a d2
Name3 1 a d1
Name3 1 b d2
Name3 2 c d1
Name4 1 a f1
Name4 1 b f2
Name4 1 c f5
Name4 2 a f1
Name4 2 b f2
Name4 2 c f5

Output

Name1 2 a d1
Name1 2 b d2
Name1 2 c d3
Name2 1 a d1
Name2 1 b d2
Name2 1 c d5
Name2 3 a d2
Name4 1 a f1
Name4 1 b f2
Name4 1 c f5
Name4 2 a f1
Name4 2 b f2
Name4 2 c f5
2

Any attempt from your side?

3

yes, I have tried, but its coming up with syntax errors

awk -F'\t' 'NR==FNR{a[$1"\t"$2]=$0;next} $[$2"\t"$3] in a {b[$2"\t"$3]=$0; if ($0=="") else print b[$0]}1' OFS='\t' file1 file2
4

Based on exactly your simple samples, try

awk     'FNR==NR        {GRP[$1]=GRP[$1]$2; next}
         $2 != GR       {if (GRP[GR] == TMPGR) print ST
                         ST=DL=TMPGR=""}
                        {ST=ST DL $0
                         DL=RS
                         NM=$1
                         GR=$2
                         TMPGR=TMPGR $3
                        }
         END            {if (GRP[GR] == TMPGR) print ST}
        ' file1 file2
Name Group Subgroup Data
Name1 2 a d1
Name1 2 b d2
Name1 2 c d3
Name2 1 a d1
Name2 1 b d2
Name2 1 c d5
Name2 3 a d2
Name4 1 a f1
Name4 1 b f2
Name4 1 c f5
Name4 2 a f1
Name4 2 b f2
Name4 2 c f5
5

Does the data need to be sorted in any way?

6

Yes, it depends on the order given.

7

It will take a while for me to try and break the code with my huge dataset...I will get back to you..thank you :slight_smile: