I Quit Coding - How I use ChatGPT instead as Data Analyst
Introduction
Today, I’m diving into an incredible way to work with data using ChatGPT—all without writing a single line of code. Imagine being a director, guiding AI through the process. I’ll show you how to merge multiple Excel files, clean the data, and create a complete Excel report, including a chart, using Python. We’ll also explore building interactive plots and dashboards, and how ChatGPT can assist with SQL and PowerQuery.
A Quick Note Before We Begin
Before we jump in, I want to clarify that the examples I’ll use won’t focus heavily on analyzing data in depth. The sample data is quite simple, primarily because I want to concentrate on the actual prompts rather than the data itself. In my current role, I’m not heavily involved in data analysis; I’m focused on automating internal workflows using Python and VBA.
Generating an Excel Report with ChatGPT
In my directory, I have a folder containing several Excel files with the same format, holding financial data for specific dates. My goal is to consolidate these files, clean the data, analyze it, and create an Excel report complete with a chart. I typically start this process in a Jupyter Notebook, where I can test things out instead of writing all the code at once.
There are two approaches to generating the Excel report. You could write a long prompt detailing your desired outcome, hoping for a script that works right away. However, I prefer breaking the task into smaller subtasks so I can check the code’s correctness along the way.
I provide ChatGPT with context, stating: “As a data analyst, I need your help with Python. Please provide efficient, readable, and Pythonic code that follows best practices.” Then I specify my first task to consolidate the spreadsheets located in a folder called “data.” After submitting my request, I receive Python code along with explanations of what it does.
Crafting an Interactive Plot with ChatGPT
Once I have the consolidated data, I can request additional features, like including the source file name in an extra column. ChatGPT adjusts the code accordingly, and I can quickly run it to see the changes.
Next, I want to create an Excel report using the xlsxwriter package. After specifying the package, ChatGPT generates the code. I ensure I have imported xlsxwriter at the top of my code before executing it. After running the code, I find a new Excel file containing my grouped data and a native Excel chart. Pretty cool, right?
Building a Dashboard using ChatGPT
But why stop there? ChatGPT can also generate an interactive plot using Plotly and save it as an HTML file. I test this code, and upon execution, I find a new HTML file with an interactive chart that includes a title and a legend. I can hover over the bars and toggle different KPIs on and off.
If you want, you can even create an entire dashboard using Streamlit. ChatGPT generates the necessary code for the app, and I can run it locally to display my KPIs in a web app format. Although this might be a bit overkill for simple analysis, it demonstrates the capabilities of ChatGPT.
Refactoring and Cleaning Up the Code
Now, while the code works, it isn’t very clean. I usually ask ChatGPT to refactor it for me, making it more Pythonic and robust by adding functions and using the pathlib module instead of os. After submitting this request, I receive cleaner code that’s easier to debug. All steps are now applied in separate functions, making it more manageable.
PowerQuery Alternative
Instead of Python, you could also use Power Query. I ask ChatGPT to show me the steps to achieve this, and it provides a step-by-step guide. I apply the steps in Power Query: getting data from a folder, combining and transforming it, adding a custom column to calculate profit, and creating a pivot chart. The results match what I achieved with Python.
SQL Query Assistance with ChatGPT
For the final example, I demonstrate how ChatGPT can assist with SQL queries. I describe the tables involved—sales, customers, and products—and request ChatGPT to group the sales by products and sort by total sales amount. After submitting my request, I receive the SQL query, which I execute to see the results grouped by product.
Essential Tips for Using ChatGPT
As I wrap up, let me share some essential tips for using ChatGPT effectively. First, avoid running any code blindly; having a basic understanding of the programming language helps you check the code quickly. Second, don’t share sensitive information like passwords or internal data to keep your information secure.
You may need to refine your prompts, as the first response might not be what you expected. Rewording your requests or breaking down larger tasks into smaller steps can yield better results. This way, you can piece together the different steps and refactor the code later.
Introducing the ChatGPT Excel Add-In
Before you go, I want to mention that I’ve created an add-in that integrates ChatGPT directly into your spreadsheet. This allows you to ask questions from Excel and receive responses right in your workbook. If that sounds interesting, check out the link in the description for the download.
Outro
Thanks for watching! Today, I demonstrated how ChatGPT can streamline data analysis tasks, from generating reports and plots to refactoring code and assisting with SQL queries. Remember, with a little guidance, you can leverage AI to make your data analysis workflow much more efficient.
