Forum Discussion
TheOriginalDeeb
8 years agoFrequent Visitor
Is Word Co-Occurence possible in Power Query?
I'm hoping to re-create a solution that was achieved using an R script (because R makes no sense to me) : https://stackoverflow.com/questions/27153320/build-word-co-occurence-edge-list-in-r Here's...
- 8 years ago
Here is a solution that is in one query in M.
let Source = DF, sentences = List.Transform(DF[text], each Text.Lower(_)), minSentences = List.Transform(sentences, each List.Distinct(Text.Split(_, " ")) ), wordsList = List.Combine(minSentences), wordsDistinct = List.Distinct(wordsList), #"Converted to Table" = Table.FromList(wordsDistinct, Splitter.SplitByNothing(), {"word"}, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"word", type text}}), crossTab = List.Accumulate(wordsDistinct, #"Changed Type", (table, col) => Table.AddColumn(table, col, each 0, Int64.Type) ), records = Table.TransformRows(crossTab, each let currentWord = [word], currentWordIndex = List.PositionOf(wordsDistinct, currentWord) in // input the current row into an accumulator that iterates over the sentences List.Accumulate(minSentences, _, (r, s) => // if the current word is in the sentence if List.Contains(s, currentWord) then let coWords = List.RemoveMatchingItems(s, {currentWord}) in // increment co-word columns if not List.IsEmpty(coWords) then // iterate the co-words operating on the row to increment the column List.Accumulate(coWords, r, (row, coWord) => if List.PositionOf(wordsDistinct, coWord) > currentWordIndex then Record.TransformFields(row, {coWord, each _ + 1}) else row ) else r else r ) ), crossTabPopulated = Table.FromRecords(records), #"Changed Type1" = Table.TransformColumnTypes(crossTabPopulated,{{"word", type text}, {"a", Int64.Type}, {"b", Int64.Type}, {"c", Int64.Type}, {"d", Int64.Type}, {"e", Int64.Type}}), #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type1", wordsDistinct, "coWord", "Count"), output = Table.SelectRows(#"Unpivoted Only Selected Columns", each [word] <> [coWord] and [Count] <> 0) in output
RobertSlattery
8 years agoResponsive Resident
Here is a solution that is in one query in M.
let
Source = DF,
sentences = List.Transform(DF[text], each Text.Lower(_)),
minSentences = List.Transform(sentences,
each List.Distinct(Text.Split(_, " "))
),
wordsList = List.Combine(minSentences),
wordsDistinct = List.Distinct(wordsList),
#"Converted to Table" = Table.FromList(wordsDistinct, Splitter.SplitByNothing(), {"word"}, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"word", type text}}),
crossTab = List.Accumulate(wordsDistinct, #"Changed Type",
(table, col) => Table.AddColumn(table, col, each 0, Int64.Type)
),
records = Table.TransformRows(crossTab, each
let
currentWord = [word],
currentWordIndex = List.PositionOf(wordsDistinct, currentWord)
in
// input the current row into an accumulator that iterates over the sentences
List.Accumulate(minSentences, _, (r, s) =>
// if the current word is in the sentence
if List.Contains(s, currentWord) then
let
coWords = List.RemoveMatchingItems(s, {currentWord})
in
// increment co-word columns
if not List.IsEmpty(coWords) then
// iterate the co-words operating on the row to increment the column
List.Accumulate(coWords, r, (row, coWord) =>
if List.PositionOf(wordsDistinct, coWord) > currentWordIndex then
Record.TransformFields(row, {coWord, each _ + 1})
else
row
)
else
r
else
r
)
),
crossTabPopulated = Table.FromRecords(records),
#"Changed Type1" = Table.TransformColumnTypes(crossTabPopulated,{{"word", type text}, {"a", Int64.Type}, {"b", Int64.Type}, {"c", Int64.Type}, {"d", Int64.Type}, {"e", Int64.Type}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type1", wordsDistinct, "coWord", "Count"),
output = Table.SelectRows(#"Unpivoted Only Selected Columns", each [word] <> [coWord] and [Count] <> 0)
in
output
- TheOriginalDeeb8 years agoFrequent Visitor
Amazing. Thank you so much!