NLP with Excel and Python

How can Excel help in developing an NLP application? By providing its flexible and simple interface to visualize each step, from pre-processing to the final model!


The functionality shown requires a premium licence. See Licensing

Tokenizing Text and Creating a Word Counter

In this tutorial we will start exploring NLP with Python and Excel using nltk webtext corpora on wine reviews. This data is an extract of wine reviews from the

We will build a simple tokenizer and word frequency counter.


All the Python code and Excel files shown are available from github in the xlslim-code-samples repo.

Below we explore how to:

Get and Clean Text Data

We will use a dataset that comes with the nltk library. If you have not yet done so, install the nltk library, import it, and then download the resources using the download() method. We then load raw data from wine.txt and store it as list of sentences by splitting on new line. Since the raw data contains the number of stars attached to the review in the same sentence, we access it by splitting the sentences on space.

get_data() method is implemented and registered in Excel as module.

import re
from collections import Counter
from typing import Optional
import pandas as pd
import nltk
from nltk.tokenize import word_tokenize
from nltk.corpus import webtext"webtext")"punkt)'stopwords')

stopwords = set(nltk.corpus.stopwords.words('english'))
additional_stopwords = {"a", "i", "yet", "if", "bit"}

def get_data() ->pd.DataFrame:

        # Make sure to run if you have not done so already
        raw_sentences = re.split("\n", webtext.raw("wine.txt"))

        df = pd.DataFrame(data=raw_sentences, columns=["reviews"])
        df['stars'] = df['reviews'].apply(lambda x: x.rsplit(" ")[-1])

        return df

In Excel you would register the module with this method and call it as shown in the image below:

Get data from nltk wine corpora

After inspecting the data in Excel, we decide to write a method to clean it. This method can accept the dataframe from the Excel handle, and calls get_data() if no arguments are passed.


We make some assumptions on how to handle reviews with several “*”s in the brackets.

def clean_data(df:pd.DataFrame=None) -> pd.DataFrame:

        if df is None:
                df = get_data()

        # After inspecting data in Excel we decide to clean it as following:

        # Make reviews lowercase
        df['reviews'] = df['reviews'].apply(lambda x: x.lower())

        # Remove the part of review extracted as stars from the reviews text
        df['reviews'] = df['reviews'].apply(lambda x: x.replace(x.rsplit(" ")[-1], ""))

        # Clean up stars column of any captured words
        df['stars'] = df['stars'].apply(lambda x: ''.join([c for c in x if c in ['*','(',')']]))

        # Remove records with no stars
        df['valid'] = df['stars'].apply(lambda x: True if x.find("*")>-1 else False)

        df = df.loc[df['valid']]

        # Remove records where review is less than 10 chars long
        df = df.loc[df['reviews'].str.len()>10]

        # Map stars to the number
        df['stars_num'] = df['stars'].apply(lambda x: len(x)-3+0.5 if x.find("(")>-1 else len(x))

        return df[['reviews','stars','stars_num']]

To access this method in Excel, save the Python file and re-run the command to register the module. Then, call it and view the handle it returns using ViewPyObject()

Clean the data

Tokenize Reviews Text

Using Excel is easy to inspect the output of clean_data() method. We are satisfied that the data is now ready to be parsed into tokens. In addition to parsing sentences, we will also remove punctuation and stop-words. Many stop-words are provided by the nltk library, but we add a few more. Since lists cannot be displayed in the dataframe returned to Excel, we return its string representation. From Excel, working with this string has the same effect as working with a list.


If the dataframe contains values with list datatypes, these will not be accessible in Excel. Cast list to strings to access their values in Excel.

def tokenize(df:pd.DataFrame=None) ->pd.DataFrame:

        if df is None:
                df = clean_data(get_data())

        # Add a column with tokenized reviews where we remove punctuations
        # Also remove stop words that are defined globally
        # Because we cannot display a list in a dataframe we return tokens as string
        df['tokens'] =  df['reviews'].apply(lambda x: ", ".join([w for w in word_tokenize(x) if w.isalpha()
                                                                                                   and w not in stopwords]))

        return df

After saving the Python module, we can re-register it in Excel and access the output of this method from the returned handle:

Tokenize the data

Build a Word Frequency Counter

We are now ready to build a word counter. Let’s write a method to accept a dataframe and an optional parameter for the number of stars to filter the reviews on. The default stars can be set to 5. The method returns a dictionary, which is conveniently saved in two Excel columns.

def get_word_freq(df: pd.DataFrame, stars_filter: Optional[int] = 5) -> dict:

        # Filter the dataframe on the stars ensuring we have passed a good value
        if stars_filter in Counter(df['stars_num']).keys():
                df_filtered = df.loc[df['stars_num']==stars_filter]

                cnt = Counter()
                df_filtered['tokens'].apply(lambda x: cnt.update(x.split(", ")))

                return dict(cnt.most_common())
                return dict()


Note that since we pass a handle to get_word_freq() method, we need to call it from Excel as get_word_freq_from_handle() passing the handle to the tokenized dataframe as the first argument.

Word frequency counter

Let’s for fun build a histogram chart from the dictionary. Note how the words distribution changes for various number of stars: higher rated wines tend to be described as complex, fine (chocolate and smoky for 6-star ranking). Lower rated wines tend to be described as quite good fruity wines.

Wine ranked 5 stars Wine ranked 2 stars