29:null 2a:["$","title",null,{"children":"Exploring complaint embeddings | Jonathan St-Onge | Observable"}] 2f:T406,viewof form = Inputs.form({ select_m: Inputs.select(["paraphrase_multilingual_MiniLM_L12_v2_True_True","paraphrase_multilingual_MiniLM_L12_v2_True_False","paraphrase_Multilingual_Minilm_L12_V2_false_true","paraphrase_multilingual_minilm_l12_v2_false_false","sentence_transformerslabse_true_false","sentence_transformerslabse_false_false","sentence_transformerslabse_false_true","sentence_transformerslabse_true_true","doc2vec_true_true","doc2vec_true_false","doc2vec_false_true","doc2vec_false_false","allMinilmL6V2_true_true", "allMinilmL6V2_false_false","allMinilmL6V2_false_true","allMinilmL6V2_true_false"], {label: "Select model", value: "paraphrase_multilingual_MiniLM_L12_v2_True_False"}), select: Inputs.select(["doc_top", "airline", "review_id", "reduced_topic", "country", "ratings", "lang", "lightgrey"], {label: "Select color"}), topic_hl: Inputs.select([""].concat(_.range(0,130)), {label: "highlight topic"}), reduced_topic_hl: Inputs.select([""].concat(_.range(0,20)), {label: "highlight reduced topic"}) })30:T5e3,emb_plot = Plot.plot({ width: 1200, height: 800, marginTop: 100, marginBottom: 200, marginLeft: 100, marginRight: 100, x: {ticks: "", label: "", labelArrow: null}, y: {ticks: "", label: "", labelArrow: null}, color: { legend: true, type: form.select === "review_id" || form.select === "doc_top" || form.select === "reduced_topic" ? "linear" : "ordinal", scheme: form.select === "review_id" || form.select === "doc_top" || form.select === "reduced_topic" ? "GnBu" : "Tableau10" // range: [0.3, 1] }, marks: [ Plot.dot(full_data_f, { x: "embedding_x", y: "embedding_y", fill: form.select, fillOpacity: 0.4, }), Plot.dot(full_data_f.filter(d => d.doc_top === form.topic_hl), { x: "embedding_x", y: "embedding_y", fill: form.topic_hl === "" ? "" : "red", fillOpacity: 0.8, }), Plot.dot(full_data_f.filter(d => d.reduced_topic === form.reduced_topic_hl), { x: "embedding_x", y: "embedding_y", fill: form.reduced_topic_hl === "" ? "" : "lime", fillOpacity: 0.3, }), Plot.tip(full_data_f, Plot.pointer({ x: "embedding_x", y: "embedding_y", lineWidth: 40, filter: d => d.doc_top !== 0, title: d => `Id: ${d.review_id}\nCountry: ${d.country}\nTopic: ${d.doc_top}\nReduced Topic: ${d.reduced_topic}\nAirline: ${d.airline}\nDate: ${d.date_pub}\n:Ratings: \n\nTitle: ${d.title}\n\nSentence: ${d.text}\n\nFull translated complaint:\n${d.translated_text}`, })) ] })31:Ta22,top_description = ({ 0: "Food ", 1: "Baggage (large chunk on bottom left of plot)", 2: "Time-related issues (and delays): may include general remarks about time that could be positive (e.g., on time)", 3: "Seat(s)", 4: "Airline company/companies: general comments/comparisons about them", 5: "Delays (likely to be mostly negative since word “delay” predominates)", 6: "Adverbs with extreme formulations/upgraders/intensifiers. Many of the sentences are quite short. and typographical features such as exclamation points and capital letters appear frequently. This topic somehow seems to relate more to linguistic aspects of the expressions rather than one topic per se. Words such as: only/very/seulement/tres/deçu/さすが・やっぱり・やはり(after all/of course), Xすぎる (excessive/exceeds X)、だけ(only)、特に(in particular)、よほど (very/quite/to a large extent)、すごい・すごく (very, awfully, to a great extent).", 7: "Airplane/physical components of plane/airline company names/flight/aviation", 8: "Negative, future-oriented advice: Never again/Do not recommend/exclamations/disgust/ amplifiers/intense/extreme expressions: lots of Japanese and French examples; but relatively fewer in English. Wouldn’t recommend it, won’t use again, もう二度とX~ません　(Won’t X again/twice)　使わないほうがいい (You should not use X [airline])", 9: "Contacting customer service/communication w/CSR email/calls/phone", 10: "Company/business", 11: "Price/money", 12: "Conduct and morality: adjectives & other words focusing on another person (e.g., flight attendant): rudeness, polite(ness), no sympathy, denigrating, deception, 嘘つき(liar)、失礼(rudeness)、筋違い (out of line/unreasonable/absurd), 無愛想 (surly/cold/brusque), バカな人間 (stupid person)", 13: "Discomfort: focusing on the feelings of the passenger/person posting review: frustration, struggle, upsetting, pissed, ridiculous, nausea, scandaleux, 最悪 (worst)", 21: "Money issues (from: JSO)", 24: "Failed expectation (from: JSO)", 30: "Service: personnel, staff, crew AirCan/Transat, some JAL", 34: "In-flight entertainment (“IFE”), wi-fi", 36: "In-flight entertainment (“IFE”), wi-fi", 35: "Schedule changes/delays/cancellations", 50: "Toilet", 58: "Seat selection/reservations/location: almost exclusively reviews in French, for both AirCan and AirTransat", 62: "Temperature (inside the plane)", 100: "Time/delay (not sure yet how these differ from topics 2&5 above)", 103: "Time/delay (not sure yet how these differ from topics 2&5 above)" })32:T6b0,db = DuckDBClient.of({ reviews: FileAttachment("tripadvisor_reviews_multi_downsampled_trans.parquet"), paraphrase_multilingual_MiniLM_L12_v2_True_True: FileAttachment("paraphrase-multilingual-MiniLM-L12-v2_True_True@2.parquet"), paraphrase_multilingual_MiniLM_L12_v2_True_False: FileAttachment("paraphrase_multilingual_MiniLM_L12_v2_True_False.parquet"), paraphrase_multilingual_MiniLM_L12_v2_False_True: FileAttachment("paraphrase-multilingual-MiniLM-L12-v2_False_True@1.parquet"), paraphrase_multilingual_MiniLM_L12_v2_False_False: FileAttachment("paraphrase-multilingual-MiniLM-L12-v2_False_False@1.parquet"), doc2vec_False_False: FileAttachment("doc2vec_False_False.parquet"), doc2vec_false_true: FileAttachment("doc2vec_False_True.parquet"), doc2vec_true_false: FileAttachment("doc2vec_True_False.parquet"), doc2vec_true_true: FileAttachment("doc2vec_True_True.parquet"), allMinilmL6V2_false_true: FileAttachment("all-MiniLM-L6-v2_False_True.parquet"), allMinilmL6V2_true_false: FileAttachment("all-MiniLM-L6-v2_True_False.parquet"), allMinilmL6V2_true_true: FileAttachment("all-MiniLM-L6-v2_True_True.parquet"), allMinilmL6V2_false_false: FileAttachment("all-MiniLM-L6-v2_False_False.parquet"), sentence_transformerslabse_false_false: FileAttachment("sentence_transformersLaBSE_False_False.parquet"), sentence_transformerslabse_false_true: FileAttachment("sentence_transformersLaBSE_False_True.parquet"), sentence_transformerslabse_true_false: FileAttachment("sentence_transformersLaBSE_True_False.parquet"), sentence_transformerslabse_true_true: FileAttachment("sentence_transformersLaBSE_True_True.parquet"), reduced_topic: FileAttachment("reduced_topic_map@1.csv") })33:[{"id":"c225734290fc50c05850f1a41780d3987d4bc9e7efc9f6faa21d9871cedba02657be19a347dca053abaf5e7447ef090307a5bd01a3a21248254d9f61939ebc9e","href":"https://static.observableusercontent.com/files/c225734290fc50c05850f1a41780d3987d4bc9e7efc9f6faa21d9871cedba02657be19a347dca053abaf5e7447ef090307a5bd01a3a21248254d9f61939ebc9e","name":"doc2vec_True_True.parquet","type":"application/octet-stream","lastModified":1686949141528.87,"size":2114594},{"id":"9a869b403cee2cd589d46c1f68386edc0dbe62f57a47786cbf70de78cf716efa8f711cd3b1090989eb2f45af833e59e30500cf794241e21494b35839f9d7730e","href":"https://static.observableusercontent.com/files/9a869b403cee2cd589d46c1f68386edc0dbe62f57a47786cbf70de78cf716efa8f711cd3b1090989eb2f45af833e59e30500cf794241e21494b35839f9d7730e","name":"all-MiniLM-L6-v2_False_True.parquet","type":"application/octet-stream","lastModified":1686949163448.301,"size":2122219},{"id":"596bf68aa1a20b555ecbf16ed262e3c3ab2d62ffce33c4f89f8832cfb4284c6c49ff4e50a2d4daee9fe15c53b27cced610d2703ef3ecafc958c91905e62e538f","href":"https://static.observableusercontent.com/files/596bf68aa1a20b555ecbf16ed262e3c3ab2d62ffce33c4f89f8832cfb4284c6c49ff4e50a2d4daee9fe15c53b27cced610d2703ef3ecafc958c91905e62e538f","name":"sentence_transformersLaBSE_True_True.parquet","type":"application/octet-stream","lastModified":1687268325983.386,"size":2119145},{"id":"829d2dd9f5267e0b0b4b9bb40f136d84ad48c8219450157b41c6ae9d1149e38a527f2fd3788287be1f3af0fc2015ab25507e827833b6fde2047222c520f31ba0","href":"https://static.observableusercontent.com/files/829d2dd9f5267e0b0b4b9bb40f136d84ad48c8219450157b41c6ae9d1149e38a527f2fd3788287be1f3af0fc2015ab25507e827833b6fde2047222c520f31ba0","name":"paraphrase_beta_table.parquet","type":"application/octet-stream","lastModified":1688417371942.718,"size":77484},{"id":"32d4876f271006851c348de96d2b96258f90f0bf83fc5e836cacbf098dc5577482a127df7cca20df8c6090761ffa0b94cf511bbad102b66978e16cbd186375d6","href":"https://static.observableusercontent.com/files/32d4876f271006851c348de96d2b96258f90f0bf83fc5e836cacbf098dc5577482a127df7cca20df8c6090761ffa0b94cf511bbad102b66978e16cbd186375d6","name":"reduced_topic_map.csv","type":"text/csv","lastModified":1688585757723.11,"size":693},{"id":"f59463adee0fed2eff62e9168d872c9f8708cba045377a6dac5c39404dc3d2aaf496728dc8e1840d513a2056b9451545c6672b3fa6bd0b18aecba4cde8b280fa","href":"https://static.observableusercontent.com/files/f59463adee0fed2eff62e9168d872c9f8708cba045377a6dac5c39404dc3d2aaf496728dc8e1840d513a2056b9451545c6672b3fa6bd0b18aecba4cde8b280fa","name":"doc2vec_False_True.parquet","type":"application/octet-stream","lastModified":1686949149873.921,"size":2117238},{"id":"b5ad1449a48e11b45e4b61a6ec841b102001eef0b3e9399d9883049a5a8589b096701bd855e1def57db278c5f55baf63fe02f939d88a82d733aea74998eff7e3","href":"https://static.observableusercontent.com/files/b5ad1449a48e11b45e4b61a6ec841b102001eef0b3e9399d9883049a5a8589b096701bd855e1def57db278c5f55baf63fe02f939d88a82d733aea74998eff7e3","name":"paraphrase-multilingual-MiniLM-L12-v2_True_True@2.parquet","type":"application/octet-stream","lastModified":1686949089746.487,"size":2134597},{"id":"1f25b500c2a1c002062361e0b9f0ec5477285d0882cdea82f526aef8308c7a42548dee5084659b89f0a79f63121adff3d78f5426e2114daadf6b801991da1f0d","href":"https://static.observableusercontent.com/files/1f25b500c2a1c002062361e0b9f0ec5477285d0882cdea82f526aef8308c7a42548dee5084659b89f0a79f63121adff3d78f5426e2114daadf6b801991da1f0d","name":"doc2vec_True_False.parquet","type":"application/octet-stream","lastModified":1686949146643.375,"size":2119794},{"id":"56f842999b3238262abe4493cab0d1276e6ffba4403cfb4667d3bcf92249b63c61d0d182aa6dc701ffec0f4f461a2c95ffba062d64c16294c9846b9ac68eaaf5","href":"https://static.observableusercontent.com/files/56f842999b3238262abe4493cab0d1276e6ffba4403cfb4667d3bcf92249b63c61d0d182aa6dc701ffec0f4f461a2c95ffba062d64c16294c9846b9ac68eaaf5","name":"all-MiniLM-L6-v2_True_True.parquet","type":"application/octet-stream","lastModified":1686949155567.399,"size":2123386},{"id":"cef51d2255951b5479591244d1bb322a42a875976408c1bde8f7972ad443a481bb79677b3ac6921009b0874407de5bc61fe6322745f4e4d8d0d2207252adc337","href":"https://static.observableusercontent.com/files/cef51d2255951b5479591244d1bb322a42a875976408c1bde8f7972ad443a481bb79677b3ac6921009b0874407de5bc61fe6322745f4e4d8d0d2207252adc337","name":"doc2vec_False_False.parquet","type":"application/octet-stream","lastModified":1686949153114.445,"size":2115573},{"id":"57dfbe061bfe51fabbebb9e1ccbe833f7b6dd03f5a29ea7079d8bf00039b4cd03cae231b4ccd86b6585488886f38138279ad956608d83dfa768354acfebed195","href":"https://static.observableusercontent.com/files/57dfbe061bfe51fabbebb9e1ccbe833f7b6dd03f5a29ea7079d8bf00039b4cd03cae231b4ccd86b6585488886f38138279ad956608d83dfa768354acfebed195","name":"all-MiniLM-L6-v2_False_False.parquet","type":"application/octet-stream","lastModified":1686949166358.537,"size":2122400},{"id":"9e9664380983036149f0561b6a52120ea2b7544b22c3a497a606899cf57915fb604869ab32e92edb7df558e8b8b9edfaaaca360a681f45cc2dc3cad811b16179","href":"https://static.observableusercontent.com/files/9e9664380983036149f0561b6a52120ea2b7544b22c3a497a606899cf57915fb604869ab32e92edb7df558e8b8b9edfaaaca360a681f45cc2dc3cad811b16179","name":"all-MiniLM-L6-v2_True_False.parquet","type":"application/octet-stream","lastModified":1686949158044.615,"size":2120245},{"id":"d313ee650752d99c4aebce1cda41686395feb60b25399b2e57befd409c1da08a7789d2bde17d17cf7f4db1aa5126a5cabb010cd6225dc5d5b846396c10ec47a7","href":"https://static.observableusercontent.com/files/d313ee650752d99c4aebce1cda41686395feb60b25399b2e57befd409c1da08a7789d2bde17d17cf7f4db1aa5126a5cabb010cd6225dc5d5b846396c10ec47a7","name":"paraphrase-multilingual-MiniLM-L12-v2_False_True@1.parquet","type":"application/octet-stream","lastModified":1686949318178.398,"size":2136836},{"id":"c6f55501c2c039cba541a4afdde85a7b8db22308b5563348c21ea9d4c064cf0429845bf60679dd66044d97778320eb9a115c7b8941f89c0de4ccce8c44685ddd","href":"https://static.observableusercontent.com/files/c6f55501c2c039cba541a4afdde85a7b8db22308b5563348c21ea9d4c064cf0429845bf60679dd66044d97778320eb9a115c7b8941f89c0de4ccce8c44685ddd","name":"paraphrase-multilingual-MiniLM-L12-v2_False_False@1.parquet","type":"application/octet-stream","lastModified":1686949321120.93,"size":2132908},{"id":"bdd7cc7bdc146384b504c4e8a56b54be603e86ba4c4ccf26816f3320a510c7f96fc083c527204d8b0765950adadfb589f42afa0852689c8d6b4be2da6fa0e2e3","href":"https://static.observableusercontent.com/files/bdd7cc7bdc146384b504c4e8a56b54be603e86ba4c4ccf26816f3320a510c7f96fc083c527204d8b0765950adadfb589f42afa0852689c8d6b4be2da6fa0e2e3","name":"paraphrase_multilingual_MiniLM_L12_v2_True_False.parquet","type":"application/octet-stream","lastModified":1687266416393.724,"size":2137446},{"id":"e8dea314010edffa3647e350d2a773547f3dc23e4c3735856822532093556f2422d6380a499d1f92441f002dbba017056b178e46d15c27b0f4d09a2a26f5947a","href":"https://static.observableusercontent.com/files/e8dea314010edffa3647e350d2a773547f3dc23e4c3735856822532093556f2422d6380a499d1f92441f002dbba017056b178e46d15c27b0f4d09a2a26f5947a","name":"sentence_transformersLaBSE_False_False.parquet","type":"application/octet-stream","lastModified":1687268325931.099,"size":2138753},{"id":"7d316f627e6f913c0c0cbc474a14f1d60eb55d8ce6bb7dd3facf62ddd989906fc0fc6a8c0d588f7840aa0257dac1bdd58af0aef589c472ebf4881c1bd5e45676","href":"https://static.observableusercontent.com/files/7d316f627e6f913c0c0cbc474a14f1d60eb55d8ce6bb7dd3facf62ddd989906fc0fc6a8c0d588f7840aa0257dac1bdd58af0aef589c472ebf4881c1bd5e45676","name":"sentence_transformersLaBSE_True_False.parquet","type":"application/octet-stream","lastModified":1687268325965.065,"size":2116249},{"id":"44c4968543154fcd7dbd8a7965f22f55da04051ba124534943dee6a2bbb0b53d1cbf175155140046475c538f5a53053e746cc8ce51c378ff010dba745a86d8a6","href":"https://static.observableusercontent.com/files/44c4968543154fcd7dbd8a7965f22f55da04051ba124534943dee6a2bbb0b53d1cbf175155140046475c538f5a53053e746cc8ce51c378ff010dba745a86d8a6","name":"sentence_transformersLaBSE_False_True.parquet","type":"application/octet-stream","lastModified":1687268326127.094,"size":2116741},{"id":"f115b8685c56139ad496779507330c5d30e1990999d206bf9103cf5a76e7b2c5b9ba1e29f0e325b093004b01c0c461dfd0fda24829a4183471ffc32ed3d17123","href":"https://static.observableusercontent.com/files/f115b8685c56139ad496779507330c5d30e1990999d206bf9103cf5a76e7b2c5b9ba1e29f0e325b093004b01c0c461dfd0fda24829a4183471ffc32ed3d17123","name":"tripadvisor_reviews_multi_downsampled_trans.parquet","type":"application/octet-stream","lastModified":1686763334490.221,"size":5198100},{"id":"a51e3bce3e74715611fa6d94d85ac255e01d050c17728aaf26d7226dca9ec3146d76f5987d5ca8bda9d4cf2401cc093fcf71d5d28f656465c8369d81c600b37b","href":"https://static.observableusercontent.com/files/a51e3bce3e74715611fa6d94d85ac255e01d050c17728aaf26d7226dca9ec3146d76f5987d5ca8bda9d4cf2401cc093fcf71d5d28f656465c8369d81c600b37b","name":"complaints (1).db","type":"application/x-sqlite3","lastModified":1688416375314.914,"size":11509760},{"id":"73481426881fd5fa5341c923a7c58d2496c69e450bf489f31db409f1b4f577b94ac173ce3d72babe7a964ac6408f0ce7a8c094967ecd0db14016588f20821ae2","href":"https://static.observableusercontent.com/files/73481426881fd5fa5341c923a7c58d2496c69e450bf489f31db409f1b4f577b94ac173ce3d72babe7a964ac6408f0ce7a8c094967ecd0db14016588f20821ae2","name":"reduced_topic_map@1.csv","type":"text/csv","lastModified":1688586739350.044,"size":806}] 2d:{"id":"e568359e97f099c9","slug":"exploring-complaints-embeddings","title":"Exploring complaint embeddings","thumbnail":"e6e6160103d835b97ddf7494ff188c8953fdb266254dfd06b62d4f5d98564f23","access_level":"public","create_time":"$D2023-06-14T17:20:35.690Z","update_time":"$D2023-11-17T15:56:39.085Z","view_time":null,"first_public_time":"$D2023-06-29T15:46:37.558Z","has_unpublished_edits":null,"fork":false,"stars":0,"starred":false,"owner":{"type":"team","id":"4fabdbefe825d266","login":"jstonge","name":"Jonathan St-Onge","avatar_url":"https://avatars-next.observableusercontent.com/48507f1ab99162ceb5bc1fccad23b163ba525a2a9a76b045557676a38f2a7014","tier":"starter_2024"},"version":2102,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"7cb18c5b30f73a44","login":"jstonge","name":"Jonathan St-Onge","avatar_url":"https://avatars-next.observableusercontent.com/48507f1ab99162ceb5bc1fccad23b163ba525a2a9a76b045557676a38f2a7014","tier":null}],"members":[{"type":"individual","id":"7cb18c5b30f73a44","login":"jstonge","name":"Jonathan St-Onge","avatar_url":"https://avatars-next.observableusercontent.com/48507f1ab99162ceb5bc1fccad23b163ba525a2a9a76b045557676a38f2a7014","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":3,"collection":null,"type":"notebook","body":{"title":"Exploring complaint embeddings","cells":[{"id":0,"value":"# Exploring complaint embeddings\n*Note*: the layout of the model name is *[model]*_*[use_named_entity_recognition]*_*[use_translation]*. NER means that we replace named entites with their tags (JAL becomes [ORG], see [here](https://spacy.io/usage/linguistic-features#named-entities) for a list). The best model is currently **[paraphrase_multilingual_MiniLM_L12_v2_True_False]**, i.e. we are using NER but we don't feed translated text.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":981,"value":"$2f","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":498,"value":"$30","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1021,"value":"full_data_f = full_data","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"full_data_f","database":"$undefined"},{"id":1724,"value":"$31","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1900,"value":"word_count = full_data_f.map(d => d.text.split(\" \").length).length","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1268,"value":"`Average word count: ${d3.mean(full_data_f.map(d => d.text.split(\" \").length))} in this cluster`","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":20,"value":"### Exploring downsampled data","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":90,"value":"miniPlots = html`\n

${[plot_count(count_lang, 'lang') ]}

${[plot_count(count_airline, 'airline') ]}

${[plot_count(count_country, 'country') ]}

${[plot_sent() ]}

${[plot_ratings() ]}

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":331,"value":"pd.legend('color')","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1400,"value":"#### Timeseries","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1178,"value":"Plot.plot({\n width: 1200,\n marginBottom: 100,\n x: {tickRotate: 55},\n color: {legend: true},\n marks: [\n Plot.ruleY([0]),\n Plot.lineY(count_timeseries, {x: \"date_pub\", y: \"n\", stroke: \"country\", z: \"country\", tip: true})\n ]\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1156,"value":"SELECT COUNT(date_pub) as n, date_pub, country \n FROM full_data WHERE (date_pub > '2016-1-1' AND country IN ('Canada', 'France', 'Japan', 'Nigeria')) \n GROUP BY (date_pub, country) \n ORDER BY date_pub, country","pinned":false,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"count_timeseries","database":"var:full_data"},{"id":1479,"value":"### Co-occurence matrix\n_https://observablehq.com/@observablehq/arsc-part-1-co-occurrence-sql-query_","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1471,"value":"cooccurrences = db.query(`\nwith tmp as (\n SELECT * FROM ${form.select_m}\n)\n\nSELECT \n a.doc_top as source, \n b.doc_top as target,\n count(*)::INT as count\nFROM tmp a\nJOIN tmp b \n ON a.review_id = b.review_id\n WHERE a.doc_top < b.doc_top\nGROUP BY a.doc_top, b.doc_top\n HAVING count > ${range} \nORDER BY count DESC\n`)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1617,"value":"viewof range = Inputs.range([0, 100], {label: \"Threshold\", step: 1, value: 20})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1819,"value":"viewof range1 = Inputs.range([0, 130], {label: \"Top N\", step: 1, value: 20})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1491,"value":"Plot.plot({\n color: { scheme: \"greens\", legend: true }, \n marks: [\n Plot.cell(cooccurrences, {\n x: \"source\",\n y: \"target\",\n fill: \"count\",\n sort: { y: \"y\", x: \"x\", limit: range1 },\n title: d => `Topic ${d.source} (${top_description[d.source]}) -> ${d.target} (${top_description[d.target]}) (co-occurred: ${d.count} times)`,\n tip: true\n })\n ],\n x: { tickRotate: 90 },\n width: range1 > 50 ? 1200 : 600,\n height: range1 > 50 ? 1200 : 600,\n marginLeft: 50,\n marginRight: 50,\n marginBottom: 50,\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1780,"value":"#### BETA table","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1784,"value":"-- downsampled country data index\nSELECT * FROM beta WHERE model_index = 19","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"complaints (1).db"},{"id":1788,"value":"-- downsampled country data index\nSELECT model_index FROM metadata \n WHERE use_ner = 1 AND use_translation = 0 AND model_path = 'paraphrase-multilingual-MiniLM-L12-v2'","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"complaints (1).db"},{"id":1840,"value":"### `paraphrase_multilingual_MiniLM_L12_v2_True_False` Beta table \n_Most representative words of topic_","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1958,"value":"viewof select1 = Inputs.select(_.range(0,130), {label: \"Select topic\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1847,"value":"SELECT word, score, topic FROM \"paraphrase_beta_table\" WHERE topic IN (${select1})","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"beta_table","database":"paraphrase_beta_table.parquet"},{"id":163,"value":"#### Raw data (each row is a complaint)","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":763,"value":"SELECT * EXCLUDE (reviewer) FROM tripadvisor_reviews_multi_downsampled_trans","pinned":false,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"tripadvisor_reviews_multi_downsampled_trans.parquet"},{"id":1227,"value":"### Relevant links\n * [top2vec](https://github.com/ddangelov/Top2Vec#pretrained) is the most basic way to understand topic modeling on embedding spaces.\n * [Illustrated transformer](https://jalammar.github.io/illustrated-transformer/). Nice intro to transformer.\n * [A network approach to topic models](https://www.science.org/doi/10.1126/sciadv.aaq1360). LDA makes bad assumptions about language, i.e. he doesn't even encode ZIPF law into its assumption (psst people who create Latent Dirichlet Allocation know that and went to do other stuff like the [Dirichlet process](https://en.wikipedia.org/wiki/Dirichlet_process)).\n * [Google's LABSE model](https://ai.googleblog.com/2020/08/language-agnostic-bert-sentence.html)\n * [sbert description](https://www.sbert.net/examples/training/paraphrases/README.html?highlight=sentence%20paraphrase%20multilingual%20minilm%20l12%20v2)\n * [doc2vec](https://medium.com/wisio/a-gentle-introduction-to-doc2vec-db3e8c0cce5e) (if you have time, not necessary)","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":13,"value":"## Appendix","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":156,"value":"#### Data wrangling","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":242,"value":"db.sql`SELECT COUNT(ratings) as tot_N, airline FROM reviews GROUP BY airline`","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":165,"value":"ratings_by_airline = db.query(`\n WITH tmp as (\n SELECT COUNT(ratings) as tot_n, airline FROM reviews GROUP BY airline\n )\n\n SELECT COUNT(ratings) as n, r.airline, tmp.tot_n, r.ratings\n FROM reviews r\n LEFT JOIN tmp\n ON tmp.airline = r.airline\n GROUP BY (r.airline, r.ratings, tmp.tot_n)\n ORDER BY r.airline\n`)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":124,"value":"sent_by_airline = db.query(`SELECT compound, airline FROM reviews`)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":51,"value":"count_lang = db.query(`SELECT COUNT(lang) as n, lang FROM reviews GROUP BY lang`)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":70,"value":"count_airline = db.query(`SELECT COUNT(airline) as n, airline FROM reviews GROUP BY airline`)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":100,"value":"count_country = db.query(`SELECT COUNT(country) as n, country FROM reviews GROUP BY country`)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":7,"value":"$32","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1760,"value":"db.query(\"SELECT * FROM reviews \")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":936,"value":"full_data = db.query(`\nWITH tmp as (\n SELECT review_id, airline, country, ratings, lang, translated_text, title, date_pub\n FROM reviews\n)\n\nSELECT embedding_x, embedding_y, doc_top, reduced_topic, text, airline, country, tmp.review_id, ratings, lang, translated_text, title, date_pub::DATE as date_pub\n FROM ${form.select_m} m \n LEFT JOIN tmp\n ON m.review_id = tmp.review_id\n LEFT JOIN reduced_topic r\n ON m.doc_top = r.original_topic \n`)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":153,"value":"#### plotting functions ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":133,"value":"function plot_sent() {\n return Plot.plot({\n marginLeft: 100,\n height: 300,\n y: {label: null},\n x: {label: 'compound sentiment →'},\n marks: [\n Plot.boxX(sent_by_airline, {x: \"compound\", y: \"airline\", fill: 'airline', sort: {y: \"x\"}})\n ]\n}) \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":184,"value":"function plot_ratings() {\n return Plot.plot({\n height: 300,\n // fx: {padding: 0, label: null, tickSize: 6},\n x: {axis: null, paddingOuter: 0.2},\n y: {label: \"% documents with ratings\", grid: true},\n facet: {\n data: ratings_by_airline,\n x: 'ratings',\n },\n marks: [\n Plot.barY(ratings_by_airline, { x: \"airline\", y: d => d.n / d.tot_n * 100, fill: 'airline', tip: true }),\n Plot.ruleY([0])\n ]\n}) \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":36,"value":"function plot_count(x, feature) {\n return Plot.plot({\n height: 300, \n width: 400,\n marginBottom: 60,\n x: {label: \"\", tickRotate: 20},\n y: {label: '↑ n documents'},\n marks: [\n Plot.barY(x, {x: feature, y: \"n\", sort: {x: \"y\", reverse: true, limit: 6}}),\n Plot.ruleY([0])\n ]\n }) \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":299,"value":"pd = plot_ratings()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W33","stdlib":"1","titling":"auto"}} 2e:"$2d:body"