Text & language processing: MQL recipes

Recipes

Word, sentence and keyword statistics of a review
Spelling suggestions with the Levenshtein distance
One label in every case convention
Match a customer message to a chatbot intent
Palindromes and anagram groups of a word corpus
Zipf law of a small corpus
Word length distribution in English and French
Break a Caesar cipher with the index of coincidence
Vocabulary growth with the Heaps law
Rhyme scheme of a poem

executed on MentDB

Word, sentence and keyword statistics of a review

A customer review must be scored for readability and routed if it mentions a refund. The script counts words, sentences and letters with Unicode regular expressions, derives the words per sentence and the mean word length, and counts a keyword.

Pipeline

01 · wordsUnicode word countstring regexp_count→02 · ratiosReadability ratiosmath round→03 · keywordCase-insensitive searchstring contains_ignore_case

Script

admin

#1. A short customer review;
-> "[txt]" "The delivery was fast. The product, however, broke after two days! I asked for a refund and the support answered within an hour.";
#2. Words, sentences and letters;
-> "[words]" (string regexp_count [txt] "\p{L}+");
-> "[sent]" (string regexp_count [txt] "[.!?]+");
-> "[letters]" (string regexp_count [txt] "\p{L}");
#3. Readability indicators and a keyword count;
concat "{\"words\": " [words] ", \"sentences\": " [sent] ", \"words_per_sentence\": " (math round (/ [words] [sent]) 1) ", \"mean_word_length\": " (math round (/ [letters] [words]) 2) ", \"the\": " (string regexp_count [txt] "(?i)\bthe\b") ", \"refund_mentioned\": " (string contains_ignore_case [txt] "REFUND") "}";

Result

mentdb

{"words": 23, "sentences": 3, "words_per_sentence": 7.7, "mean_word_length": 4.39, "the": 3, "refund_mentioned": 1}

Go further

Split into words with string split on "[^\p{L}]+" for a frequency table
Normalise first with string lower and string unaccent

executed on MentDB

Spelling suggestions with the Levenshtein distance

A user typed 'acommodation'. The script compares it with dictionary candidates, computes the edit distance and a similarity ratio 1 - d / max length for each, and sorts the suggestions best first.

Pipeline

01 · distanceEdit distancestring levenshtein_distance→02 · similarity1 - d / max lengthmath max→03 · collectOne object per candidatejson iarray→04 · rankBest firstjson sort

Script

admin

#1. A misspelled search term and the dictionary candidates;
-> "[q]" "acommodation";
json load "rx_dict" "[\"accommodation\", \"accumulation\", \"commendation\", \"recommendation\"]";
json load "rx_res" "[]";
#2. Edit distance and similarity of each candidate;
json parse_array "rx_dict" "/" "[w]" {
	-> "[d]" (string levenshtein_distance [q] [w]);
	-> "[sim]" (math round (- 1 (/ [d] (math max (string length [q]) (string length [w])))) 3);
	json iarray "rx_res" "/" (concat "{\"word\": \"" [w] "\", \"distance\": " [d] ", \"similarity\": " [sim] "}") OBJ;
};
#3. Best match first;
json sort "rx_res" "/" "similarity" "DESC";
json doc "rx_res";

Result

mentdb

[
  {
    "distance": 1,
    "similarity": 0.923,
    "word": "accommodation"
  },
  {
    "distance": 3,
    "similarity": 0.75,
    "word": "commendation"
  },
  {
    "distance": 4,
    "similarity": 0.714,
    "word": "recommendation"
  },
  {
    "distance": 4,
    "similarity": 0.667,
    "word": "accumulation"
  }
]

Go further

Match whole sentences with string sentence_distance
Deduplicate a customer file: same loop on the names after string unaccent and string lower

executed on MentDB

One label in every case convention

A column label comes from a French spreadsheet with accents and an apostrophe. The script cleans it and gives the URL slug, the camelCase, snake_case and kebab-case identifiers, and the title and upper case forms.

Pipeline

01 · cleanAccents and apostrophestring unaccent→02 · slugURL slugstring slugify→03 · identifierscamel, snake, kebabstring camel_case→04 · titleCapitalised wordsstring capitalize_words

Script

admin

#1. A column label coming from a French spreadsheet;
-> "[label]" "Date de Naissance de l'Élève";
#2. Remove the accents and the apostrophe;
-> "[clean]" (string regexp_replace (string unaccent [label]) "'" " ");
#3. The same name in every case convention;
concat "{\"slug\": \"" (string slugify [label]) "\", \"camel\": \"" (string camel_case [clean]) "\", \"snake\": \"" (string snake_case (string camel_case [clean])) "\", \"kebab\": \"" (string kebab_case (string camel_case [clean])) "\", \"title\": \"" (string capitalize_words (string lower [clean])) "\", \"upper\": \"" (string upper [clean]) "\"}";

Result

mentdb

{"slug": "date-de-naissance-de-l-eleve", "camel": "dateDeNaissanceDeLEleve", "snake": "date_de_naissance_de_l_eleve", "kebab": "date-de-naissance-de-l-eleve", "title": "Date De Naissance De L Eleve", "upper": "DATE DE NAISSANCE DE L ELEVE"}

Go further

Shorten long labels for a column name with string truncate
Rename every key of a JSON header row in a json parse_array loop

executed on MentDB

Match a customer message to a chatbot intent

A support chatbot knows one reference sentence per intent. The script normalises a message with typos (case, accents, punctuation), counts its words and finds the closest reference sentence with a word distance tolerant to spelling errors, with the score of every intent.

Pipeline

01 · normaliseLower, no accentsstring unaccent→02 · tokensWordsstring split_sentence→03 · matchClosest sentencestring sentences_distance→04 · scoresScore per intentstring sentence_distance

Script

admin

#1. Reference phrasings of four intents of a support chatbot, o is the intent;
-> "[ref]" "{\"1\": {\"i\": \"where is my parcel\", \"o\": \"tracking\"}, \"2\": {\"i\": \"i want a refund for my order\", \"o\": \"refund\"}, \"3\": {\"i\": \"change my delivery address\", \"o\": \"address\"}, \"4\": {\"i\": \"cancel my subscription\", \"o\": \"cancel\"}}";
#2. A customer message with typos: normalise case, accents and punctuation;
-> "[msg]" "Hello, WHERE is my parcell ??? I ordered it last week.";
-> "[clean]" (string trim (string itrim (string regexp_replace (string lower (string unaccent [msg])) "[^a-z ]" " ")));
json load "rx_w" (string split_sentence [clean] " ");
#3. Closest reference sentence, words compared with a Levenshtein tolerance;
json load "rx_best" (string sentences_distance 40 70 [clean] [ref]);
-> "[intent]" (json select "rx_best" "/best_predict");
#4. Score of the message against each intent, to see the margin;
-> "[scores]" "";
for (-> "[k]" 1) (<= [k] 4) (++ "[k]") {
	json load "rx_r" [ref];
	-> "[scores]" (concat [scores] (json select "rx_r" (concat "/" [k] "/o")) "=" (string sentence_distance 40 70 [clean] (json select "rx_r" (concat "/" [k] "/i"))) " ");
};
-> "[out]" (concat "clean: " [clean] " | " (json count "rx_w" "/") " words" " | intent: " [intent] " (" (json select "rx_best" "/best_sentence") ") | scores: " (string trim [scores]));
-> "[best]" (json doc "rx_best");
json unload "rx_best"; json unload "rx_r"; json unload "rx_w";
[out]

Result

mentdb

clean: hello where is my parcell i ordered it last week | 10 words | intent: tracking (where is my parcel) | scores: tracking=400.0 refund=0.0 address=0.0 cancel=0.0

Go further

Add several reference sentences per intent: the best one wins
Under a minimum score, answer with a question instead of a wrong intent

executed on MentDB

Palindromes and anagram groups of a word corpus

For a word-game corpus, the linguist normalises each entry (lower case, no accents, letters only), keeps the palindromes, including whole French sentences, and groups the single words that are anagrams of each other by their sorted letters.

Pipeline

01 · normaliseLetters onlystring unaccent→02 · palindromeRead backwardsstring reverse→03 · lettersSplit the lettersstring split→04 · keySorted lettersstring order→05 · groupAnagram groupsjson uobject

Script

admin

#1. Words and phrases collected for a word-game corpus;
json load "rx_lex" "[\"Ésope reste ici et se repose\", \"Kayak\", \"chien\", \"niche\", \"Marie\", \"aimer\", \"ramie\", \"Engage le jeu que je le gagne\", \"Laval\"]";
json load "rx_ana" "{}";
-> "[pal]" "";
json parse_array "rx_lex" "/" "[w]" {
	#2. Normalise: lower case, no accents, letters only;
	-> "[n]" (string regexp_replace (string lower (string unaccent [w])) "[^a-z]" "");
	#3. Palindrome when the letters read the same backwards;
	if (equal [n] (string reverse [n])) {-> "[pal]" (concat [pal] [w] " | ");};
	#4. Anagram key: the sorted letters, single words only;
	if (not (string contains [w] " ")) {
		-> "[key]" (string regexp_replace (string order (string split [n] "" -1)) "[^a-z]" "");
		if (string contains (json fields "rx_ana" "/") (concat "\"" [key] "\"")) {json uobject "rx_ana" "/" [key] (concat (json select "rx_ana" (concat "/" [key])) ", " [w]) STR;} {json iobject "rx_ana" "/" [key] [w] STR;};
	};
};
-> "[out]" (concat "palindromes: " [pal] "anagram groups: " (string regexp_replace (json doc "rx_ana") "\n *" ""));
json unload "rx_lex"; json unload "rx_ana";
[out]

Result

mentdb

palindromes: Ésope reste ici et se repose | Kayak | Engage le jeu que je le gagne | Laval | anagram groups: {"aallv": "Laval","cehin": "chien, niche","aeimr": "Marie, aimer, ramie","aakky": "Kayak"}

Go further

Keep only the groups of two words or more to publish the anagram list
Compare near-anagrams with string levenshtein_distance on the sorted keys

executed on MentDB

Zipf law of a small corpus

The linguist checks the Zipf law (the frequency of a word is about inversely proportional to its rank) on a short text. The script tokenises the text, counts each word type, sorts the counts from the largest to give the ranks, fits a power model frequency = a.rank^b and draws the points with the fitted curve.

Pipeline

01 · tokensLower case tokensstring split→02 · countCount per typejson uobject→03 · rankSorted countsmath order_int→04 · fitPower modelstat fit→05 · plotPoints and curveplot fit

Script

admin

#1. A short English corpus, lower case and letters only, split into tokens;
-> "[txt]" "The cat sat on the mat and the dog sat on the rug. The cat saw the dog and the dog saw the cat. A bird sang in the tree and the cat looked at the bird. The dog ran to the tree, the bird flew away and the cat sat on the mat again. It was a quiet day in the garden and the sun was on the grass.";
json load "rx_tok" (string split (string trim (string regexp_replace (string lower [txt]) "[^a-z]+" " ")) " " -1);
#2. Count of each word type;
json load "rx_cnt" "{}";
json parse_array "rx_tok" "/" "[w]" {
	if (string contains (concat "," (string regexp_replace (json fields "rx_cnt" "/") "[\[\]\"\s]" "") ",") (concat "," [w] ",")) {json uobject "rx_cnt" "/" [w] (+ (json select "rx_cnt" (concat "/" [w])) 1) INT;} {json iobject "rx_cnt" "/" [w] 1 INT;};
};
#3. Frequencies sorted, read from the largest to give the ranks 1, 2, 3...;
json load "rx_f" "[]";
json parse_obj "rx_cnt" "/" "[w]" "[n]" {json iarray "rx_f" "/" [n] INT;};
json load "rx_s" (math order_int (json doc "rx_f"));
json load "rx_r" "[]"; json load "rx_y" "[]"; -> "[r]" 1;
for (-> "[i]" (- (json count "rx_s" "/") 1)) (>= [i] 0) (-- "[i]") {json iarray "rx_r" "/" [r] INT; json iarray "rx_y" "/" (json select "rx_s" (concat "/[" [i] "]")) INT; -> "[r]" (+ [r] 1);};
#4. Zipf law: frequency = a . rank^-b with b close to 1, fitted then drawn;
-> "[x]" (json doc "rx_r"); -> "[y]" (json doc "rx_y");
-> "[fit]" (stat fit [x] [y] "power");
json unload "rx_tok"; json unload "rx_cnt"; json unload "rx_f"; json unload "rx_s"; json unload "rx_r"; json unload "rx_y";
plot fit [x] [y] [fit] "x"

Result

mentdb

The chart is shown in the Plot tab of the editor.

Go further

On this text the fit gives b = -0.84 with r2 = 0.88: 'the' alone makes 19 of the 70 tokens
On a large corpus draw it in log-log: Zipf is a straight line of slope close to -1

executed on MentDB

Word length distribution in English and French

Comparing the same public notice in English and French, the linguist measures the word lengths with Unicode letters (accents kept, elisions split at the apostrophe). The script gives the number of words, the most frequent length, the mean, the population variance, the cumulative share of short words (3 letters or less) and the share of long words.

Pipeline

01 · tokensUnicode letters onlystring regexp_replace→02 · lengthLength of each wordstring length→03 · tableCumulative frequenciesstat frequency→04 · modeMost frequent lengthstat mode→05 · spreadPopulation variancestat pvariance

Script

admin

#1. The same notice in English and in French, word lengths in letters, apostrophes split the French elisions;
-> "[en]" "Please keep your ticket until the end of the trip. Inspectors may ask to see it at any time and a fine applies to passengers without a valid ticket.";
-> "[fr]" "Veuillez conserver votre billet jusqu'à la fin du trajet. Les contrôleurs peuvent demander à le voir à tout moment et une amende s'applique aux voyageurs sans billet valable.";
-> "[out]" "";
json load "rx_txt" (concat "{\"en\": \"" [en] "\", \"fr\": \"" [fr] "\"}");
json parse_obj "rx_txt" "/" "[lang]" "[t]" {
	#2. Tokens of letters only, Unicode aware, then the length of each word;
	json load "rx_tok" (string split (string trim (string regexp_replace [t] "[^\p{L}]+" " ")) " " -1);
	json load "rx_len" "[]";
	json parse_array "rx_tok" "/" "[w]" {json iarray "rx_len" "/" (string length [w]) INT;};
	#3. Cumulative frequency of the short words up to 3 letters, and the long words of 7 letters or more;
	json load "rx_fq" (stat frequency (json doc "rx_len"));
	-> "[short]" 0; -> "[long]" 0;
	json parse_array "rx_fq" "/" "[r]" {
		json load "rx_r" [r];
		if (<= (json select "rx_r" "/value") 3) {-> "[short]" (json select "rx_r" "/cumulative_frequency");};
		if (>= (json select "rx_r" "/value") 7) {-> "[long]" (+ [long] (json select "rx_r" "/count"));};
	};
	#4. Mode, mean and population variance of the lengths;
	-> "[out]" (concat [out] [lang] ": " (json count "rx_len" "/") " words, mode " (stat mode (json doc "rx_len")) ", mean " (math round (stat mean (json doc "rx_len")) 2) ", variance " (math round (stat pvariance (json doc "rx_len")) 2) ", short words " (math round (* [short] 100) 1) " pct, long words " (math round (* (/ [long] (json count "rx_len" "/")) 100) 1) " pct; ");
};
json unload "rx_txt"; json unload "rx_tok"; json unload "rx_len"; json unload "rx_fq"; json unload "rx_r";
[out]

Result

mentdb

en: 29 words, mode [3], mean 4.07, variance 5.17, short words 51.7 pct, long words 13.8 pct; fr: 30 words, mode [6], mean 4.77, variance 7.51, short words 40 pct, long words 26.7 pct;

Go further

French words are longer here (mode 6 against 3): English uses more short function words
Test the difference on a large sample with stat mann_whitney on the two length series

executed on MentDB

Break a Caesar cipher with the index of coincidence

In a cryptanalysis class, the linguist receives a message in capitals. The index of coincidence (0.066 for English, 0.038 for random text) shows a monoalphabetic cipher. The script then tries the 26 shifts, scores each with a chi-square test against the English letter frequencies and deciphers with the best one.

Pipeline

01 · countsLetter countsstring regexp_count→02 · icIndex of coincidencemath round→03 · shiftRotate the countsmath mod→04 · scoreChi-square vs Englishstat chi2_test→05 · decipherLetter codesstring ascii

Script

admin

#1. An intercepted message enciphered with a Caesar shift, letters only;
-> "[c]" "WKHPHHWLQJLVPRYHGWRWKHROGOLJKWKRXVHDWGDZQEULQJWKHPDSVDQGWKHUDGLRDQGWHOOQRRQHHOVHZKHUHZHDUHJRLQJ";
-> "[N]" (string length [c]);
#2. Index of coincidence sum n.n-1 / N.N-1, about 0.066 for English and 0.038 for random letters;
-> "[ic]" 0; json load "rx_obs" "[]";
for (-> "[i]" 0) (< [i] 26) (++ "[i]") {
	-> "[n]" (string regexp_count [c] (string char (+ 65 [i])));
	-> "[ic]" (+ [ic] (* [n] (- [n] 1)));
	json iarray "rx_obs" "/" [n] INT;
};
-> "[ic]" (/ [ic] (* [N] (- [N] 1)));
#3. Try the 26 shifts: chi-square of the shifted counts against the English letter frequencies, keep the smallest;
-> "[en]" "[8.2, 1.5, 2.8, 4.3, 12.7, 2.2, 2.0, 6.1, 7.0, 0.15, 0.77, 4.0, 2.4, 6.7, 7.5, 1.9, 0.095, 6.0, 6.3, 9.1, 2.8, 0.98, 2.4, 0.15, 2.0, 0.074]";
-> "[best]" 0; -> "[bchi]" 1000000;
for (-> "[s]" 0) (< [s] 26) (++ "[s]") {
	json load "rx_sh" "[]";
	for (-> "[i]" 0) (< [i] 26) (++ "[i]") {json iarray "rx_sh" "/" (json select "rx_obs" (concat "/[" (math mod (+ [i] [s]) 26) "]")) INT;};
	json load "rx_t" (stat chi2_test (json doc "rx_sh") [en]);
	-> "[chi]" (json select "rx_t" "/chi2");
	if (< [chi] [bchi]) {-> "[bchi]" [chi]; -> "[best]" [s];};
};
#4. Decipher with the best shift;
-> "[p]" "";
for (-> "[i]" 0) (< [i] [N]) (++ "[i]") {-> "[p]" (concat [p] (string char (+ 65 (math mod (+ (- (string ascii (string mid [c] [i] (+ [i] 1))) 65) (- 26 [best])) 26))));};
json unload "rx_obs"; json unload "rx_sh"; json unload "rx_t";
concat "IC " (math round [ic] 4) " = monoalphabetic, shift " [best] ", chi2 " (math round [bchi] 1) " | " (string lower [p])

Result

mentdb

IC 0.0721 = monoalphabetic, shift 3, chi2 19.6 | themeetingismovedtotheoldlighthouseatdawnbringthemapsandtheradioandtellnooneelsewherewearegoing

Go further

A Vigenere cipher has an IC near 0.04: compute it on every k-th letter to find the key length
The same chi-square scoring identifies the language of a text from its letter frequencies

executed on MentDB

Vocabulary growth with the Heaps law

From a field report the linguist measures how the vocabulary grows with the text: the number of distinct words V after every 10 tokens. The script fits the Heaps law V = K.n^beta, gives the type-token ratio and extrapolates the vocabulary of a 1 million token corpus.

Pipeline

01 · tokensTokens in orderstring split→02 · typesNew typesstring contains→03 · fitPower modelstat fit→04 · extrapolateK.n^betamath power

Script

admin

#1. Running text of a field report, tokens in reading order;
-> "[txt]" "We reached the village at noon after a long walk through the forest. The elders met us under a large tree and offered water and fruit. They spoke a dialect close to the coastal language but with many old words for plants, tools and fishing. Children repeated our questions and laughed at our accent. In the evening we recorded three stories about the river, the first harvest and the founding of the village. Each story used the same opening formula and the same closing song, which the elders said came from their grandparents. We will return next month with a better microphone and a list of words to check with the women who sell fish at the market.";
json load "rx_tok" (string split (string trim (string regexp_replace (string lower [txt]) "[^a-z]+" " ")) " " -1);
#2. Vocabulary size V after every 10 tokens;
-> "[seen]" ","; -> "[v]" 0; -> "[i]" 0;
json load "rx_n" "[]"; json load "rx_v" "[]";
json parse_array "rx_tok" "/" "[w]" {
	-> "[i]" (+ [i] 1);
	if (not (string contains [seen] (concat "," [w] ","))) {-> "[seen]" (concat [seen] [w] ","); -> "[v]" (+ [v] 1);};
	if (== (math mod [i] 10) 0) {json iarray "rx_n" "/" [i] INT; json iarray "rx_v" "/" [v] INT;};
};
#3. Heaps law V = K . n^beta fitted as a power model, then extrapolated to a corpus of 1 million tokens;
json load "rx_fit" (stat fit (json doc "rx_n") (json doc "rx_v") "power");
-> "[K]" (json select "rx_fit" "/a"); -> "[b]" (json select "rx_fit" "/b");
-> "[out]" (concat [i] " tokens, " [v] " types, TTR " (math round (/ [v] [i]) 3) " | K " (math round [K] 3) ", beta " (math round [b] 3) ", r2 " (math round (json select "rx_fit" "/r2") 4) " | predicted vocabulary for 1M tokens " (math round (* [K] (math power 1000000 [b])) 0));
json unload "rx_tok"; json unload "rx_n"; json unload "rx_v"; json unload "rx_fit";
[out]

Result

mentdb

118 tokens, 82 types, TTR 0.695 | K 1.314, beta 0.873, r2 0.9968 | predicted vocabulary for 1M tokens 228503

Go further

beta = 0.87 on 118 tokens is high: on large corpora beta is usually between 0.4 and 0.6, the extrapolation needs more text
Plot the growth with plot fit on the two arrays and the fitted model

executed on MentDB

Rhyme scheme of a poem

For a metrics course the script finds the rhyme scheme of two stanzas: the last word of each line, a rhyme key made of its last vowel group and the following consonants, and one letter per distinct key in order of appearance.

Pipeline

01 · lastLast wordatom get_last→02 · keyRhyme keystring regexp_extract→03 · knownKey already seenatom find→04 · letterLetter of the schemeatom size

Script

admin

#1. Two stanzas of a poem, one line per element;
json load "rx_poem" "[\"The harbour lights were burning low\", \"When fishing boats came in at night\", \"And every sailor seemed to know\", \"The weather would be turning bright\", \"She walked along the empty quay\", \"And counted every mast in sight\", \"The gulls were sleeping on the sea\", \"And nothing moved till morning light\"]";
-> "[keys]" ""; -> "[scheme]" ""; -> "[letters]" "ABCDEFGH";
json parse_array "rx_poem" "/" "[l]" {
	#2. Rhyme key = last vowel group of the final word and the consonants after it;
	-> "[w]" (string lower (atom get_last (string trim [l]) " "));
	-> "[k]" (string regexp_extract [w] "[aeiouy]+[^aeiouy]*$" 0);
	#3. A known key takes the letter of its first line, a new key takes the next letter;
	-> "[pos]" (atom find [keys] [k] ",");
	if (== [pos] 0) {
		-> "[keys]" (if (equal [keys] "") {[k];} {concat [keys] "," [k];});
		-> "[pos]" (atom size [keys] ",");
	};
	-> "[scheme]" (concat [scheme] (string mid [letters] (- [pos] 1) [pos]));
};
json unload "rx_poem";
#4. Eye rhymes such as quay and sea are missed by spelling: a phonetic key would be needed;
concat "scheme " [scheme] " | rhyme keys " [keys] " | " (atom size [keys] ",") " rhyme sounds by spelling"

Result

mentdb

scheme ABABCBDB | rhyme keys ow,ight,uay,ea | 4 rhyme sounds by spelling

Go further

The result ABABCBDB is wrong for quay / sea, which rhyme by sound but not by spelling: a phonetic dictionary key is needed
French poetry also checks the gender of the rhyme: a final mute e makes a feminine rhyme