List frequently occurring words across different rows

Question

I have following data which has id, text. I want to find frequently occurring words from the text column across rows for the same "id". I don't want to consider frequently occurring words in the same row (sentence in text column). I tried using TF-IDF Algorithm to achieve it.

id,text
1,Interface Down GigabitEthernet0/1/2 null .  
1,Interface Gi0/1/2 Down on node BMAC69RT01  
1,Interface Down MEth0/0/1 null .  
1,Interface MEth0/0/1 Down on node  
2,Interface Up FastEthernet0/0/0 null 
2,Interface Fa0/0/0 Down on node

First i created tokens from the text column

val tokenizer = new Tokenizer().setInputCol("text").setOutputCol("words")

Then i tried using countvectorizer and IDF to get the commonly used words.I think countvectorizer is not needed here as i don't need to consider term frequency in the same sentence.

  val countVectors = new CountVectorizer()
    .setInputCol("words")
      .setOutputCol("vectorText")
 val idf = new IDF().setInputCol("vectorText").setOutputCol("features")

This gives me an output as follows

|1  |(11,[0,1,2,6],[0.0,0.15415067982725836,0.3364722366212129,1.252762968495368]) 
|1  |(11,[0,1,2,3,4,5,8],[0.0,0.3083013596545167,0.3364722366212129,1.1192315758708453,0.5596157879354227,0.5596157879354227,1.252762968495368]) 
|1  |(11,[0,1,2,3],[0.0,0.15415067982725836,0.3364722366212129,0.5596157879354227]) 
|1  |(11,[0,1,3,4,5],[0.0,0.15415067982725836,0.5596157879354227,0.5596157879354227,0.5596157879354227]) 
|2  |(11,[0,2,7,9],[0.0,0.3364722366212129,1.252762968495368,1.252762968495368]) 
|2  |(11,[0,1,4,5,10],[0.0,0.15415067982725836,0.5596157879354227,0.5596157879354227,1.252762968495368])

I know the above output gives me features and frequency for each word. But from the above output how can i get the real words and its frequency. I want a output similar to the below output. Any other algorithm available in spark to achieve below output

Label | (Word, Frequency)
1, | (Interface, 4) (Down, 4) (null, 2) (on, 2)
2, | (Interface, 2)

I want to eliminate least frequent words, also remove stop words which is easier using spark ml library. whether above logic can be easily implemented using RDD API. I mostly worked on Spark SQL and dataframe. — Mohan

Puneeth Reddy V Puneeth Reddy V · Accepted Answer · 2018-04-05T10:18:46

Thinking that this post might help you, here is a way to get your required output using fold operation

import scala.io.Source
Source.fromFile("fileName").getLines()
 .toList.tail //remove headers
 .foldLeft(Map.empty[Int,Map[String,Int]]){ //use fold with seed
    (map, line) => { 
       val words = line.split("\\W+") //split each line into words
       val lineNumber = words.apply(0).toInt //get line number this can throw error
       var existingCount = map.getOrElse(lineNumber, Map.empty[ String, Int]) //check for existing count
       words.tail.map(word => { 
          val result: Int = existingCount.getOrElse(word,0) 
          existingCount = existingCount + (word -> (result + 1))
       })
       map + (lineNumber -> existingCount)
   }
}.foreach(e => println(e._1+ " | "+e._2.map(x => "("+x._1+", "+x._2+")")))

// 1 | List((Interface, 3), (MEth0, 2), (BMAC69RT01, 1), (null, 1), (1, 3), (on, 2), (Down, 3), (0, 2), (Gi0, 1), (2, 1), (node, 2))
// 2 | List((Interface, 2), (null, 1), (Fa0, 1), (on, 1), (Down, 1), (0, 4), (FastEthernet0, 1), (Up, 1), (node, 1))

List frequently occurring words across different rows

1 Answers