Pyvot for Excel

  • Article

I’m thrilled to see the availability of Pyvot, a python package for manipulating tabular data in excel. This is part of the Python Tools for Visual Studio (PTVS) ecosystem.

Check out the codeplex site at https://pytools.codeplex.com/wikipage?title=Pyvot or the tutorial on python.org.

Excel does expose an object model through COM, but it’s tricky to use.  Pyvot provides a very simple python programming experience that focuses on your data instead of Excel COM object trivia. Here are some of my favorite examples:

  • Easy to send python data into excel, manipulate it in excel, and then send it back to python.
  • if you ask for a column in Excel’s object model, it will give you back the entire Excel column, including the one million empty cells. Wheras Pyvot will just give you back the data you used.
  • Pyvot will recognize column header names from tables.
  • Pyvot makes it easy to compute new columns and add them to your table.
  • Pyvot makes it easy to connect to an existing excel workbook, even if the workbook has not even been saved to a file. (This involved scanning down the running object table, and doing smart name matching). This allows you to use excel as a scratchpad for python.
  • Pyvot works naturally with Excel’s existing auto-filters. This enables a great scenario where you can start with data in python, send it to excel and manipulate it with excel auto filters (sort it, remove bad values, etc), and then pull the cleaned data back into python.

Some other FAQs:

  1. What can’t Pyvot do? Pyvot is really focused on tabular data. Excel becomes a Datatable viewer for Python. However Pyvot is not intended to be a full excel automation solution.
  2. How does Pyvot compare to VBA? a) Pyvot is just Python and so you can use vast existing Python libraries. b) Also, VBA is embedded in a single excel workbook and is hard to share across workbooks. Pyvot is about real Python files that live outside of the workbook and can be shared and managed under source control.  c) VBA uses the excel object model, whereas Pyvot provides a much simpler experience for tabular data.
  3. How does Pyvot compare to an Excel-addin? a) Pyvot runs entirely out-of-process, so you don’t need to worry about it crashing Excel on you.  b) Excel-addins, like VBA, use the excel object model. c) Excel addins need to be installed. Pyvot is just loose python files that don’t interfere with your excel installation.

Anyway, if you need to excel goodness, especially filters, check out Pyvot and PTVS.