Reading Excel Spreadsheets with Python, Flask, and Openpyxl

Reading Excel Spreadsheets with Python, Flask, and Openpyxl

Data stored in Excel spreadsheets can be hard to read with anything other than Excel and it’s especially tough to compare two specific datasets within all that data. One possible solution is Python. It can do the dirty work of finding the information for us while also being pretty fun.

In this post we will read NBA statistics from a Microsoft Excel sheet using the Openpyxl library. How will we know which statistics to look for and return? Text a Twilio phone number two players and a type of basketball statistic (like total points or three-point-shot-percentage) and then the SMS response will look up the statistics of the corresponding players like so:

Want to skip the tutorial and jump right into the code?  No problem, head over to the complete code.

Otherwise, let’s get started.

Getting the Data

This post uses data for each player from the 2016 season on the NBA website here. To get the data I specified which season, season type (playoffs versus regular season), quantifying data type (game average versus total for the season), which dates to look at, and the stats this post uses like age, games played, wins, losses, minutes, points, field goal percentage, and three-point shot percentage, among others. You can see the data in full here, and export it as an Excel file like this.

Setup your Developer Environment

Before we dig into the code and Openpyxl, make sure your Python and Flask development environment is setup.  If you’re new to Python and Flask, this handy guide is a great place to get started. If you’re already familiar with Flask, go ahead and stand up a new empty Flask application.

Your Flask app will need to be visible from the internet so Twilio can send requests to it. Ngrok lets us do this so install it if you haven’t already. Once that’s done, run the following command in your terminal in the directory you’ll put your code in.

This gives us a publicly-accessible URL to the Flask app so we can configure our Twilio phone number.

You’ll also need a Twilio phone number to send and receive SMS messages. If you need to get one you can do that here, and let’s make sure it’s configured as shown in this following gif.

Parsing Data with Openpyxl

Once you have your environment set up and have acquired a Twilio phone number you can start building the app by installing Openpyxl, an open source Python library that reads and writes Microsoft Excel .xlsx files.

Type the following into your terminal in your project directory.

Open up a new file and save it as main.py. At the very top, include the following imports.

Let’s use two separate lists to store the data we read from the files: one of players and one of their corresponding statistics we want to search (ie. games played, wins, losses, minutes, points, field goal percentage, etc.) These will be used later on to make a dictionary where the keys are the players’ names and the value is their corresponding statistics. Now let’s create a higher-order function called parse_data_into_dict in main.py, and put in the following code.  This code maps the statistics we’re interested in to different columns of the Excel sheet, represented by letters in stat_dict:

Next, let’s fill these lists with the data in the spreadsheet. Start by loading the Excel file using the load_workbook function and reading the existing worksheet. Our parse_data_into_dict function should now look like this:

More complex apps and data may have different worksheets within a single workbook. With the data we have, one could be Regular Season while another could be for the Playoffs. We just want the worksheet at index zero since we only have one worksheet, 

After we’ve loaded the Excel worksheet, let’s iterate through each cell in the worksheet. Each column of the worksheet is represented by a letter (which is why we made the dictionary above so that the values match the columns in the Excel sheets). Knowing this, let’s add on to our parse_data_into_dict function.

The “A” column contains the players’ names so each player in that column is added to the list_of_players list. Then, using the stat_dict list, we loop through the columns which contain the other stats we are interested in and add those to the separate list_of_stats list.

These two lists are then zipped together into one dictionary with players as keys and the corresponding statistic numbers as values. This dictionary will be returned in a send_sms function which we will now make.

Building the Flask App and Sending SMS

This send_sms function provides the heart of the code: it’s the route for the Flask app and checks if the body of the incoming SMS is in our dictionary and sends an outbound SMS accordingly.

Let’s break this down. request.form['Body'].lower() looks at the input SMS and converts the Body to lowercase so it’s easier to compare. Then, we break it up by whitespace and add each piece to a string array. If that array has a length of five (which is what we expect because input should be first and last names of two players followed by a statistic), then we save those two players and the statistic as variables.

Next, we call parse_data_into_dict and use the dictionary it returns to check that it contains the the two players and statistic the message asks for. If it does, we check if the data of one player is greater than the other. Depending on that, a different message is returned. If one or both of the players are not in the dictionary, we return an error message.

Run the following command on the command line to run our Flask app.

python main.py

Now try out the app.  Text your Twilio number two players (with first and last names) and a statistic like “pts.” It could look something like this.

Conclusion

Wow! You just used Openpyxl to read an Excel spreadsheet. What’s next? You can use Openpyxl for financial, baseball, or any sort of data. Here are some more Openpyxl resources and tutorials you may find interesting that go even more in depth.

1. Openpyxl docs
2. pythonexcel.com tutorial
3. tutsplus  

The completed code is on GitHub. Questions or comments on the data or code used? Find me online here:

• Email: lsiegle@twilio.com
• Twitter: @lizziepika
• Github: elizabethsiegle