Filtering a column in a Spark Dataframe to find percentage of each element
<p>I am trying to filter a column in a Spark Dataframe with pyspark, I want to know which records represents 10% or less than the total column, </p>

<p>For example I have the following column entitled "Animal" in my DataFrame :</p>

<p><strong>Animal</strong></p>

<ul>
<li>Cat</li>
<li>Cat</li>
<li>Dog</li>
<li>Dog</li>
<li>Cat</li>
<li>Cat</li>
<li>Dog</li>
<li>Dog</li>
<li>Cat</li>
<li>Rat</li>
</ul>

<p>To find the record " Rat ", I tried </p>

<pre><code>df.filter(df.groupBy("Animal").count() &lt;= 0.1 * df.select("Animal").count()).collect()
</code></pre>

<p>and I got the following error " TypeError : condition should be string or column" </p>

<p>How can I find the records that represent less than 10% ?</p>

<p>PS : Would it be simpler in SQL ?  </p>

<p>Something like :</p>

<pre><code>result = spark.sql("SELECT Animal, COUNT(ANIMAL) FROM Table HAVING COUNT(Animal) &lt; 0.1 * COUNT(Animal))
</code></pre>

<p>I know it is a simple operation but I just can't figure out how to code the 
10% of total part.</p>

<p>Thanks for any help !</p>

<p>You first have to count the total and in a second step use it to filter.</p>

<p>In condensed code (pyspark, spark 2.0):</p>

<pre><code>import pyspark.sql.functions as F
df=sqlContext.createDataFrame([['Cat'],['Cat'],['Dog'],['Dog'],
    ['Cat'],['Cat'],['Dog'],['Dog'],['Cat'],['Rat']],['Animal'])
total=df.count()
result=(df.groupBy('Animal').count()
    .withColumn('total',F.lit(total))
    .withColumn('fraction',F.expr('count/total'))
    .filter('fraction&gt;0.1'))
result.show()
</code></pre>

<p>Gave result:</p>

<pre><code>+------+-----+-----+--------+
|Animal|count|total|fraction|
+------+-----+-----+--------+
|   Dog|    4|   10|     0.4|
|   Cat|    5|   10|     0.5|
+------+-----+-----+--------+
</code></pre>

<p>To filter your initial set:</p>

<pre><code>filtered=df.join(result,df.Animal==result.Animal,'leftsemi')
filtered.show()
</code></pre>

<p>The 'leftsemi' join keeps the records in df that have a matching key in result</p>

python filtering pyspark spark-dataframe pyspark-sql