title: Converting RDD to Contingency Table: Pyspark
body: <p>Currently I am trying to convert an RDD to a <a href="https://en.wikipedia.org/wiki/Contingency_table" rel="nofollow">contingency table</a> in-order to use the <code>pyspark.ml.clustering.KMeans</code> module, which takes a dataframe as input. </p>
  
  <p>When I do <code>myrdd.take(K)</code>,(where K is some number) the structure looks as follows:</p>
  
  <blockquote>
    <p>[[u'user1',('itm1',3),...,('itm2',1)], [u'user2',('itm1',7),...,
    ('itm2',4)],...,[u'usern',('itm2',2),...,('itm3',10)]]</p>
  </blockquote>
  
  <p>Where each list contains an entity as the first element and the set of all items and their counts that was liked by this entity in the form of tuple. </p>
  
  <p>Now, my objective is to convert the above into a spark <code>DataFrame</code> that resembles the following contingency table.</p>
  
  <pre><code>+----------+------+----+-----+
  |entity    |itm1  |itm2|itm3 |
  +----------+------+----+-----+
  |    user1 |     3|   1|    0|
  |    user2 |     7|   4|    0|
  |    usern |     0|   2|   10|
  +----------+------+----+-----+
  </code></pre>
  
  <p>I have used the <code>df.stat.crosstab</code> method as cited in the following link  : </p>
  
  <p><a href="https://databricks.com/blog/2015/06/02/statistical-and-mathematical-functions-with-dataframes-in-spark.html" rel="nofollow">Statistical and Mathematical Functions with DataFrames in Apache Spark - 4. Cross Tabulation (Contingency Table)</a> </p>
  
  <p>and it is almost close to what I want. </p>
  
  <p>But if there is one more count field like in the above tuple i.e., <code>('itm1',3)</code> how to incorporate (or add) this value <em>3</em> into the final result of the contingency table (or entity-item matrix). </p>
  
  <p>Of course, I take the long route by  converting the above list of <code>RDD</code> into a matrix and write them as csv file and then read back as a <code>DataFrame</code>.</p>
  
  <p>Is there a simpler way to do it using DataFrame ?</p>