# Removing rows whose elements appear less than a certain number

I have a csv file, size around 50Mb. The data is 986132 by 3 matrix of the following form.

data={{a1,b1,1},{a1,b2,1},{a1,b3,1},{a1,b4,1},{a2,b1,1},{a2,b5,1},{a2,b6,1},{a3,b1,1},{a3,b2,1},{a3,b6,1},{a3,b7,1},...{a3012,b1423,1}}

The third column is always 1 and there is no pattern between a's and b's.

In the matrix form of data, I focus on the second column (b's) and want to remove all the rows that has b$$i$$ that appears less than a certain number. Say it's 10.

So, for example, if b10, b31, b412 each appears less than 10 times in data, I want remove all the rows that contain b11, b31 and b412.

How can I do this? I've tried to convert it to a pivot table and sort it by the number of each b, but my computer can't process it maybe because of the memory limitation..

Make some fake data:

SeedRandom; numOccurrences = 5;
data = Table[{RandomInteger[{0, 100}], RandomInteger[{1, 100}], 1}, 100];


Group the data by the value of b, then select only those values of b for which at least numOccurrences instances are present, then take the values of the resulting association and flatten them back into the desired shape:

Select[Length[#] >= numOccurrences &]@ GroupBy[#[] &]@ data;
Values[%]~Flatten~1

(* Out:
{{83, 1, 1}, {33, 1, 1}, {27, 1, 1}, {12, 1, 1}, {74, 1, 1}}
*)


To generate data (with a[i] instead of ai but the principle is unchanged):

SeedRandom;
n = 20;
indexA = RandomInteger[{1, 4}, n];
indexB = RandomInteger[{1, 4}, n];
data = Table[{a[indexA[[i]]], b[indexB[[i]]], 1}, {i, n}]

(* {{a, b, 1}, {a, b, 1}, {a, b, 1}, {a, b,
1}, {a, b, 1}, {a, b, 1}, {a, b, 1}, {a, b,
1}, {a, b, 1}, {a, b, 1}, {a, b, 1}, {a, b,
1}, {a, b, 1}, {a, b, 1}, {a, b, 1}, {a, b,
1}, {a, b, 1}, {a, b, 1}, {a, b, 1}, {a, b,
1}} *)


Then, store variablesn that appear less than min times in the second colum, and select rows whose second value in not in the list of bad indices badB:

min = 5;
badB = Select[Tally[data[[All, 2]]], #[] < min &][[All, 1]]
Select[data, MemberQ[badB, #[]] == False &]

(* {{a, b, 1}, {a, b, 1}, {a, b, 1}, {a, b,
1}, {a, b, 1}, {a, b, 1}, {a, b, 1}, {a, b,
1}} *)

ClearAll[pick]
pick = Pick[#,
DeveloperToPackedArray @ UnitStep[(Counts[#[[All, 2]]] /@ #[[All, 2]]) - #2], 1] &;


Using data from MarcoB's answer:

SeedRandom;
data = Table[{RandomInteger[{0, 100}], RandomInteger[{1, 100}], 1}, 100];
pick[data, 5]

 {{83, 1, 1}, {33, 1, 1}, {27, 1, 1}, {12, 1, 1}, {74, 1, 1}}
`

Note: This approach preserves the ordering of kept rows.