Excel has long been a go-to tool for data analysts to crunch numbers and perform data analysis. Traditionally, its ability to handle text data, particularly for advanced analysis tasks like sentiment analysis, was seen as limited. Analysts seeking meaningful insights from text often resorted to more specialized tools or cumbersome workarounds.

However, Excel’s data analysis capabilities are rapidly evolving with the integration of Python—a programming language celebrated for its extensive libraries that support data science and natural language processing (NLP). This integration significantly expands Excel’s functionality, making sophisticated text analysis accessible to a broader audience.

Additionally, the introduction of Copilot in Excel marks a significant advancement in text data processing. Copilot employs advanced machine learning algorithms to function as a smart assistant within Excel, capable of automating repetitive tasks, crafting complex data transformations, and delivering insights more efficiently than traditional methods.

This post explores how Python and Copilot now enable advanced text analysis in Excel. We will discuss everything from basic text preparation to complex NLP techniques such as sentiment analysis and predictive analytics. With these tools, Excel is well-equipped to handle detailed NLP tasks.

You can follow along by downloading the exercise file below:

This well-known dataset comprises 50,000 movie reviews, each labeled with either positive or negative sentiment. To begin analyzing this data, please start the Advanced Analysis session by following the steps detailed in a previous blog post:

As noted in that blog post, the outputs provided by Copilot and Advanced Analysis may vary, potentially leading to different outcomes with each attempt. For instance, I obtained a set of data visualizations built with Python code, as seen below:

Let’s explore each visualization and what it reveals about the data. You might also consider using Copilot for assistance here.

The first chart illustrates the distribution of reviews classified as either positive or negative. This visualization is crucial as it provides a quick view of the overall sentiment balance within the reviews, allowing analysts to discern which sentiment prevails. Such information is essential for understanding general audience reception and can be particularly valuable when building predictive models, as many models perform better with an equal number of records in each category.

The second chart shows the distribution of review lengths, measured by the number of words per review. This chart is insightful as it indicates the typical length of reviews, reflecting the depth of analysis usually provided by reviewers. This information could aid in assessing the comprehensiveness of reviews and might be used to filter out very short, potentially less informative reviews.

The third visualization, a word cloud, displays the most frequent words found in positive reviews, with larger sizes denoting higher frequency. While this highlights common themes or terms in positive feedback, helping to understand which aspects of movies are most appreciated, the visualization has several drawbacks. It lacks quantitative precision, as it does not provide exact counts or an easy way to compare the frequency of different terms. Furthermore, words in a word cloud are stripped of context, and common but uninformative words can dominate. The size of words might also lead to misleading interpretations of their significance without considering the context in which they are used.

To address some of these issues, I’m going to attempt to reduce the noise and explore differences in word choice between positive and negative sentiments by asking Copilot to generate the Python code for the following query:

“What are the top 15 most common words for each sentiment?”

Copilot will generate the Python code, and it appears to return the results as a tuple containing DataFrames. If you’re not familiar with this data structure, that’s fine, but understanding it will help clarify why this data isn’t directly visible in your workbook.

If you find yourself in a situation where you can’t directly see the results, requesting a visualization is a great strategy. Visualizations ensure that you can clearly observe the outcomes produced by Copilot. For instance, we can use a visualization to effectively display the top 15 words by sentiment from each review, making it easier to interpret the data. This approach not only helps in visual comprehension but also enhances the presentation and accessibility of the analysis.

You’ll notice that while common positive words like “good” and “great” appear in positive reviews, there are also many neutral words such as “film,” “film,” and “movie.” Interestingly, even negative reviews frequently use the word “good.” This highlights the complexity and nuance of language, demonstrating how challenging it can be to work with text data. The presence of typically positive words in negative reviews can reflect the subtleties in how people express mixed or nuanced sentiments.

Given these complexities, text data analysis requires sophisticated approaches to accurately capture and interpret sentiments. This is a prime example of how advanced text analytics can reveal the nuanced use of language, ironically through the very tools designed for such analysis like Copilot.

Now, let’s take this further by building a predictive model. I will ask Copilot to help us create a model that predicts the sentiment of a review based solely on its text. This step will allow us to apply what we’ve learned about text data and explore machine learning techniques to handle sentiment analysis more effectively.

Copilot provides data, once again as a tuple, but this time it gets printed directly. It’s a logistic regression model, and we are told that it can predict the sentiment of a review with about 88% accuracy. Interesting! However, it’s crucial for you to validate and sanity-check these results. You can even enlist Copilot’s help for this. For instance, I just asked Copilot to explain some of the assumptions required for using logistic regression to classify text sentiment. It provided valuable insights, and I could ask it to start verifying these assumptions. For example, it checks for balanced samples and even calculates the frequencies by group in the worksheet, allowing you to easily confirm these details.

The ability to analyze text data truly opens up a new frontier in Excel, but remember, this is advanced territory. It’s important to understand the pros and cons of the methods and techniques used with Python and Copilot for sentiment analysis.

Do you have any questions about text analytics with Python for Copilot in Excel or about the Advanced Analysis feature more generally? Let me know in the comments.

The post Advanced analysis with Python in Copilot: How to work with text data first appeared on Stringfest Analytics.