Distinct Record Count in Spark dataframe

Question

I'm trying to display a distinct count of a couple different columns in a spark dataframe, and also the record count after grouping the first column.
So if I had col1, col2, and col3, I want to groupBy col1, and then display a distinct count of col2 and also a distinct count of col3. Then, I would like to display the record count after that same groupBy of col1. And finally, do this all in one agg statement.. Any ideas?

Answer 1

Below is the code you are looking for

df.groupBy("COL1").agg(countDistinct("COL2"),countDistinct("COL3"),count($"*")).show

=======Tested Below============

scala>  val lst = List(("a","x","d"),("b","D","s"),("ss","kk","ll"),("a","y","e"),("b","c","y"),("a","x","y"));
lst: List[(String, String, String)] = List((a,x,d), (b,D,s), (ss,kk,ll), (a,y,e), (b,c,y), (a,x,y))

scala> val rdd=sc.makeRDD(lst);
rdd: org.apache.spark.rdd.RDD[(String, String, String)] = ParallelCollectionRDD[7] at makeRDD at <console>:26

scala> val df = rdd.toDF("COL1","COL2","COL3");
df: org.apache.spark.sql.DataFrame = [COL1: string, COL2: string ... 1 more field]

scala> df.printSchema
root
 |-- COL1: string (nullable = true)
 |-- COL2: string (nullable = true)
 |-- COL3: string (nullable = true)


scala> df.groupBy("COL1").agg(countDistinct("COL2"),countDistinct("COL3"),count($"*")).show
+----+--------------------+--------------------+--------+
|COL1|count(DISTINCT COL2)|count(DISTINCT COL3)|count(1)|
+----+--------------------+--------------------+--------+
|  ss|                   1|                   1|       1|
|   b|                   2|                   2|       2|
|   a|                   2|                   3|       3|
+----+--------------------+--------------------+--------+


scala>