Q:

Dataframe GroupBy aggregate on column that contains pattern

i have a dataframe, with column c1, c2. I want to group them on c1 and want to pick c2 such that c2 value contains a pattern, if all c2 don't contain pattern return anyone
example df :
c1  c2
1   ai_za
1   ah_px
1   ag_po
1   af_io
1   ae_aa
1   ad_iq
1   ac_on
1   ab_eh
1   aa_bs
2   aa_ab
2   aa_ac

if pattern needed in c2 is '_io'
expected result:
c1  c2
1   af_io
2   aa_ab

1   af_io is returned as it contains '_io' pattern
2   aa_ab is returned as random as no one in group 2 contains pattern '_io'
How to get this using spark dataframe/dataset api ?

A:

If it doesn't matter which row to pick if there is no match, you can try:
df.groupByKey(_.getAs[Int]("c1")).
   reduceGroups((x, y) => if(x.getAs[String]("c2").matches(".*_io")) x else y).
   toDF("key", "value").
   select("value.c1", "value.c2").show

+---+-----+

| c1|   c2|
+---+-----+
|  1|af_io|
|  2|aa_ac|
+---+-----+

Note: this picks the first row that matches the pattern and picks the last row in the group if there is no match.

