analyze_lessons_step3.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ac47a1a0",
   "metadata": {},
   "source": [
    "## Analyzing CSA's Lessons Learned | Analyse des leçons apprises par l'ASC \n",
    "### Step 3: Topic Modelling | Étape 3 : Modélisation des sujets  \n",
    "This notebook takes the results of step 2 (translated text, sentiment analysis scores) and applies latent Dirichlet allocation (LDA) for topic modelling. \n",
    "This workflow could be adapted to any other spreadsheet or csv. All that is needed as input is a spreadsheet with a column of text in English, French, or both.  \n",
    "\n",
    "Ce cahier reprend les résultats de l'étape 2 (texte traduit, analyse de sentiments) et y applique l'allocation de Dirichlet latente (LDA) pour faire la modélisation des sujets. \n",
    "Ce workflow pourrait être adapté à tout autre tableur ou csv. Tout ce qui est requis c'est un tableur qui contient une colonne de texte en anglais, français, ou les deux langues. \n",
    "\n",
    "Author/Auteur: N Fee, Canadian Space Agency/Agence spatiale canadienne, 2021-06-18 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "5cbbe9d3",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\DevSoftware\\Anaconda38\\lib\\site-packages\\gensim\\similarities\\__init__.py:15: UserWarning: The gensim.similarities.levenshtein submodule is disabled, because the optional Levenshtein package <https://pypi.org/project/python-Levenshtein/> is unavailable. Install Levenhstein (e.g. `pip install python-Levenshtein`) to suppress this warning.\n",
      "  warnings.warn(msg)\n"
     ]
    }
   ],
   "source": [
    "import nltk\n",
    "import pandas as pd \n",
    "\n",
    "#Libraries for topic modelling \n",
    "import gensim.corpora as corpora\n",
    "from gensim.utils import simple_preprocess\n",
    "from gensim.models import CoherenceModel\n",
    "import gensim \n",
    "\n",
    "\n",
    "import spacy #for lemmatization (ie. grouping together inflected forms of a word - studying and studious become study, etc...)  \n",
    "import pyLDAvis.gensim_models # for visualizing the topics\n",
    "import pyLDAvis\n",
    "\n",
    "import operator #useful for dealing with topic tuples\n",
    "import warnings #useful for disabling an annoying deprecation warning \n",
    "\n",
    "warnings.filterwarnings(\"ignore\",category=DeprecationWarning) #otherwise one of the libraries keeps logging errors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "5e395549",
   "metadata": {},
   "outputs": [],
   "source": [
    "infile = \"2_Output/LessonsLearned_step2.xlsx\"\n",
    "outfile = \"2_Output/LessonsLearned_step3.xlsx\"\n",
    "\n",
    "stopwords_en = nltk.corpus.stopwords.words(\"english\") #stopwords are words that should be removed before analysis (generally because they are common - 'the', 'a', etc...)\n",
    "stopwords_fr = nltk.corpus.stopwords.words(\"french\") #same but in french\n",
    "stopwords_bil = stopwords_en + stopwords_fr #same but including stopwords from english and french. This is for analysing the untranslated text\n",
    "\n",
    "newStopWords_en = ['csa','project','projects','also', 'agency', 'space'] #New stopwords relevant for the CSA dataset\n",
    "newStopWords_fr = ['asc','projet','projets', 'cette','agence', 'spatial']\n",
    "\n",
    "#Add the new stopwords to the existing stopwords\n",
    "stopwords_en.extend(newStopWords_en)\n",
    "stopwords_fr.extend(newStopWords_fr)\n",
    "stopwords_bil.extend(newStopWords_en+newStopWords_fr)\n",
    "                     \n",
    "#More user-friendly column names for the results. If you change the number of topics, you'll want to change this as well.                      \n",
    "colnames = ['Topic 1', 'Topic 1 Probability',\n",
    "            'Topic 2', 'Topic 2 Probability',\n",
    "            'Topic 3', 'Topic 3 Probability',\n",
    "            'Topic 4', 'Topic 4 Probability',\n",
    "            'Topic 5', 'Topic 5 Probability',\n",
    "            'Topic 6', 'Topic 6 Probability',\n",
    "            'Top Topic', 'Top Topic Probability']\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "bc332aa4",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Tokenizes the text (ie. each word is an element in a list - this is necessary for analysis)\n",
    "def sentence_to_words(sentences):\n",
    "    for sentence in sentences:\n",
    "        yield(gensim.utils.simple_preprocess(str(sentence), deacc=True))  # deacc=True to remove punctuation\n",
    "        \n",
    "# Remove the stopwords from the tokenized text\n",
    "def remove_stopwords(row,colname,stop_words):\n",
    "    text = str(row[colname])\n",
    "    text_clean = simple_preprocess(text)\n",
    "    return [word for word in text_clean if word not in stop_words]\n",
    "\n",
    "#Lemmatize the tokenized text (ie. grouping together inflected forms of a word - studying and studious become study, etc...)\n",
    "def lemmatization(texts, nlp_lang, allowed_postags=['NOUN','VERB', 'ADJ','ADV']):\n",
    "    text_ls = []\n",
    "    for sent in texts:\n",
    "        doc = nlp_lang(\" \".join(sent)) \n",
    "        text_ls.append([token.lemma_ for token in doc if token.pos_ in allowed_postags])\n",
    "    return text_ls\n",
    "\n",
    "#Create a corpus and dictionary based on the lemmatized text (very simply put, prep the text for the lda model). More info about this here: https://radimrehurek.com/gensim/auto_examples/core/run_core_concepts.html#core-concepts-corpus (external site in english only)   \n",
    "def lemmatized_to_corpus(data_lemmatized):\n",
    "    id2word = corpora.Dictionary(data_lemmatized)\n",
    "    texts = data_lemmatized\n",
    "    corpus = [id2word.doc2bow(text) for text in texts]\n",
    "    return corpus, id2word\n",
    "\n",
    "#Build the latent dirichlet allocation (LDA) model. This is being used for topic modelling. \n",
    "def build_lda(corpus,id2word,ntopics=10):\n",
    "    lda_model = gensim.models.ldamodel.LdaModel(corpus= corpus,\n",
    "                                                   id2word=id2word,\n",
    "                                                   num_topics=ntopics, \n",
    "                                                   random_state=100,\n",
    "                                                   update_every=1,\n",
    "                                                   chunksize=100,\n",
    "                                                   passes=100,\n",
    "                                                   alpha='auto',\n",
    "                                                   per_word_topics=True)\n",
    "    return lda_model\n",
    "\n",
    "#Compute the coherence score. \n",
    "def compute_coherence(ldamodel,data_lemmatized,id2word):\n",
    "    coherence_model_lda = CoherenceModel(model=ldamodel, texts=data_lemmatized, dictionary=id2word, coherence='c_v')\n",
    "    coherence_lda = coherence_model_lda.get_coherence()\n",
    "    return coherence_lda\n",
    "\n",
    "#Compute the perplexity score and print it and the coherence score \n",
    "def print_perplexity_coherence(ldamodel,corpus, title, data_lemmatized, id2word):\n",
    "    coherence_lda = compute_coherence(ldamodel,data_lemmatized, id2word)\n",
    "    print(title)\n",
    "    print('Perplexity:      %6.3f'%ldamodel.log_perplexity(corpus))  # a measure of how good the model is. lower the better.\n",
    "    print('Coherence Score: %6.3f \\n'%coherence_lda)\n",
    "\n",
    "# Based on a list of topics, determine the topic that the text probably falls under\n",
    "def assign_topic(lda_model,row,corpus_col):\n",
    "    topic_list = lda_model.get_document_topics(row[corpus_col], minimum_probability=0, minimum_phi_value=None, per_word_topics=False)    \n",
    "    top_topic = max(topic_list, key=operator.itemgetter(1))\n",
    "    topic_list.append(top_topic)\n",
    "    \n",
    "    topic_list = [(item[0],round(item[1],2)) for item in topic_list] #round the probabilities \n",
    "    topic_list  = [item for t in topic_list for item in t] #list of tuples to a flat list \n",
    "    return topic_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "8c418d46",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(infile)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c110255b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    [honoured, present, state, canadian, sector, r...\n",
       "1    [efforts, soutien, innovation, collaboration, ...\n",
       "2    [several, differing, programs, services, csin,...\n",
       "3    [encourager, lancement, nouvelles, entreprises...\n",
       "4    [research, development, expenditures, totalled...\n",
       "5    [plan, proposes, commitment, fund, initial, th...\n",
       "6    [réseau, innovation, canadien, risc, fournira,...\n",
       "7    [better, reflect, current, best, practices, ma...\n",
       "8    [plan, opérationnel, décrit, détails, personne...\n",
       "9    [gouvernement, canada, appuie, depuis, longtem...\n",
       "Name: lesson_clean_bil, dtype: object"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Remove stopwords\n",
    "df['lesson_clean_en'] = df.apply(lambda row: list(remove_stopwords(row,'Lessons EN',stopwords_en)), axis=1)\n",
    "df['lesson_clean_fr'] = df.apply(lambda row: list(remove_stopwords(row,'Lessons FR',stopwords_fr)), axis=1)\n",
    "df['lesson_clean_bil'] = df.apply(lambda row: list(remove_stopwords(row,'Lesson Learned',stopwords_bil)), axis=1)\n",
    "df['lesson_clean_bil'][:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "07550525",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    [honour, preser, state, canadian, report, cove...\n",
       "1    [soutien, innovation, collaboration, manifeste...\n",
       "2    [several, differ, program, service, csin, offe...\n",
       "3    [encourager, lancement, nouveau, entreprise, s...\n",
       "4    [development, expenditur, total, organization,...\n",
       "5    [plan, propose, commitment, fund, initial, yea...\n",
       "6    [réseau, innovation, canadien, risc, fournir, ...\n",
       "7    [well, reflect, current, good, practic, matter...\n",
       "8    [plan, opérationnel, décrire, détail, personne...\n",
       "9    [gouvernement, canader, appuie, longtemp, init...\n",
       "Name: lemmatized_bil, dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Initialize spacy 'en' and 'fr' models. You only need to do this once. \n",
    "#python -m spacy download fr_core_news_sm\n",
    "#python -m spacy download en_core_web_sm\n",
    "\n",
    "\n",
    "nlp_en = spacy.load(\"en_core_web_sm\")\n",
    "nlp_fr = spacy.load(\"fr_core_news_sm\") \n",
    "\n",
    "\n",
    "# Do lemmatization keeping only noun, adj, vb, adv\n",
    "df['lemmatized_en'] = lemmatization(df['lesson_clean_en'], nlp_en)\n",
    "df['lemmatized_fr'] = lemmatization(df['lesson_clean_fr'], nlp_fr)\n",
    "\n",
    "# Some fancy footwork to lemmatize the bilingual text\n",
    "df['lemmatized_bil'] = lemmatization(df['lesson_clean_bil'], nlp_en)\n",
    "df['lemmatized_bil'] = lemmatization(df['lemmatized_bil'], nlp_fr)\n",
    "\n",
    "df['lemmatized_bil'][:10]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "43ee1efb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    [(0, 2), (1, 1), (2, 1), (3, 1), (4, 2), (5, 1...\n",
       "1    [(3, 1), (30, 1), (31, 1), (32, 1), (33, 1), (...\n",
       "2    [(11, 1), (77, 1), (78, 1), (79, 1), (80, 1), ...\n",
       "3    [(49, 1), (56, 1), (57, 1), (67, 2), (71, 1), ...\n",
       "4    [(0, 1), (20, 3), (26, 1), (91, 1), (122, 1), ...\n",
       "5    [(39, 1), (59, 1), (79, 1), (83, 1), (90, 1), ...\n",
       "6    [(16, 1), (37, 1), (50, 1), (64, 1), (66, 2), ...\n",
       "7    [(0, 1), (4, 1), (24, 1), (126, 1), (142, 1), ...\n",
       "8    [(49, 1), (55, 1), (57, 1), (59, 2), (64, 2), ...\n",
       "9    [(38, 1), (47, 1), (50, 1), (67, 2), (71, 1), ...\n",
       "Name: corpus_bil, dtype: object"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create corpora based on the lemmatized text (ie. a vector where each word is described by an ID and by the frequency it appears in the text)\n",
    "df['corpus_en'],id2word_en = lemmatized_to_corpus(df['lemmatized_en'])\n",
    "df['corpus_fr'],id2word_fr = lemmatized_to_corpus(df['lemmatized_fr'])\n",
    "df['corpus_bil'],id2word_bil = lemmatized_to_corpus(df['lemmatized_bil'])\n",
    "df['corpus_bil'][:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "f96b3ebb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ENGLISH\n",
      "Perplexity:      -6.147\n",
      "Coherence Score:  0.429 \n",
      "\n",
      "FRENCH\n",
      "Perplexity:      -6.233\n",
      "Coherence Score:  0.455 \n",
      "\n",
      "BILINGUAL\n",
      "Perplexity:      -6.314\n",
      "Coherence Score:  0.487 \n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Build the topic models\n",
    "lda_model_en = build_lda(df['corpus_en'], id2word_en, ntopics = 6)\n",
    "lda_model_fr = build_lda(df['corpus_fr'], id2word_fr, ntopics = 6)\n",
    "lda_model_bil = build_lda(df['corpus_bil'], id2word_bil, ntopics = 6)\n",
    "\n",
    "# Determine how successful the topic models are (ideally we want low perplexity, high coherence)\n",
    "print_perplexity_coherence(lda_model_en,df['corpus_en'],'ENGLISH', df['lemmatized_en'], id2word_en)\n",
    "print_perplexity_coherence(lda_model_fr,df['corpus_fr'],'FRENCH', df['lemmatized_fr'], id2word_fr)\n",
    "print_perplexity_coherence(lda_model_bil,df['corpus_bil'],'BILINGUAL', df['lemmatized_bil'], id2word_bil)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "b369d019",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ENGLISH Topics\n",
      "[(0, '0.029*\"year\" + 0.025*\"increase\" + 0.020*\"new\" + 0.019*\"canadian\" + 0.019*\"innovation\" + 0.019*\"support\" + 0.015*\"revenue\" + 0.015*\"growth\" + 0.015*\"provide\" + 0.015*\"network\"'), (1, '0.039*\"export\" + 0.027*\"europe\" + 0.027*\"state\" + 0.014*\"market\" + 0.014*\"increase\" + 0.014*\"large\" + 0.014*\"continue\" + 0.014*\"organization\" + 0.014*\"year\" + 0.014*\"row\"'), (2, '0.028*\"sector\" + 0.026*\"organization\" + 0.017*\"year\" + 0.016*\"expenditure\" + 0.016*\"activity\" + 0.016*\"research\" + 0.016*\"canada\" + 0.012*\"support\" + 0.012*\"canadian\" + 0.011*\"patent\"'), (3, '0.020*\"service\" + 0.015*\"activity\" + 0.015*\"study\" + 0.015*\"hqp\" + 0.015*\"research\" + 0.015*\"sector\" + 0.015*\"canadian\" + 0.015*\"member\" + 0.015*\"program\" + 0.010*\"report\"'), (4, '0.033*\"sector\" + 0.025*\"year\" + 0.017*\"business\" + 0.017*\"access\" + 0.017*\"program\" + 0.017*\"facilitate\" + 0.017*\"report\" + 0.009*\"generally\" + 0.009*\"order\" + 0.009*\"measure\"'), (5, '0.018*\"sector\" + 0.018*\"canadian\" + 0.012*\"datum\" + 0.012*\"risc\" + 0.012*\"downstream\" + 0.012*\"node\" + 0.012*\"goal\" + 0.012*\"network\" + 0.012*\"operation\" + 0.012*\"kernel\"')]\n",
      "\n",
      "FRENCH Topics\n",
      "[(0, '0.016*\"méthodologie\" + 0.016*\"soutien\" + 0.016*\"secteur\" + 0.016*\"réseau\" + 0.016*\"fournir\" + 0.016*\"nouveau\" + 0.016*\"canadien\" + 0.009*\"cohérence\" + 0.009*\"faire\" + 0.009*\"donnée\"'), (1, '0.042*\"année\" + 0.028*\"spatial\" + 0.019*\"dernier\" + 0.015*\"rapport\" + 0.015*\"innovation\" + 0.015*\"secteur\" + 0.015*\"exportation\" + 0.015*\"canadien\" + 0.015*\"passer\" + 0.010*\"canada\"'), (2, '0.022*\"spatial\" + 0.022*\"canadien\" + 0.022*\"secteur\" + 0.015*\"information\" + 0.015*\"organisation\" + 0.015*\"activité\" + 0.015*\"rapport\" + 0.015*\"employer\" + 0.008*\"domaine\" + 0.008*\"demande\"'), (3, '0.022*\"service\" + 0.022*\"programme\" + 0.022*\"réseau\" + 0.022*\"objectif\" + 0.017*\"membre\" + 0.017*\"existant\" + 0.017*\"plan\" + 0.017*\"pouvoir\" + 0.012*\"csin\" + 0.012*\"tirer\"'), (4, '0.032*\"secteur\" + 0.019*\"spatial\" + 0.019*\"activité\" + 0.016*\"recherche\" + 0.016*\"canadien\" + 0.010*\"étranger\" + 0.010*\"étude\" + 0.010*\"aval\" + 0.010*\"dépense\" + 0.010*\"obtenir\"'), (5, '0.023*\"rapport\" + 0.016*\"pouvoir\" + 0.016*\"plus\" + 0.016*\"présent\" + 0.016*\"employer\" + 0.016*\"utiliser\" + 0.016*\"phq\" + 0.016*\"fin\" + 0.009*\"ingénieur\" + 0.009*\"inclure\"')]\n",
      "\n",
      "BILINGUAL Topics\n",
      "[(0, '0.020*\"recherche\" + 0.020*\"source\" + 0.014*\"université\" + 0.014*\"européen\" + 0.014*\"réseau\" + 0.014*\"activité\" + 0.014*\"fondre\" + 0.014*\"spatial\" + 0.014*\"étranger\" + 0.014*\"soutien\"'), (1, '0.019*\"objectif\" + 0.013*\"note\" + 0.013*\"nœud\" + 0.013*\"fonctionnement\" + 0.013*\"position\" + 0.013*\"hqp\" + 0.013*\"réseau\" + 0.013*\"noyau\" + 0.013*\"plan\" + 0.013*\"personnel\"'), (2, '0.022*\"donné\" + 0.022*\"secteur\" + 0.022*\"canadien\" + 0.015*\"aval\" + 0.015*\"spatial\" + 0.015*\"référence\" + 0.015*\"activité\" + 0.008*\"habituellement\" + 0.008*\"hautement\" + 0.008*\"important\"'), (3, '0.027*\"secteur\" + 0.022*\"innovation\" + 0.022*\"spatial\" + 0.016*\"économique\" + 0.011*\"soutien\" + 0.011*\"faciliter\" + 0.011*\"effort\" + 0.011*\"infrastructur\" + 0.011*\"nouveau\" + 0.011*\"plan\"'), (4, '0.040*\"year\" + 0.023*\"report\" + 0.018*\"canadian\" + 0.018*\"service\" + 0.018*\"program\" + 0.014*\"member\" + 0.014*\"revenir\" + 0.014*\"organization\" + 0.014*\"activity\" + 0.014*\"sector\"'), (5, '0.015*\"total\" + 0.015*\"secteur\" + 0.015*\"organization\" + 0.015*\"expenditur\" + 0.010*\"canader\" + 0.010*\"segment\" + 0.010*\"étude\" + 0.010*\"lorsqu\" + 0.010*\"invention\" + 0.010*\"organisme\"')]\n"
     ]
    }
   ],
   "source": [
    "print('ENGLISH Topics')\n",
    "print(lda_model_en.print_topics())\n",
    "\n",
    "print('\\nFRENCH Topics')\n",
    "print(lda_model_fr.print_topics())\n",
    "\n",
    "print('\\nBILINGUAL Topics')\n",
    "print(lda_model_bil.print_topics())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "7a97cf07",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Visualize the topics\n",
    "pyLDAvis.enable_notebook()\n",
    "vis_en = pyLDAvis.gensim_models.prepare(lda_model_en, df['corpus_en'], id2word_en, sort_topics = False)\n",
    "pyLDAvis.save_html(vis_en, '2_Output/LessonsLearned_topics_EN.html')\n",
    "\n",
    "vis_fr = pyLDAvis.gensim_models.prepare(lda_model_fr, df['corpus_fr'], id2word_fr,sort_topics = False)\n",
    "pyLDAvis.save_html(vis_fr, '2_Output/LessonsLearned_topics_FR.html')\n",
    "\n",
    "vis_bil = pyLDAvis.gensim_models.prepare(lda_model_bil, df['corpus_bil'], id2word_bil,sort_topics = False)\n",
    "pyLDAvis.save_html(vis_bil, '2_Output/LessonsLearned_topics_BIL.html')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "d8044247",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Topic 1</th>\n",
       "      <th>Topic 1 Probability</th>\n",
       "      <th>Topic 2</th>\n",
       "      <th>Topic 2 Probability</th>\n",
       "      <th>Topic 3</th>\n",
       "      <th>Topic 3 Probability</th>\n",
       "      <th>Topic 4</th>\n",
       "      <th>Topic 4 Probability</th>\n",
       "      <th>Topic 5</th>\n",
       "      <th>Topic 5 Probability</th>\n",
       "      <th>Topic 6</th>\n",
       "      <th>Topic 6 Probability</th>\n",
       "      <th>Top Topic</th>\n",
       "      <th>Top Topic Probability</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0.81</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.19</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Topic 1  Topic 1 Probability  Topic 2  Topic 2 Probability  Topic 3  \\\n",
       "0        0                 0.00        1                  0.0        2   \n",
       "1        0                 0.81        1                  0.0        2   \n",
       "2        0                 0.00        1                  0.0        2   \n",
       "3        0                 0.00        1                  0.0        2   \n",
       "4        0                 0.00        1                  0.0        2   \n",
       "5        0                 0.00        1                  0.0        2   \n",
       "6        0                 1.00        1                  0.0        2   \n",
       "7        0                 1.00        1                  0.0        2   \n",
       "8        0                 0.00        1                  0.0        2   \n",
       "9        0                 0.00        1                  0.0        2   \n",
       "\n",
       "   Topic 3 Probability  Topic 4  Topic 4 Probability  Topic 5  \\\n",
       "0                 1.00        3                  0.0        4   \n",
       "1                 0.19        3                  0.0        4   \n",
       "2                 0.00        3                  1.0        4   \n",
       "3                 0.00        3                  0.0        4   \n",
       "4                 1.00        3                  0.0        4   \n",
       "5                 1.00        3                  0.0        4   \n",
       "6                 0.00        3                  0.0        4   \n",
       "7                 0.00        3                  0.0        4   \n",
       "8                 0.00        3                  0.0        4   \n",
       "9                 1.00        3                  0.0        4   \n",
       "\n",
       "   Topic 5 Probability  Topic 6  Topic 6 Probability  Top Topic  \\\n",
       "0                  0.0        5                  0.0          2   \n",
       "1                  0.0        5                  0.0          0   \n",
       "2                  0.0        5                  0.0          3   \n",
       "3                  1.0        5                  0.0          4   \n",
       "4                  0.0        5                  0.0          2   \n",
       "5                  0.0        5                  0.0          2   \n",
       "6                  0.0        5                  0.0          0   \n",
       "7                  0.0        5                  0.0          0   \n",
       "8                  0.0        5                  1.0          5   \n",
       "9                  0.0        5                  0.0          2   \n",
       "\n",
       "   Top Topic Probability  \n",
       "0                   1.00  \n",
       "1                   0.81  \n",
       "2                   1.00  \n",
       "3                   1.00  \n",
       "4                   1.00  \n",
       "5                   1.00  \n",
       "6                   1.00  \n",
       "7                   1.00  \n",
       "8                   1.00  \n",
       "9                   1.00  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Assign topics to each text based on the trained model\n",
    "topics = pd.DataFrame(df.apply(lambda row: assign_topic(lda_model_en,row,'corpus_en'), axis=1), columns = ['topics'])\n",
    "\n",
    "# Useful for formatting \n",
    "topics_df=  pd.DataFrame(topics['topics'].to_list(), columns=colnames)\n",
    "\n",
    "topics_df[:10]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "c89e000f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Lesson Learned</th>\n",
       "      <th>Language</th>\n",
       "      <th>Lessons EN</th>\n",
       "      <th>Lessons FR</th>\n",
       "      <th>Negative Sentiment</th>\n",
       "      <th>Neutral Sentiment</th>\n",
       "      <th>Positive Sentiment</th>\n",
       "      <th>Compound Sentiment Score</th>\n",
       "      <th>lesson_clean_en</th>\n",
       "      <th>...</th>\n",
       "      <th>Topic 3</th>\n",
       "      <th>Topic 3 Probability</th>\n",
       "      <th>Topic 4</th>\n",
       "      <th>Topic 4 Probability</th>\n",
       "      <th>Topic 5</th>\n",
       "      <th>Topic 5 Probability</th>\n",
       "      <th>Topic 6</th>\n",
       "      <th>Topic 6 Probability</th>\n",
       "      <th>Top Topic</th>\n",
       "      <th>Top Topic Probability</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>I am honoured to present the State of the Cana...</td>\n",
       "      <td>en</td>\n",
       "      <td>I am honoured to present the State of the Cana...</td>\n",
       "      <td>J'ai l'honneur de présenter le rapport sur l'é...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.932</td>\n",
       "      <td>0.068</td>\n",
       "      <td>0.6369</td>\n",
       "      <td>[honoured, present, state, canadian, sector, r...</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Les efforts de soutien à l’innovation et de co...</td>\n",
       "      <td>fr</td>\n",
       "      <td>Efforts to support innovation and collaboratio...</td>\n",
       "      <td>Les efforts de soutien à l’innovation et de co...</td>\n",
       "      <td>0.013</td>\n",
       "      <td>0.822</td>\n",
       "      <td>0.164</td>\n",
       "      <td>0.9184</td>\n",
       "      <td>[efforts, support, innovation, collaboration, ...</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>0.19</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>There are several differing programs and servi...</td>\n",
       "      <td>en</td>\n",
       "      <td>There are several differing programs and servi...</td>\n",
       "      <td>Il existe plusieurs programmes et services dif...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.950</td>\n",
       "      <td>0.050</td>\n",
       "      <td>0.5095</td>\n",
       "      <td>[several, differing, programs, services, csin,...</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Encourager le lancement de nouvelles entrepris...</td>\n",
       "      <td>fr</td>\n",
       "      <td>Encourage the launch of new businesses in the ...</td>\n",
       "      <td>Encourager le lancement de nouvelles entrepris...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.860</td>\n",
       "      <td>0.140</td>\n",
       "      <td>0.8225</td>\n",
       "      <td>[encourage, launch, new, businesses, sector, d...</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Research and development (R&amp;D) expenditures to...</td>\n",
       "      <td>en</td>\n",
       "      <td>Research and development (R&amp;D) expenditures to...</td>\n",
       "      <td>Les dépenses de recherche et développement (R&amp;...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.922</td>\n",
       "      <td>0.078</td>\n",
       "      <td>0.6705</td>\n",
       "      <td>[research, development, expenditures, totalled...</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>The plan proposes a commitment to fund the ini...</td>\n",
       "      <td>en</td>\n",
       "      <td>The plan proposes a commitment to fund the ini...</td>\n",
       "      <td>Le plan propose un engagement à financer les t...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.858</td>\n",
       "      <td>0.142</td>\n",
       "      <td>0.7003</td>\n",
       "      <td>[plan, proposes, commitment, fund, initial, th...</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>Le Réseau d’innovation spatial canadien (RISC)...</td>\n",
       "      <td>fr</td>\n",
       "      <td>The Canadian Space Innovation Network (RISC) w...</td>\n",
       "      <td>Le Réseau d’innovation spatial canadien (RISC)...</td>\n",
       "      <td>0.032</td>\n",
       "      <td>0.698</td>\n",
       "      <td>0.270</td>\n",
       "      <td>0.9072</td>\n",
       "      <td>[canadian, innovation, network, risc, provide,...</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>To better reflect the current best practices a...</td>\n",
       "      <td>en</td>\n",
       "      <td>To better reflect the current best practices a...</td>\n",
       "      <td>Afin de mieux refléter les meilleures pratique...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.820</td>\n",
       "      <td>0.180</td>\n",
       "      <td>0.8934</td>\n",
       "      <td>[better, reflect, current, best, practices, ma...</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>Le plan opérationnel décrit les détails sur le...</td>\n",
       "      <td>fr</td>\n",
       "      <td>The operational plan describes the details of ...</td>\n",
       "      <td>Le plan opérationnel décrit les détails sur le...</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.977</td>\n",
       "      <td>0.023</td>\n",
       "      <td>0.3818</td>\n",
       "      <td>[operational, plan, describes, details, person...</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>Le gouvernement du Canada appuie depuis longte...</td>\n",
       "      <td>fr</td>\n",
       "      <td>The Government of Canada has a long history of...</td>\n",
       "      <td>Le gouvernement du Canada appuie depuis longte...</td>\n",
       "      <td>0.040</td>\n",
       "      <td>0.734</td>\n",
       "      <td>0.226</td>\n",
       "      <td>0.8934</td>\n",
       "      <td>[government, canada, long, history, supporting...</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0                                     Lesson Learned Language  \\\n",
       "0           0  I am honoured to present the State of the Cana...       en   \n",
       "1           1  Les efforts de soutien à l’innovation et de co...       fr   \n",
       "2           2  There are several differing programs and servi...       en   \n",
       "3           3  Encourager le lancement de nouvelles entrepris...       fr   \n",
       "4           4  Research and development (R&D) expenditures to...       en   \n",
       "5           5  The plan proposes a commitment to fund the ini...       en   \n",
       "6           6  Le Réseau d’innovation spatial canadien (RISC)...       fr   \n",
       "7           7  To better reflect the current best practices a...       en   \n",
       "8           8  Le plan opérationnel décrit les détails sur le...       fr   \n",
       "9           9  Le gouvernement du Canada appuie depuis longte...       fr   \n",
       "\n",
       "                                          Lessons EN  \\\n",
       "0  I am honoured to present the State of the Cana...   \n",
       "1  Efforts to support innovation and collaboratio...   \n",
       "2  There are several differing programs and servi...   \n",
       "3  Encourage the launch of new businesses in the ...   \n",
       "4  Research and development (R&D) expenditures to...   \n",
       "5  The plan proposes a commitment to fund the ini...   \n",
       "6  The Canadian Space Innovation Network (RISC) w...   \n",
       "7  To better reflect the current best practices a...   \n",
       "8  The operational plan describes the details of ...   \n",
       "9  The Government of Canada has a long history of...   \n",
       "\n",
       "                                          Lessons FR  Negative Sentiment  \\\n",
       "0  J'ai l'honneur de présenter le rapport sur l'é...               0.000   \n",
       "1  Les efforts de soutien à l’innovation et de co...               0.013   \n",
       "2  Il existe plusieurs programmes et services dif...               0.000   \n",
       "3  Encourager le lancement de nouvelles entrepris...               0.000   \n",
       "4  Les dépenses de recherche et développement (R&...               0.000   \n",
       "5  Le plan propose un engagement à financer les t...               0.000   \n",
       "6  Le Réseau d’innovation spatial canadien (RISC)...               0.032   \n",
       "7  Afin de mieux refléter les meilleures pratique...               0.000   \n",
       "8  Le plan opérationnel décrit les détails sur le...               0.000   \n",
       "9  Le gouvernement du Canada appuie depuis longte...               0.040   \n",
       "\n",
       "   Neutral Sentiment  Positive Sentiment  Compound Sentiment Score  \\\n",
       "0              0.932               0.068                    0.6369   \n",
       "1              0.822               0.164                    0.9184   \n",
       "2              0.950               0.050                    0.5095   \n",
       "3              0.860               0.140                    0.8225   \n",
       "4              0.922               0.078                    0.6705   \n",
       "5              0.858               0.142                    0.7003   \n",
       "6              0.698               0.270                    0.9072   \n",
       "7              0.820               0.180                    0.8934   \n",
       "8              0.977               0.023                    0.3818   \n",
       "9              0.734               0.226                    0.8934   \n",
       "\n",
       "                                     lesson_clean_en  ... Topic 3  \\\n",
       "0  [honoured, present, state, canadian, sector, r...  ...       2   \n",
       "1  [efforts, support, innovation, collaboration, ...  ...       2   \n",
       "2  [several, differing, programs, services, csin,...  ...       2   \n",
       "3  [encourage, launch, new, businesses, sector, d...  ...       2   \n",
       "4  [research, development, expenditures, totalled...  ...       2   \n",
       "5  [plan, proposes, commitment, fund, initial, th...  ...       2   \n",
       "6  [canadian, innovation, network, risc, provide,...  ...       2   \n",
       "7  [better, reflect, current, best, practices, ma...  ...       2   \n",
       "8  [operational, plan, describes, details, person...  ...       2   \n",
       "9  [government, canada, long, history, supporting...  ...       2   \n",
       "\n",
       "  Topic 3 Probability Topic 4 Topic 4 Probability Topic 5 Topic 5 Probability  \\\n",
       "0                1.00       3                 0.0       4                 0.0   \n",
       "1                0.19       3                 0.0       4                 0.0   \n",
       "2                0.00       3                 1.0       4                 0.0   \n",
       "3                0.00       3                 0.0       4                 1.0   \n",
       "4                1.00       3                 0.0       4                 0.0   \n",
       "5                1.00       3                 0.0       4                 0.0   \n",
       "6                0.00       3                 0.0       4                 0.0   \n",
       "7                0.00       3                 0.0       4                 0.0   \n",
       "8                0.00       3                 0.0       4                 0.0   \n",
       "9                1.00       3                 0.0       4                 0.0   \n",
       "\n",
       "  Topic 6 Topic 6 Probability  Top Topic  Top Topic Probability  \n",
       "0       5                 0.0          2                   1.00  \n",
       "1       5                 0.0          0                   0.81  \n",
       "2       5                 0.0          3                   1.00  \n",
       "3       5                 0.0          4                   1.00  \n",
       "4       5                 0.0          2                   1.00  \n",
       "5       5                 0.0          2                   1.00  \n",
       "6       5                 0.0          0                   1.00  \n",
       "7       5                 0.0          0                   1.00  \n",
       "8       5                 1.0          5                   1.00  \n",
       "9       5                 0.0          2                   1.00  \n",
       "\n",
       "[10 rows x 32 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a new dataframe that merges the initial dataframe with the new topics dataframe\n",
    "df1 = pd.concat([df,topics_df], axis =1)\n",
    "df1[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "e9439397",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save the results\n",
    "df1.to_excel(outfile,index=False, encoding=\"utf-8\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "82ecba96",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}