This post reviews the 5 most popular Python spreadsheets, their features, and costs. Before jumping to the list we cover some background on the concept, the definition of a Python spreadsheets and the criteria to consider when selecting a Python spreadsheet. Continue reading below or jump straight to one of the sections in the table of contents below.
- Background
- What are Python spreadsheets?
- Python packages
- Criteria when choosing a Python spreadsheet
- Top 5 Python spreadsheets
- Conclusion
Background
Python has become the analytical programming language of choice for data analysis and is reported to be used by over 10M people worldwide. The most popular analytical tool, measured by quantity of users, is the spreadsheet and as Python has grown in popularity, it was inevitable the concept of a Python spreadsheet would arise. Created in 2001, PyExcelerator was the first Python package for interacting with spreadsheets but it is no longer maintained and new libraries have become more popular.
The preference for a Python spreadsheet is largely due to the complimentary benefits of Python and a spreadsheet. Python is great for analysis of big data sets, automating processes, connecting to hosted data sets, and complex analyses. Spreadsheets on the other hand, generally do not share the same strengths as Python and are more useful for viewing and visualizing data, simple computations, and sharing with team members.
The two most widely used spreadsheet applications are Microsoft Excel and Google Sheets. Excel now supports Python but very recently only supported a programming language called Visual Basic Analysis (VBA) and Google Sheets supports a javascript based appscript. While VBA and appscript extend the capabilities of the two popular spreadsheet applications, neither language is popular for analytical work. In contrast, Python has a robust open source ecosystem of libraries, like Pandas, Numpy, and Scipy, that make complicated analyses easy. The Python community would also prefer to use the language they are familiar with rather than learn something new and Python has shown to be easy to learn for those who aren't already familiar with it.
What are Python spreadsheets?
A real spreadsheet is one that accepts normal spreadsheet functions and gives users the flexibility to copy/paste, format, visualize, and modify data. There are other applications that provide a tabular view of data sets and allow users to write Python but those are not the focus of this article as they are not real spreadsheets. A Python spreadsheet is an application that combines a full-featured spreadsheet interface with the ability to write and/or execute python code in the spreadsheet. Using this definition of a python spreadsheet, we reviewed the most popular Python spreadsheets: Pyspread, Pyxll, Xlwings, Excel, and Row Zero.
Python Packages
Another class of software not covered here are many of the Python libraries that enable interactions with spreadsheets. The list includes XlsxWriter, pyexcel, pycel, PyExcelerate, openpyxl, and many others.
Criteria when choosing a Python spreadsheet
When considering what Python spreadsheet to use, think through the following questions:
Will the user know how to get Python running on their computer?
Getting Python running on a computer requires downloading and installing the latest version of Python. Then installing a code editor and maintaining versioning across any packages used. The tasks aren't terribly complicated but it is not trivial.
Will the user want more spreadsheet capabilities or Python freedom?
Software engineers may prefer to do more work in Python and only display a summary or results in a spreadsheet whereas others may prefer to use Python to import data or run modeling packages with the rest of the analysis done in a spreadsheet.
How big are the data sets that will be used?
Using Python for data analysis enables a user to load many millions of rows of data whereas Excel and Google Sheets top out at 1M rows and suffer from slow performance well before they hit that total. If millions of rows are needed for the analysis, traditional spreadsheets may struggle to support the data but could still be used to display summary statistics or graphs.
Should the analysis be run on a local computer or in the cloud?
A local spreadsheet application can be accessed offline, may be preferable for certain data security concerns, and is limited to the processing power of the local hardware. A hosted application requires an internet connections, necessitates data being uploaded to servers, and will have access to great processing power.
Will the analysis be shared?
A static view or image of the final results can always be shared in a ppt or an email; however, if the analysis is intended to be shared in an Excel file, some programs require installation of the Python add-in wherever the file will be shared. Hosted solutions make sharing easy as there is often a 'Share' button and the ability to assign read-only or editor permissions.
Top 5 Python Spreadsheets
The following list identifies and reviews the most popular python spreadsheets, which support features to automate tasks, connect to data sets, use python libraries, and other tasks in a spreadsheet.
Pyspread
Pyspread is an open source python spreadsheet. Unlike other products that plug into Excel, Pyspread has its own spreadsheet interface. The product supports python code in cells, accessing python libraries, importing and exporting CSVs, SVGs and PDFs, and charts. To run the application, a you must already have Python installed on your computer, then download Pyspread and run it. Pyspread is free for all users.
Pyxll
Pyxll is an Excel add-in that allows developers to use python in their spreadsheets, build applications, stream data sets, and create models. Pyxll also facilitates running a Jupyter notebook inside an Excel spreadsheet. The application runs locally on a computer and you will need to have Python running on local computer. Python IDEs, like Conda, Pycharm, or VSCode, can be used to write and debug the code that is consumed in the spreadsheet and pyxll provides a wide range of documentation and support resources to help you share workbooks, perform analysis, and debug code. The product comes with a free 30 day trial and is $29/month beyond 30 days.
Xlwings
Xlwings is an open source Python package that comes pre-installed with Anaconda and WinPython. An Excel add-in can also be installed. The package enables automating excel with Python scripts, writing macros in Python, writing user defined functions, and building custom add-ins. To use the free version a Python environment must be maintained on your local computer. A professional plan is available that hosts the Python environment and provides increased functionality, like 1-click installers, web-based add-ins that run everywhere and support for Excel 365 and Google Sheets. The professional plan costs $1490 annually.
Excel
After many years of Python loyalists asking for it, Excel is finally adding Python support in Excel for Windows. Microsoft is gradually rolling out Python support in Excel to Windows users using its beta channel. Once the feature is available, you will be able to type '=PY' in a cell to turn it into a Python cell and then write custom Python functions in the formula bar. There will be an 'Insert Python" button in the menu bar. Cells will contain Excel values or Python objects. Data import with Python will need to process through PowerQuery. A Microsoft Office 365 license is needed at a cost of $6.99/month.
Row Zero
Row Zero is a spreadsheet application that runs in the cloud, supports a billion of rows of data, and lets users write Python directly into the application's code window or spreadsheet cells. No installations are needed on your local computer as Row Zero runs entirely in the cloud. The product can be accessed by logging in on the website. The application consists of a highly performant spreadsheet alongside a Python code window. In the code window, users can write Python functions that are consumed in the spreadsheet, import Python packages, like numpy, Scipy, and Pandas, or connect to hosted data sets and APIs. The product has a free tier with a workbook limit and a paid tier the offers unlimited workbooks.
Conclusion
In summary, Python spreadsheets have emerged as a powerful tool, combining the analytical prowess of Python with the user-friendly interface of traditional spreadsheets. They cater to a diverse range of needs, from handling massive datasets to facilitating complex analyses, while also offering the familiarity and ease of spreadsheet applications. The top Python spreadsheet options, including Pyspread, Pyxll, Xlwings, Excel with Python support, and Row Zero, each provide unique features and cater to different user requirements, whether it be cloud-based solutions, integration with existing spreadsheet software, or standalone applications. This blend of Python's robust capabilities with spreadsheet functionality represents a significant advancement in data analysis, offering both ease of use and advanced analytical power to meet the evolving demands of users in various fields. Our personal preference is Row Zero, a blazingly fast, connected, programmable Python spreadsheet which can be tried for free.